mysql configuration help

[btsteed]

I've been using vbulletin for about 3 months now, but I've never really looked at the configurations in my.cnf to see if there was anything I can tweak. So.... I went to take a look at it, and this is all I found:

my.cnf

[mysqld]
set-variable = max_connections=500

Any assistance someone can offer would be helpful. Here's some information:

http://www.scrapjazz.com/mysql.php

I'm particularly worried about the line:


| Open_tables | 64 | 100% of table_cache in use



Processor Info

Processor #1 Vendor: GenuineIntel
Processor #1 Name: Intel(R) Celeron(TM) CPU 1200MHz
Processor #1 speed: 1202.147 MHz
Processor #1 cache size: 256 KB


Memory Information
Memory: 509160k/523136k available (1725k kernel code, 11524k reserved, 91k data, 224k init, 0k highmem)


System Information
Linux host.scrapjazz.com 2.4.9-31 #1 Tue Feb 26 07:11:02 EST 2002 i686 unknown

Operating System Linux
Apache Version 1.3.23 (Unix)
PHP Version 4.1.2
Mysql Version 3.23.45

Any help anyone could provide would be greatly appreciated.


bsteed

[eva2000]

okay 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

9. if you run Apache and you have your own dedicated server or access to your httpd.conf (apache configuration file) can you post the values you have set for the following :

KeepAlive
MaxKeepAliveRequests
KeepAliveTimeout
MinSpareServers
MaxSpareServers
StartServers
MaxClients

[btsteed]

I answered several of these, but here they are again in order:



1. your server specs, such as mysql and php version

Intel(R) Celeron(TM) CPU 1200MHz
1202.147 MHz
cache size: 256 KB

Operating System Linux
Apache Version 1.3.23 (Unix)
PHP Version 4.1.2
Mysql Version 3.23.45

2. if possible how mysql was compiled/installed
no idea, I'm on a dedicated server that uses Cpanel, but I'm the only site on it.

3. your top stats


1:37pm up 15:02, 2 users, load average: 2.73, 2.50, 2.23
122 processes: 115 sleeping, 6 running, 1 zombie, 0 stopped
CPU states: 85.2% user, 13.9% system, 0.0% nice, 0.7% idle
Mem: 511772K av, 501036K used, 10736K free, 364K shrd, 54056K buff
Swap: 1052248K av, 110764K used, 941484K free 233196K cached


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

[mysqld]
set-variable = max_connections=500

That's it. I know that can't be right.


but, when I type the mysqladmin ... variables, I DO get the following: (Actually, see the attachment).

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

mysqladmin -u root -p extended-status

See Attachement.

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

http://www.scrapjazz.com/mysql.php

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

vb and Photopost [http://www.photopost.com].

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

roughly 60 -100...not near enough for big problems.

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

http://www.scrapjazz.com/phpinfo.php

9. if you run Apache and you have your own dedicated server or access to your httpd.conf (apache configuration file) can you post the values you have set for the following :

KeepAlive ON
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 150

[eva2000]

okay looks like you're running mysql default configuration settings which may not be adequate for your mysql usage.. also you seem to have swapped to disk alot - this could be due to your unoptimised mysql variables such as table_cache value

replace your current /etc/my.cnf file with the below contents and restart mysql


[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=450
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=7200
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10

[safe_mysqld]
open_files_limit=8192

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

[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M
also you might want to upgrade at least mysql from 3.23.45 to 3.23.51

[btsteed]

Thanks, this seems to be helping. I did however notice that there seem to be several more msyql processes showing up when I run the top command. Is this because the settings I changed allowed connections to persist longer or what?

Thanks again for the tweaks.

Ben