Mysql primary database usage

Question

Hi,

Q:

I've inherited a system that consists (for simplicity) of 2 application servers that write to a single master database. One application server performs quite a few operations {small amount of time, like milli seconds. } per unit of time. The other application server acts like an API Server, through which clients interact. This "API" server operates on half the tables in the database most of which are not needed by the other application server. However the "API" server does cause the other application server, through its interaction with SQL Server, to lose time and performance.

I wanted to know what would be a good approach in resolving this.

idea's so far

[1] create a second database which will be master-master slaved with current database. Getting http://mysql-mmm.org/ scripts and running then. (concurrency?)

[2] slowly begin moving tables from "master" database into a new "API" database. (lots of legacy code..)

[3] some kind of a SQL priority queue.. (how fault tolerant can this be?)

Answer 1

Step 1 - work out where your bottleneck is

Step 2 - decide where your best return on effort is

If you simply want to make it perform better, then you have to work out where the slow point is. Ideally you would use 3 hosts, one for each application server and one for the database. In this configuration, you should quickly be able to work out if it is the database working the disks hard, or if it's CPU loading, lock contention etc.

Once you know where the bottleneck is, you'll have a much more focussed problem to fix. The options you have suggested may or may not help depending on what the real bottleneck is.