MySQL Performance Issues

Question

Over the past 3 weeks we have been having bottle-necking issues at our MySQL server. I have been using MonYOG to watch the processlist in an attempt to get a grasp on the issues. We are aware that some of the queries and processes we are running in our code is less than optimized, but I am not completely convinced that is the main source of our issues. I feel our server should be able to overcome these issues.

Our tables are a mix of innodb and myISAM. I am by no means a DBA and do not pretend to be one so I am not sure what engine is the best in our current environment. We are pretty read heavy, but mix in a great deal of updates and inserts as well. I was seeing a lot of locked tables which made me think innodb might be better since it does row lock instead of table lock. I converted a few of our tables from MyISAM over to innodb to take advantage of some of the settings that are available. The developers are using a great deal of complex joins as well.

Here is what we are running in our my.cnf:

[mysqld]
datadir=/var/lib/mysql
port=3306
socket=/var/lib/mysql/mysql.sock
user=mysql

key_buffer_size=512M

innodb_file_per_table
innodb_buffer_pool_size=6GB
#the following line is causing some odd errors when doing db dump
#innodb_log_file_size=128M
innodb_log_buffer_size=8M
innodb_additional_mem_pool_size=32M

max_allowed_packet=16M
join_buffer_size=8M
sort_buffer_size=8M
max_connections=500
wait_timeout=500
skip-name-resolve
thread_cache=256
table_cache=256
tmp_table_size=48M
max_heap_table_size=48M

query_cache_size=64M

#logging of slow queries
log-slow-queries=/var/log/mysql-slow-query.log

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
#old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid

I changed the current "innodb_buffer_pool_size" to 6GB last week and that made a huge improvement to the bottle-necking. We have actually only really started seeing issues again today. Traffic has increased this week since we have schools starting and football season starting as well.

I have all kinds of statistical information I can supply you if needed. I am not sure what else to supply at this time.

If I cannot get this figured out I will be forced to call in a consultant. I am hoping someone will be able to give me a hand with some of these settings.

Thanks in advance.

------------------ EDIT--------------------

Here is some additional information requested:

MySQL Version - 5.0.95
OS version - CentOS 5.6 (will be upgrading to 6.4 in the coming weeks)

Server - Dell PowerEdge R710
CPU - Dual Quad Core 2.8GHz
RAM - 12GB
This is a dedicated machine. The only thing running on this box is MySQL

Answer 1

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.