469,934 Members | 1,907 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,934 developers. It's quick & easy.

mysql server crashes - high context switch rate - large 'merge' table

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:

--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
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 my***@lists.mysql.com
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this
or one of the libraries it was linked against is corrupt, improperly
or misconfigured. This error can also be caused by malfunctioning
We will try our best to scrape up some info that will hopefully help
the problem, but since we have already crashed, something is definitely
and this may fail.
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
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

Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

Version: GnuPG v1.2.1 (GNU/Linux)



Jul 19 '05 #1
0 2908

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by steve | last post: by
reply views Thread by Google Mike | last post: by
reply views Thread by Shane Niebergall | last post: by
9 posts views Thread by Dejan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.