14 messages in com.mysql.lists.mysqlRe: MySQL locked| From | Sent On | Attachments |
|---|---|---|
| Nam Nguyen | 07 Jul 2000 09:21 | |
| Ed Carp | 07 Jul 2000 09:49 | |
| August Zajonc | 07 Jul 2000 10:07 | |
| Jeremy D. Zawodny | 07 Jul 2000 10:42 | |
| David Hodgkinson | 07 Jul 2000 11:36 | |
| sas...@mysql.com | 07 Jul 2000 13:51 | |
| Steven Roussey | 07 Jul 2000 14:51 | |
| Jerome Abela | 07 Jul 2000 15:01 | |
| sin...@mysql.com | 08 Jul 2000 04:40 | |
| John Zook | 08 Jul 2000 15:54 | |
| indrek siitan | 08 Jul 2000 16:04 | |
| Jerome Abela | 01 Sep 2000 05:30 | |
| Micke Bjork Coolbase | 01 Sep 2000 05:51 | |
| sas...@mysql.com | 04 Sep 2000 10:38 |
| Subject: | Re: MySQL locked |
|---|---|
| From: | Jerome Abela (Jero...@free.fr) |
| Date: | 09/01/2000 05:30:54 AM |
| List: | com.mysql.lists.mysql |
Hi, it's me again with my MySQL completely locked up once or twice a week. But today, it happenned in real time while I was looking at the processlist (instead of in the middle of the night, our watchdog waking me up), so I have more info.
On Sat, Jul 08, 2000 at 02:40:57PM +0300, sin...@mysql.com wrote:
Jerome Abela writes:
For the 2nd time in 3 days, I have the following problem. All the threads are locked, except a DELAYED insert thread, which does not do anything, the CPU is not used:
Lockups are quite rare these days.
What MySQL version are you using, how have you built it, with what compiler, which compiler and configure options, what OS are you running it on, what is it's version and versions of C and thread libs.
I was using 3.22.32 (your RPM), and I upgraded to 3.23.22 (your RPM). The host is a RH6.2, libc is 2.1.2.
Last, but not least, can you forward us the entire output from mysqladmin proc, when lockup repeats.
It happens in 3 steps:
1. Everything is fine, and as most queries are really fast, I don't see anything most of the time in the processlist (and there was not much queries at this moment because the successive locks made the website unavailable these 2 last days): +------+----------------+------+-----------------+-----------------------+ | Id | Command | Time | State | Info | +------+----------------+------+-----------------+-----------------------+ | 49 | Delayed_insert | 256 | Waiting on cond | data | | 1026 | Query | 0 | NULL | show full processlist | | 1067 | Sleep | 4 | | NULL | | 1072 | Sleep | 2 | | NULL | +------+----------------+------+-----------------+-----------------------+
2. At some point, because there are data to write, the DELAYED INSERT thread wakes up, but for some reason, it doesn't get the write lock even when there are no other threads ! +-------+----------------+------+----------------+-----------------------+ | Id | Command | Time | State | Info | +-------+----------------+------+----------------+-----------------------+ | 49 | Delayed_insert | 4935 | upgrading lock | data | | 1026 | Query | 0 | NULL | show full processlist | | 1067 | Sleep | 3 | | NULL | | 18557 | Sleep | 2 | | NULL | +-------+----------------+------+----------------+-----------------------+ This doesn't lock the table yet, because most queries are SELECTs, which continue to run successfully.
3. Later on (about 1 hour later), the fatal event arrives, someone else requests a real write lock: +-------+----------------+------+------------------+--------------------------+ | Id | Command | Time | State | Info | +-------+----------------+------+------------------+--------------------------+ | 49 | Delayed_insert | 8449 | upgrading lock | data | | 1067 | Sleep | 0 | | NULL | | 18557 | Sleep | 190 | | NULL | | 29115 | Sleep | 732 | | NULL | | 33247 | Query | 88 | Sending data | select * from data into o| | 33813 | Sleep | 17 | | NULL | | 33823 | Sleep | 15 | | NULL | | 33824 | Connect | 16 | Reading from net | NULL | | 33825 | Query | 15 | Locked | DELETE LOW_PRIORITY FROM | | 33830 | Query | 14 | Locked | SELECT COUNT(*) FROM data| | 33835 | Query | 12 | Locked | SELECT COUNT(*) FROM data| | 33838 | Query | 11 | Locked | SELECT count(*) FROM data| | 33840 | Query | 11 | Locked | SELECT COUNT(*) FROM data| | 33842 | Query | 11 | Locked | SELECT name,info,start,en| | 33844 | Query | 11 | Locked | SELECT COUNT(*) FROM data| | 33845 | Query | 10 | Locked | SELECT count(*) FROM data| | 33847 | Query | 10 | Locked | SELECT COUNT(*) FROM data| | 33848 | Query | 10 | Locked | SELECT count(*) FROM data| | 33850 | Query | 8 | Locked | SELECT COUNT(*) FROM data| | 33854 | Query | 8 | Locked | SELECT name,info,start,en| | 33857 | Query | 6 | Locked | SELECT name,info,start,en| | 33859 | Query | 0 | NULL | show full processlist | | 33864 | Query | 2 | Locked | SELECT name,info,start,en| | 33865 | Query | 2 | Locked | SELECT name,info,start,en| | 33866 | Query | 2 | Locked | SELECT COUNT(*) FROM data| | 33867 | Query | 1 | Locked | SELECT name,info,start,en| | 33868 | Query | 1 | Locked | SELECT count(*) FROM data| | 33869 | Query | 1 | Locked | SELECT count(*) FROM data| +-------+----------------+------+------------------+--------------------------+ And 1 minute later, the limit of 100 threads is reached, and I have to stop and restart MySQL.
Jerome.