Tweet
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:
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:
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.
This query (from a search) is killing us:
PHP Code:
# User@Host: 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.title, post.pagetext) AGAINST ('shocks') AND thread.forumid NOT IN (0,31,54,52,64,46,27,47,41,66,65,62) AND thread.replycount >= 8 AND thread.forumid IN(26)
LIMIT 200;
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
Thanks.
Comment