for some reason when things get busy, we get a whole ton of locked threads
(see processlist below...), and at some point when there are enough threads,
the whole thing bogs down to a crawl. EVEN after i kill the process that's
holding it all up, it still stays all stuck up. I've seen people conplain
about this, but haven't seen an answer anywhere.
my questions are:
1. under what conditions do threads get 'locked'?
2. the slowdown seems like it may be caused by the server swapping to disk
ie temp files getting created on disk instead of in memory - under what
conditions does this happen?
3. WHY does one process hold all the others up? ie what is the bottleneck
that mysql can only do one of at a time?
4. what is the most likely cause of this?
basically, it seems as though it gets to a certain level of busyness, and
all hell breaks loose & all of a sudden EVERYthing gets slow, and the # of
threads gets up to 400+
the server is NOT out of hard drive space, it's not too busy (load barely
goies above 1 or 2), 1 gig of ram.
--------------------------------------------------------
mysql> show processlist;
+-----+----------------------+-------------------+----------+---------+-----
-+----------------------+---------------------------------------------------
---------------------------------------------------+
| Id | User | Host | db | Command | Time
| State | Info
|
+-----+----------------------+-------------------+----------+---------+-----
-+----------------------+---------------------------------------------------
---------------------------------------------------+
| 41 | world | claw.worldbid.com | worldbid | Query | 0
| NULL | show processlist
|
| 770 | world | claw.worldbid.com | worldbid | Query | 1
| Locked | select O.ID, keywords, deletedate, O.company_name,
'', userID,
LEFT(bid,150), stars, O.country, sub |
| 778 | world | claw.worldbid.com | worldbid | Query | 6
| Copying to tmp table | select O.country, count(1)
FROM Offers_To_Buy as O
JOIN Categories
LEFT JOIN User_Extras
ON(reg |
| 781 | world | claw.worldbid.com | worldbid | Query | 2
| Locked | select O.ID, keywords, deletedate, O.company_name,
'', userID,
LEFT(bid,150), stars, O.country, sub |
| 785 | world | claw.worldbid.com | worldbid | Query | 3
| Locked | select O.ID, keywords, deletedate, O.company_name,
'', userID,
LEFT(bid,150), stars, O.country, sub |
| 788 | world | claw.worldbid.com | worldbid | Query | 3
| Locked | UPDATE User_Extras SET last_visited=CURRENT_DATE()
WHERE registration_id='927269' |
| 789 | world | claw.worldbid.com | worldbid | Query | 3
| Locked | SELECT
R.ID,subscription_expiry>=CURRENT_DATE(),account_disabled,source,super_user
FROM New_Registra |
| 792 | world | claw.worldbid.com | worldbid | Query | 3
| Locked | SELECT count(*) FROM User_Extras
|
| 798 | world | claw.worldbid.com | worldbid | Query | 3
| Locked | select password,R.ID,account_disabled FROM
New_Registrations AS R LEFT JOIN User_Extras ON
(R.ID=re |
| 803 | world | claw.worldbid.com | worldbid | Query | 1
| Locked | select password,R.ID,account_disabled FROM
New_Registrations AS R LEFT JOIN User_Extras ON
(R.ID=re |
| 805 | unauthenticated user | claw.worldbid.com | NULL | Connect | NULL
| Reading from net | NULL
|
+-----+----------------------+-------------------+----------+---------+-----
-+----------------------+---------------------------------------------------
---------------------------------------------------+
---------------------------------------------------------------------
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
