I administer a site with about 4.2 million posts, over 350,000 threads, and typically 1200-1400 users online (15 minute cookie).

This query (from a search) is killing us:

PHP Code:
[email protected]: xxxxx[xxxxx] @  [192.168.xxx.xxx]
# Query_time: 38  Lock_time: 0  Rows_sent: 3  Rows_examined: 24856
SELECT DISTINCT thread.threadid
FROM thread 
AS thread
INNER JOIN post 
AS post ON(thread.threadid post.threadid  AND post.userid IN(21704))
WHERE MATCH(post.titlepost.pagetextAGAINST ('shocks') AND thread.forumid NOT IN (0,31,54,52,64,46,27,47,41,66,65,62) AND thread.replycount >= AND thread.forumid IN(26)
LIMIT 200
This type of query often results in "Copying to tmp table". I don't have any evidence that this is going to disk, but it's very slow, may take several minutes. We are using fulltext search, and when this copy to tmp occurs, all new posts are blocked, and it cascades to the point where it may lock up the site for posting for a minute or more.

The database is MySQL v 4.1.18, running on a dedicated server, dual xeon 2.8 with 2GB RAM. Gentoo Linux. RAID-1 10Krpm disks. We were fine until we upgraded to 3.5, really seemed to hit the wall at 3.5.2, but we're not certain.

my.cnf:
PHP Code:
[mysqld]
ft_min_word_len 2
user            
mysql
pid
-file        = /var/run/mysqld/mysqld.pid
socket          
= /var/run/mysqld/mysqld.sock
log
-error       = /var/log/mysql/mysqld.err
basedir         
= /usr
datadir         
= /var/lib/mysql
tmpdir          
= /tmp/mysql
language        
= /usr/local/mysql/share/mysql/english
skip
-locking
skip
-name-resolve


back_log 
50
bind
-address=192.168.0.100

max_connections 
650
key_buffer 
16M
myisam_sort_buffer_size 
64M
join_buffer_size 
1M
read_buffer_size 
1M
sort_buffer_size 
2M
table_cache 
1800
thread_cache_size 
512
wait_timeout 
180
connect_timeout 
10
tmp_table_size 
64M
max_allowed_packet 
64M
bulk_insert_buffer_size 
8M
thread_concurrency 
4
query_cache_limit 
2M
query_cache_size 
64M
query_cache_type 
1
query_prealloc_size 
16384
query_alloc_block_size 
16384





log_slow_queries
long_query_time 
30

key_buffer 
512M
read_rnd_buffer_size 
16M
bulk_insert_buffer_size 
32M
myisam_sort_buffer_size 
32M

myisam_max_sort_file_size 
6G
myisam_max_extra_sort_file_size 
6G

myisam_repair_threads 
2



skip
-bdb


innodb_additional_mem_pool_size 
18M
innodb_buffer_pool_size 
128M

innodb_data_file_path 
ibdata1:10M:autoextend
innodb_thread_concurrency 
8

innodb_flush_log_at_trx_commit 
1

innodb_log_buffer_size 
8M

innodb_log_file_size 
100M
innodb_log_files_in_group 
2

innodb_max_dirty_pages_pct 
90


innodb_lock_wait_timeout 
50 
I will post more later (gotta run to work), but for now, I'd like any pointers or tips for the right way to debug this.

Thanks.