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

Johannes B. Ullrich

--=-WKgoK98ejo9BZyG Yc3N/
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.mys ql.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_siz e=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

--=-WKgoK98ejo9BZyG Yc3N/
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+j2yGK 3kPox+jM=
=a0zb
-----END PGP SIGNATURE-----

--=-WKgoK98ejo9BZyG Yc3N/--

Jul 19 '05 #1

Subscribe Reply

3128

Similar topics

1954

reapir mysql table from php

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 the error is trapped. The problem is that then I would have multiple repair table commands initiated from different browser sessions. How can I make sure that only one "repair table" is running at a time. --

PHP

1206

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

by: Johannes B. Ullrich | last post by:

MySQL Database

1987

And once more on MySQL Table Sizes on Linux

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: http://www.mysql.com/doc/en/Table_size.html However, people have posted replies at the bottom of this page saying that this information is severely out of date. Okay, so has anyone taken the latest stable release of their version

MySQL Database

22523

Mysql large table SLOW (3 gig table, 3 days to reindex)

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 cards, dell 2600/4600's with single channel backplanes (new ones will have dual channel) All have 2 gig of ram, but I've never seen mysql use more than 300mb of ram.

MySQL Database

1807

Update performance on large table

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 have 2 questions: 1) I know about the EXPLAIN command for select queries, but is there something like that for updates? I get an error when I try to do "EXPLAIN UPDATE mytable..." Do updates use indexes the same way as selects?

MySQL Database

2789

Mysql table rows delete HELP

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 this: id Name Surname pictr0 picrt1 pictr2 pictr3

PHP

3162

server notify client to update MySQL table

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: client.c:147: error: incompatible implicit declaration of function UpdateStatus client.c:37: error: previous implicit declaration of UpdateStatus was here The code is as below:

C / C++

4410

using php to move rows from text file into mysql table

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 table. Then read the second line in text file and repeat. Thanks for your time Ian

PHP

1804

How to access and modify and add records in MySQL table from a Windows Client?

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 information I need, can I make a PHP program in Windows OS to get the saved file or even better, to access the table in the Linus Server OS? Do I have another options? Thank you very much in advance Best Regards JAYO

PHP

8379

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

8294

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

8816

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

7309

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6162

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

4150

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

4297

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

2719

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

1597

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General