homepage Welcome to WebmasterWorld Guest from 54.227.41.242
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Hardware and OS Related Technologies / Linux, Unix, and *nix like Operating Systems
Forum Library, Charter, Moderators: bakedjake

Linux, Unix, and *nix like Operating Systems Forum

    
my.cnf optimization
enotalone




msg:3291350
 8:16 pm on Mar 23, 2007 (gmt 0)

Hi everyone, I would appreciate if somone could help me what I need to do to bring the load a little bit under control. For few days now it would not go down from 2-3, sometimes goes up to 10.

Most presure on the server is from a forum (vbulltin) with 2-3 thousands posts a day. Overall, working with about 20-30 thousands visitors a day, but this server handled way more than this before without going into panic.

Below is some data on the server, my.cnf, http.conf. Thank you in advance.

1. Dedicated server.

2. Server: double processor Super Xeon 2.4 GHz
Primary HDD: 80GB Hard Drive
Secondary HDD: 80GB Hard Drive
RAM: ECC Registered 2048 MB RAM
Uplink Port Speed: 10 Mbps Uplink
Operating System: Red Hat Enterprise Linux,
Control Panel: cPanel/WHM with Fantastic

3. Mysql 4.1.21

4. php 5.2.1

My.cnf

[mysqld]
max_connections = 500
key_buffer = 64M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 4M
table_cache = 1800
thread_cache_size = 64
wait_timeout = 180
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb
log-slow-queries = /var/lib/mysql/mysql-slow.log
long_query_time = 10
old-passwords = 1

[mysqld_safe]
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

httpd.conf

KeepAlive On
MaxKeepAliveRequests 200
KeepAliveTimeout 15
MinSpareServers 10
MaxSpareServers 20
StartServers 5
MaxClients 600
MaxRequestsPerChild 1000

Mysql extended Status

14:36:13 up 22:31, 1 user, load average: 1.70, 1.88, 1.87
202 processes: 198 sleeping, 4 running, 0 zombie, 0 stopped
total 36.1% 0.0% 23.9% 0.0% 0.2% 0.4% 39.2%
cpu00 58.6% 0.0% 40.3% 0.0% 0.9% 0.0% 0.0%
cpu01 17.3% 0.0% 0.9% 0.0% 0.0% 0.0% 81.7%
cpu02 39.0% 0.0% 47.6% 0.0% 0.0% 0.9% 12.3%
cpu03 29.5% 0.0% 6.6% 0.0% 0.0% 0.9% 62.8%
Mem: 2055364k av, 1981968k used, 73396k free, 0k shrd, 39220k buff
1490856k actv, 295144k in_d, 29104k in_c
Swap: 2040244k av, 48948k used, 1991296k free 1460960k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
574 mysql 18 0 159M 155M 2604 R 24.8 7.7 3:00 0 mysqld
1526 mysql 18 0 159M 155M 2604 R 24.8 7.7 2:21 2 mysqld
8887 nobody 20 0 1092 1092 764 R 0.7 0.0 0:00 3 top
7376 nobody 15 0 10084 9.8M 4356 R 0.4 0.4 0:25 1 httpd

Http processes currently running = 69
Mysql processes currently running = 4

Netstat information summary
1 CLOSE_WAIT
1 0 TIME_WAIT
1 1 ESTABLISHED
1 1 TIME_WAIT
1 13 ESTABLISHED
1 2 TIME_WAIT
1 3 TIME_WAIT
1 4 ESTABLISHED
1 4 FIN_WAIT2
1 4 TIME_WAIT
1 5 FIN_WAIT2
1 6 ESTABLISHED
1 6 TIME_WAIT
1 7 TIME_WAIT
1 9 FIN_WAIT2
1 9 TIME_WAIT
2 2 FIN_WAIT2
2 7 FIN_WAIT2
4 FIN_WAIT1
9 FIN_WAIT2
27 LISTEN
40 TIME_WAIT
45 ESTABLISHED

Top

16:15:58 up 1 day, 10 min, 1 user, load average: 4.90, 2.24, 1.52
205 processes: 203 sleeping, 2 running, 0 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 10.7% 0.0% 3.3% 0.0% 0.0% 1.2% 84.6%
cpu00 12.3% 0.0% 4.1% 0.1% 0.0% 2.4% 80.9%
cpu01 7.5% 0.0% 3.5% 0.0% 0.0% 2.2% 86.6%
cpu02 12.6% 0.0% 3.4% 0.0% 0.0% 0.2% 83.6%
cpu03 10.3% 0.0% 2.1% 0.0% 0.0% 0.1% 87.3%
Mem: 2055364k av, 1994324k used, 61040k free, 0k shrd, 43264k buff
1506596k actv, 291372k in_d, 28496k in_c
Swap: 2040244k av, 48700k used, 1991544k free 1465844k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
2230 mysql 16 0 159M 155M 2600 R 2.4 7.7 2:21 0 mysqld
1373 mysql 16 0 159M 155M 2600 S 1.1 7.7 2:44 2 mysqld
7389 nobody 15 0 9552 9552 4256 S 1.1 0.4 1:14 2 httpd
11485 nobody 16 0 9472 9472 4144 S 1.1 0.4 0:01 2 httpd
11501 nobody 15 0 9432 9432 4144 S 1.0 0.4 0:00 2 httpd
7376 nobody 15 0 9912 9912 4512 S 0.9 0.4 1:18 3 httpd
7402 nobody 16 0 9812 9812 4476 S 0.9 0.4 1:32 3 httpd
7383 nobody 16 0 14276 13M 4344 S 0.7 0.6 1:33 0 httpd
7386 nobody 16 0 9636 9636 4280 S 0.7 0.4 1:24 0 httpd
7387 nobody 16 0 9716 9716 4340 S 0.6 0.4 1:20 1 httpd
1454 mysql 15 0 159M 155M 2600 S 0.4 7.7 2:51 1 mysqld
7399 nobody 16 0 12500 12M 4492 S 0.4 0.6 1:28 2 httpd
1250 mysql 15 0 159M 155M 2600 S 0.3 7.7 2:42 0 mysqld
2265 mysql 15 0 159M 155M 2600 S 0.2 7.7 3:01 2 mysqld
11506 nobody 16 0 9056 9056 4080 S 0.2 0.4 0:00 0 httpd
1596 mysql 15 0 159M 155M 2600 S 0.1 7.7 2:25 2 mysqld
1825 mysql 15 0 159M 155M 2600 S 0.1 7.7 2:23 3 mysqld
11455 root 16 0 1240 1240 892 R 0.1 0.0 0:00 3 top
11507 nobody 15 0 9144 9144 4132 S 0.1 0.4 0:00 3 httpd
11508 nobody 16 0 8856 8856 3992 S 0.1 0.4 0:00 2 httpd
1 root 15 0 488 480 432 S 0.0 0.0 0:06 3 init
2 root RT 0 0 0 0 SW 0.0 0.0 0:00 0 migration/0
3 root RT 0 0 0 0 SW 0.0 0.0 0:00 1 migration/1
4 root RT 0 0 0 0 SW 0.0 0.0 0:00 2 migration/2
5 root RT 0 0 0 0 SW 0.0 0.0 0:00 3 migration/3
6 root 15 0 0 0 0 SW 0.0 0.0 0:05 1 keventd
7 root 34 19 0 0 0 SWN 0.0 0.0 0:00 0 ksoftirqd/0
8 root 34 19 0 0 0 SWN 0.0 0.0 0:00 1 ksoftirqd/1
9 root 34 19 0 0 0 SWN 0.0 0.0 0:00 2 ksoftirqd/2
10 root 34 19 0 0 0 SWN 0.0 0.0 0:00 3 ksoftirqd/3
13 root 15 0 0 0 0 SW 0.0 0.0 0:00 3 bdflush
11 root 15 0 0 0 0 SW 0.0 0.0 0:17 3 kswapd
12 root 15 0 0 0 0 SW 0.0 0.0 0:17 1 kscand
14 root 15 0 0 0 0 SW 0.0 0.0 0:05 2 kupdated
15 root 25 0 0 0 0 SW 0.0 0.0 0:00 0 mdrecoveryd

 

mcavic




msg:3292765
 7:51 pm on Mar 25, 2007 (gmt 0)

I suspect something is making MySQL work too hard. From the mysql command prompt, try show processlist; a few times, and see what it's doing. The time on each query shouldn't reach more than 1 second unless it's doing something intense, and if so, it'll tell you what the query is.

Also, I'd check to make sure you have the latest version of vBulletin.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Hardware and OS Related Technologies / Linux, Unix, and *nix like Operating Systems
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved