mysql optimization help needed :)

[gafami]

hey there,

I just wanted to ask, if someone (eva eventually) could help us/me ... optimizing mysql on our rackshack box. it's and amd xp 1700+, 512mb ram etc. nothing spectecular, but I am not sure, whether or not mysql is set up properly:


http://www.newzbin.com/mysql-status.php


maybe someone can have a look up there and tellme what I should do. We are getting some very high loads during peak times (>9) and i am a little worried :\


cheers & thanks in advanced,

-joerg

[jphilipson]

Well I set up a friends box, similiar setup with rackshack.. he uses it for vhosting, but has a few mysql sites... keep in mind it only has 512 ram so I didn't want to allocate too much memory to mysql.. this is the config I used for mysql and it seems to be running fine. If I gave it any more the system lagged...

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

skip-locking
set-variable = max_connections=400
set-variable = key_buffer=24M
set-variable = table_cache=1024
set-variable = sort_buffer=3M
set-variable = join_buffer=2M
set-variable = record_buffer=2M
set-variable = thread_cache=256
set-variable = thread_concurrency=2
set-variable = myisam_sort_buffer_size=24M


[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

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

[mysql]
no-auto-rehash

[isamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[eva2000]

Originally posted by gafami
hey there,

I just wanted to ask, if someone (eva eventually) could help us/me ... optimizing mysql on our rackshack box. it's and amd xp 1700+, 512mb ram etc. nothing spectecular, but I am not sure, whether or not mysql is set up properly:


http://www.newzbin.com/mysql-status.php


maybe someone can have a look up there and tellme what I should do. We are getting some very high loads during peak times (>9) and i am a little worried :\


cheers & thanks in advanced,

-joerg 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