John, >From my experience it is a lot more on how big is your data, not necessarily the amount of data. Which version of mysql are you running? Are you using a mysql prepared version (you downloaded it from mysql.com). I'm using 4.1.3 and I have a table that has a char 68 with 29 million rows that is fulltext indexed and all of my queries using something similar to yours take 0.1 to 0.2 seconds max. Also if you provided your full table structure including the indexes that would help.
Donny > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 26, 2004 8:08 AM > To: [EMAIL PROTECTED] > Subject: Re: Slow Queries on Fast Server? > > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
