Can anyone tell me what variables I should have set in the my.ini for a site with 5000 visitors a day and every page that accesses the db?

It seems that once we get to 100 online users the CPU is maxing out.

We have a windows 2000 server with a xeon 2.4gig cpu and 2 gig ddr ram with raid 5 on sata drives.

Sounds good to me, but it seems the db takes all the cpu time.

So far I have managed to optimise it slightly but not nearly enough , here are the current settings:

buffer =256M
thread_cache = 500
timeout=15
table_cache = 128
sort_buffer = 4M
tmp_table_size=32M
back_log=100

Thanks!

I think the config file settings are okay. 5,000 hits per day / 1,440 minutes in each day works out at 3.47 hit per minute. You'd have to ask if the 'eating up the CPU' issues couldn't simply be resolved by looking at the queries that are being run and the table design / use of indexes.



credits: I copied the following from a post by Shawn Green (Database Administrator, Unimin Corporation - Spruce Pine) on the mysql mailling list, dated 22/07/2004 18:27

From: http://dev.mysql.com/doc/mysql/en/Se...variables.html

Key_reads
The number of physical reads of a key block from disk. If Key_reads is
big, then your key_buffer_size value is probably too small. The cache miss
rate can be calculated as Key_reads/Key_read_requests.

Select_full_join
The number of joins that do not use indexes. If this value is not 0, you
should carefully check the indexes of your tables. This variable was added
in MySQL 3.23.25.

Select_full_range_join
The number of joins that used a range search on a reference table. This
variable was added in MySQL 3.23.25.

Select_range
The number of joins that used ranges on the first table. (It's normally
not critical even if this is big.) This variable was added in MySQL
3.23.25.

Select_range_check
The number of joins without keys that check for key usage after each row.
(If this is not 0, you should carefully check the indexes of your tables.)
This variable was added in MySQL 3.23.25.

Select_scan
The number of joins that did a full scan of the first table. This variable
was added in MySQL 3.23.25.

Sort_scan
The number of sorts that were done by scanning the table. This variable
was added in MySQL 3.23.25.

I would still check the slow query log to see if there are any common
queries that could use an index or two. I would also look at changing some
simple indexes into compound indexes (indices?) as another means of
speeding things up.