The system admin mentioned a high CPU usage caused by MySQL. Checking the logs, I found MySQL crashing few times with an interval of every hour with the error below:
socket: ‘/tmp/mysql.sock’ port: 3306 Source distribution100411 12:19:25 - mysqld got signal 10;This could be because you hit a bug. It is also possible that this binaryor one of the libraries it was linked against is corrupt, improperly built,or misconfigured. This error can also be caused by malfunctioning hardware.We will try our best to scrape up some info that will hopefully help diagnosethe problem, but since we have already crashed, something is definitely wrongand this may fail.key_buffer_size=16777216read_buffer_size=258048max_used_connections=1max_connections=100threads_connected=1It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 92783 Kbytes of memoryHope that’s ok; if not, decrease some variables in the equation.100411 12:19:25 mysqld restarted
Checked other posts, and some had changed the values in the my.cnf file from:
[isamchk][myisamchk]key_buffer = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2M
[myisamchk]key_buffer = 64Msort_buffer_size = 64Mread_buffer = 16Mwrite_buffer = 16M
[mysqld]port = 3306socket = /tmp/mysql.sockskip-lockingkey_buffer = 16Mmax_allowed_packet = 1Mtable_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8M
[mysqld]port = 3306socket = /tmp/mysql.sockskip-lockingmax_connections = 800max_connect_errors = 10key_buffer = 36Mmax_allowed_packet = 16Mtable_cache = 1024sort_buffer_size = 512Kthread_cache_size = 286interactive_timeout = 25wait_timeout = 1800connect_timeout = 10net_buffer_length = 8Kjoin_buffer_size = 2Mread_buffer_size = 2Msort_buffer_size = 3Mquery_cache_limit = 1Mquery_cache_size = 16Mquery_cache_type = 1tmp_table_size = 16Mread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 64M
My my.cnf looks like this:
[client]port = 3306socket = /tmp/mysql.sock[mysqld]basedir = /opt/csw/mysql5datadir = /opt/csw/mysql5/varport = 3306socket = /tmp/mysql.sockskip-lockingkey_buffer = 16Mmax_allowed_packet = 1Mtable_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8Mlog-bin=mysql-binserver-id = 1innodb_data_home_dir = /opt/csw/mysql5/var/innodb_data_file_path = ibdata1:10M:autoextendinnodb_log_group_home_dir = /opt/csw/mysql5/var/innodb_log_arch_dir = /opt/csw/mysql5/var/innodb_buffer_pool_size = 16Minnodb_additional_mem_pool_size = 2Minnodb_log_file_size = 5Minnodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 1innodb_lock_wait_timeout = 50[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash[isamchk]key_buffer = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2M[myisamchk]key_buffer = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout
What should I change and up to what values? How would increasing these values affect my server’s performance?
Kindly note, this is a main production server.