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 'root@localhost.localdomain' 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?