Tune performance of fulltext search

Hi,

I am running MySQL on a well equipped server, 2Xeon, 4G RAM and
SCSI-RAID.
I got one real big table with 10M rows and fulltext index.
Most searches run fast enough, even on peak hours. Only when using very
common words in a fulltext-query (but not common enough to stopword
them), I get awful response times.
Even when I stop all other activity on the server and I issue a
fulltext query with 2 common words, the search can take longer than 30
sec!

When monitoring the server activity (via vmstat) when issuing the
query, I see the cpu idle state going down from 100% to exactly 75% and
minor read activity from the hard disk during search time. I just
cannot figure out, where is the bottleneck. It seems as if there were
enough computing power to execute the query much faster.

This is my custom configuration (all other values are default):
key_buffer = 512M
max_allowed_packet = 128M
thread_stack = 512K
max_connections = 600
max_connect_errors = 10000
table_cache = 256
sort_buffer = 128M
read_buffer_size = 8M
read_rnd_buffer_size = 8M

BTW: Is there a way to tell MySQL to read the whole index file of one
specific table into memory, so that searches are executed without any
disk access?

Greetz,
Jens

jens.bertheau@gmx.de wrote:

Jens, as a native German speaker you may want to ask your question
in de.comp.datenbanken.mysql as well.

> I am running MySQL on a well equipped server, 2Xeon, 4G RAM and
> SCSI-RAID.

I guess that's Hypertreading Xeons, so you have 2 physical but
4 logical CPUs, right?

> I got one real big table with 10M rows and fulltext index.

How big is the index? (the .MYI file)

> When monitoring the server activity (via vmstat) when issuing the
> query, I see the cpu idle state going down from 100% to exactly 75% and
> minor read activity from the hard disk during search time.

With 4 CPUs and 75% idle that means, one of the CPUs is at 100%.
"minor read activity" could be read as "data is cached", however
this could be misleading if your disks are doing many searches
(heads moving). iostat shows you #requests and device utilization.
However it looks as your servers performance is CPU bound.

....

> key_buffer = 512M

With 4GB RAM you may set this as high as 2GB. If you're running on
a 32-bit platform (I guess you are) this is also the limit.

> read_buffer_size = 8M
> read_rnd_buffer_size = 8M

Setting those higher might take load from your disks.

> BTW: Is there a way to tell MySQL to read the whole index file of one
> specific table into memory, so that searches are executed without any
> disk access?

This is done automatically as long as your key_buffer is large enough.


XL

> I guess that's Hypertreading Xeons, so you have 2 physical but
> 4 logical CPUs, right?

Correct.

> > I got one real big table with 10M rows and fulltext index.
> How big is the index? (the .MYI file)

It´s 3.7G, so no way to fit it completely into RAM

> However it looks as your servers performance is CPU bound.

Yes, probably. Is there any way to tell MySQL to handle queries with
more than one logical CPU simultaneously? When I monitor CPU load it
rarely drops below 50% idle, so, if technically possible, I could gain
performance, if multiple CPUs could handle a single request.

Jens

jens.bertheau@gmx.de wrote:
>
>> > I got one real big table with 10M rows and fulltext index.
>> How big is the index? (the .MYI file)
>
> It's 3.7G, so no way to fit it completely into RAM

That's bad. To get good performance, all active indexes should fit into
key_buffer. Otherwise you lose performance due to extra I/O operations.

>> However it looks as your servers performance is CPU bound.
>
> Yes, probably. Is there any way to tell MySQL to handle queries with
> more than one logical CPU simultaneously?

Not with current MySQL. Future versions will be able to process a query
in multiple threads, but this will be limited to partitioned tables.
I don't know of any plans to use multiple threads in fulltext search.

Maybe you want to have a look at an external search engine. I heard
good things about Lucene.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/