MySQL crashing - got signal 10 - changing my.cnf

MySQL & MariaDB Other MySQL® Questions
1

Hi,

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:

1-

[isamchk][myisamchk]key_buffer = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2M

to 2-

[myisamchk]key_buffer = 64Msort_buffer_size = 64Mread_buffer = 16Mwrite_buffer = 16M

and from:
3-

[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

to 4-

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

Thanks,

Tamam

2

Tamam,

For the first sight, your configuration looks good.
However, you should give details about your configuration, server version etc.

And also some history on your moves. Is this just comes from nothing? I mean you made no changes on the server and its just started to work like this?

Thanks,
Istvan

3

Hi Istvan,

Thanks for looking into this.

The server’s is running on a dual 64bit processor and 8GB hardware. MySQL’s version is 5.0.51.

History: The server is running simple applications to support a website. Nothing major happened to any of the applications recently, however, the sys admin mentioned the CPU usage is becoming high. He mentioned there are many log events in the Apache log. I asked him to get me all the logs for the Apache, PHP and MySQL. Apache’s logs seemed normal. All requested were returning 200 and 304 results to different pages. PHP’s didn’t have major errors, except few warnings. MySQL’s, however, had the crashing events several times especially in recent days. (File attached).

I have requested my sys admin to replace my.cnf with the below:

[client]port = 3306socket = /tmp/mysql.sock[mysqld]basedir = /opt/csw/mysql5datadir = /opt/csw/mysql5/varport = 3306socket = /tmp/mysql.sockskip-lockingkey_buffer = 64Mmax_allowed_packet = 16Mtable_cache = 1024sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 2Mread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 64Mlog-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 = 512Minnodb_additional_mem_pool_size = 128Minnodb_log_file_size = 128Minnodb_log_buffer_size = 64Minnodb_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 = 64Msort_buffer_size = 64Mread_buffer = 16Mwrite_buffer = 16M[mysqlhotcopy]interactive-timeout

Couple of the tables maintain about 13,000 records only, and these are the major tables.

Where else could I look for errors? Is there a way to monitor PHP applications and their performance?

Thanks,

UPDATE: After changing my.cnf, some of the tables became corrupted with the 1033 error. Reverted back to the older my.cnf file and the tables are fixed. Back to fresh now.

Tamam