MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Mark Maunder Date:February 11 2004 10:02am
Subject:Fulltext performance and RAM upgrade
View as plain text  
I am considering an upgrade on our server from 512 megs of RAM to 1 or
possibly 1.5 gigs, and would like to know if I'm going to get a
significant performance boost. Any suggestions or information is much
appreciated. Our configuration is as follows:

The table has around 100,000 records (but will grow up to 1 million
records) with a single fulltext index on two fields (both varchar). The
MYI file for the table is about 20 megs. 

The OS is redhat linux kernel version 2.4.18-27. The web server is
Apache. The processor is an AMD Duron 1 GigaHZ. The table is hit hard by
the webserver. It does huge multi-row inserts (multiple inserts in a
single statement) of up to 2000 records, and then queries the same table
immediately expecting the index to be updated with the new data which
has been added to the existing 100,000 records. We get multiple
concurrent requests like this hitting the DB very hard. 

I run my web server and mysql server on the same machine. I have the
following settings in my.cnf:


Also, I have ft_min_word_len=2 (we have to match state abbreviations)
and have disabled stopwords. 

The fulltext queries are a combination of boolean and non-boolean (if
that helps).

I am hoping that with the extra RAM I can increase the key_buffer to
around 700 Megs (Will that help?) and get better file caching from the
operating system. Running `free` on the OS gives me the following:

             total       used       free     shared    buffers    
Mem:        505940     498268       7672          0       9352    
-/+ buffers/cache:     294060     211880
Swap:      1052248       8060    1044188

The web server takes about half of available RAM. The slow queries are
the selects on the fulltext index after the huge inserts. I am also
getting some slow insert statements on other tables, but less so. I
suspect it's a side effect of heavy load on the DB from the big
insert/select statements on the fulltext index.

Any help or suggestions are appreciated. Thanks,


Fulltext performance and RAM upgradeMark Maunder11 Feb