Description:
I have a PHP + MySQL system running on a Linux Fedora Core 1 Pentium 4 2.8 HT.
This has happened twice so far: at some point, the system stops responding. When I log in via ssh to the server and run "top", I see that MySQL is using 99% of the CPU.
I then run a "SHOW PROCESSLIST" and see that a SELECT DISTINCT ORDER BY query is stuck with the status "Copying to tmp table". I then kill the query and everything goes back to normal.
This query takes about 3 seconds to execute the first time, and .9 seconds after being cached. But it never hangs, except for these two times so far.
Here is the query:
SELECT DISTINCT devedores1.cpf,
devedores1.id
FROM devedores AS devedores1,
devedores AS devedores2
WHERE devedores1.cpf = devedores2.cpf
AND devedores1.cpf <> '000.000.000-00'
AND devedores1.id <> devedores2.id
ORDER BY devedores1.cpf, devedores1.id
devedores.id is a primary autonumber int(11) key. devedores.cpf is a index varchar(100). There is another index on another varchar(100) field. There are 39 other varchar(100) fields, 1 varchar(8) field and one text field. The table has 31354 rows and 19678 KB.
I saw another bug report about a tmp table being very big when you run SELECT DISTINCT ORDER BY (bug #2933). Maybe this is related...
Here are the results from the SHOW PROCESSLIST:
1st time (from phpmyadmin):
Process list
ID User Host Database Command Time Status SQL-query
Kill 133037 cobrafix cobrafix1:37878 cobrafix Query 1521 Copying to tmp table SELECT DISTINCT devedores1.cpf, devedores1.id
FROM d
Kill 133121 cobrafix cobrafix1:37968 cobrafix Query 1215 Locked UPDATE devedores SET cpf = '699.340.371-49' WHERE id =21023
Kill 133128 cobrafix cobrafix1:37975 cobrafix Query 1180 Locked SELECT devedores
Kill 133132 cobrafix cobrafix1:37979 cobrafix Query 1164 Locked SELECT DISTINCT
Kill 133133 cobrafix cobrafix1:37980 cobrafix Query 1169 Locked SELECT id
FROM devedores
WHERE id =22373
Kill 133134 cobrafix cobrafix1:37981 cobrafix Query 1160 Locked SELECT devedores.nome AS devedores_nome,
Kill 133135 cobrafix cobrafix1:37982 cobrafix Query 1144 Locked SELECT devedores.nome AS devedores_nome,
Kill 133139 cobrafix cobrafix1:37986 cobrafix Query 1117 Locked SELECT devedores.nome AS devedores_nome,
Kill 133141 cobrafix cobrafix1:37988 cobrafix Query 1095 Locked SELECT id
FROM devedores
WHERE id =22371
Kill 133146 cobrafix cobrafix1:37993 cobrafix Query 1084 Locked SELECT devedores.nome AS devedores_nome,
Kill 133147 cobrafix cobrafix1:37994 cobrafix Query 1084 Locked SELECT devedores.nome AS devedores_nome,
Kill 133151 cobrafix cobrafix1:37998 cobrafix Query 1011 Locked SELECT id
FROM devedores
WHERE id =69857
Kill 133152 cobrafix cobrafix1:37999 cobrafix Query 1008 Locked SELECT devedores.nome AS devedores_nome,
Kill 133158 cobrafix cobrafix1:38005 cobrafix Query 881 Locked SELECT id
FROM devedores
WHERE id =22373
Kill 133163 cobrafix cobrafix1:38010 cobrafix Query 865 Locked SELECT devedores.nome AS devedores_nome,
Kill 133166 cobrafix cobrafix1:38013 cobrafix Query 854 Locked SELECT devedores.nome AS devedores_nome,
Kill 133173 cobrafix cobrafix1:38020 cobrafix Query 774 Locked SELECT devedores.id AS devedor, devedores . *
FROM devedores
WHERE devedores.id = '0'
Kill 133179 cobrafix cobrafix1:38026 cobrafix Query 705 Locked SELECT devedores.id AS devedor, devedores . *
FROM devedores
WHERE devedores.id = '0'
Kill 133192 cobrafix cobrafix1:38039 cobrafix Query 652 Locked SELECT devedores.nome AS devedores_nome,
Kill 133195 cobrafix cobrafix1:38042 cobrafix Query 638 Locked SELECT devedores.nome AS devedores_nome,
Kill 133210 cobrafix cobrafix1:38057 cobrafix Query 532 Locked SELECT id
FROM devedores
WHERE id =69838
Kill 133212 cobrafix cobrafix1:38059 cobrafix Query 521 Locked SELECT devedores.nome AS devedores_nome,
Kill 133217 cobrafix cobrafix1:38064 cobrafix Query 512 Locked SELECT devedores.nome AS devedores_nome,
Kill 133220 cobrafix cobrafix1:38067 cobrafix Query 501 Locked SELECT devedores.nome AS devedores_nome,
Kill 133225 cobrafix cobrafix1:38072 cobrafix Query 419 Locked SELECT devedores.id AS devedor, devedores . *
FROM devedores
WHERE devedores.id = '0'
Kill 133230 cobrafix cobrafix1:38077 cobrafix Query 338 Locked SELECT devedores.nome AS devedores_nome,
Kill 133248 italo cobrafix1:38099 test Query 135 Locked SHOW TABLE STATUS FROM `cobrafix`
Kill 133252 cobrafix cobrafix1:38103 cobrafix Query 93 Locked SELECT id
FROM devedores
WHERE id =69857
Kill 133256 cobrafix cobrafix1:38107 cobrafix Query 76 Locked SELECT id
FROM devedores
WHERE id =22373
Kill 133263 italo cobrafix1:38115 mysql Query 0 --- SHOW PROCESSLIST
2nd time (from console):
+-------+----------+-----------------+----------+---------+------+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+----------+-----------------+----------+---------+------+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 43158 | cobrafix | cobrafix1:52043 | cobrafix | Query | 1007 | Copying to tmp table | SELECT DISTINCT devedores1.cpf, devedores1.id FROM devedores AS devedores1, devedores AS devedores2 WHERE devedores1.cpf = devedores2.cpf AND devedores1.cpf <> '000.000.000-00' AND devedores1.id <> devedores2.id ORDER BY devedores1.cpf, devedores1.id |
| 43196 | cobrafix | cobrafix1:52081 | cobrafix | Query | 775 | Waiting for table | DROP TABLE IF EXISTS devedores |
| 43197 | cobrafix | cobrafix1:52082 | cobrafix | Query | 656 | Waiting for table | SELECT id FROM devedores WHERE id = 152615 |
| 43204 | cobrafix | cobrafix1:52089 | cobrafix | Query | 233 | Waiting for table | SELECT id FROM devedores WHERE id = 51727 |
| 43208 | italo | cobrafix1:52093 | cobrafix | Query | 198 | Waiting for table | SHOW TABLE STATUS FROM `cobrafix` |
| 43210 | italo | cobrafix1:52095 | cobrafix | Query | 174 | Waiting for table | SHOW TABLE STATUS FROM `cobrafix` |
| 43216 | italo | cobrafix1:52101 | cobrafix | Query | 58 | Waiting for table | SHOW TABLE STATUS FROM `cobrafix` |
| 43217 | italo | cobrafix1:52102 | NULL | Query | 0 | NULL | show full processlist |
+-------+----------+-----------------+----------+---------+------+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
How to repeat:
Well, the problem is random. Happened twice so far, on different days. The query runs very few times a day on the system.
Description: I have a PHP + MySQL system running on a Linux Fedora Core 1 Pentium 4 2.8 HT. This has happened twice so far: at some point, the system stops responding. When I log in via ssh to the server and run "top", I see that MySQL is using 99% of the CPU. I then run a "SHOW PROCESSLIST" and see that a SELECT DISTINCT ORDER BY query is stuck with the status "Copying to tmp table". I then kill the query and everything goes back to normal. This query takes about 3 seconds to execute the first time, and .9 seconds after being cached. But it never hangs, except for these two times so far. Here is the query: SELECT DISTINCT devedores1.cpf, devedores1.id FROM devedores AS devedores1, devedores AS devedores2 WHERE devedores1.cpf = devedores2.cpf AND devedores1.cpf <> '000.000.000-00' AND devedores1.id <> devedores2.id ORDER BY devedores1.cpf, devedores1.id devedores.id is a primary autonumber int(11) key. devedores.cpf is a index varchar(100). There is another index on another varchar(100) field. There are 39 other varchar(100) fields, 1 varchar(8) field and one text field. The table has 31354 rows and 19678 KB. I saw another bug report about a tmp table being very big when you run SELECT DISTINCT ORDER BY (bug #2933). Maybe this is related... Here are the results from the SHOW PROCESSLIST: 1st time (from phpmyadmin): Process list ID User Host Database Command Time Status SQL-query Kill 133037 cobrafix cobrafix1:37878 cobrafix Query 1521 Copying to tmp table SELECT DISTINCT devedores1.cpf, devedores1.id FROM d Kill 133121 cobrafix cobrafix1:37968 cobrafix Query 1215 Locked UPDATE devedores SET cpf = '699.340.371-49' WHERE id =21023 Kill 133128 cobrafix cobrafix1:37975 cobrafix Query 1180 Locked SELECT devedores Kill 133132 cobrafix cobrafix1:37979 cobrafix Query 1164 Locked SELECT DISTINCT Kill 133133 cobrafix cobrafix1:37980 cobrafix Query 1169 Locked SELECT id FROM devedores WHERE id =22373 Kill 133134 cobrafix cobrafix1:37981 cobrafix Query 1160 Locked SELECT devedores.nome AS devedores_nome, Kill 133135 cobrafix cobrafix1:37982 cobrafix Query 1144 Locked SELECT devedores.nome AS devedores_nome, Kill 133139 cobrafix cobrafix1:37986 cobrafix Query 1117 Locked SELECT devedores.nome AS devedores_nome, Kill 133141 cobrafix cobrafix1:37988 cobrafix Query 1095 Locked SELECT id FROM devedores WHERE id =22371 Kill 133146 cobrafix cobrafix1:37993 cobrafix Query 1084 Locked SELECT devedores.nome AS devedores_nome, Kill 133147 cobrafix cobrafix1:37994 cobrafix Query 1084 Locked SELECT devedores.nome AS devedores_nome, Kill 133151 cobrafix cobrafix1:37998 cobrafix Query 1011 Locked SELECT id FROM devedores WHERE id =69857 Kill 133152 cobrafix cobrafix1:37999 cobrafix Query 1008 Locked SELECT devedores.nome AS devedores_nome, Kill 133158 cobrafix cobrafix1:38005 cobrafix Query 881 Locked SELECT id FROM devedores WHERE id =22373 Kill 133163 cobrafix cobrafix1:38010 cobrafix Query 865 Locked SELECT devedores.nome AS devedores_nome, Kill 133166 cobrafix cobrafix1:38013 cobrafix Query 854 Locked SELECT devedores.nome AS devedores_nome, Kill 133173 cobrafix cobrafix1:38020 cobrafix Query 774 Locked SELECT devedores.id AS devedor, devedores . * FROM devedores WHERE devedores.id = '0' Kill 133179 cobrafix cobrafix1:38026 cobrafix Query 705 Locked SELECT devedores.id AS devedor, devedores . * FROM devedores WHERE devedores.id = '0' Kill 133192 cobrafix cobrafix1:38039 cobrafix Query 652 Locked SELECT devedores.nome AS devedores_nome, Kill 133195 cobrafix cobrafix1:38042 cobrafix Query 638 Locked SELECT devedores.nome AS devedores_nome, Kill 133210 cobrafix cobrafix1:38057 cobrafix Query 532 Locked SELECT id FROM devedores WHERE id =69838 Kill 133212 cobrafix cobrafix1:38059 cobrafix Query 521 Locked SELECT devedores.nome AS devedores_nome, Kill 133217 cobrafix cobrafix1:38064 cobrafix Query 512 Locked SELECT devedores.nome AS devedores_nome, Kill 133220 cobrafix cobrafix1:38067 cobrafix Query 501 Locked SELECT devedores.nome AS devedores_nome, Kill 133225 cobrafix cobrafix1:38072 cobrafix Query 419 Locked SELECT devedores.id AS devedor, devedores . * FROM devedores WHERE devedores.id = '0' Kill 133230 cobrafix cobrafix1:38077 cobrafix Query 338 Locked SELECT devedores.nome AS devedores_nome, Kill 133248 italo cobrafix1:38099 test Query 135 Locked SHOW TABLE STATUS FROM `cobrafix` Kill 133252 cobrafix cobrafix1:38103 cobrafix Query 93 Locked SELECT id FROM devedores WHERE id =69857 Kill 133256 cobrafix cobrafix1:38107 cobrafix Query 76 Locked SELECT id FROM devedores WHERE id =22373 Kill 133263 italo cobrafix1:38115 mysql Query 0 --- SHOW PROCESSLIST 2nd time (from console): +-------+----------+-----------------+----------+---------+------+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+----------+-----------------+----------+---------+------+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 43158 | cobrafix | cobrafix1:52043 | cobrafix | Query | 1007 | Copying to tmp table | SELECT DISTINCT devedores1.cpf, devedores1.id FROM devedores AS devedores1, devedores AS devedores2 WHERE devedores1.cpf = devedores2.cpf AND devedores1.cpf <> '000.000.000-00' AND devedores1.id <> devedores2.id ORDER BY devedores1.cpf, devedores1.id | | 43196 | cobrafix | cobrafix1:52081 | cobrafix | Query | 775 | Waiting for table | DROP TABLE IF EXISTS devedores | | 43197 | cobrafix | cobrafix1:52082 | cobrafix | Query | 656 | Waiting for table | SELECT id FROM devedores WHERE id = 152615 | | 43204 | cobrafix | cobrafix1:52089 | cobrafix | Query | 233 | Waiting for table | SELECT id FROM devedores WHERE id = 51727 | | 43208 | italo | cobrafix1:52093 | cobrafix | Query | 198 | Waiting for table | SHOW TABLE STATUS FROM `cobrafix` | | 43210 | italo | cobrafix1:52095 | cobrafix | Query | 174 | Waiting for table | SHOW TABLE STATUS FROM `cobrafix` | | 43216 | italo | cobrafix1:52101 | cobrafix | Query | 58 | Waiting for table | SHOW TABLE STATUS FROM `cobrafix` | | 43217 | italo | cobrafix1:52102 | NULL | Query | 0 | NULL | show full processlist | +-------+----------+-----------------+----------+---------+------+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 8 rows in set (0.00 sec) How to repeat: Well, the problem is random. Happened twice so far, on different days. The query runs very few times a day on the system.