Re: Slow Queries on Fast Server?

Thu, 26 Aug 2004 09:10:22 -0700

Capable? I can't think if why it wouldn't be capable. From your posts I assume your definition of "capable" in this case is a quick response. Are you running 4.0 or 4.1? I think the indexing was changed in 4.1 so it would give you better response. 5-20 seconds does seem long, assuming your disks are fast.

You may try doubling or tripling your sort_buffer and myisam_sort_buffer settings and maybe you read_buffer. Remember the template configuration files are still all purpose configurations. You are really looking to optimize a specific area.

Unfortunately, I don't have a dataset large enough yet to test myself, but I am curious. Improved performance is on the to do list.


On Aug 26, 2004, at 9:07 AM, [EMAIL PROTECTED] wrote:

I'm gathering by the lack of response that perhaps MySQL is incapable of executing a count of the number of fulltext matches on 3 million rows.
I really thought that MySQL 4 was really suppose to be able to handle such a load....
I still think my configuration may be to blame
?
- John






--------------------------
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)




[EMAIL PROTECTED] 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



[EMAIL PROTECTED] 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



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to