High load problems - please help

#1
I've been running Litespeed enterprise (2CPU) for a while now and recently moved my MySQL database to another server in the hope it would speed things up a bit. Unfortunately, it doesn't seem to have made a great deal of difference and I'm left wondering whether there's something in my LS configuration that could be slowing things down. What are the main settings I should be looking at in order to optimise for my site, which receives over 750,000 pageviews and 90,000 file downloads (avg. size 500KB) per day? I'm hosting Litespeed on a dedicated 2 X Dual Core Woodcrest 5130; 4GB RAM; 2 x 320GB SATA (RAID 1). Although not relevant to LS, the database is on a 2 x Clovertown 5335 Quad Core; 4GB RAM; 2 x 147GB SCSI 10K (RAID 1).

User concurrency wise.. peak times will see 1600 users online (15 min timeout) and 800 during quiter times. Although there is a small speed increase during quieter periods, it's nothing significant. Page execution occassionally (but far too often) takes up to 15 seconds and other times it will be an ok 0.8.

Current LS settings:

Max connections: 2000
Connection timeout: 60
Max Keep-alive requests: 2000
Smart Keep-Alive: Yes
Smart K-A Timeout: 10

lsphp5:

Max Connections: 100
PHP_LSAPI_MAX_REQUESTS=500
PHP_LSAPI_CHILDREN=100

I'm desperately struggling to find a solution to my problems, so if you could be so kind as to assist, I would really appreciate it. If these settings are already ok, is it worth upgrading to a 2 x Clovertown 5335 Quad core or do you believe something else might be the bottleneck?

Here's an excerpt from my top stats during a busy period (it constantly hovers around 6-8):

top - 17:03:54 up 13 days, 22:03, 1 user, load average: 8.45, 6.06, 5.13
Tasks: 176 total, 2 running, 174 sleeping, 0 stopped, 0 zombie
Cpu(s): 1.2% us, 2.9% sy, 24.1% ni, 67.1% id, 3.5% wa, 0.1% hi, 1.2% si
Mem: 4137244k total, 3442740k used, 694504k free, 251116k buffers
Swap: 4096532k total, 144k used, 4096388k free, 2642100k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
28287 nobody 16 1 13412 5020 2988 S 22 0.1 0:02.34 lsphp5
29192 nobody 16 1 13420 5476 3452 S 18 0.1 0:01.83 lsphp5
26441 nobody 16 1 16352 8424 3300 R 15 0.2 0:09.41 lsphp5
29484 nobody 16 1 13388 4924 2916 S 14 0.1 0:00.67 lsphp5
27610 nobody 16 1 13412 5244 3212 S 13 0.1 0:02.42 lsphp5
29204 nobody 16 1 13536 5424 3284 S 11 0.1 0:01.81 lsphp5
29135 nobody 17 1 13468 5032 2944 S 5 0.1 0:01.52 lsphp5
29549 nobody 15 0 93464 87m 36m S 5 2.2 83:18.49 lshttpd
29245 nobody 16 1 13408 5004 2976 S 3 0.1 0:01.38 lsphp5
29543 nobody 16 1 18020 9260 2736 S 2 0.2 0:00.07 lsphp5
29524 mailnull 15 0 8372 3860 2648 S 1 0.1 0:00.02 exim
29526 mailnull 15 0 8372 3864 2652 S 1 0.1 0:00.02 exim
29542 ic2it 15 0 1824 916 488 S 0 0.0 0:00.01 pop3d
29547 root 15 0 5796 2808 1120 S 0 0.1 0:30.37 lshttpd
1 root 15 0 1700 548 468 S 0 0.0 0:22.77 init
Here's some stats from one the quietest periods, during a spike:

top - 23:18:13 up 15 days, 4:17, 1 user, load average: 11.74, 7.30, 4.61
Tasks: 199 total, 7 running, 190 sleeping, 2 stopped, 0 zombie
Cpu0 : 1.6% us, 3.1% sy, 18.9% ni, 67.9% id, 5.9% wa, 0.2% hi, 2.3% si
Cpu1 : 1.1% us, 1.9% sy, 15.6% ni, 78.1% id, 2.7% wa, 0.0% hi, 0.5% si
Cpu2 : 0.4% us, 3.1% sy, 34.2% ni, 58.8% id, 1.7% wa, 0.1% hi, 1.7% si
Cpu3 : 1.2% us, 2.7% sy, 22.8% ni, 64.5% id, 8.0% wa, 0.0% hi, 0.6% si
Mem: 4137244k total, 2562148k used, 1575096k free, 678184k buffers
Swap: 4096532k total, 144k used, 4096388k free, 1230884k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1754 nobody 17 1 13804 6272 3860 S 8 0.2 0:05.22 lsphp5
2141 root 18 0 29356 24m 648 R 8 0.6 0:31.19 lfd
2505 nobody 16 1 13444 5304 3252 S 8 0.1 0:04.35 lsphp5
32413 nobody 16 1 13820 6104 3676 S 8 0.1 0:05.60 lsphp5
2543 nobody 16 1 18728 10m 3884 S 7 0.3 0:06.27 lsphp5
3536 nobody 16 1 16496 7892 2836 R 6 0.2 0:00.13 lsphp5
3540 nobody 16 1 13384 4848 2852 S 6 0.1 0:00.30 lsphp5
3546 nobody 16 1 18040 8768 2760 R 6 0.2 0:00.14 lsphp5
3560 nobody 16 1 18068 9848 3088 S 6 0.2 0:00.15 lsphp5
3542 nobody 16 1 13392 4852 2844 S 5 0.1 0:00.24 lsphp5
31115 nobody 16 1 13840 6392 3944 S 5 0.2 0:21.44 lsphp5
803 nobody 16 1 13504 5384 3272 S 4 0.1 0:07.13 lsphp5
3545 nobody 16 1 18024 9288 2764 S 3 0.2 0:00.09 lsphp5
3548 nobody 16 1 13424 5204 3184 S 3 0.1 0:00.23 lsphp5
29712 nobody 16 1 13828 6324 3888 S 3 0.2 0:15.92 lsphp5
801 nobody 16 1 18476 10m 3896 S 3 0.3 0:14.62 lsphp5
2516 nobody 16 1 18080 10m 3508 S 3 0.2 0:05.39 lsphp5
2534 nobody 16 1 17640 9300 3256 S 3 0.2 0:06.45 lsphp5
3541 nobody 16 1 13384 4856 2860 S 3 0.1 0:00.27 lsphp5
2284 root 0 -20 0 0 0 S 2 0.0 36:39.36 loop0
3535 nobody 16 1 17044 8432 2828 S 2 0.2 0:00.16 lsphp5
3547 nobody 20 1 17156 9424 3468 R 2 0.2 0:00.20 lsphp5
3550 nobody 16 1 13432 4908 2860 S 2 0.1 0:00.31 lsphp5
21354 nobody 16 0 78036 73m 24m D 2 1.8 9:50.61 lshttpd
Top stats a few mins later:

top - 23:27:04 up 15 days, 4:26, 1 user, load average: 3.46, 3.40, 3.75
Tasks: 178 total, 4 running, 165 sleeping, 9 stopped, 0 zombie
Cpu(s): 0.6% us, 2.5% sy, 17.3% ni, 75.5% id, 3.2% wa, 0.0% hi, 0.8% si
Mem: 4137244k total, 2854040k used, 1283204k free, 682852k buffers
Swap: 4096532k total, 144k used, 4096388k free, 1599360k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
6063 nobody 16 1 16260 7764 2932 S 15 0.2 0:02.15 lsphp5
5989 nobody 16 1 17080 8604 2908 S 12 0.2 0:00.85 lsphp5
2412 nobody 16 1 16656 9172 3872 S 10 0.2 0:05.82 lsphp5
3972 nobody 16 1 16568 9012 3648 S 7 0.2 0:09.05 lsphp5
5983 nobody 17 1 16536 7964 2840 S 6 0.2 0:00.24 lsphp5
6042 nobody 16 1 16536 8500 3208 S 5 0.2 0:01.58 lsphp5
6171 nobody 16 1 15716 7004 2812 R 4 0.2 0:00.13 lsphp5
3801 nobody 16 1 16656 9244 3928 S 3 0.2 0:14.33 lsphp5
21354 nobody 15 0 78592 73m 24m S 2 1.8 10:04.34 lshttpd
4229 nobody 16 1 16652 9156 3856 S 2 0.2 0:07.52 lsphp5
5920 nobody 16 1 16288 8168 3236 S 2 0.2 0:01.70 lsphp5
6169 nobody 16 1 16248 7712 2908 S 2 0.2 0:00.06 lsphp5
5917 nobody 16 1 16628 8820 3592 S 2 0.2 0:00.62 lsphp5
6127 nobody 16 1 16232 7644 2764 S 2 0.2 0:00.17 lsphp5
5918 nobody 16 1 16256 7808 2880 S 1 0.2 0:00.83 lsphp5
6170 nobody 18 1 16216 7252 2448 S 1 0.2 0:00.04 lsphp5
6172 nobody 16 1 14800 5340 2436 R 1 0.1 0:00.04 lsphp5
Many thanks
 

anewday

Well-Known Member
#2
You should increase your max connections, max requests and, lsapi children. Seems it's too low which causes server to spawn many lsphp5 processes or children?

My lsphp settings on a not so busy server:

Max Connections: 80
PHP_LSAPI_MAX_REQUESTS=10000
PHP_LSAPI_CHILDREN=20
LSAPI_MAX_IDLE_CHILDREN=20
Instances: 4

Your values should be a LOT higher. What is Memory I/O Buffer set at?

I'll be interested hearing more on how to optimize from mistwang. :)
 
Last edited:

mistwang

LiteSpeed Staff
#3
For you system, you need to find out the real bottle neck.

It could be web server CPU bound, web server Disk I/O bound, web<-->DB server network I/O bound, DB server CPU bound, DB server disk I/O bound.


From the information you provided, the web server is not CPU bound, so adding faster CPU or more CPU may not help much. CPU idle time is still very high. You can try increase "Max Connection" and " PHP_LSAPI_CHILDREN" to "150" or "200". It will not help if the bottle neck is somewhere else.

Web server I/O wait is not very high, so web server is not disk I/O bound.

Do you have a dedicate Gb connection between you DB and Web Server? check your netstats on DB server with command "netstat -s", pay attention to "Tcp segments retransmited".

Check the I/O wait of your DB server during busy hour, if I/O wait is higher than 15%, you should add more memory.

Usually, the bottle neck of a busy vB forum is the MySQL DB, it uses table level lock, if one slow query lock a important table for long time, all other SQL querys are blocked. There is no good solution if the code is designed to do so.

Pay attention to the "Request per second" of lsphp5 external app in LSWS real-time report. If all of sudden it becomes very low, you may have a DB server problem, because PHP is waiting for the DB queries.
 
#4
Thanks for such a thorough reply.

I increased those variables and restarted. A few minutes later load was hitting 20+ on the web server, remaining at 1-3 on the database server. I outputted some top stats during the spike for both web and db:

Web:

top - 14:17:23 up 15 days, 19:17, 1 user, load average: 13.75, 11.18, 7.97
Tasks: 253 total, 5 running, 247 sleeping, 1 stopped, 0 zombie
Cpu(s): 3.7% us, 4.7% sy, 36.1% ni, 16.4% id, 37.7% wa, 0.1% hi, 1.3% si
Mem: 4137244k total, 2762204k used, 1375040k free, 671796k buffers
Swap: 4096532k total, 144k used, 4096388k free, 1222080k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
11584 root 18 0 28312 23m 644 D 11 0.6 0:20.68 lfd
12821 nobody 16 1 16256 7832 2904 S 6 0.2 0:00.30 lsphp5
12877 nobody 17 1 16344 8036 3100 R 5 0.2 0:00.46 lsphp5
12783 nobody 16 1 16236 7656 2840 S 5 0.2 0:00.29 lsphp5
12918 nobody 17 1 16000 7468 2632 R 5 0.2 0:00.14 lsphp5
12822 nobody 16 1 16520 8248 3124 S 4 0.2 0:00.27 lsphp5
12838 nobody 16 1 16236 7760 2856 S 4 0.2 0:00.20 lsphp5
12911 nobody 16 1 16236 7728 2820 S 4 0.2 0:00.18 lsphp5
12858 nobody 16 1 16280 8072 3148 S 4 0.2 0:00.29 lsphp5
2284 root 0 -20 0 0 0 D 3 0.0 38:06.43 loop0
12734 nobody 17 1 13404 4900 2876 S 3 0.1 0:00.59 lsphp5
12798 nobody 16 1 13408 5200 3192 S 3 0.1 0:00.15 lsphp5
12880 nobody 16 1 13376 4828 2832 S 3 0.1 0:00.16 lsphp5
12901 nobody 16 1 13392 4828 2824 S 3 0.1 0:00.16 lsphp5
12772 nobody 16 1 13392 4836 2828 S 3 0.1 0:00.18 lsphp5
12784 nobody 16 1 13392 4836 2828 S 3 0.1 0:00.19 lsphp5
12841 nobody 17 1 13420 5212 3192 S 3 0.1 0:00.23 lsphp5
12856 nobody 16 1 16236 7672 2844 S 3 0.2 0:00.29 lsphp5
12862 nobody 16 1 18040 9556 2836 S 3 0.2 0:00.24 lsphp5
12835 nobody 17 1 13392 4832 2824 S 3 0.1 0:00.16 lsphp5
12743 nobody 16 1 18040 9604 2924 S 2 0.2 0:00.42 lsphp5
12744 nobody 16 1 13456 5368 3232 S 2 0.1 0:00.56 lsphp5
12762 nobody 16 1 16496 7984 2864 S 2 0.2 0:00.57 lsphp5
12794 nobody 16 1 16496 7884 2772 S 2 0.2 0:00.13 lsphp5
12796 nobody 16 1 16496 7988 2868 S 2 0.2 0:00.37 lsphp5
12816 nobody 16 1 17048 8484 2800 S 2 0.2 0:00.13 lsphp5
12818 nobody 16 1 13392 5100 3092 S 2 0.1 0:00.31 lsphp5
12828 nobody 16 1 13384 4852 2852 S 2 0.1 0:00.25 lsphp5
12891 nobody 16 1 16236 7736 2828 S 2 0.2 0:00.23 lsphp5
12721 nobody 16 1 16256 7824 2908 S 2 0.2 0:00.59 lsphp5
12736 nobody 16 1 16284 8048 3184 S 2 0.2 0:00.64 lsphp5
12738 nobody 16 1 16236 7756 2900 S 2 0.2 0:00.59 lsphp5
12742 nobody 17 1 16512 8004 2876 S 2 0.2 0:00.55 lsphp5
12757 nobody 16 1 16260 7676 2796 S 2 0.2 0:00.43 lsphp5
12779 nobody 16 1 16220 7720 2832 S 2 0.2 0:00.20 lsphp5
12788 nobody 16 1 16236 7744 2840 S 2 0.2 0:00.29 lsphp5
12811 nobody 16 1 16256 7764 2888 S 2 0.2 0:00.35 lsphp5
12839 nobody 16 1 16236 7628 2828 S 2 0.2 0:00.24 lsphp5
12888 nobody 16 1 16248 7940 3068 S 2 0.2 0:00.15 lsphp5
12720 nobody 17 1 16252 8012 3156 S 2 0.2 0:00.63 lsphp5
12732 nobody 17 1 16276 7936 3108 S 2 0.2 0:00.16 lsphp5
12756 nobody 16 1 18072 9192 2776 S 2 0.2 0:00.27 lsphp5
12797 nobody 16 1 16236 7736 2828 S 2 0.2 0:00.13 lsphp5
12799 nobody 16 1 16236 7720 2816 S 2 0.2 0:00.14 lsphp5
12809 nobody 16 1 16236 7656 2756 S 2 0.2 0:00.14 lsphp5
12829 nobody 16 1 16268 8116 3204 S 2 0.2 0:00.18 lsphp5
DB:

top - 14:17:04 up 15 days, 19:23, 1 user, load average: 1.74, 2.68, 2.63
Tasks: 96 total, 1 running, 95 sleeping, 0 stopped, 0 zombie
Cpu(s): 9.5% us, 3.7% sy, 0.0% ni, 86.8% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 4148796k total, 3983148k used, 165648k free, 133036k buffers
Swap: 4096564k total, 208k used, 4096356k free, 1602724k cached

PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
21511 mysql 15 0 105 16580:44 7.8 689m 315m 4344 S mysqld
14697 apache 15 0 0 0:00.29 0.2 24800 9676 3264 S httpd
14698 apache 15 0 0 0:00.21 0.2 24476 9436 3348 S httpd
14692 apache 15 0 0 0:00.20 0.2 24220 9104 3260 S httpd
14696 apache 15 0 0 0:00.16 0.2 24208 9056 3280 S httpd
14695 apache 15 0 0 0:00.12 0.2 23556 8280 3088 S httpd
14693 apache 15 0 0 0:00.09 0.2 23368 8076 3064 S httpd
14699 apache 15 0 0 0:00.04 0.2 22780 7124 2768 S httpd
14723 apache 15 0 0 0:00.03 0.2 22780 7120 2764 S httpd
Here's the result of netstat. To answer your question, the server does have a dedicated GB connection.

Tcp:
968 active connections openings
17112731 passive connection openings
0 failed connection attempts
321 connection resets received
108 connections established
1127089895 segments received
1776855450 segments send out
6236 segments retransmited
0 bad segments received.
6497 resets sent
I monitored requests per second and got the following over a few minutes:

Requests in Processing | Request/Second
245 151
128 277
56 284
86 178

I'm totally lost :(

Thanks for your help, it is appreciated.

Edit: The load again spiked a little, but wait io was low...

top - 14:48:28 up 15 days, 19:48, 1 user, load average: 9.02, 7.92, 6.28
Tasks: 179 total, 3 running, 173 sleeping, 3 stopped, 0 zombie
Cpu(s): 0.8% us, 3.5% sy, 31.9% ni, 60.4% id, 2.0% wa, 0.1% hi, 1.3% si
Mem: 4137244k total, 3480576k used, 656668k free, 668452k buffers
Swap: 4096532k total, 144k used, 4096388k free, 2195688k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
21394 nobody 16 1 13684 5620 3248 S 23 0.1 0:01.37 lsphp5
21401 nobody 16 1 16536 8204 2952 S 19 0.2 0:02.12 lsphp5
21411 nobody 16 1 16788 8496 3216 S 17 0.2 0:02.03 lsphp5
17263 nobody 16 1 18444 10m 3732 S 16 0.3 0:15.41 lsphp5
21351 nobody 16 1 16288 8184 3236 S 10 0.2 0:03.13 lsphp5
20959 nobody 16 1 16816 9124 3740 S 10 0.2 0:05.76 lsphp5
21443 nobody 16 1 16236 7620 2828 S 6 0.2 0:00.19 lsphp5
21441 nobody 16 1 18116 9488 2784 S 6 0.2 0:00.18 lsphp5
12713 nobody 15 0 86528 81m 40m S 6 2.0 1:14.03 lshttpd
21125 nobody 16 1 16528 8300 3200 S 5 0.2 0:03.87 lsphp5
21442 nobody 16 1 16236 7720 2828 S 5 0.2 0:00.16 lsphp5
21144 nobody 16 1 16264 8136 3228 S 5 0.2 0:02.24 lsphp5
14380 nobody 16 1 18448 10m 3940 S 3 0.3 0:18.71 lsphp5
21092 nobody 16 1 18064 9876 3240 S 2 0.2 0:01.44 lsphp5
21444 nobody 16 1 18020 9332 2668 S 2 0.2 0:00.07 lsphp5
21447 nobody 17 1 13372 4600 2696 S 2 0.1 0:00.06 lsphp5
21448 nobody 17 1 16476 7704 2628 S 2 0.2 0:00.06 lsphp5
20910 nobody 16 1 16336 8192 3252 S 2 0.2 0:03.39 lsphp5
21446 nobody 17 1 16216 7240 2448 S 1 0.2 0:00.04 lsphp5
21450 nobody 16 1 14668 5400 2084 R 1 0.1 0:00.02 lsphp5
3215 named 25 0 56148 3336 1900 S 0 0.1 8:33.53 named
21198 root 15 0 2096 1028 764 R 0 0.0 0:00.12 top
 
Last edited:

mistwang

LiteSpeed Staff
#5
The I/O wait is too high in the first top output. You need to find out what causes it.
Have the CPU utilization of mysqld ever been higher than 200%? I am not sure your MySQL takes advantage of all CPU available on your DB server. If the maximum CPU utilzation is around 100%, then it only uses one CPU and it is maxed out.
 

Matthew_K

Active Member
#6
I have a question about this as well. My site will be very high load (About 500K-1Mill uniques/mo). I have LSLB and 2 LSWS servers.

All three are running 4GB Mem and 2xDual Core Xeons.

Are these good settings for a mostly dynamic site?

Max Connections: 1000
PHP_LSAPI_MAX_REQUESTS=50000
PHP_LSAPI_CHILDREN=20
LSAPI_MAX_IDLE_CHILDREN=20
Instances: 10

Thanks,
Matt.
 

mistwang

LiteSpeed Staff
#7
Max Connections: 200
PHP_LSAPI_MAX_REQUESTS=50000
PHP_LSAPI_CHILDREN=200
LSAPI_MAX_IDLE_CHILDREN=60
Instances: 1

start with above configuration.
Either "Intances" and "PHP_LSAPI_CHILDREN" should match "Max connections". It is not recommended to use "Instances" and "PHP_LSAPI_CHIDLREN" together. For single large web site, "Instances=1" is recommended.
 

anewday

Well-Known Member
#9
Max Connections: 200
PHP_LSAPI_MAX_REQUESTS=50000
PHP_LSAPI_CHILDREN=200
LSAPI_MAX_IDLE_CHILDREN=60
Instances: 1

start with above configuration.
Either "Intances" and "PHP_LSAPI_CHILDREN" should match "Max connections". It is not recommended to use "Instances" and "PHP_LSAPI_CHIDLREN" together. For single large web site, "Instances=1" is recommended.
Does this apply for most cases? The default config has both together.
 
Last edited:

PSS

Well-Known Member
#10
Try also:

Virtual host:
Smart Keep-Alive: Yes

Server:
I/O Event Dispatcher: epoll (Linux 2.6 kernel)
Use sendfile(): Yes

And the most important: use mysql slow query log and see what is locking the tables - which is the usual reason for slow page loads. Also, you need to have enough RAM to hold indexes you have set.

what is your mysql SHOW GLOBAL STATUS:
Max_used_connections
Slow_queries
Threads_created

Software/database optimization is number one, no web server can help bad code.

Placing database on another server is a common method to speed things up. But it seldom does - faster dedicated db disk, some db tuning and my.cnf tuning plus more RAM helps most!

If you run vbulletin let me know, I can assist a bit with optimizing that.
 
#12
I'm still struggling with my situation. I've just upgraded my web server to a Quad-core Clovertown (2-CPU), but although the load seems lower it's made no difference to speed. The site will intermittently load pages extremely slow as before. I've tried altering the lsphp5 variables (max connections=150 or 200 etc), but whatever I do seems to make the slowness worse, yet the server load seems to go down whenever I increase anything.

Looking at real-time stats in the ls admin section I notice the slowness is at it's worst when lsphp5 has anything in the WaitQ (although it can still be slow, though not as bad, when nothing is in wait queue):

Right now its:

Name: lsphp5
Max: 100
In use: 100
WaitQ: 165

Why could this be? Despite the high I/O wait in some of my top outputs, it remains around 1-4% most of the time so it's not a disk issue. My DC has checked RAID and other possible causes, but everything seems to be ok. The database server doesn't "appear" to be the problem. It doesn't use any swap and IO is constantly 0%.

Is there some limiting factor in my configuration of litespeed, possibly?

Thanks for your help.
 
Last edited:

mistwang

LiteSpeed Staff
#13
What is connection between mysql and web server? It should be private Gb link.
have you checked packet retransmission on your DB server? it should be very close to 0.0000%
 

mistwang

LiteSpeed Staff
#15
OK. Just make sure they do not rate limiting the Gb port if a switch is used instead of a cross over cable.

Is your site custom coded or some standard package like vB?

Have you installed a PHP opcode cache? Is the PHP code and SQL optimized? Has MySQL being optimized? turn on thread cache and slow query cache, etc.

Maybe you can show us the MySQL extended status.

LiteSpeed cannot cure the bad code. ;)
 

PSS

Well-Known Member
#16
Without knowing what kind of software you run and what kind of database you have it is quite hard to say anything meaningful. When Litespeed renders a page with php, phplsapi calls mysql with a php function and that process needs to be on until mysql returns a result. When one script locks tables (MYISAM), all other scripts need to wait for those locks to be released. If your database is slow, indexes can not be used effectively, queries are unoptimized -- it will lead to more php child processes tied to mysql processes.

Install phpmyadmin and see "Processes", is it a long list? See "Show MySQL runtime information": what's your "max. concurrent connections"? What is your my.cnf max_connections?

Are you mysql indexes active? Often after a dump you will need to do repair table to give indexes a kickstart.

How much is your combined index size from all databases in use? Does it fit to my.cnf key_buffer_size? I've put below a script which I coded to count index sizes.

Fo you have query cache on? What size is it? Very large query cache may slow down page load time because its constant management in RAM is inefficient.

Does your thread_concurrency match number of CPU cores?

Do you have long_query_time and log-slow-queries set? Which are the slow queries and is there anything you can do to improve them?

For very good MySQL tuning info, see Peter Zaitsev's site http://www.mysqlperformanceblog.com, start with document

http://www.mysqlperformanceblog.com/files/presentations/UC2007-MySQL-Server-Settings-Tuning.pdf


---

Below is a php script to show index sizes of all databases for a user (among other things).

Mysql user name and pass are on line 12 or so.

Code:
<?php
// database viewer and total index counter. Code by Pekka Saarinen 2007 http://photography-on-the.net
// parts of code borrowed from Exhibit Engine 2 (by permission :) ).

error_reporting(255);


if (!function_exists("mysql_connect")) {
	print "<p>FATAL ERROR: MySQL support not installed for PHP!";
}

$servername = "localhost";
$username	= "mysql_user_name";
$password	= "mysql_user_pass";



$ee_mysql_connection = @mysql_connect($servername, $username, $password); 

if (!$ee_mysql_connection)  {
	print "cannot connect";
	exit();  
}

?>
<html><body>
<title>count</title>
<style type="text/css" media="screen">				 <!--
				 SAMP {
				 	font-style: normal;
				 }
				 IMG {
				 	text-decoration: none;
				 	color: #000000;
				 }
				 A {
				 	text-decoration: none;
					color: #0000ff;
				 }
				 
				 A:hover {
				 	text-decoration: underline;
				 	color: #007700;
				 }
				 
				 BIG {
				 	font-size: 18px;
				 	font-style : normal;
				 	font-family: arial,helvetica,sans-serif;
				 	font-weight : bold;
				 }

				 H2 {
				 	font-size: 19px;
				 	font-style : normal;
				 	font-family: arial,helvetica,sans-serif;
				 }
				 
				 H2 A {
				 	font-size: 19px;
				 	font-style : normal;
				 	font-family: arial,helvetica,sans-serif;
				 	color: #000000;
				 }
				 
				 P,LI,UL,FORM,TD,BLOCKQUOTE {
				 	font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;
				 	font-size: 9px;
				 }

				 option {
				 	font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;
				 	font-size: 10px;
					background-color : #fcf7ed;
				 }

				 SELECT {
				 	font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;
				 	font-size: 10px;
					background-color : #eeeeee;
				 }
				 
				 INPUT,TEXTAREA {
				 	font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;
				 	font-size: 10px;
				 }
				 
				 FORM {
				 	margin-bottom : 0px;
				 	margin-top : 0px;
				 }

				 BODY {
				 	background: #ffffff;
				 	color: #000000;
				 	margin-left : 10px;
				 	margin-top : 10px;
				 	margin-right : 10px;
				 	margin-bottom : 10px;
				 	font-family: arial,helvetica,sans-serif;
				 	font-size: 11px;
				 	background-image : none;
				 	background-position : right;
				 	background-repeat : no-repeat;
				 	background-attachment : fixed;
				 	background-color : White;
				 }

				 -->
</style>
<?php

function ee_humanfilesize_accurate($size,$dec=2,$disp=1) {
// function originated from http://fi.php.net/manual/en/function.filesize.php
    $kb = 1024;
    $mb = 1024 * $kb;
    $gb = 1024 * $mb;
    $tb = 1024 * $gb;
	
    if ($size < $mb) {
	if ($disp == 1) {
		$abbr = " KB";
	}
        return round($size/$kb,$dec)."{$abbr}";
    }
    else {
	if ($disp == 1) {
		$abbr = " MB";
	}
        return round($size/$mb,$dec)."{$abbr}";
    }
return;
}


$total_datasize_all = 0;
$total_indexsize_all = 0;
$table_count_all = 0;

$ee_main_database_list = array();

$show = mysql_query("
SHOW DATABASES
"); 
	if ($show) {   
		while ($showstats = mysql_fetch_array($show)) {
			$ee_main_database_list[] = $showstats[0];
		}
	}






foreach ($ee_main_database_list as $key => $databasename) {

	if ($databasename == "mysql") {
		continue;
	}
	
	if ($databasename == "information_schema") {
		continue;
	}
	
	mysql_select_db($databasename);
	
	print "<p><big>{$databasename}</big></p>";
	
	$ee_main_table_list = array();
	
	$show = mysql_query("
	SHOW TABLES
	"); 
		if ($show) {   
			while ($showstat = mysql_fetch_array($show)) {
				$ee_main_table_list[] = $showstat[0];
			}
		}
	
	$table_count = count($ee_main_table_list);
	$table_count_all = $table_count + $table_count_all;
	
	print "<table border=\"0\" cellpadding=\"1\" cellspacing=\"0\"><tr><td nowrap><b>Table</b></td><td>&nbsp;</td><td nowrap><b>Rows</b></td><td>&nbsp;</td><td nowrap><b>Data length</b></td><td>&nbsp;</td><td nowrap><b>Index lenght</b></td><td>&nbsp;</td><td nowrap><b>Total Lenght</b></td><td>&nbsp;</td><td nowrap><b>Overhead</b></td><td>&nbsp;</td><td nowrap><b>Last Updated</b></td><td>&nbsp;</td><td nowrap><b>Status</b></td><td>&nbsp;</td><td nowrap><b>Comment</b></td>";
		
		$rowcolor = 0;
		$total_datasize = 0;
		$total_indexsize = 0;
		$total_backupsize = 0;
		$columns_list = array();
		foreach ($ee_main_table_list as $key => $k) {
		
			$bgcolor = "#cdddde";
			($rowcolor % 2)  ? (0) : ($bgcolor = "#ffffff");
			
			$checked_status = "";
			$totalsize = 0;
			$table_rows = 0;
			$table_bytesize = 0;
			$index_bytesize = 0;
			$table_comment = "";
			$data_free = 0;
			$mismatch = FALSE;
			$last_updated = "n/a";
			$status = mysql_query("
				SHOW TABLE STATUS FROM `{$databasename}` LIKE '{$k}'
				"); 
			if ($status) {   
				while ($stat = mysql_fetch_array($status)) {
					$table_rows = @$stat["Rows"];
					$table_bytesize = @$stat["Data_length"];
					$index_bytesize = @$stat["Index_length"];
					$last_updated = @$stat["Update_time"];
					$table_comment = @$stat["Comment"];
					$data_free = @$stat["Data_free"];
					$totalsize =   $table_bytesize+$index_bytesize;
				}
			}
			if (!$status) {   
				$totalsize = 0;
			}
			
			$checked_status = "---";

			/* remove for table status check
			$check = mysql_query("
				CHECK TABLE {$k}
				"); 
			if ($check) {   
				while ($checkstat = mysql_fetch_array($check)) {
					$Msg_type = $checkstat["Msg_type"];
					$Msg_text = $checkstat["Msg_text"];
				}
				$checked_status = $Msg_type . ": " . $Msg_text;
			}
			if (!$check) {   
				$checked_status = "---";
			}
			remove for table status check */

			
			$total_datasize = $total_datasize+$table_bytesize;
			$total_indexsize = $total_indexsize+$index_bytesize;
			
			print "<tr bgcolor=\"{$bgcolor}\"><td nowrap><b>";
					print $k;
			print "</b></td><td>&nbsp;</td><td nowrap>";
					print $table_rows;
			print "</td><td>&nbsp;</td><td nowrap>";
					print ee_humanfilesize_accurate(intval($table_bytesize));
			print "</td><td>&nbsp;</td><td nowrap>";
					print ee_humanfilesize_accurate(intval($index_bytesize));
			print "</td><td>&nbsp;</td><td nowrap>";
					print ee_humanfilesize_accurate(intval($totalsize));
			print "</td><td>&nbsp;</td><td nowrap>";
					print ee_humanfilesize_accurate(intval($data_free));
			print "</td><td>&nbsp;</td><td nowrap>";
					print $last_updated;
			print "</td><td>&nbsp;</td><td nowrap>";
					print $checked_status;
			print "</td><td>&nbsp;</td><td nowrap>";
					print $table_comment;
			print "&nbsp;&nbsp;</td>";
			
			$rowcolor++;
		}
	
		?>
		</tr></table></td>
		
		</tr></table>
		<?php
		
		$total_datasize_all = $total_datasize + $total_datasize_all;
		$total_indexsize_all = $total_indexsize + $total_indexsize_all;
		print "<br><b>TOTALS</b>";
		print "<br>Table data size: " . ee_humanfilesize_accurate(intval($total_datasize));
		print "<br>Index (key) size: " . ee_humanfilesize_accurate(intval($total_indexsize));
		
		?>
		
		</td></tr></table>
	<br><br><br>

	<?php

}

$indexes = 0;
$indexes = intval($total_indexsize_all);

print "<hr><big>TOTALS</big>";
	print "<br>Table data size: " . ee_humanfilesize_accurate(intval($total_datasize_all));
	print "<br>Index (key) size: " . ee_humanfilesize_accurate($indexes,2);
	print "<br>Tables: " . $table_count_all;
	print "<span style=\"color:#ff0000;\">";
	print "<br><br><strong>Recommendations for my.cnf:</strong> ";
	print "<br>table_cache: " . $table_count_all*8 . " + headroom";
	print "<br>key_buffer_size: " . ee_humanfilesize_accurate($indexes,0,0) . "M + headroom";
	print "</span>";
	
print "<hr><br><br><br><br><br>";

?>

</body></html>
 
Last edited:
Top