I have a strange problem with MySQL 3.23.30 running on my server.
Configuration:
1 Intel Pentium III
512 meg. RAM
RedHat Linux.
Basically eveithing works fine. Eventually, the system comes to a grinding
halt,
with load average hovering around 20.00.
Here's a processlist :
+------+-------+-----------+------------+---------+------+------------------
----+-----------------------------------------------------------------------
-------------------------------+
| Id | User | Host | db | Command | Time | State
|
Info
+------+-------+-----------+------------+---------+------+------------------
----+-----------------------------------------------------------------------
-------------------------------+
| 2121 | reado | localhost | molotok_bg | Query | 1499 | Copying to tmp
table |
select l.id, l.Name as lName, l.PicPath='', unix_timestamp(l.RegDate) as
timeStart,
l.LastPrice as p |
| 2125 | reado | localhost | molotok_bg | Query | 1496 | Copying to tmp
table |
select l.id, l.Name as lName, l.PicPath='', unix_timestamp(l.RegDate) as
timeStart,
l.LastPrice as p |
| 2262 | reado | localhost | molotok_ro | Query | 0 | cleaning up2
|
select count(id) from forumsOnLots where LotID=43078
| 2325 | reado | localhost | molotok_bg | Query | 1242 | Copying to tmp
table |
select l.id, l.Name as lName, l.PicPath='', unix_timestamp(l.RegDate) as
timeStart,
l.LastPrice as p |
| 2435 | reado | localhost | molotok_bg | Query | 1107 | Copying to tmp
table |
select l.id, l.Name as lName, l.PicPath='', unix_timestamp(l.RegDate) as
timeStart,
l.LastPrice as p |
| 2463 | reado | localhost | molotok_bg | Query | 1071 | Copying to tmp
table |
select l.id, l.Name as lName, l.PicPath='', unix_timestamp(l.RegDate) as
timeStart,
l.LastPrice as p |
| 2665 | reado | localhost | molotok_bg | Query | 884 | Copying to tmp
table |
select l.id, l.Name as lName, l.PicPath='', unix_timestamp(l.RegDate) as
timeStart,
l.LastPrice as p |
| 2911 | reado | localhost | molotok_bg | Query | 591 | Locked
|
LOCK TABLES Auctions WRITE, Lots WRITE, LotsHis WRITE,Auctions_arc WRITE,
Lots_arc
WRITE, LotsHis_ar |
| 3212 | reado | localhost | molotok_bg | Query | 164 | Locked
|
select l.id, l.Name as lName ,l.PicPath='', unix_timestamp(l.RegDate) as
timeStart,
l.LastPrice as p |
| 3216 | reado | localhost | molotok_bg | Query | 162 | Locked
|
select count(*) from Auctions left join catalogs on
catalogs.id=Auctions.catalogID
left join Periods |
| 3217 | reado | localhost | molotok_bg | Query | 161 | Locked
|
select count(*) from Auctions left join catalogs on
catalogs.id=Auctions.catalogID
left join Periods |
| 3222 | reado | localhost | molotok_bg | Query | 158 | Locked
|
select l.Name,l.LastPrice,l.Currency,l.PicPath,
unix_timestamp(l.RegDate),l.StPrice,l.Count, ((unix_ |
| 3227 | reado | localhost | molotok_bg | Query | 154 | Locked
|
select l.id, l.Name as lName ,l.PicPath='', unix_timestamp(l.RegDate) as
timeStart,
l.LastPrice as p |
| 3230 | reado | localhost | molotok_bg | Query | 151 | Locked
|
select l.id, l.Name as lName ,l.PicPath='', unix_timestamp(l.RegDate) as
timeStart,
l.LastPrice as p |
| 3231 | reado | localhost | molotok_bg | Query | 149 | Locked
|
select count(*) from Auctions left join catalogs on
catalogs.id=Auctions.catalogID
left join Periods |
| 3237 | reado | localhost | molotok_bg | Query | 139 | Locked
|
select l.id, l.Name as lName ,l.PicPath='', unix_timestamp(l.RegDate) as
timeStart,
l.LastPrice as p |
| 3240 | reado | localhost | molotok_bg | Query | 137 | Locked
|
select count(*) from Auctions left join catalogs on
catalogs.id=Auctions.catalogID
left join Periods |
| 3241 | reado | localhost | molotok_bg | Query | 137 | Locked
|
select l.id, l.Name as lName ,l.PicPath='', unix_timestamp(l.RegDate) as
timeStart,
l.LastPrice as p |
| 3248 | reado | localhost | molotok_bg | Query | 132 | Locked
|
select l.id, l.Name as lName ,l.PicPath='', unix_timestamp(l.RegDate) as
timeStart,
l.LastPrice as p |
| 3252 | reado | localhost | molotok_bg | Query | 129 | Locked
|
select count(*) from Auctions left join catalogs on
catalogs.id=Auctions.catalogID
left join Periods |
| 3253 | reado | localhost | molotok_bg | Query | 128 | Locked
|
select count(*) from Auctions left join catalogs on
catalogs.id=Auctions.catalogID
left join Periods |
| 3256 | reado | localhost | molotok_bg | Query | 126 | Locked
|
select count(*) from Auctions left join catalogs on
catalogs.id=Auctions.catalogID
left join Periods |
| 3257 | reado | localhost | molotok_bg | Query | 123 | Locked
|
select count(*) from Auctions left join catalogs on
catalogs.id=Auctions.catalogID
left join Periods |
| 3261 | reado | localhost | molotok_bg | Query | 120 | Locked
|
select count(*) from Auctions left join catalogs on
catalogs.id=Auctions.catalogID
left join Periods |
| 3262 | reado | localhost | molotok_bg | Query | 119 | Locked
|
select count(*) from Auctions left join catalogs on
catalogs.id=Auctions.catalogID
left join Periods |
| 3269 | reado | localhost | molotok_bg | Query | 111 | Locked
|
select count(*) from Auctions
left join catalogs on catalogs.id=Auctions.catalogID
left |
| 3273 | reado | localhost | molotok_bg | Query | 109 | Locked
|
select l.id, l.Name as lName ,l.PicPath='', unix_timestamp(l.RegDate) as
timeStart,
l.LastPrice as p |
| 3276 | reado | localhost | molotok_bg | Query | 107 | Locked
|
select count(*),catalogID,catalogs.Name from Auctions left join catalogs on
catalogs.id=Auctions.cat |
| 3292 | reado | localhost | molotok_bg | Query | 91 | Locked
|
select l.id, l.Name as lName ,l.PicPath='', unix_timestamp(l.RegDate) as
timeStart,
l.LastPrice as p |
| 3293 | reado | localhost | molotok_bg | Query | 88 | Locked
|
select UserID from Auctions where LotID=''
| 3295 | reado | localhost | molotok_bg | Query | 82 | Locked
|
select count(*) from Auctions left join catalogs on
catalogs.id=Auctions.catalogID
left join Periods |
| 3298 | reado | localhost | molotok_bg | Query | 78 | Locked
|
select l.id, l.Name as lName ,l.PicPath='', unix_timestamp(l.RegDate) as
timeStart,
l.LastPrice as p |
| 3299 | reado | localhost | molotok_bg | Query | 78 | Locked
|
select l.Name,l.LastPrice,l.Currency,l.PicPath,
unix_timestamp(l.RegDate),l.StPrice,l.Count, ((unix_ |
| 3301 | reado | localhost | molotok_bg | Query | 76 | Locked
|
select l.id, l.Name as lName ,l.PicPath='', unix_timestamp(l.RegDate) as
timeStart,
l.LastPrice as p |
| 3307 | reado | localhost | molotok_bg | Query | 68 | Locked
|
select count(*) from Auctions left join catalogs on
catalogs.id=Auctions.catalogID
left join Periods |
| 3310 | reado | localhost | molotok_bg | Query | 61 | Locked
|
select l.id, l.Name as lName ,l.PicPath='', unix_timestamp(l.RegDate) as
timeStart,
l.LastPrice as p |
| 3312 | reado | localhost | molotok_bg | Query | 58 | Locked
|
select count(*) from Auctions left join catalogs on
catalogs.id=Auctions.catalogID
left join Periods |
| 3314 | reado | localhost | molotok_bg | Query | 57 | Locked
|
select count(*),catalogID,catalogs.Name from Auctions left join catalogs on
catalogs.id=Auctions.cat |
| 3316 | reado | localhost | molotok_bg | Query | 53 | Locked
|
select count(*) from Auctions
left join catalogs on catalogs.id=Auctions.catalogID
left |
| 3317 | reado | localhost | molotok_bg | Query | 53 | Locked
|
select l.id, l.Name, a.id, a.CategorieID, u.Nick, u.EMail, c.Parent, c.id,
u.id from
Lots as l, Auct |
| 3319 | reado | localhost | molotok_ro | Sleep | 49 |
|
| 3321 | reado | localhost | molotok_bg | Query | 47 | Locked
|
select l.id, l.Name as lName ,l.PicPath='', unix_timestamp(l.RegDate) as
timeStart,
l.LastPrice as p |
| 3326 | reado | localhost | molotok_bg | Query | 36 | Locked
|
select count(*) from Auctions left join catalogs on
catalogs.id=Auctions.catalogID
left join Periods |
| 3327 | reado | localhost | molotok_bg | Query | 36 | Locked
|
select count(*) from Auctions left join catalogs on
catalogs.id=Auctions.catalogID
left join Periods |
| 3331 | reado | localhost | molotok_bg | Query | 33 | Locked
|
select count(*) from Auctions, Lots
left join catalogs on catalogs.id=Auctions.catalogID
|
| 3343 | reado | localhost | molotok_bg | Query | 20 | Locked
|
select count(*) from Auctions left join catalogs on
catalogs.id=Auctions.catalogID
left join Periods |
| 3351 | reado | localhost | molotok_bg | Query | 10 | Locked
|
select l.id, l.Name as lName ,l.PicPath='', unix_timestamp(l.RegDate) as
timeStart,
l.LastPrice as p |
| 3361 | nik | localhost | | Query | 1 |
|
show processlist
+------+-------+-----------+------------+---------+------+------------------
----+-----------------------------------------------------------------------
-------------------------------+
Why there is so many threads with "Locked" or "Copying to tmp table" state ?
How to solve this problem ?
______________________________________________
Anton Zabannikh | http://www.securityelf.net
work: (3432)-651811 | Mobile: 8-34348-31924
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
