List:General Discussion« Previous MessageNext Message »
From:JVanV8 Date:August 25 2004 3:42pm
Subject:Re: Slow Queries on Fast Server?
View as plain text  
>Could you send the output of an EXPLAIN for your query?

Sure, pretty sure the index is fine though:

mysql> EXPLAIN SELECT COUNT(*) FROM product_fulltext WHERE MATCH (search_text) AGAINST
> ('black');
+------------------+----------+---------------+-------------+---------+------+------+-------------+
| table            | type     | possible_keys | key         | key_len | ref  | rows |
Extra       |
+------------------+----------+---------------+-------------+---------+------+------+-------------+
| product_fulltext | fulltext | search_text   | search_text |       0 |      |    1 |
Using where |
+------------------+----------+---------------+-------------+---------+------+------+-------------+
1 row in set (0.00 sec)




JVanV8@stripped wrote:
>>Have you checked the "Optimization" section of the manual yet?
>>http://dev.mysql.com/doc/mysql/en/MySQL_Optimization.html
> 
> 
> Oh yes, as I've attempted to configure the my.cnf file for best performance.  The
> query is correct.  The fulltext index is correct as I built the fulltext index on
> the
> single column (took 9 minutes) and even did "repair" and "optimize" on the table...
> so I
> don't think its the index.  I'm thinking its the server config...
> 
> - John
> 
> 
> 
> JVanV8@stripped wrote:
> 
>>I'm running into a problem with some queries running on a dedicated mysql server
> (2.0
>>GHz, 2GB RAM).  Fulltext searching really exemplifies this as most MATCH, AGAINST
> queries
>>are taking 5-20 seconds.  Performance was excellent for some reason one day (0.2
> -
> 0.75
>>seconds) but it was only fast for a day or so.
>>Here's the rundown:
>>
>>TABLE:  fulltext_table (some_id, the_text) 
>>Rows: 3,237,981 
>>Type: MyISAM
>>Size: 920.8 MB 
>>    
>>QUERY: SELECT COUNT(*) FROM fulltext_table WHERE MATCH (the_text) AGAINST
> ('blue');
>>or 
>>QUERY: SELECT SQL_CALC_FOUND_ROWS some_id FROM fulltext_table WHERE MATCH
> (the_text)
>>AGAINST ('blue') LIMIT 0, 20;
>>
>>Both are problematic.  I even tried placing a limit of 20000 on the first query
> but
>>it didn't improve anything.  The table has a fulltext index on the column and is
>>optimized.  No other users are connected to the server.
>>
>>Is there a RED FLAG in here somewhere?
>>
>>MySQL configuration settings (using my-huge.cnf template):
>>key_buffer = 500M
>>max_allowed_packet = 1M
>>table_cache = 512
>>sort_buffer_size = 10M
>>read_buffer_size = 2M
>>myisam_sort_buffer_size = 64M
>>#thread_cache = 8
>>thread_concurrency = 8
>>#----- Modifications ----------- #
>>ft_min_word_len = 3
>>set-variable = table_cache=1024
>>set-variable = max_heap_table_size=64M
>>set-variable = tmp_table_size=128M
>>set-variable = query_cache_limit=2M
>>query_cache_type=1
>>
>>
>>Performance Test:
>>SELECT COUNT(*) FROM fulltext_table WHERE MATCH (the_text) AGAINST ('white');
>>+----------+
>>| COUNT(*) |
>>+----------+
>>|    95074 |
>>+----------+
>>1 row in set (27.83 sec)
>>
>>Statistics for vmstat 1 (my apologies if this doesn't look pretty):
>>-----------------------
>>procs                      memory      swap          io     system         cpu
>> r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
>>0  0  19500  17800  42432 1771728    0    0     0    60  113    30  0  0 99  1
>> 0  1  19500  21524  42428 1765728  724    0   960     0  536   444  5  1 82 12
>> 0  1  19500  19512  42424 1767820    0    0  2348     0  912   592  0  1 50 49
>> 0  1  19500  17788  42424 1769540    0    0  1980     0  868   588  0  1 51 48
>> 0  1  19500  17568  42424 1769760    0    0  2300     0  723   401  0  0 50 49
>> 0  1  19500  17704  42428 1769620    0    0  1936    20  662   364  0  0 51 49
>> 0  1  19500  17560  42428 1769764    0    0  2224     0  696   400  0  0 51 49
>> 0  1  19500  17504  42424 1769824    0    0  2136     0  670   380  0  0 51 49
>> 0  1  19500  17616  42424 1769712    0    0  2228     0  693   415  0  0 51 49
>> 0  1  19508  17608  42420 1769724    0    8  2348     8  692   389  0  0 50 50
>> 0  1  19508  17532  42428 1769792    0    0  1896   108  654   366  0  0 50 49
>> 0  1  19512  17644  42424 1769684    0    4  2220     4  720   450  0  1 50 49
>> 0  1  19516  17620  42420 1769712    0    4  2104     4  707   424  0  0 51 48
>> 0  1  19516  17744  42420 1769588    0    0  2476     0  762   462  0  1 50 49
>> 0  1  19516  17532  42416 1769804    0    0  2292     0  719   401  0  0 51 49
>>procs                      memory      swap          io     system         cpu
>> r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
>> 0  1  19516  17388  42424 1769940    0    0  2216    16  699   388  0  0 51 49
>> 0  1  19516  17632  42420 1769700    0    0  1836     0  629   380  0  0 51 49
>> 0  1  19516  17596  42420 1769732    0    0  2112     0  661   374  0  1 51 48
>> 0  1  19516  17580  42416 1769752    0    0  1836     0  631   396  0  0 51 49
>> 0  1  19516  17624  42416 1769708    0    0  2036     0  654   368  0  0 51 49
>> 0  1  19516  17556  42420 1769772    0    0  1880    16  643   381  0  0 50 50
>> 0  1  19516  17652  42420 1769676    0    0  1984     0  657   380  0  0 51 49
>> 0  1  19516  17532  42416 1769800    0    0  1940     0  646   386  0  1 50 49
>> 0  1  19516  17520  42416 1769812    0    0  1832     0  631   389  0  0 50 49
>> 0  1  19516  17548  42412 1769788    0    0  2052     0  648   387  0  1 50 49
>> 0  1  19516  17700  42412 1769636    0    0  2440    28  741   448  0  0 50 50
>> 0  1  19516  17656  42408 1769684    0    0  2384     0  683   412  0  1 50 49
>> 0  1  19516  17676  42408 1769660    0    0  2316     0  679   387  0  1 50 49
>> 0  1  19516  17624  42404 1769712    0    0  2128     0  652   407  0  1 50 49
>> 0  0  19516  19056  42404 1769752    0    0    40     0  132    40  0  0 97  2
>>
>>Statistics for top command:
>>-----------------------------
>> PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND
>> 4784 root      15   0   488  488   420 S     0.2  0.0   0:00   0 vmstat
>> 3979 mysql     16   0 68128  52M  2188 S     0.1  2.6   0:06   1 mysqld
>> 3982 mysql     15   0 68128  52M  2188 S     0.1  2.6   0:05   2 mysqld
>>    1 root      15   0   512  512   452 S     0.0  0.0   0:05   2 init
>>    2 root      RT   0     0    0     0 SW    0.0  0.0   0:00   0 migration/0
>>    3 root      RT   0     0    0     0 SW    0.0  0.0   0:00   1 migration/1
>>    4 root      RT   0     0    0     0 SW    0.0  0.0   0:00   2 migration/2
>>    5 root      RT   0     0    0     0 SW    0.0  0.0   0:00   3 migration/3
>>    6 root      15   0     0    0     0 SW    0.0  0.0   0:00   1 keventd
>>    7 root      34  19     0    0     0 SWN   0.0  0.0   0:00   0 ksoftirqd/0
>>    8 root      34  19     0    0     0 SWN   0.0  0.0   0:00   1 ksoftirqd/1
>>
>> 07:58:06  up 1 day, 20:51,  4 users,  load average: 0.36, 0.16, 0.05
>>82 processes: 81 sleeping, 1 running, 0 zombie, 0 stopped
>>CPU states:  cpu    user    nice  system    irq  softirq  iowait    idle
>>           total    0.2%    0.0%    0.4%   0.0%     0.0%    9.4%   89.9%
>>           cpu00    0.2%    0.0%    0.8%   0.0%     0.0%    0.0%   99.0%
>>           cpu01    0.0%    0.0%    0.6%   0.0%     0.0%   18.4%   81.0%
>>           cpu02    0.4%    0.0%    0.2%   0.0%     0.2%    0.0%   99.2%
>>           cpu03    0.2%    0.0%    0.0%   0.0%     0.0%   19.2%   80.6%
>>Mem:  2061636k av, 2042580k used,   19056k free,       0k shrd,   42412k buff
>>                   1007792k actv,  689868k in_d,   32652k in_c
>>Swap: 2040244k av,   19516k used, 2020728k free                 1769752k cached
>>
>>
>>Would greatly appreciate any advice or comments
>>- John
Thread
Slow Queries on Fast Server?JVanV825 Aug
  • Re: Slow Queries on Fast Server?V. M. Brasseur25 Aug
Re: Slow Queries on Fast Server?JVanV825 Aug
  • Re: Slow Queries on Fast Server?V. M. Brasseur25 Aug
Re: Slow Queries on Fast Server?JVanV825 Aug
Re: Slow Queries on Fast Server?JVanV826 Aug
  • Re: Slow Queries on Fast Server?Brent Baisley26 Aug
  • RE: Slow Queries on Fast Server?Donny Simonton27 Aug
Re: Slow Queries on Fast Server?JVanV827 Aug
  • Re: Slow Queries on Fast Server?Brent Baisley27 Aug