Need Server Help - Anyone here a MySQL expert?

[Wayne 962]

I'm having a problem with queries slowing down this server. It's giving me the following info when I show processes:

mysql> show processlist;
+------+-------------+-----------+-----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-------------+-----------+-----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 9 | mysql-login | localhost | NULL | Sleep | 1 | | NULL |
| 397 | ODBC | localhost | NULL | Query | 0 | NULL | show processlist |
| 834 | mysql-login | localhost | vbulletin | Sleep | 122 | | NULL |
| 950 | mysql-login | localhost | vbulletin | Sleep | 73 | | NULL |
| 1079 | mysql-login | localhost | vbulletin | Query | 12 | closing tables | SELECT DISTINCT thread.threadid FROM thread
,post
WHERE thread.threadid=post.threadid AN |
| 1084 | mysql-login | localhost | vbulletin | Query | 0 | Copying to tmp table |
SELECT DISTINCT post.userid,
thread.threadid,thread.threadid AS postid,thread.title AS t |
| 1085 | mysql-login | localhost | vbulletin | Query | 0 | Locked | UPDATE user SET
posts=posts+1,
usertitle='Senior Member',lastpost='1046406047' W |
| 1086 | mysql-login | localhost | vbulletin | Query | 0 | Sending data | SELECT attachmentid,postid,visible FROM post WHERE threadid='99842' AND username='ZCAT3' AND userid= |
| 1087 | mysql-login | localhost | vbulletin | Query | 0 | Opening tables | SELECT postid FROM post WHERE postid=701122 |
| 1089 | mysql-login | localhost | vbulletin | Query | 0 | Locked | UPDATE user SET lastactivity=1046406040,inforum='8' WHERE userid='5831' |
| 1090 | mysql-login | localhost | vbulletin | Query | 0 | Locked |
SELECT
post.*,post.username AS postusername,post.ipaddress AS ip,user.*,userfield.*,icon.title as |
| 1091 | mysql-login | localhost | vbulletin | Query | 0 | Locked | UPDATE user SET lastactivity=1046406040,inforum='17' WHERE userid='13124' |
| 1094 | mysql-login | localhost | vbulletin | Query | 0 | closing tables | SELECT templatesetid,replacementsetid,userselect FROM style WHERE styleid='2' or styleid=1 ORDER BY |
| 1095 | mysql-login | localhost | vbulletin | Sleep | 0 | | NULL |
| 1096 | mysql-login | localhost | vbulletin | Query | 0 | Locked | SELECT usergroupid FROM user WHERE userid=13213 |
| 1097 | mysql-login | localhost | vbulletin | Query | 0 | closing tables | SELECT * FROM forum WHERE displayorder<>0 AND active=1 ORDER BY parentid,displayorder |
+------+-------------+-----------+-----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
16 rows in set (0.00 sec)


"Copying to tmp table" appears to be the problem.

Any suggestions?

-Wayne

[cstreit]

Wayne... Often when there is not a proper index on a table, it will build temporary versions of it (smaller) to do full table scans or build temp indexes.

I don't know what db you are using, but temp tablespaces are also used in sorting activity as well, also related to indexes.

In this case the query is SELECT DISTINCT post.userid,
thread.threadid,thread.threadid So you could try adding an index on Post.userid and Thread.threadid.?

THat's a weird query though, because it selects threadid twice...

[Wayne 962]

We're using MYSQL, I increased the size of the tmp_table_size setting to 133554432 from 33554432 - hopefully that will do the trick. What happens is that the processes pile up because the table is locked, waiting for this query to finish. Thus, the server seems slow...

-Wayne