Announcement

Announcement Module
Collapse
No announcement yet.

MySQL crashing - got signal 10 - changing my.cnf

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL crashing - got signal 10 - changing my.cnf

    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=2580 48max_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

    Comment


    • #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

      Comment

      Working...
      X