MySQL Server Optimization Request [Archive]

memdy

Thu 3rd Mar '05, 11:01am

We sometimes hit spikes of 300+ users and our load average sometimes peaks between 4 and 6. Perhaps we can tweak the server a bit to give us some extra time to add more capacity. Thanks in advance.

------------------------
Server Type
------------------------

Dedicated Server

------------------------
Server Specs
-------------------------

3.0Ghz CPU
1GB RAM
120GB HD
RedHat9
Apache 2.0.40
PHP 4.3.10
MySQL 3.23.58
Zend 2.5.7

-------------------------
Top Stats
-------------------------

09:24:15 up 91 days, 18:00, 1 user, load average: 4.37, 2.60, 2.10
120 processes: 119 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states: 44.4% user 13.0% system 0.0% nice 0.0% iowait 41.4% idle
CPU1 states: 49.0% user 7.3% system 0.0% nice 0.0% iowait 43.0% idle
Mem: 1025160k av, 1011460k used, 13700k free, 0k shrd, 812k buff
295364k active, 320796k inactive
Swap: 2048276k av, 119560k used, 1928716k free 446944k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
25265 mysql 9 0 37316 20M 3276 S 99.9 2.0 14:44 0 mysqld
11146 root 9 0 4992 2080 2012 S 71.3 0.2 10:43 1 httpd
6395 root 13 0 1128 1128 868 R 1.3 0.1 0:02 1 top
5 root 9 0 0 0 0 SW 0.1 0.0 51:51 0 kswapd
1 root 9 0 460 432 416 S 0.0 0.0 1:57 1 init
2 root 9 0 0 0 0 SW 0.0 0.0 0:00 0 keventd
3 root 19 19 0 0 0 SWN 0.0 0.0 0:29 0 ksoftirqd_CPU0
4 root 19 19 0 0 0 SWN 0.0 0.0 0:04 1 ksoftirqd_CPU1
6 root 9 0 0 0 0 SW 0.0 0.0 6:40 1 bdflush
7 root 9 0 0 0 0 SW 0.0 0.0 74:57 1 kupdated
8 root 9 0 0 0 0 SW 0.0 0.0 2:01 0 pagebufd
9 root 9 0 0 0 0 SW 0.0 0.0 9:40 0 xfslogd/0
10 root 9 0 0 0 0 SW 0.0 0.0 0:00 1 xfslogd/1
11 root 9 0 0 0 0 SW 0.0 0.0 0:00 0 xfsdatad/0
12 root 9 0 0 0 0 SW 0.0 0.0 0:00 1 xfsdatad/1
14 root 9 0 0 0 0 SW 0.0 0.0 2:55 0 kjournald
101 root 9 0 0 0 0 SW 0.0 0.0 0:30 1 xfssyncd
102 root 9 0 0 0 0 SW 0.0 0.0 0:53 1 xfssyncd
103 root 9 0 0 0 0 SW 0.0 0.0 0:25 1 xfssyncd
424 root 9 0 840 648 580 S 0.0 0.0 0:50 1 dhclient
472 root 9 0 556 524 476 S 0.0 0.0 12:45 0 syslogd
476 root 9 0 416 364 364 S 0.0 0.0 0:00 1 klogd
569 root 9 0 812 664 620 S 0.0 0.0 0:20 1 sshd
600 ntp 9 0 2412 2412 2172 S 0.0 0.2 9:34 0 ntpd
743 root 9 0 18904 156 156 S 0.0 0.0 0:00 1 spamd
1162 postgres 9 0 548 296 264 S 0.0 0.0 0:16 0 postmaster
1164 postgres 9 0 492 160 160 S 0.0 0.0 0:00 1 postmaster
1165 postgres 9 0 512 160 160 S 0.0 0.0 0:00 1 postmaster
1265 xfs 9 0 2316 168 168 S 0.0 0.0 0:00 1 xfs
1286 daemon 9 0 188 168 168 S 0.0 0.0 0:03 1 atd
1303 mailman 9 0 3204 536 536 S 0.0 0.0 0:00 1 python
1304 mailman 9 0 3132 1256 772 S 0.0 0.1 33:12 0 python
1305 mailman 9 0 3856 1976 1668 S 0.0 0.1 0:35 0 python
1306 mailman 9 0 3844 1876 1360 S 0.0 0.1 32:44 0 python
1307 mailman 9 0 3860 1984 1444 S 0.0 0.1 33:04 1 python
1308 mailman 9 0 3900 1964 1428 S 0.0 0.1 32:33 1 python
1309 mailman 9 0 3588 1668 1104 S 0.0 0.1 34:31 0 python
1310 mailman 9 0 3848 1924 1416 S 0.0 0.1 32:59 1 python
1315 root 9 0 384 336 336 S 0.0 0.0 0:00 1 mingetty
1316 root 9 0 384 336 336 S 0.0 0.0 0:00 1 mingetty
1317 root 9 0 384 336 336 S 0.0 0.0 0:00 0 mingetty
1318 root 9 0 384 336 336 S 0.0 0.0 0:00 0 mingetty
1319 root 9 0 388 336 336 S 0.0 0.0 0:00 0 mingetty
1321 root 9 0 388 336 336 S 0.0 0.0 0:00 0 mingetty
1322 root 9 0 420 368 368 S 0.0 0.0 0:00 0 agetty
627 bb 9 0 1060 848 848 S 0.0 0.0 0:00 1 runbb.sh
628 bb 8 0 436 416 416 S 0.0 0.0 0:05 0 bbrun
19926 root 9 0 568 540 496 S 0.0 0.0 0:15 0 crond

--------------------------------
InnoDB usage
--------------------------------
Based on the my.cnf shown below, i would say yes.

----------------------------
Mysql Config Variables
----------------------------

[root@titan cron.daily]# cat /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-networking
set-variable = max_allowed_packet=32M

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

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

--------------------------------------------
MySQL Stats Script for 3.x.x
-------------------------------------------

http://www.offshoreonly.com/forums/mysql-stats.php

----------------------------------------
Other apps on the same server
---------------------------------------

There is a classifieds application that runs on the same server that is php/mysql based.

----------------------------------------
average and max concurrent users on your vB forum
----------------------------------------

At the time of this writing, 208 Users Online (149 members and 59 guests)

We get spikes of 350 users online on a daily basis.

----------------------------------------
PHP Info
----------------------------------------

http://www.offshoreonly.com/forums/phpinfo.php

----------------------------------------
Apache variables
----------------------------------------

Keepalive Off
MaxKeepAliveRequests 100
KeepAliveTimeout 15
<IfModule prefork.c>
StartServers 8
MinSpareServers 5
MaxSpareServers 20
MaxClients 150
MaxRequestsPerChild 1000
</IfModule>

--------------------------------
vBulletin Version
--------------------------------

vBulletin v3.0.3

eva2000

Fri 4th Mar '05, 12:27pm

what's your cookie timeout set to ?

things to do in this exact order

1. upgrade MySQL from 3.23.58 to 4.0.23
2. change /etc/my.cnf to below settings and restart mysql server

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-networking
skip-locking
skip-innodb
max_connections = 650
key_buffer = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 10800
connect_timeout = 10
tmp_table_size = 32M
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1

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

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

3. install eaccelerator from http://eaccelerator.net/InstallEacceleratorUk read http://www.vbulletin.com/forum/showpost.php?p=805040&postcount=441

4. upgrade to vB 3.0.7