MySQL performance

Question

I have this LAMP application with approximately 900k rows in MySQL, and I'm having some performance issues.

Background. In addition to the LAMP stack, there is also a Java (multi-threaded) process that runs in its own JVM. Thus, together with LAMP and java, they form a complete solution. The java process is responsible for inserts / updates and several selections. These inserts / updates are usually in bulk / batch mode, somewhere between 5-150 lines. Front-end PHP code executes only SELECT.

Problem. PHP / SELECT queries become very slow when starting a java process. When the java process is stopped, SELECT is working fine. I mean the difference in performance. When starting a java process, any action performed on the front side of php leads to 80% CPU usage for the mysqld process.

Any help would be appreciated.

MySQL works with parameters and defaults.

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

We need to know a lot more about the system in order to say whether this is normal or how to solve the problem.

with approximately 900k rows in MySQL

, - , - .

, , , . , . .

.

Answer 2

MySQL? , MyISAM, - , .

: MySQL

:

* 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.

, concurrency MySQL. , , InnoDB, , concurrency . InnoDB . , , , , , , .., .

:

, , . , , . . - . / , exasterbates , MyISAM. , - , .

, , . . . . / , db . , , , , .

InnoDB:

, Java- . ? , , , / .

, :

• , , , ?
• ? , 100 , , ?
• , Java-, ?
• ( ?) IOP, ?