Serious MySQL Problems

[Jose Cardoso]

For the last couple of days our MySQL server has been having some serious load issues.

We host two very active boards on this one server and each has about 150 active members at peak. The server is a PIII 1.2Ghz system with about 3GB of RAM. Most of this is used up if the server is left on for about a month but with recent reboots it never seems to use more than 1GB.

Could some knowledgable soul look at our MySQL settings and offer any suggestions (see the output.txt file)? These settings were based on past suggestions in this forum when we only had one board running on the server. I'm sure something may need to be changed. Our top output has also been included.

[eva2000]

okay let's see :)

please provide the following

1. your server specs, such as mysql and php version
2. if possible how mysql was compiled/installed
3. your top stats
4. your mysql configuration variables located at /etc/my.cnf or c:\my.cnf if on Windows server if you don't have that file you need to log into telnet and as root user type

mysqladmin -u root -p variables

copy and paste output here

5. your mysql extended-status output either still telnet as root user type

mysqladmin -u root -p extended-status

copy and paste output here

or preferred is to installed extended-status output script which is located at http://vbulletin.com/forum/showthread.php?threadid=3477

and post url to that here

6. oh and is your vB the only thing on the server? or other scripts? sites?

7. how many average and max concurrent users on your vB forum ?

8. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site

<?
phpinfo();
?>

i.e. yourdomain.com/phpinfo.php

[Jose Cardoso]

1. Sorry, forgot these :) MySQL is 3.23.49 and PHP is 4.1.2.

2. MySQL installed as a FreeBSD port.

3/4/5. Erm, see the output.txt file I attached above :)

6. No, the entire site is PHP/MySQL based with most pages grabbing a few vB threads for display (LIMIT's used where possible).

7. Varies during the day but average is around 150, max can be 210 at times. Second board averages 70 and 140 max.

8. Done -> http://www.digitalspy.co.uk/phpinfo.php

If you need anything else, just shout. We've had to close our main board to try and keep the server stable.

Note: Just to mention we aren't using persistant connections on either board. This was carried over from an old virtual host that couldn't handle this option. Should this be enabled perhaps?

[Jose Cardoso]

Just an update...problem was tracked down to an errant MySQL script being run by one of our hosting clients. Script was removed and server has gone back to the fast server it once was :)

However, I would be grateful if someone could please look at our current MySQL ssettings and offer suggestions based on the answers given to the questions posed further up this thread.

[eva2000]

your mysql buffers have been allocated too much memory try changing your my.cnf file to the following



[mysqld]
datadir=/home/www/db
socket = /tmp/mysql.sock
skip-locking
set-variable = max_connections=500
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=3M
set-variable = record_buffer=3M
set-variable = sort_buffer=4M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=7200
set-variable = connect_timeout=10

[safe_mysqld]
open_files_limit=8192

[mysqldump]
quick
set-variable = max_allowed_packet=128M

[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=16M
set-variable = write_buffer=16M restart mysql

[Jose Cardoso]

Cheers! Will try those settings out and will see how it goes.