Mysql – “Field List” command in thesql processlist

InnoDB for MySQL 5.1 exhibits single-threaded behavior because the number of read and write I/O threads are fixed at 4 each. I was just reminded that versions of MySQL before 5.1.38 has only one thread for read and one for write. Therefore, scaling up hardware will not improve performance at all.

You need to upgrade to MySQL 5.5/5.6 because there are settings that will enable InnoDB to engage multiple CPUs and multiple cores (such as innodb_read_io_threads and innodb_write_io_threads).

• Sep 20, 2012 : Multi cores and MySQL Performance
• Apr 26, 2012 : Is the CPU performance relevant for a database server?
• May 26, 2011 : About single threaded versus multithreaded databases performance

CAVEAT : You must tune MySQL 5.5/5.6 properly because left unconfigured MySQL 5.1 is faster than 5.5/5.6 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)

Give it a Try !!!

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.