MySQL performance

Question

I have this LAMP application with about 900k rows in MySQL and I am having some performance issues.

Background - Apart from the LAMP stack , there's also a Java process (multi-threaded) that runs in its own JVM. So together with LAMP & java, they form the complete solution. The java process is responsible for inserts/updates and few selects as well. These inserts/updates are usually in bulk/batch, anywhere between 5-150 rows. The PHP front-end code only does SELECT's.

Issue - the PHP/SELECT queries become very slow when the java process is running. When the java process is stopped, SELECT's perform alright. I mean the performance difference is huge. When the java process is running, any action performed on the php front-end results in 80% and more CPU usage for mysqld process.

Any help would be appreciated.

MySQL is running with default parameters & settings.

Software stack -

• Apache - 2.2.x
• MySQL -5.1.37-1ubuntu5
• PHP - 5.2.10
• Java - 1.6.0_15
• OS - Ubuntu 9.10 (karmic)

Answer 1

What engine are you using for MySQL? The thing to note here is if you're using MyISAM, then you're going to have locking issues due to the table locking that engine uses.

From: MySQL Table Locking

Table locking is also disadvantageous under the following scenario:

* A session issues a SELECT that takes a long time to run.
* Another session then issues an UPDATE on the same table. This session 
  waits until the SELECT is finished.
* Another session issues another SELECT statement on the same table.
  Because UPDATE has higher priority than SELECT, this SELECT waits for the UPDATE to finish, 
  after waiting for the first SELECT to finish.

I won't repeat them here, but the page has some tips on increasing concurrency on a table within MySQL. Obviously, one option would be to change to an engine like InnoDB which has a more complex row locking mechanism that for high concurrency tables can make a huge difference in performance. For more info on InnoDB go here. Prior to changing the engine though it would probably be worth looking at the other tips like making sure your table is indexed properly, etc. as this will increase select and update performance regardless of the storage engine.

Edit based on user comment:

I would say it's one possible solution based on the symptoms you've described, but it may not be the one that will get you where you want to be. It's impossible to say without more information. You could be doing full table scans due to the lack of indexes. This could be causing I/O contention on your disk, which just further exasterbates the table locks used by MyISAM. If this is the case then the root of the cause is the improper indexing and rectifying that would be your best course of action before changing storage engines.

Also, make sure your tables are normalized. This can have profound implications on performance especially on updates. Normalized tables can allow you to update a single row instead of hundreds or thousands in an un-normalized table. This is due to unduplicated values. It can also save huge amounts of I/O on selects as the db can more efficiently cache data blocks. Without knowing the structure of the tables you're working with or the indexes you have present it's difficult to provide you with a more detailed response.

Edit after user attempted using InnoDB:

You mentioned that your Java process is multi-threaded. Have you tried running the process with a single thread? I'm wondering if maybe it's possibly you're sending the same rows to update out to multiple threads and/or the way you're updating across threads is causing locking issues.

Outside of that, I would check the following:

1. Have you checked your explain plans to verify you have reasonable costs and that the query is actually using the indexes you have?
2. Are your tables normalized? More specifically, are you updating 100 rows when you could update a single record if the tables were normalized?
3. Is it possible that you're running out of physical memory when the Java process is running and the machine is busy swapping stuff in and out?
4. Are you flooding your disk (a single disk?) with more IOPs than it can reasonably handle?