By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,130 Members | 2,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,130 IT Pros & Developers. It's quick & easy.

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

P: n/a
--=-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 my***@lists.mysql.com
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/--

Jul 19 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.