Mysql key_buffer_size already 16GB but some fulltext searches are still slow

Asked Jul 11 '13 at 03:07

Active Jul 11 '13 at 06:54

Viewed 551 times

I have a query w/ full text search which takes 5 - 10 secs. key_buffer_size is already 16GB. Table has 15 million records and has a compound fulltext index on fields: categories and company.

Table Size:
Data Length - 0.9GB
Index Length - 1.1GB

    SELECT c.id FROM basetable c 
    WHERE MATCH(c.categories, c.company) 
    AGAINST('+keyword1 +keyword2 +keyword3' IN BOOLEAN MODE)
    LIMIT 100

Query Results:
only 88 rows

Query Status:
Key_read_requests - 4414336
Key_reads - 1029
Key_write_requests - 12
Key_writes - 2
Handler_read_next - 89

Mysql Global Variables:
key_buffer_size - 16GB
key_cache_age_threshold - 300
key_cache_block_size - 1024
key_cache_division_limit - 100
max_heap_table_size - 6gb
tmp_table_size - 6gb

Server:
Dual XEONS CPUs
32GB RAM
HD 15kRPM.

Note that not all queries are slow. Some are less than a second; it depends on the keywords used on Full Text search.

Do you have any thoughts on how to improve this?

edited Jul 11 '13 at 06:54

asked Jul 11 '13 at 03:07

john

John, please take a look on this post http://stackoverflow.com/questions/12804785/improving-full-text-search-mysql – medina Jul 11 '13 at 03:36
Thanks! I also have a search engine based site. But the idea is to be something like Google search. The categories field contains all category (CSV); it is still slow even when I remove the name. – john Jul 11 '13 at 03:48
Have you ever tried Solr? Maybe it worth a look at. =) – medina Jul 11 '13 at 06:33
What are your innodb values in config ? – cyrianox Jul 11 '13 at 07:00
for example : innodb_buffer_pool_size = 256M, innodb_additional_mem_pool_size = 32M – cyrianox Jul 11 '13 at 07:02
I am using Myisam tables. – john Jul 11 '13 at 07:02
Last option for me is to try Sphinx, Solr, etc. – john Jul 11 '13 at 07:04

Mysql key_buffer_size already 16GB but some fulltext searches are still slow

0 Answers0