Johannes, InnoDB asserts because it has waited for a page to be read from a data file for > 600 seconds.
I fixed a bug to 4.0.14 where a big index scan on an InnoDB table could cause this phenomenon. You can try upgrading to 4.0.14. If the problem is in the MERGE table, then InnoDB is innocent. If the computer really badly 'freezes' for 600 seconds, then a file read might take that long. I do not see how queries waiting for table locks on the MERGE table could cause context switches inside mysqld. They should just sit and wait. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ ----- Original Message ----- From: ""Johannes B. Ullrich"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Monday, August 25, 2003 5:51 PM Subject: mysql server crashes - high context switch rate - large 'merge' table > --=-WKgoK98ejo9BZyGYc3N/ > Content-Type: text/plain > Content-Transfer-Encoding: quoted-printable > > > I am having problems with MySQL 4.0.12 on RedHat Advanced Server 2.1 > using a dual Xeon with 8 GByte of RAM. > > I have a database collecting logs. Each day, a new table is created. In > order to allow for queries across more than one day, I use 'MERGE' > tables. Each individual table contains 10-30 Million rows. The merge > table covers 30 days (one month). > > At the start of a new day, the merge table is rebuild using an 'alter > table' statement. However, this statement is sometimes locked for quite > a while, if queries against the table are pending. In this case, > the context switch rate of the server 'explodes' to 100,000 and higher. > The server becomes essentially unresponsive. > > Usually, I can 'fix' things by killing some queries that lock the 'alter > table' querie. However, this causes the server to crash in some cases > (not well reproducable). > > Error message from log: > =20 > > --Thread 17924200 has waited at ../../innobase/btr/../include/btr0btr.ic > line 28 for 1288.00 seconds the semaphore: > S-lock on RW-latch at 4d0b43b4 created in file buf0buf.c line 369 > a writer (thread id 17567808) has reserved it in mode exclusive > number of readers 0, waiters flag 1 > Last time read locked in file ../../innobase/btr/../include/btr0btr.ic > line 28 > Last time write locked in file buf0buf.c line 1332 > InnoDB: Error: semaphore wait has lasted > 600 seconds > InnoDB: We intentionally crash the server, because it appears to be > hung. > 030824 23:48:00 InnoDB: Assertion failure in thread 24583 in file > sync0arr.c line 934 > InnoDB: We intentionally generate a memory trap. > InnoDB: Send a detailed bug report to [EMAIL PROTECTED] > mysqld got signal 11; > This could be because you hit a bug. It is also possible that this > binary > or 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 > diagnose > the problem, but since we have already crashed, something is definitely > wrong > and this may fail. > =20 > key_buffer_size=3D209715200 > read_buffer_size=3D10481664 > =20 > Number of processes running now: 1 > mysqld process hanging, pid 26963 - killed > 030824 23:48:01 mysqld restarted > 030824 23:48:02 InnoDB: Database was not shut down normally. > InnoDB: Starting recovery from log files... > InnoDB: Starting log scan based on checkpoint at > InnoDB: log sequence number 0 1245512626 > InnoDB: Doing recovery: scanned up to log sequence number 0 1245512626 > InnoDB: In a MySQL replication slave the last master binlog file > InnoDB: position 0 200671951, file name Sundown2-bin.019 > InnoDB: Last MySQL binlog file position 0 624229439, file name > ./bob-bin.052 > 030824 23:48:03 InnoDB: Flushing modified pages from the buffer pool... > 030824 23:48:03 InnoDB: Started > /usr/sbin/mysqld: ready for connections. > Version: '4.0.12-log' socket: '/tmp/mysql.sock' port: 3306 > > > > --=-WKgoK98ejo9BZyGYc3N/ > Content-Type: application/pgp-signature; name=signature.asc > Content-Description: This is a digitally signed message part > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.2.1 (GNU/Linux) > > iD8DBQA/Sh7bR1p7hYJvB/wRAkQ8AJ9Xa2z5v+gAzTrQPDeQfuIS+BJQ+ACcDrzf > cmTpPV+y7+j2yGK3kPox+jM= > =a0zb > -----END PGP SIGNATURE----- > > --=-WKgoK98ejo9BZyGYc3N/-- > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
