Mysql memory and server overload

We have a dedicated unix server with 3 decent forums, and some small html sites that use hardly any resources.
Two are vBulletin. One is PHPBB.

These are all hobby / gamer sites and run by volunteers / gamers.

It does seem like the Vbulletin forums can have a couple hundred users and it doesnt affect the server performance.

But when the PHPBB forum gets about 100 or more users, the box starts to die. Pages take 10 seconds or more to load, if they even load. Site eventually becomes unavailable.

At these ‘convienient’ moments the server will also decide to do some statistics software logging and generation. Or, handle a big batch of emails. Once the box starts swapping hard drive, it starts to go down until things are totally unresponcive.

Looked everywhere in cPanel to find every used resource and theres nothing obvious anymore about why it does it.

Processor #1 Name: Intel® Pentium® 4 CPU 2.00GHz
Memory: 2042768k/2088896k available (1710k kernel code, 41520k reserved, 1296k data, 228k init, 1171392k highmem)

Right now mysql is using 67.40% of the memory alone and most of the CPU usage. 76 users, 40 users, and 49 users online. It would be nice if each of the sites had 200 users online and ran well.

It might just be a lot for a server like ours but, sites used to run smoothly and suport hundreds of users. Cant understand why the box is dying when running only 3 forums and some small HTML based sites.

These run on a dedicated server with unix, apache, mysql. No, we cannot rent out a seperate mysql server =/

Any insite on whats happening is welcome.

-Dan


phpmyadmin also gave me me these warnings for the phpbb site. But im not sure if theres alot I can do about it.

Handler_read_rnd 1 M

The number of requests to read a row based on a
fixed position. This is high if you are doing a lot of queries that require
sorting of the result. You probably have a lot of queries that require MySQL to
scan whole tables or you have joins that don’t use keys properly.

Handler_read_rnd_next 50 M

The number of requests to read the
next row in the data file. This is high if you are doing a lot of table scans.
Generally this suggests that your tables are not properly indexed or that your
queries are not written to take advantage of the indexes you have.

Created_tmp_disk_tables 436

The number of temporary tables on
disk created automatically by the server while executing statements. If
Created_tmp_disk_tables is big, you may want to increase the tmp_table_size
value to cause temporary tables to be memory-based instead of disk-based.

Key_reads 74 k

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 14

The number of joins that do not use indexes.
If this value is not 0, you should carefully check the indexes of your tables.

Opened_tables 12 k

The number of tables that have been opened. If
opened tables is big, your table cache value is probably too small.

Table_locks_waited 4 k

The number of times that a table lock
could not be acquired immediately and a wait was needed. If this is high, and
you have performance problems, you should first optimize your queries, and then
either split your table or tables or use replication.

First I should note about memory - It is expected for MySQL to be configured to take large portion of memory in the system as it needs it for various caches etc. If you’re using MyISAM only tables I would probably reduce it a bit as these need some OS cache to perform well.

I would suggest you run mysqladmin extended -i10 -r during high load for few printouts and post it here. This would allow to see how MySQL Settings need to be adjusted.

I would also check queries which are executed by enabling slow query log. Even popular software may have some things done not optimally.

[B]Peter wrote on Wed, 13 September 2006 16:39[/B]
I would suggest you run mysqladmin extended -i10 -r during high load for few printouts and post it here. This would allow to see how MySQL Settings need to be adjusted.

The SRO game servers will be down 9 hours today for maintainance, im sure that slow time is comming very soon. Perhaps even, box death. Myself don’t fully understand how to run “mysqladmin extended -i10 -r” but one of the other admins probobly does. I will ask.