473,320 Members | 1,916 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 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
0 3104

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: steve | last post by:
I have an on-going problem that my ".MYI" mysql files get corrupted. I have written php trapping routine to report on the problem to admin. My next step is to initiate "repair table" right after...
0
by: Johannes B. Ullrich | last post by:
--=-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...
0
by: Google Mike | last post by:
This has been discussed before back in 2002, and then in Feb of 2003 when I did a search on Google Groups for MySQL Table Sizes. Back then, people said go here: ...
6
by: Matt Liverance | last post by:
I REALLY dont want to switch to oracle :( but I cant get these tables working any faster. I've got 2 dedicated servers, each with a slave, all run 32gig 15k rpm raid 5 on u320 perc raid...
0
by: Shane Niebergall | last post by:
Hi guys - I have a table that is approaching 4 gigs. I have optimized as much as I can with indexes so that select statements are ok, but updating entries seems to be taking a bit of time. I...
9
by: Dejan | last post by:
Hy, Sorry for my terreble english I have this simple code for deleting rows in mysql table... Everything works fine with it. So, what do i wanna do...: my sql table looks something like...
4
by: whitemoss | last post by:
Hi, I've made some changes to my coding..but unfortunately, there were errors when compiling it..dunno how to solve it..hope anyone can help me...the errors: client.c: In function senddata:...
12
by: mantrid | last post by:
Hello Can anyone point me in the right direction for the way to read a text file a line at a time and separate the fields on that line and use them as data in an INSERT to add a record to a mysql...
3
by: JAYO | last post by:
The MySQL table is located in a Linux Server OS, so I need the information from that table for updating Paradox tables in Windows OS. If a PHP program in the Linux Server can save a file with the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.