Problematic performance with continuous UPDATE / INSERT in Mysql

Currently we have a database and a script which has 2 update and 1 select, 1 insert.

The problem is we have 20,000 People who run this script every hour. Which cause the mysql to run with 100% cpu.

For the insert, it's for logging, we want to log all the data to our mysql, but as the table scale up, application become slower and slower. We are开发者_JAVA百科 running on InnoDB, but some people say it should be MyISAM. What should we use? In this log table, we do sometimes pull out the log for statistical purpose. 40->50 times a day only.

Our solution is to use Gearman [http://gearman.org/] to delay insert to the database. But how about the update.

We need to update 2 table, 1 from the customer to update the balance(balance = balance -1), and the other is to update the count from another table.

How should we make this faster and more CPU efficient?

Thank you

but as the table scale up, application become slower and slower

This usually means that you're missing an index somewhere.

MyISAM is not good: in addition to being non ACID compliant, it'll lock the whole table to do an insert -- which kills concurrency.

Read the MySQL documentation carefully:

http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html

Especially "innodb_flush_log_at_trx_commit" - http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html

I would stay away from MyISAM as it has concurrency issues when mixing SELECT and INSERT statements. If you can keep your insert tables small enough to stay in memory, they'll go much faster. Batching your updates in a transaction will help them go faster as well. Setting up a test environment and tuning for your actual job is important.

You may also want to look into partitioning to rotate your logs. You'd drop the old partition and create a new one for the current data. This is much faster than than deleting the old rows.