Monday, January 14, 2008

MAJOR Problems in mysql-5.0.51

mysql-5.0.51 causes huge blocking locks under high load.

It also causes relay-log corruptions. For instance, how can a syntax error make it into replication, the reason is mysql-5.0.51 is truncating the stream.

The only work around is to rebuild the relay log.

The BUG is listed here for replication problems. I suspected IO_CACHE corruption as the cause.



update: this is the cause of the huge blocks


InnoDB: Warning: a long semaphore wait:
--Thread 1173899616 has waited at btr0cur.c line 424 for 292.00 seconds the semaphore:
S-lock on RW-latch at 0x2df5159f58 created in file buf0buf.c line 497
a writer (thread id 1173899616) has reserved it in mode  exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr0cur.c line 424
Last time write locked in file buf0buf.c line 1768
InnoDB: Warning: a long semaphore wait:
--Thread 1172302176 has waited at btr0cur.c line 424 for 295.00 seconds the semaphore:
S-lock on RW-latch at 0x2df50706a8 created in file buf0buf.c line 497
a writer (thread id 1172302176) has reserved it in mode  exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr0cur.c line 424
Last time write locked in file buf0buf.c line 1768
InnoDB: Warning: a long semaphore wait:
--Thread 1182951776 has waited at trx0trx.c line 1627 for 283.00 seconds the semaphore:
Mutex at 0x2a9eac52b8 created file srv0srv.c line 872, lock var 0
waiters flag 0
InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
InnoDB: Pending preads 1, pwrites 0




Additionally mySQL is putting self in swap for a memory config that has not changed from my previous version.

I'm thinking that there is some memory leak in 5.0.51
at

6 comments:

  1. Steffen WeberMon Jan 14, 12:12:00 PM

    Could you please be a little bit more specific about the "huge blocking locks"?

    ReplyDelete
  2. Steffen WeberMon Jan 14, 01:06:00 PM

    So these locking problems seem to be InnoDB-specifc. Thanks!

    ReplyDelete
  3. veganloveburgerWed Jan 16, 01:16:00 PM

    We also experienced the relay log corruptions with high I/O and load, and also that it had the tendency to crash MySQL. One good thing about 5.0.54 is that this bug is fixed:

    http://bugs.mysql.com/bug.php?id=31793

    However, we still have a script to automatically reset replication and start the slave threads anew.

    ReplyDelete
  4. Mark CallaghanFri Jan 25, 12:07:00 AM

    The relay log corruption bug is still open in 5.0.54. See http://bugs.mysql.com/bug.php?id=26489. Most of the time, doing 'stop slave, reset slave, change master to the SQL thread position' is sufficient to fix the corruption.

    I don't understand the blocking problem that you mention. We have intermittent long lock waits on the adaptive hash index latch (the lock holder doesn't release it while doing something that take a long time -- like sorting). This can be fixed by adding calls in a few places to release the latch. Is that your problem?

    I am using 5.0.37 now and hope to upgrade to 5.0.54.

    ReplyDelete
  5. AnonymousSun Feb 03, 03:30:00 PM

    try
    innodb_adaptive_hash_index = 0
    (5.0.52 or higher)
    This should fix the locking issues. The slave / relay log corruption is a different issue, not sure what version the fix is making it into, but likely not in 5.0.54.

    ReplyDelete
  6. AnonymousThu Feb 07, 09:31:00 AM

    Dathan, there is 1 known memory leak, which is to do with index_merge queries. Please check if this bug is relevant: http://bugs.mysql.com/bug.php?id=27732

    ReplyDelete