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