| Server Load
server load cgi mysql |
burstweb
msg:3587300 | 5:25 pm on Feb 28, 2008 (gmt 0) |
We are running Perl CGI scripts which query our MySQL database. We are running into a problem with our server load and things running quite slow. While monitoring using 'top' i've found it doesn't go much higher then 25% idle and under 10% idle quite often. We have an average of 5-15 users on accessing the site at one time and they are all going to be running at least one CGI script that will query the database. There are times when one page will load up 2-4 CGI scipts which all have at least one select query. Here is how I open a connection and query the database. use DBI; $dsn = 'DBI:mysql:database:localhost';
$db_user_name = 'username';
$db_password = 'password';
$dbh = DBI->connect($dsn, $db_user_name, $db_password); $sth = $dbh->prepare("select a,b,c,d,e,f from table where e = '$value'");
$sth->execute();
while (($a,$b,$c,$d,$e,$f) = $sth->fetchrow_array())
{
if (!$f)
{
push(@randoms,"$a\¦$b\¦$c\¦$d\¦$e");
}
}
$sth->finish(); Here are a few examples from 'top' when the server is running under a high load Any help or suggestions on how to ease up on the server load is greatly appreciated! Thanks,
David [edited by: encyclo at 8:41 pm (utc) on Mar. 1, 2008]
[edit reason] no URLs thanks [/edit]
|
burstweb
msg:3589345 | 4:08 pm on Mar 2, 2008 (gmt 0) |
Here are some snap shots of top. 11:11:15 up 16:25, 2 users, load average: 11.53, 11.70, 12.68
163 processes: 139 sleeping, 12 running, 1 zombie, 11 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 68.3% 0.0% 28.4% 0.0% 0.0% 0.0% 3.2%
cpu00 53.6% 0.0% 46.3% 0.0% 0.0% 0.0% 0.0%
cpu01 76.3% 0.0% 18.2% 0.0% 0.0% 0.0% 5.4%
cpu02 64.5% 0.0% 29.0% 0.0% 0.0% 0.0% 6.3%
cpu03 78.9% 0.0% 21.0% 0.0% 0.0% 0.0% 0.0%
Mem: 3493764k av, 3435252k used, 58512k free, 0k shrd, 279324k buff
2893168k active, 269612k inactive
Swap: 4096532k av, 253224k used, 3843308k free 678172k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
1822 mysql 16 0 63028 29M 3456 R 3.2 0.8 0:01 1 mysqld
3411 user 23 0 19084 10M 5696 R 2.9 0.3 0:00 2 scriptD.cgi
1821 user 16 0 54148 49M 1652 S 2.6 1.4 0:01 1 scriptA.cgi
3413 mysql 19 0 63028 29M 3456 S 1.5 0.8 0:00 2 mysqld
3412 mysql 19 0 63028 29M 3456 R 1.3 0.8 0:00 0 mysqld
3497 mysql 17 0 63028 29M 3456 R 0.9 0.8 0:00 0 mysqld
1948 mysql 17 0 63028 29M 3456 S 0.8 0.8 0:00 0 mysqld
3393 user 16 0 13012 8940 1656 S 0.6 0.2 0:00 2 scriptA.cgi
3397 mysql 17 0 63028 29M 3456 R 0.6 0.8 0:00 0 mysqld
3432 mysql 17 0 63028 29M 3456 R 0.6 0.8 0:00 1 mysqld
3518 user 19 0 8416 4356 1628 S 0.5 0.1 0:00 1 scriptA.cgi
3395 mysql 17 0 63028 29M 3456 R 0.4 0.8 0:00 0 mysqld
3425 user 15 0 7060 4356 1628 S 0.4 0.1 0:00 0 scriptA.cgi
3427 user 15 0 7540 4236 1624 S 0.4 0.1 0:00 0 scriptE.cgi
3434 user 18 0 8108 4360 1628 S 0.4 0.1 0:00 3 scriptA.cgi
3454 user 15 0 7960 4364 1628 S 0.4 0.1 0:00 0 scriptA.cgi
3505 user 18 0 7988 4360 1628 S 0.4 0.1 0:00 1 scriptA.cgi
3506 user 16 0 7000 4292 1592 S 0.4 0.1 0:00 2 scriptB.cgi
3507 user 18 0 7040 4368 1628 S 0.4 0.1 0:00 1 scriptA.cgi
3503 user 17 0 7772 3540 1584 S 0.3 0.1 0:00 1 scriptH.cgi
3540 user 21 0 7800 3988 1592 S 0.3 0.1 0:00 1 scriptE.cgi
26157 root 16 0 2104 1184 748 S 0.2 0.0 0:31 2 top
1810 root 16 0 1972 1040 748 R 0.2 0.0 0:00 3 top
3410 user 18 0 7520 4364 1628 S 0.2 0.1 0:00 0 scriptA.cgi
3865 nobody 16 0 5988 2892 1456 S 0.1 0.0 2:23 2 httpd
32022 nobody 15 0 5532 2020 976 S 0.1 0.0 0:00 1 httpd
1978 nobody 16 0 5532 2016 972 S 0.1 0.0 0:00 1 httpd
1 root 16 0 1412 476 424 S 0.0 0.0 0:00 3 init
11849 root 16 0 1480 504 424 S 0.0 0.0 0:00 0 syslogd
11861 root 17 0 1404 348 296 S 0.0 0.0 0:00 1 courierlogger
11862 root 16 0 1732 544 460 S 0.0 0.0 0:00 1 authdaemond
11883 root 16 0 1732 320 228 S 0.0 0.0 0:00 2 authdaemond
11884 root 16 0 1732 320 228 S 0.0 0.0 0:00 3 authdaemond
11885 root 16 0 1732 320 228 S 0.0 0.0 0:00 2 authdaemond
11886 root 15 0 1732 320 228 S 0.0 0.0 0:00 0 authdaemond
11888 root 16 0 1732 320 228 S 0.0 0.0 0:00 3 authdaemond
11899 root 16 0 3532 920 740 S 0.0 0.0 0:00 2 sshd
11909 root 18 0 2012 708 604 S 0.0 0.0 0:00 0 xinetd
11974 root 15 0 5540 4016 1188 S 0.0 0.1 0:01 1 chkservd
|
burstweb
msg:3589347 | 4:09 pm on Mar 2, 2008 (gmt 0) |
11:28:54 up 16:43, 2 users, load average: 13.41, 14.01, 13.48
79 processes: 56 sleeping, 11 running, 1 zombie, 11 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 79.7% 0.0% 16.3% 0.0% 0.0% 1.0% 2.8%
cpu00 80.0% 0.0% 15.8% 0.0% 0.0% 0.0% 4.1%
cpu01 80.8% 0.0% 14.7% 0.0% 0.0% 0.7% 3.6%
cpu02 81.0% 0.0% 17.5% 0.0% 0.0% 0.0% 1.4%
cpu03 77.4% 0.0% 16.9% 0.0% 0.0% 4.2% 1.4%
Mem: 3493764k av, 3225256k used, 268508k free, 0k shrd, 250172k buff
2743224k active, 253264k inactive
Swap: 4096532k av, 253240k used, 3843292k free 712436k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
30667 user 18 0 0 0 0 Z 0.4 0.0 0:00 0 scriptA.cgi <defunct>
13861 nobody 16 0 5532 2028 984 S 0.2 0.0 0:01 3 httpd
3866 nobody 16 0 5952 2860 1460 S 0.1 0.0 2:30 3 httpd
3959 nobody 15 0 5852 2848 1452 S 0.1 0.0 2:22 2 httpd
3967 nobody 15 0 5856 2864 1456 S 0.1 0.0 2:10 0 httpd
3968 nobody 15 0 5856 2856 1452 S 0.1 0.0 2:27 2 httpd
24241 nobody 16 0 5848 2824 1448 S 0.1 0.0 0:29 3 httpd
6083 nobody 16 0 5988 2872 1448 R 0.1 0.0 0:12 3 httpd
22481 nobody 16 0 5984 2456 1044 S 0.1 0.0 0:10 3 httpd
26157 root 16 0 2104 1184 748 S 0.1 0.0 0:43 2 top
28478 nobody 15 0 5536 2052 996 S 0.1 0.0 0:02 3 httpd
32022 nobody 16 0 5536 2048 992 S 0.1 0.0 0:01 3 httpd
1810 root 16 0 1972 1116 748 R 0.1 0.0 0:12 3 top
11446 nobody 16 0 5844 2412 1044 S 0.1 0.0 0:01 1 httpd
24420 nobody 15 0 5708 2168 1124 S 0.1 0.0 0:00 1 httpd
30660 user 18 0 6160 3004 1276 R 0.1 0.0 0:00 3 scriptA.cgi
30670 user 18 0 4832 2516 1212 R 0.1 0.0 0:00 2 scriptA.cgi
30671 user 18 0 4512 2608 1188 R 0.1 0.0 0:00 2 scriptB.cgi
1 root 15 0 1412 484 428 S 0.0 0.0 0:00 1 init
11974 root 15 0 5540 4016 1188 S 0.0 0.1 0:01 3 chkservd
12264 root 16 0 5736 1264 988 S 0.0 0.0 0:00 1 pure-ftpd
12267 root 16 0 3432 892 712 S 0.0 0.0 0:00 3 pure-authd
13470 root 18 0 13828 7992 504 S 0.0 0.2 0:00 2 cpdavd
13835 root 15 0 5968 3972 1120 S 0.0 0.1 0:00 3 cpbandwd
13836 root 39 19 13896 11M 1752 S N 0.0 0.3 0:09 0 cpanellogd
3704 root 16 0 5120 2324 1704 S 0.0 0.0 0:03 3 httpd
3864 root 16 0 7136 4856 1680 S 0.0 0.1 0:00 2 leechprotect
3865 nobody 15 0 5988 2892 1456 S 0.0 0.0 2:25 0 httpd
3867 nobody 15 0 5952 2860 1456 S 0.0 0.0 2:26 1 httpd
3868 nobody 16 0 5844 2848 1456 S 0.0 0.0 2:25 3 httpd
3869 nobody 15 0 5952 2856 1456 S 0.0 0.0 2:16 1 httpd
3948 nobody 16 0 5848 2848 1452 S 0.0 0.0 2:19 0 httpd
3958 nobody 15 0 6000 2876 1452 S 0.0 0.0 2:15 1 httpd
15756 nobody 15 0 5848 2840 1452 S 0.0 0.0 0:59 0 httpd
21868 nobody 15 0 5844 2840 1448 S 0.0 0.0 0:45 3 httpd
32280 nobody 15 0 5956 2852 1448 S 0.0 0.0 0:34 2 httpd
7220 user 34 19 13896 10M 744 S N 0.0 0.2 0:00 0 cpanellogd
7221 user 34 19 1392 360 320 S N 0.0 0.0 0:00 0 logrunner
7222 user 34 19 4128 2028 872 T N 0.0 0.0 0:17 3 webalizer
|
burstweb
msg:3589348 | 4:09 pm on Mar 2, 2008 (gmt 0) |
11:12:47 up 16:27, 2 users, load average: 11.38, 11.58, 12.54
163 processes: 137 sleeping, 12 running, 3 zombie, 11 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 57.5% 0.0% 39.6% 0.0% 0.0% 0.0% 2.8%
cpu00 52.5% 0.0% 45.9% 0.0% 0.0% 0.0% 1.4%
cpu01 47.3% 0.0% 47.3% 0.0% 0.0% 0.0% 5.2%
cpu02 70.1% 0.0% 29.1% 0.0% 0.0% 0.0% 0.7%
cpu03 62.2% 0.0% 34.9% 0.0% 0.0% 0.0% 2.7%
Mem: 3493764k av, 3388252k used, 105512k free, 0k shrd, 280424k buff
2849248k active, 281484k inactive
Swap: 4096532k av, 253224k used, 3843308k free 670592k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
5223 mysql 16 0 62900 30M 3456 S 1.7 0.8 0:00 1 mysqld
5243 mysql 16 0 62900 30M 3456 S 1.3 0.8 0:00 3 mysqld
5228 mysql 15 0 62900 30M 3456 S 1.2 0.8 0:00 3 mysqld
5237 mysql 15 0 62900 30M 3456 S 1.2 0.8 0:00 1 mysqld
5245 mysql 15 0 62900 30M 3456 S 1.2 0.8 0:00 3 mysqld
5261 mysql 19 0 62900 30M 3456 R 1.1 0.8 0:00 0 mysqld
5258 mysql 17 0 62900 30M 3456 R 0.8 0.8 0:00 1 mysqld
5248 mysql 17 0 62900 30M 3456 R 0.6 0.8 0:00 3 mysqld
5256 mysql 17 0 62900 30M 3456 R 0.6 0.8 0:00 1 mysqld
5250 mysql 17 0 62900 30M 3456 R 0.4 0.8 0:00 3 mysqld
5251 mysql 17 0 62900 30M 3456 R 0.4 0.8 0:00 3 mysqld
5254 mysql 17 0 62900 30M 3456 R 0.4 0.8 0:00 1 mysqld
1631 mysql 16 0 62704 29M 3456 S 0.0 0.8 0:05 0 mysqld
1632 mysql 16 0 62704 29M 3456 S 0.0 0.8 0:06 0 mysqld
1633 mysql 20 0 62704 29M 3456 S 0.0 0.8 0:00 1 mysqld
1634 mysql 16 0 62704 29M 3456 S 0.0 0.8 0:00 1 mysqld
1635 mysql 16 0 62704 29M 3456 S 0.0 0.8 0:00 1 mysqld
1636 mysql 20 0 62704 29M 3456 S 0.0 0.8 0:00 1 mysqld
1637 mysql 16 0 62704 29M 3456 S 0.0 0.8 0:02 0 mysqld
1638 mysql 16 0 62704 29M 3456 S 0.0 0.8 0:01 2 mysqld
1639 mysql 16 0 62704 29M 3456 S 0.0 0.8 0:00 0 mysqld
1640 mysql 16 0 62704 29M 3456 S 0.0 0.8 0:10 3 mysqld
7204 mysql 17 0 62704 29M 3456 S 0.0 0.8 0:00 0 mysqld
5259 mysql 20 0 62900 30M 3456 S 0.0 0.8 0:00 2 mysqld
5269 mysql 20 0 62900 30M 3456 S 0.0 0.8 0:00 0 mysqld
|
burstweb
msg:3589350 | 4:09 pm on Mar 2, 2008 (gmt 0) |
12:21:58 up 52 min, 1 user, load average: 12.68, 10.57, 10.97
194 processes: 164 sleeping, 22 running, 8 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 70.8% 0.0% 27.9% 0.0% 0.0% 0.5% 0.6%
cpu00 73.5% 0.0% 23.1% 0.0% 0.0% 2.6% 0.6%
cpu01 79.5% 0.0% 18.3% 0.0% 0.0% 0.0% 2.0%
cpu02 83.0% 0.0% 16.9% 0.0% 0.0% 0.0% 0.0%
cpu03 46.5% 0.0% 52.7% 0.0% 0.0% 0.0% 0.6%
Mem: 3493764k av, 3414076k used, 79688k free, 0k shrd, 218396k buff
2954624k active, 235516k inactive
Swap: 4096532k av, 253032k used, 3843500k free 708836k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
22026 user 25 0 91916 72M 68044 R 4.1 2.1 0:12 2 scriptD.cgi
24087 user 15 0 11248 8940 1656 S 0.6 0.2 0:00 3 scriptA.cgi
21521 user 18 0 32208 19M 15308 R 0.5 0.5 0:06 0 scriptE.cgi
24036 user 16 0 12124 8940 1656 S 0.3 0.2 0:00 3 scriptA.cgi
24048 user 15 0 13716 9564 1652 S 0.3 0.2 0:00 1 scriptA.cgi
24081 user 15 0 6968 4360 1628 S 0.3 0.1 0:00 3 scriptA.cgi
24082 user 16 0 7220 4364 1628 S 0.3 0.1 0:00 2 scriptA.cgi
24084 user 15 0 7524 4364 1628 S 0.3 0.1 0:00 3 scriptA.cgi
24095 user 16 0 7416 4360 1628 S 0.3 0.1 0:00 2 scriptA.cgi
24103 user 16 0 7068 4360 1628 S 0.3 0.1 0:00 0 scriptA.cgi
24108 user 18 0 8236 4364 1628 S 0.3 0.1 0:00 1 scriptA.cgi
24134 user 16 0 7864 4360 1628 S 0.3 0.1 0:00 2 scriptA.cgi
24216 user 17 0 7600 3536 1584 S 0.2 0.1 0:00 2 scriptC.cgi
24040 user 16 0 11436 8940 1656 S 0.1 0.2 0:00 3 scriptA.cgi
23960 user 16 0 11348 8936 1656 S 0.0 0.2 0:00 1 scriptA.cgi
23965 user 18 0 0 0 0 Z 0.0 0.0 0:00 3 scriptB.cgi <defunct>
23967 user 16 0 0 0 0 Z 0.0 0.0 0:00 2 scriptC.cgi <defunct>
23976 user 15 0 12256 8940 1656 S 0.0 0.2 0:00 3 scriptA.cgi
23985 user 16 0 12576 8940 1656 S 0.0 0.2 0:00 1 scriptA.cgi
23993 user 18 0 0 0 0 Z 0.0 0.0 0:00 3 scriptF.cgi <defunct>
23995 user 17 0 0 0 0 Z 0.0 0.0 0:00 2 scriptG.cgi <defunct>
23996 user 15 0 12348 8932 1652 S 0.0 0.2 0:00 3 scriptA.cgi
23997 user 16 0 12604 8932 1652 S 0.0 0.2 0:00 3 scriptA.cgi
24003 user 16 0 9872 6296 1640 S 0.0 0.1 0:00 1 scriptH.cgi
24006 user 16 0 12348 8936 1656 S 0.0 0.2 0:00 1 scriptA.cgi
24039 user 15 0 7120 3660 1592 S 0.0 0.1 0:00 1 scriptI.cgi
24047 user 18 0 0 0 0 Z 0.0 0.0 0:00 1 scriptB.cgi <defunct>
24050 user 16 0 0 0 0 Z 0.0 0.0 0:00 0 scriptC.cgi <defunct>
24059 user 15 0 9032 5416 1624 R 0.0 0.1 0:00 1 scriptJ.cgi
24061 user 16 0 7460 4360 1628 S 0.0 0.1 0:00 0 scriptA.cgi
24070 user 16 0 8532 4372 1628 S 0.0 0.1 0:00 1 scriptA.cgi
24072 user 17 0 0 0 0 Z 0.0 0.0 0:00 2 scriptB.cgi <defunct>
24073 user 16 0 7604 4364 1628 S 0.0 0.1 0:00 1 scriptA.cgi
24075 user 16 0 7024 3540 1584 S 0.0 0.1 0:00 1 scriptC.cgi
24102 user 18 0 5172 1092 872 R 0.0 0.0 0:00 3 suexec
24193 user 15 0 0 0 0 Z 0.0 0.0 0:00 2 scriptB.cgi <defunct>
|
jtara
msg:3594730 | 3:07 am on Mar 8, 2008 (gmt 0) |
First thing I'd do is stop using CGI and use mod_perl or some other solution that runs scripts in-process. ESPECIALLY if one page is going to need to use 2-4 CGI scripts! (Really? You got frames? Iframes? AJAX?) Every time a user hits a CGI script, you are starting a new process, and that is expensive resource-wise.
|
mcavic
msg:3594798 | 5:37 am on Mar 8, 2008 (gmt 0) |
Absolutely, CGI scripts are very inefficient. But aside from that, there has to be something wrong with the scripts that are running. A couple of them are using 50 to 70 megs of RAM. Maybe you're reading too much data from MySQL into memory?
|
Lexur
msg:3594855 | 7:25 am on Mar 8, 2008 (gmt 0) |
I don't know too much about MySQL but a) there's too much open processes. Can't you close the sleeping processes and those related MySQL connections. b) if you use old scripts (or some custom made) they have not the option to cache pages or DB queries Maybe is time to start thinking to change to PHP.
|
burstweb
msg:3595199 | 8:00 pm on Mar 8, 2008 (gmt 0) |
jtara -
I'm definately going to look into using mod_perl. Thanks for the tip! mcavic -
I'll look into the MySQL data load. Lexur -
I think if I converted to mod_perl that would reduce the number of processes? The scripts currently do not cache pages or DB queries and thats definately another good option i'll look into.
|
|
|
