Hi, could you help me to optimize this system Centos 5, 2.6.18-53.1.19.el5 x86_64
Please if is possible to offer me better values for apache configuration parameters also for mysql.
The problem with this system is that loading is higher and we want to make it stable. Some times mysql is using more cpu, not every time, but time to time. Together with apache loading, system loading is going higher.
This is apache configuration. I was trying with these values and with other, but i can't get apache to work enough good.
Code:
#
# Timeout: The number of seconds before receives and sends time out.
#
Timeout 100
#
# KeepAlive: Whether or not to allow persistent connections (more than
# one request per connection). Set to "Off" to deactivate.
#
KeepAlive On
#
# MaxKeepAliveRequests: The maximum number of requests to allow
# during a persistent connection. Set to 0 to allow an unlimited amount.
# We recommend you leave this number high, for maximum performance.
#
MaxKeepAliveRequests 1000
#
# KeepAliveTimeout: Number of seconds to wait for the next request from the
# same client on the same connection.
#
KeepAliveTimeout 2
##
## Server-Pool Size Regulation (MPM specific)
##
# prefork MPM
# StartServers: number of server processes to start
# MinSpareServers: minimum number of server processes which are kept spare
# MaxSpareServers: maximum number of server processes which are kept spare
# ServerLimit: maximum value for MaxClients for the lifetime of the server
# MaxClients: maximum number of server processes allowed to start
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule prefork.c>
StartServers 25
MinSpareServers 10
MaxSpareServers 50
ServerLimit 100
MaxClients 100
MaxRequestsPerChild 15000
</IfModule>
my.cnf
Code:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=0
bind-address=127.0.0.1
max_connections=300
# Set MyISAM parameters
key_buffer_size=3M
tmp_table_size=16M
# Set InnoDB parameters
innodb_file_per_table
# Set buffer pool size to 50 - 80% of availiable memory.
innodb_buffer_pool_size=1000M
innodb_additional_mem_pool_size=50M
#innodb_flush_method=O_DIRECT
# Set the log file size to about 25% of the buffer pool size.
#innodb_log_file_size=5M
#innodb_log_buffer_size=1M
table_cache=1024
#sort_buffer_size=3M
query_cache_type=1
query_cache_limit=1M
query_cache_size=30M
net_read_timeout=120
#long_query_time=10
#log_warnings=2
#log_slow_queries=/var/log/mysql/slow-queries.log
collation_server=utf8_unicode_ci
character_set_server=utf8
[mysql]
default-character-set=utf8
[mysqldump]
default-character-set=utf8
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
dstat output:
Code:
0 1 99 1 0 0| 1.8 2.1 2.3|2306M 88M 1025M 537M| 15k 24k| 0 0 0| 122M 6021M|1073 172
0 0 98 1 0 0| 1.8 2.1 2.3|2306M 88M 1025M 537M|1691B 21k| 0 0 0| 122M 6021M|1060 141
26 7 66 1 0 0| 1.8 2.1 2.3|2306M 88M 1025M 537M| 927B 1124B| 1 0 0| 122M 6021M|1027 616
44 6 43 6 0 0| 1.8 2.1 2.3|2306M 88M 1025M 537M| 536B 2926B| 1 0 0| 122M 6021M|1196 866
49 13 38 0 0 0| 1.8 2.1 2.3|2308M 88M 1025M 534M| 338B 18k| 1 0 0| 122M 6021M|1021 1821
32 13 55 0 0 0| 1.8 2.1 2.3|2306M 88M 1025M 537M|2315B 13k| 1 0 0| 122M 6021M|1038 600
41 10 50 0 0 0| 1.8 2.1 2.3|2306M 88M 1025M 537M| 752B 6672B| 1 0 0| 122M 6021M|1022 695
45 6 49 1 0 0| 1.8 2.1 2.3|2306M 88M 1025M 537M|1615B 5959B| 1 0 0| 122M 6021M|1029 1677
33 8 59 1 0 0| 1.8 2.1 2.3|2306M 88M 1025M 537M|1054B 968B| 1 0 0| 122M 6021M|1036 1698
3 1 95 0 0 0| 1.6 2 2.3|2306M 88M 1025M 537M|2481B 30k| 2 0 0| 122M 6021M|1048 210
61 18 20 2 0 0| 1.6 2 2.3|2306M 88M 1025M 537M|2560B 4390B| 5 0 0| 122M 6021M|1036 1245
88 12 0 0 0 0| 1.6 2 2.3|2307M 88M 1025M 536M|2695B 1433B| 12 0 0| 122M 6021M|1014 2206
78 22 0 0 0 1| 1.6 2 2.3|2306M 88M 1025M 537M| 810B 9771B| 6 0 0| 122M 6021M|1056 1188
86 15 0 0 0 0| 1.6 2 2.3|2310M 88M 1025M 532M|2008B 8749B| 16 0 0| 122M 6021M|1019 1411
77 23 0 0 0 0| 2.3 2.1 2.4|2319M 88M 1025M 523M|1929B 16k| 10 0 0| 122M 6021M|1024 1868
81 19 0 0 0 0| 2.3 2.1 2.4|2325M 88M 1025M 517M| 909B 6562B| 12 0 0| 122M 6021M|1021 1502
83 17 0 0 0 0| 2.3 2.1 2.4|2329M 88M 1025M 514M| 818B 3826B| 10 0 0| 122M 6021M|1009 1831
82 17 0 0 0 0| 2.3 2.1 2.4|2335M 88M 1025M 507M|1133B 784B| 14 0 0| 122M 6021M|1035 2253
81 19 0 0 0 0| 2.3 2.1 2.4|2347M 88M 1025M 496M|5109B 20k| 5 0 1| 122M 6021M|1063 1917
85 14 0 0 0 0| 2.9 2.3 2.4|2331M 88M 1025M 511M|3441B 83k| 6 0 0| 122M 6021M|1093 2186
86 15 0 0 0 0| 2.9 2.3 2.4|2327M 88M 1025M 515M|4960B 61k| 3 0 0| 122M 6021M|1092 1847
70 15 16 0 0 0| 2.9 2.3 2.4|2320M 88M 1025M 523M|4788B 59k| 2 0 0| 122M 6021M|1038 2317
25 7 68 0 0 0| 2.9 2.3 2.4|2321M 88M 1025M 522M|4617B 38k| 2 0 0| 122M 6021M|1063 703
41 10 50 0 0 0| 2.9 2.3 2.4|2325M 88M 1025M 518M|4213B 76k| 2 0 0| 122M 6021M|1029 683
44 6 49 0 0 0| 2.8 2.3 2.4|2326M 88M 1025M 516M|2768B 31k| 2 0 0| 122M 6021M|1031 2002
----total-cpu-usage---- ---load-avg--- ------memory-usage----- -net/total- ---procs--- -swp/total- ---system--
usr sys idl wai hiq siq| 1m 5m 15m | used buff cach free| recv send|run blk new| used free| int csw
68 17 16 1 0 0| 2.8 2.3 2.4|2324M 88M 1025M 519M|2906B 23k| 5 0 0| 122M 6021M|1054 2513
84 17 0 0 0 0| 2.8 2.3 2.4|2330M 88M 1025M 512M|4795B 35k| 7 0 0| 122M 6021M|1046 2108
79 21 0 0 0 0| 2.8 2.3 2.4|2332M 88M 1025M 511M|5198B 22k| 12 0 0| 122M 6021M|1048 1432
62 16 22 1 0 1| 2.8 2.3 2.4|2323M 88M 1025M 520M|4844B 38k| 3 0 0| 122M 6021M|1111 1770
40 11 50 0 0 0| 2.6 2.2 2.4|2327M 89M 1026M 516M|6348B 36k| 2 0 2| 122M 6021M|1048 1925
35 10 55 0 0 0| 2.6 2.2 2.4|2322M 89M 1026M 520M|2588B 7360B| 1 0 0| 122M 6021M|1022 477
17 6 77 0 0 0| 2.6 2.2 2.4|2322M 89M 1026M 520M|2220B 21k| 1 0 0| 122M 6021M|1043 531
0 0 100 0 0 0| 2.6 2.2 2.4|2322M 89M 1026M 520M|3587B 24k| 1 0 0| 122M 6021M|1038 132
22 10 67 0 0 0| 2.6 2.2 2.4|2322M 89M 1026M 520M|1892B 8625B| 1 0 0| 122M 6021M|1042 491
43 12 43 2 1 0| 2.5 2.2 2.4|2322M 89M 1026M 520M|2660B 33k| 4 0 0| 122M 6021M|1044 923
83 17 0 0 0 0| 2.5 2.2 2.4|2327M 89M 1026M 515M|2877B 4659B| 9 0 0| 122M 6021M|1020 1316
79 21 0 0 0 0| 2.5 2.2 2.4|2331M 89M 1026M 511M|4465B 43k| 5 0 0| 122M 6021M|1063 2207
87 12 0 0 0 0| 2.5 2.2 2.4|2339M 89M 1026M 503M|5910B 57k| 6 0 0| 122M 6021M|1055 1990
74 17 8 0 0 0| 2.5 2.2 2.4|2332M 89M 1026M 510M|4242B 21k| 2 0 0| 122M 6021M|1252 2689
26 7 66 2 0 0| 2.4 2.2 2.4|2331M 89M 1026M 511M|4415B 53k| 2 0 0| 122M 6021M|1071 1092
49 12 38 0 0 0| 2.4 2.2 2.4|2331M 89M 1026M 511M|5817B 27k| 2 0 0| 122M 6021M|1030 947
45 6 50 0 0 0| 2.4 2.2 2.4|2331M 89M 1026M 511M| 460B 12k| 2 0 0| 122M 6021M|1012 1713
29 7 64 0 0 0| 2.4 2.2 2.4|2331M 89M 1026M 511M| 326B 4984B| 1 0 0| 122M 6021M|1031 1445
0 1 78 21 0 0| 2.4 2.2 2.4|2331M 89M 1026M 511M|1966B 20k| 1 0 0| 122M 6021M|1282 141
0 0 100 0 0 0| 2.2 2.2 2.4|2331M 89M 1026M 511M| 70B 438B| 1 0 0| 122M 6021M|1030 88
25 7 67 1 0 0| 2.2 2.2 2.4|2331M 89M 1026M 511M| 556B 4648B| 2 0 0| 122M 6021M|1048 588
72 16 13 0 0 0| 2.2 2.2 2.4|2332M 89M 1026M 510M|4263B 22k| 4 0 2| 122M 6021M|1046 1402
81 18 0 1 0 0| 2.2 2.2 2.4|2331M 89M 1026M 511M|2054B 10k| 7 0 0| 122M 6021M|1034 1803
77 14 9 0 0 0| 2.2 2.2 2.4|2331M 89M 1026M 511M|4505B 14k| 3 0 0| 122M 6021M|1103 1945
top:
Code:
top - 20:27:35 up 3 days, 8:40, 3 users, load average: 2.31, 2.50, 2.56
Tasks: 135 total, 1 running, 134 sleeping, 0 stopped, 0 zombie
Cpu(s): 37.3%us, 11.8%sy, 0.0%ni, 50.4%id, 0.5%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 4051316k total, 3634788k used, 416528k free, 86980k buffers
Swap: 6291448k total, 125436k used, 6166012k free, 999584k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
31241 apache 16 0 400m 89m 47m S 54 2.3 1:46.83 httpd
30981 apache 15 0 414m 105m 48m S 36 2.7 2:02.34 httpd
31341 apache 15 0 413m 98m 42m S 7 2.5 1:16.73 httpd
29235 mysql 15 0 1435m 845m 4580 S 1 21.4 5:43.70 mysqld
31456 apache 15 0 399m 83m 40m S 0 2.1 0:36.05 httpd
31751 anebi 15 0 92940 6812 1696 S 0 0.2 0:00.36 python
31859 anebi 15 0 12716 1080 792 R 0 0.0 0:00.21 top
1 root 18 0 10312 580 544 S 0 0.0 0:04.58 init
2 root RT 0 0 0 0 S 0 0.0 0:00.48 migration/0
3 root 34 19 0 0 0 S 0 0.0 0:01.84 ksoftirqd/0
4 root RT 0 0 0 0 S 0 0.0 0:00.00 watchdog/0
5 root RT 0 0 0 0 S 0 0.0 0:00.05 migration/1
6 root 34 19 0 0 0 S 0 0.0 0:00.05 ksoftirqd/1
7 root RT 0 0 0 0 S 0 0.0 0:00.00 watchdog/1
8 root 10 -5 0 0 0 S 0 0.0 0:00.01 events/0
9 root 10 -5 0 0 0 S 0 0.0 0:00.19 events/1
10 root 10 -5 0 0 0 S 0 0.0 0:00.00 khelper
47 root 11 -5 0 0 0 S 0 0.0 0:00.00 kthread
52 root 10 -5 0 0 0 S 0 0.0 0:00.15 kblockd/0
53 root 10 -5 0 0 0 S 0 0.0 0:00.45 kblockd/1
54 root 15 -5 0 0 0 S 0 0.0 0:00.00 kacpid
top memory order
Code:
top - 20:42:33 up 3 days, 8:55, 3 users, load average: 2.32, 2.02, 2.17
Tasks: 132 total, 2 running, 130 sleeping, 0 stopped, 0 zombie
Cpu(s): 76.3%us, 18.1%sy, 0.0%ni, 4.8%id, 0.7%wa, 0.2%hi, 0.0%si, 0.0%st
Mem: 4051316k total, 3623656k used, 427660k free, 94456k buffers
Swap: 6291448k total, 125436k used, 6166012k free, 1073264k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
29235 mysql 15 0 1435m 854m 4580 S 6 21.6 6:20.32 mysqld
31812 root 19 0 633m 117m 15m S 0 3.0 0:07.19 java
31885 apache 15 0 423m 108m 40m S 0 2.7 0:21.03 httpd
31899 apache 15 0 402m 86m 40m S 0 2.2 0:18.86 httpd
31888 apache 15 0 397m 80m 41m S 0 2.0 0:27.83 httpd
31890 apache 15 0 396m 80m 40m S 0 2.0 0:22.36 httpd
32040 apache 15 0 395m 79m 40m S 0 2.0 0:18.91 httpd
31896 apache 15 0 396m 79m 40m S 0 2.0 0:14.92 httpd
32041 apache 15 0 396m 79m 40m S 0 2.0 0:18.93 httpd
32039 apache 15 0 395m 79m 40m S 0 2.0 0:10.70 httpd
32031 apache 15 0 393m 78m 41m S 0 2.0 0:12.85 httpd
32035 apache 15 0 393m 77m 39m S 0 2.0 0:10.70 httpd
31900 apache 15 0 393m 76m 40m S 0 1.9 0:17.99 httpd
32045 apache 15 0 392m 76m 40m S 7 1.9 0:17.18 httpd
32044 apache 15 0 392m 76m 40m S 0 1.9 0:19.12 httpd
31894 apache 18 0 391m 76m 40m S 0 1.9 0:29.99 httpd
32032 apache 19 0 392m 76m 39m S 18 1.9 0:23.35 httpd
32046 apache 15 0 390m 75m 41m S 0 1.9 0:13.53 httpd
31895 apache 15 0 391m 75m 40m S 0 1.9 0:23.40 httpd
31902 apache 15 0 391m 75m 40m S 0 1.9 0:28.83 httpd
31892 apache 21 0 392m 75m 39m S 37 1.9 0:25.35 httpd
32037 apache 16 0 390m 74m 40m S 50 1.9 0:20.91 httpd
32043 apache 15 0 390m 74m 40m S 0 1.9 0:08.57 httpd
31897 apache 15 0 388m 72m 40m S 0 1.8 0:27.93 httpd
31884 apache 15 0 387m 71m 41m S 0 1.8 0:10.93 httpd
31908 apache 15 0 386m 71m 41m S 0 1.8 0:30.18 httpd
31901 apache 15 0 386m 70m 41m S 0 1.8 0:22.81 httpd
31891 apache 15 0 386m 70m 41m S 0 1.8 0:24.55 httpd
31907 apache 15 0 385m 70m 40m S 0 1.8 0:15.27 httpd
31906 apache 15 0 385m 69m 40m S 0 1.8 0:30.79 httpd
32030 apache 15 0 388m 69m 39m S 0 1.8 0:35.22 httpd
31898 apache 15 0 384m 69m 40m S 0 1.8 0:23.15 httpd
31903 apache 15 0 385m 69m 40m S 0 1.7 0:28.69 httpd
31887 apache 15 0 385m 68m 41m S 0 1.7 0:33.89 httpd
31904 apache 15 0 385m 68m 40m S 0 1.7 0:26.29 httpd
Also what i see is that when crawlers and bots accessing site, then loading is increasing.
Please help to make this system more stable. If you need other info, please tell me and i will past it here.
Mysql database use Innodb Engine and database is with more that 1 million records.
Thanks in advanced!
P.S.
I changed KeepAlive to off and configure it this way:
<IfModule prefork.c>
StartServers 25
MinSpareServers 10
MaxSpareServers 70
ServerLimit 150
MaxClients 150
MaxRequestsPerChild 6000
</IfModule>
this helped to decrease loading little bit, i think that it can be optimize more. Please help for this.
