It seems that MySQL 3.23.32 has an internal deadlock problem, causing the database to stop responding. Situation: 6 processes (each on a different system) connect to a mysql database and insert various system data into a set of MyISAM tables. Each process uses the user 'logdaemon' which has insert privilege to the required tables. Data is extacted by php scripts using the user 'logreader', which has select privilege to the required tables. It happens at least once a day that MySQL stops responding. First I did assume that missing locks could cause the problem, so I modified all accesses to the database to use read and write locks on the required tables. This didn't help. If you look at the attached output of the 'show processlist' command the most obvious thing to see is that 2 IDs are in state 'System Lock' on the same table. Killing the threads doesn't help. The thread state changes to 'killed' but that's it. Regular shutdown of MySQL is impossible, all MySQL PIDs must be sent the TERM signal. Note that even as MySQL is compiled with BDB support there are only MyISAM tables. All inserts are "regular" inserts, there is no 'insert delayed'. the amount of inserts/day of all tables of this database is about 10000. The amount of selects is currently much lower (less than 1000/day). Thus concurrent inserts and selects to the same table may easily cause a denial of service condition. The only perhaps non-standard thing I did here was not to set a default database but to address the tables directly (db.table) which may be, ahem, not usual but still is a valid way to access a table. I'm going now to change this, i.e. all connecting processes will set the database and see if the problem persists. Nevertheless this is a somewhat bad situation that should be resolved as soon as possible. As a side effect of this problem I had the database first set to replicate in Robin Round manner between three systems. Unfortunately, when the above situation happens and MySQL must be killed, replication is killed for good, too, as the slave thread of the killed MySQL database will just loop in a 'waiting to reconnect after a failed read' state after MySQL restart. Thus the current replication setup is already that there's just two shadow databases for the master that handles all reads and writes. This leads to an additional thought: What happens to replication after a power outage or a system crash? Yes, I' using a ups but it can happen, that these things fail, causing a home made power outage. It wouldn't be too good if a non-standard shutdown of a slave would mean to restart replication from scratch. Andreas Steinmetz D.O.M. Datenverarbeitung GmbH
Id User Host db Command Time State Info 7 replicate 10.1.1.2 NULL Binlog Dump 78339 Waiting for update NULL 9 replicate 10.1.1.6 NULL Binlog Dump 78339 Waiting for update NULL 187 logdaemon 10.1.1.3 NULL Query 7015 System lock LOCK TABLES syslog.pcp WRITE 188 logdaemon 10.1.1.7 NULL Query 6963 Opening tables LOCK TABLES syslog.pcp WRI TE 189 logdaemon 10.1.1.6 NULL Query 6961 Opening tables LOCK TABLES syslog.pcp WRI TE 190 logdaemon 10.1.1.4 NULL Query 7017 System lock LOCK TABLES syslog.pcp WRITE 191 logdaemon 10.1.1.2 NULL Query 6961 Opening tables LOCK TABLES syslog.pcp WRI TE 192 logdaemon localhost NULL Query 6963 Opening tables LOCK TABLES syslog.pcp WR ITE 246 logreader 10.1.1.3 NULL Query 6465 Opening tables lock tables syslog.ipchain s read 247 logreader 10.1.1.3 NULL Query 6447 Opening tables lock tables syslog.ipchain s read 248 logreader 10.1.1.3 NULL Query 6230 Opening tables lock tables syslog.ipchain s read 249 logreader 10.1.1.3 NULL Query 5651 Opening tables lock tables syslog.ipchain s read 250 logreader 10.1.1.3 NULL Query 5640 Opening tables lock tables syslog.ipchain s read 251 logreader 10.1.1.3 NULL Query 4606 Opening tables lock tables syslog.ipchain s read 252 logreader 10.1.1.3 NULL Query 4493 Opening tables lock tables syslog.ipchain s read 253 logreader 10.1.1.3 NULL Query 3445 Opening tables lock tables syslog.ipchain s read 254 logreader 10.1.1.3 NULL Query 3145 Opening tables lock tables syslog.ipchain s read 255 logreader 10.1.1.3 NULL Query 2329 Opening tables lock tables syslog.ipchain s read 256 logreader 10.1.1.3 NULL Query 1332 Opening tables lock tables syslog.ipchain s read 257 logreader 10.1.1.3 NULL Query 1088 Opening tables lock tables syslog.ipchain s read 258 logreader 10.1.1.3 NULL Query 1087 Opening tables lock tables syslog.ipchain s read 259 logreader 10.1.1.3 NULL Query 823 Opening tables lock tables syslog.ipchains read 260 system localhost mysql Query 384 Opening tables SELECT User, Select_priv FRO M user where User = 'dbmaster' 263 dbmaster localhost NULL Query 0 NULL show processlist
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
