Request for Help with MySQL setup

[macrumors]

Hi there,

I was struggling with the mysql setup on my vbulletin forums - and found that I could get advise here... so here I am to ask for help. :)

Actually, my forums were doing well on a server with 512mb ram, AMD K7 700ish mhz without specific mysql tweaks... but then I moved it to:

AMD Duron 1GHz
512MB RAM

MySQL 3.23.41
PHP 4.1.1
Apache
vBulletin 2.2.6 (forums.macrumors.com)

Today, after I had moved the forums over, I hit the "too many connections" error very quickly... I think # of users was only 150-200 or so... Previously, it wouldn't be until 400-500 users before problems like that would arise...

So, I did some seaches, and increased the max_connections to 300 and decreased the wait_timeout to a few minutes... and then I got repeated "Lost connection to MySQL server during query".

anyhow... suggestions please?

here's my "top" presently, in case it helps...

9:10pm up 3:59, 1 user, load average: 0.72, 0.47, 0.42
137 processes: 135 sleeping, 1 running, 1 zombie, 0 stopped
CPU states: 16.7% user, 3.6% system, 0.7% nice, 78.9% idle
Mem: 496732K av, 357084K used, 139648K free, 1368K shrd, 36348K buff
Swap: 1020116K av, 0K used, 1020116K free 189380K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
18696 apache 11 0 10452 10M 8516 S 13.4 2.1 0:00 httpd
18730 root 14 0 1124 1120 824 R 8.0 0.2 0:00 top
17973 mysql 5 -10 9388 9388 2284 S < 4.4 1.8 0:17 mysqld
17853 mysql 5 -10 9388 9388 2284 S < 0.8 1.8 0:00 mysqld
18726 apache 9 0 0 0 0 Z 0.8 0.0 0:00 httpd <defunct>
1 root 8 0 524 524 456 S 0.0 0.1 0:05 init
2 root 8 0 0 0 0 SW 0.0 0.0 0:00 keventd
3 root 9 0 0 0 0 SW 0.0 0.0 0:00 kapm-idled
4 root 19 19 0 0 0 SWN 0.0 0.0 0:00 ksoftirqd_CPU0
5 root 9 0 0 0 0 SW 0.0 0.0 0:00 kswapd
6 root 9 0 0 0 0 SW 0.0 0.0 0:00 kreclaimd
7 root 9 0 0 0 0 SW 0.0 0.0 0:00 bdflush
8 root 9 0 0 0 0 SW 0.0 0.0 0:00 kupdated
9 root -1 -20 0 0 0 SW< 0.0 0.0 0:00 mdrecoveryd
13 root 9 0 0 0 0 SW 0.0 0.0 0:01 kjournald
90 root 9 0 0 0 0 SW 0.0 0.0 0:00 khubd
213 root 9 0 0 0 0 SW 0.0 0.0 0:00 kjournald
759 root 9 0 596 596 488 S 0.0 0.1 0:00 syslogd
772 root 9 0 1108 1108 448 S 0.0 0.2 0:00 klogd
831 root 9 0 2252 2252 1396 S 0.0 0.4 0:00 snmpd
852 named 9 0 3404 3404 2052 S 0.0 0.6 0:00 named
855 named 9 0 3404 3404 2052 S 0.0 0.6 0:00 named
856 named 9 0 3404 3404 2052 S 0.0 0.6 0:00 named
857 named 9 0 3404 3404 2052 S 0.0 0.6 0:00 named
858 named 9 0 3404 3404 2052 S 0.0 0.6 0:00 named
874 root 9 0 1288 1288 1144 S 0.0 0.2 0:00 sshd
895 root 9 0 1240 1240 1036 S 0.0 0.2 0:00 sshd
929 root 8 0 1016 1000 780 S 0.0 0.2 0:00 xinetd
1014 root 8 0 2432 2432 1752 S 0.0 0.4 0:00 sendmail
1074 root 6 0 8480 8480 8252 S 0.0 1.7 0:03 httpd
1132 postgres 8 0 1688 1688 1540 S 0.0 0.3 0:00 postmaster
1140 apache 9 0 10740 10M 8464 S 0.0 2.1 0:54 httpd
1164 root 8 0 1080 1080 808 S 0.0 0.2 0:00 proftpd

[Steve Machol]

Did you restart MySQL after making those changes?

[macrumors]

Originally posted by smachol
Did you restart MySQL after making those changes?

yes, I did....

and i tried some settings on this thread (http://www.vbulletin.com/forum/showthread.php?s=&threadid=51304) but continued to get lost connections... actually more so.

arn

[Steve Machol]

Here's info on that particular error:

http://www.vbulletin.com/forum/showthread.php?s=&postid=309680&highlight=lost+connection+server#post310265

[eva2000]

post your /etc/my.cnf contents here and 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

[macrumors]

Is this what you needed?

http://ads.macrumors.com/ext.php3

thanks
(diff url, same server)

arn

[eva2000]

Originally posted by macrumors
Is this what you needed?

http://ads.macrumors.com/ext.php3

thanks
(diff url, same server)

arn yup plus your /etc/my.cnf file contents

[macrumors]

Originally posted by eva2000
yup plus your /etc/my.cnf file contents

oops...

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=300
set-variable = wait_timeout=3600
set-variable = connect_timeout=10

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

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


...it's like pulling teeth, eh? :)

arn

[eva2000]

Originally posted by macrumors


oops...

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=300
set-variable = wait_timeout=3600
set-variable = connect_timeout=10

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

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


...it's like pulling teeth, eh? :)

arn looks like you're lacking a few settings for better mysql performance

try this my.cnf file and restart mysql


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=600
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

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

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
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

[macrumors]

thank you for your assistance... everything seems to be working smoothing now...

appreciate the help,
arn

[eva2000]

Originally posted by macrumors
thank you for your assistance... everything seems to be working smoothing now...

appreciate the help,
arn no probs :D