We currently have 1 master db on its own server and slave db on app server.
App executes very frequent but light updates (like increasing counters), and occasional (once in a few minutes) heavy selects (which is the most important part of the app). When app was connected only to master db there were no performance issues. With slave db introduction CPU load avg on app server increased to about 6-10 during that heavy select period (from 3-4 as before).
When server doesn't run those frequent updates it seems like performance for selects stays within the limits. So I have a feeling that those updates is what is causing the performance drop (also these frequent updates are not critical so if slave db doesn't have them in sync with master for some time it would be ok).
What would be a good db replication setup for such kind of app? What are the replication parameters we could tweak?
Thanks.
Run the heavy selects on the master server and use the slave as a backup of the data only. If your master goes down then you can use the slave temporarily until you get the master running again.
A slave (for redundancy) will generally have lower load than its master (no selects, single execution thread), but still has nearly all of the disk write capacity requirements. If your app also needs disk resources, then it will be competing with the slave for it, and that's when your load average goes up.
I'd say get another box for your slave.