Server load and MYSQL

[Dave Hull]

On Tue, 22 Oct 2002, Aaron wrote:

> > Do you have adequate indexes on your MySQL tables?
> 
> Not sure.  MySQL is not my strong point at all.

You said this was the backend db for a message board and you've got a 
large number of inserts taking place. Given that, I would take a look at 
your most frequent queries and determine if your indexing is adequate. 
Indexing on fields in where clauses can greatly increase the speed at 
which data is read from the database, however, it can also slow down 
writes to the database as indexed records are updated in the data tables, 
an additional write must be performed in the indexes. Take a hard look at 
your most frequent queries with these factors in mind. Adjust accordingly.

> > You said this box has 1GB of RAM, how much of it is being used at any
> > given time, on average, max?
> 
> Average, most of the time is about 995MB..  Max I see about 16-25MB of swap
> being used.

If you can, double the amount of RAM. Databases need RAM like the Donger 
need food. ;)

> Okay, Again, not my strong suit.  Here's the my.cnf file. Anyone see
> anything wierd that should be changed?
> 
> set-variable = max_connections=650
> set-variable = key_buffer=16M

After you've doubled the amount of RAM, double your key_buffer.

> set-variable = table_cache=1024

And double your table_cache, though the settings you have currently do 
seem reasonable, I'd increase them once more RAM has been introduced.

Since this is a backend for a message board, I would guess that you've got 
a field of variable length defined in the database for storing message 
content. If you have plenty of disk space, consider hard setting the 
length of this field (and any other varchars) to some reasonable value + a 
little bit more for verbose posters. Though this will require additional 
disk space (some of which will be wasted) rows of a fixed-length are 
processed faster than variable-length rows.

Again, the need for additional RAM can't be overstated.

I recently took an Oracle server from 256MB or RAM to 1.25GB of RAM and 
increased the "table_cache" by several hundred percent, thus keeping more 
data in RAM and greatly decreasing disk IO. Our cache hit ratio went from 
5% to more than 80%.

-- 
Dave Hull
http://insipid.com

Message from Our Sponsor on ttyTV at 13:58 ...