MySQL Temp Tables

محروم.كوم · #1 08-28-2010, 07:40 AM

Greetings,

I've been having some MySQL issues lately where some queries are taking in excess of 200 seconds to execute. Before I did some tuning, there were some that were over 700 seconds. These appear to be search queries. I have fulltext searching turned on. Here is an example of a long query:

Code:
# Time: 100827 17:33:48
# User@Host:
# Query_time: 61.328692 Lock_time: 0.000129 Rows_sent: 90 Rows_examined: 23704
SET timestamp=1282948428;
SELECT
DISTINCT thread.threadid
FROM thread AS thread

INNER JOIN post AS post ON(thread.threadid = post.threadid )
WHERE MATCH(post.title, post.pagetext) AGAINST ('queue button') AND thread.forumid NOT IN (0,98,76,87,85,96,139,150,156,80,5,119,136,73,78,1 21,40,22,88) AND thread.forumid IN(163) AND post.visible = 1
LIMIT 200;
This kind of query is fairly common and obviously I'd like to cut down on the slowness here. The MySQL adjustment that made the most difference was increasing my tmp_table_size variable - it seems that all of these queries are using temporary tables and that the server has to create them on disk which is what is causing the slowdown. Some general stats:

tmp_table_size: 768 MB
key_buffer_size: 384 MB
vB database size: 1 GB
vB post table size: ~550 MB data + ~280 MB indexes
Queries per second: ~20
Slow queries per day: ~30

Hosting Situation:
I have full root access on cloud machines - MySQL has a server by itself and vB connects over the private network to retrieve info. For most things, my setup is VERY fast. I compiled MySQL manually from source. Currently running CentOS linux. The database server has 1 GB of RAM total.
MySQL version (has been upgraded to new minor point releases with no change in the issue): 5.1.50
PHP Version: 5.3.3 (Same as MySQL - has been upgraded with no changes)
Webserver: Lighttpd 1.4.27 (Same as MySQL - has been upgraded with no changes)
vB Version: 3.8.4

Things at issue:

I am under the impression that only necessary data gets copied to a temp table. Why would 768 MB not be enough that these queries need to use disk?
Could these queries indeed be copying to RAM from disk and THAT is what is taking so long?
If that is the case, then why does a query that once took ~760 seconds to execute now take ~1.5 seconds to execute but these other ones still have trouble?
Obvious: Are there any fixes for this and/or has this problem been encountered before (searching hasn't yielded many results for me, unfortunately)?

Thanks very much for any help, and let me know if there is any other information I can provide. __DEFINE_LIKE_SHARE__