Mysql – Performance issues on a MySQL database with 150 million rows

The table locks on MyISAM can be a killer and migrating to InnoDB is probably one of the best things you can do to continue to improve scalability. Of course, your change to innodb_buffer_pool_size won't impact tables that aren't InnoDB.

One problem, however, is that the version of InnoDB in MySQL 5.0 is still quite primitive compared to later releases, especially when it comes to multicore machines and internal scaling. This is discussed at length in High-Performance MySQL, which was last updated a little over a year ago but is still quite useful even though MySQL 5.6 was discussed in a mix of present- and future-tense due to its release status at the time. I don't have any affiliation with the book or its authors, I just think it's a great reference. If you don't have it, I'd recommend it, because it goes into a lot of detail about what to do, what not to do, and why.

But, if the system under consideration were my system, I'd be planning to upgrade to MySQL 5.5, at a minimum, and possibly MySQL 5.6, because of the significant improvements in the internals of InnoDB as well as elsewhere.

Looking at your config, the query cache is always something to consider when you're looking at performance issues. It could be that a larger cache would help (perhaps 128M to 256M) but it's also possible that a smaller or disabled query cache might be beneficial, since it does represent a global choke-point that every SELECT query has to pass through. The appropriate setting is almost entirely workload-specific.

Nothing jumps out at me in your configuration as being particularly sub-optimal, but I would add that if you've been tempted to use any of the tuning "scripts" you find online... try to resist that temptation. "Tune" only what you have a specific reason to tune, and only one parameter at a time. After you successfully upgrade, try to remove as much of the customized values as possible (except innodb_buffer_pool_size) and let the behavior of the new version with its default values dictate what needs to be tweaked.

The officially recommended path when upgrading across versions is always to do a full mysqldump from the old, and restore on the new installation, though it is possible to do a "binary" upgrade where you simply start the new version code against the old version's datadir and do mysql_upgrade. The official path would be to go from 5.0 to 5.1, and then from 5.1 to 5.5.

http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html

There's a lot to digest but the bottom line is that 5.0 is at end-of-life and hasn't had so much as a bugfix release in over a year... and the newer versions represent a substantially-improved product that should not require major changes in your application.

5000 inserts per minute are about 83 inserts per second. With 5 indexes that's 400 physical rows inserted per second. If the workload was in-memory this would not pose a problem even to the smallest of servers. Even if this was a row-by-row insert using the most inefficient way I can think of. 83 trivial queries per second are just not interesting from a CPU standpoint.

Probably, you are disk-bound. You can verify this by looking at wait stats or STATISTICS IO.

Your queries probably touch a lot of different pages so that the buffer pool does not have space for all of them. This causes frequent page reads and probably random disk writes as well.

Imagine a table where you only physically insert at the end because of an ever-increasing key. The working set would be one page: the last one. This would generate sequential IO as well wen the lazy writer or checkpoint process writes the "end" of the table to disk.

Imagine a table with randomly-placed inserts (classic example: a guid key). Here, all pages are the working set because a random page will be touched for each insert. IOs are random. This is the worst case when it comes to working set.

You're in the middle. Your indexes are of the structure (SomeValue, SequentialDateTime). The first component partially randomizes the sequentiality provided by the second. I guess there are quite a few possible values for "SomeValue" so that you have many randomly-placed insert-points in your indexes.

You say that data is split into 10GB tables per week. That's a good starting point because the working set is now bounded by 10GB (disregarding any reads you might do). With 12GB of server memory it is unlikely, though, that all relevant pages can stay in memory.

If you could reduce the size of the weekly "partitions" or increase server memory by a bit you are probably fine.

I'd expect that inserts at the beginning of the week are faster then at the end. You can test this theory on a dev server by running a benchmark with a certain data size and gradually reducing server memory until you see performance tank.

Now even if all reads and writes fit into memory you might still have random dirty page flushing IO. The only way to get rid of that is to write into co-located positions in your indexes. If you can at all convert your indexes to use (more) sequential keys that would help a lot.

As a quick solution I'd add a buffering layer between the clients and the main table. Maybe accumulate 15min of writes into a staging table and periodically flush it. That takes away the load spikes and uses a more efficient plan to write to the big table.