My website has several PHP+MySQL applications. I noticed my website has been very unstable after I installed Limesurvey. myisam tables crashed every 3-4 days.
I copied mysql logs here:
110812 04:10:47 mysqld started 110812 4:10:47 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.0.77' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution 110815 23:11:02 mysqld started 110815 23:11:02 [Warning] 'user' entry 'email@example.com' ignored in --skip-name-resolve mode. 110815 23:11:02 [Warning] 'user' entry '@localhost.localdomain' ignored in --skip-name-resolve mode. 110815 23:11:02 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.0.77' socket: '/var/lib/mysql/mysql.sock' port: 0 Source distribution 110815 23:14:10 [ERROR] /usr/libexec/mysqld: Table './DBXXX/TableXXX' is marked as crashed and should be repaired
As you can see, mysqld was started on port 3306 on 08/12/2011 and it had been running for >3.5. days. Then another mysqd was started on port0 on 08/15.
I suspect both MySQL processes access the same database tables and caused the crash. I never set up multiple mysql processes on my webserver and I don't know why another mysqld was started on port 0.
Here's the part of my.cnf file that may be related:
datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql old_passwords=1 skip-locking skip-name-resolve local-infile = 0 skip-networking myisam-recover=backup,force key_buffer_size = 375M max_connections = 1200 thread_cache_size=60 interactive_timeout=1200 wait_timeout=1200 max_connect_errors=50 max_allowed_packet = 64M myisam_sort_buffer_size = 64M query_cache_limit=1M query_cache_size=64M query_cache_type=1 read_buffer_size = 256k read_rnd_buffer_size = 512k sort_buffer_size = 512k join_buffer_size=512K tmp_table_size = 64M # default is 32M = 33554432 max_heap_table_size = 64M #recommend same size as tmp_table_size bulk_insert_buffer_size = 64M #recommend same size as tmp_table_size table_cache=512 thread_concurrency=4 skip-innodb [myisamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysqldump] quick quote-names max_allowed_packet=128M
The myisam setting doesn't seem to be working at all. I had to fix the crashed tables using the Repair DB function in PhpMyadmin after my whole website went down. It would be very helpful if I could make myisam-recover work
another issue I noticed is it takes a very long time to stop mysqld service. and the response always says "Failed" but I can still start mysqld service.
MySQl version is 5.0.77
Looks like I still have a couple of days before this problem happens again. Can anybody please help me?