High Load ? Something Wrong

Portal Home > Knowledgebase > Articles Database > High Load ? Something Wrong

Posted by 5need, 10-05-2007, 03:25 PM
Hello , I have 2 servers Dual Quad Core 5310 One for Mysql and the other one for Httpd I have one large website , when i put this website in one server i mean by one server is making the website work with mysql and httpd in one server no external server it's load seems to be on 1 - 5 but when i but the mysql on other server the load got high and i got high httpd connections , also when i try to see the the amount connections between the servers it's too high in one of them and low in the other and i am always suffering from load . here the details 192.168.0.1 the mysql server 192.168.0.2 httpd server [root@httpd etc]# netstat -n | grep 192.168 | wc -l 453 this the mysql server my.cnf conf [root@mysql ~]# cat /etc/my.cnf [mysqld] safe-show-database skip-name-resolve skip-innodb max_connections = 800 key_buffer = 96M myisam_sort_buffer_size = 64M join_buffer_size = 1M read_buffer_size = 1M sort_buffer_size = 2M table_cache = 2500 thread_cache_size = 384 wait_timeout = 30 connect_timeout = 10 tmp_table_size = 128M max_heap_table_size = 128M max_allowed_packet = 64M max_connect_errors = 10 thread_concurrency = 8 read_rnd_buffer_size = 1M bulk_insert_buffer_size = 8M query_cache_limit = 8M query_cache_size = 128M query_cache_type = 1 query_prealloc_size = 131072 query_alloc_block_size = 65536 [mysqld_safe] nice = -10 open_files_limit = 8192 [mysqldump] quick max_allowed_packet = 16M [myisamchk] key_buffer = 64M sort_buffer = 64M read_buffer = 16M write_buffer = 16M [mysqlhotcopy] interactive-timeout [root@mysql ~]# this the httpd.conf config Timeout 30 KeepAlive On MaxKeepAliveRequests 120 KeepAliveTimeout 1 MinSpareServers 15 MaxSpareServers 25 StartServers 15 MaxClients 256 MaxRequestsPerChild 1000 here the total connections in the mysql server [root@mysql ~]# netstat -n | grep 192.168 | wc -l 25 [root@mysql ~]# [root@mysql ~]# netstat -n | grep 192.168 tcp 0 0 192.168.0.1:3306 192.168.0.2:47876 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:48923 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:46610 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:47142 TIME_WAIT tcp 0 0 192.168.0.1:3306 192.168.0.2:49209 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:44344 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:44365 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:44359 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:46406 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:44355 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:44352 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:48217 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:47208 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:46463 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:47229 TIME_WAIT tcp 0 0 192.168.0.1:3306 192.168.0.2:46459 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:47987 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:46469 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:42371 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:47259 TIME_WAIT tcp 0 0 192.168.0.1:3306 192.168.0.2:47252 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:46480 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:47275 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:47293 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:47291 TIME_WAIT tcp 0 0 192.168.0.1:3306 192.168.0.2:47311 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:47304 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:47303 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:46530 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:49089 ESTABLISHED tcp 0 100740 192.168.0.1:3306 192.168.0.2:47318 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:47315 ESTABLISHED tcp 0 0 192.168.0.1:3306 192.168.0.2:47312 ESTABLISHED [root@mysql ~]# Do you see the high different number between connections !! I am sorry for bad english but i am really get more pain daily with the high load My server kernel Linux httpd 2.6.18-8.1.8.el5 #1 SMP Tue Jul 10 06:50:22 EDT 2007 i686 i686 i386 GNU/Linux I use CentOS 5 and httpd 1.3 and php 4.4.7 I try to upgrade them to httpd 2.0 and php 5.2.4 but most users can't browse the site so i come back to 1.3 Thanks , Zi5
Posted by 5need, 10-05-2007, 05:57 PM
root@httpd etc]#netstat -n | grep 192.168 tcp 0 0 192.168.0.2:44355 192.168.0.1:3306 ESTABLISHED tcp 0 0 192.168.0.2:44359 192.168.0.1:3306 ESTABLISHED tcp 0 0 192.168.0.2:44344 192.168.0.1:3306 ESTABLISHED tcp 0 0 192.168.0.2:42371 192.168.0.1:3306 ESTABLISHED tcp 0 0 192.168.0.2:47252 192.168.0.1:3306 ESTABLISHED tcp 0 0 192.168.0.2:47987 192.168.0.1:3306 ESTABLISHED tcp 0 0 192.168.0.2:47876 192.168.0.1:3306 ESTABLISHED tcp 0 0 192.168.0.2:48217 192.168.0.1:3306 ESTABLISHED tcp 0 0 192.168.0.2:48923 192.168.0.1:3306 ESTABLISHED tcp 0 0 192.168.0.2:49089 192.168.0.1:3306 ESTABLISHED tcp 0 0 192.168.0.2:46416 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46417 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46418 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46419 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46420 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46421 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46422 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46423 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46424 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46425 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46426 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46427 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46428 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46429 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46430 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46431 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46400 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46401 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46402 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46403 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46404 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46405 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46406 192.168.0.1:3306 ESTABLISHED tcp 0 0 192.168.0.2:46407 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46408 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46409 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46410 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46411 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46412 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46413 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46414 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46415 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46448 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46449 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46450 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46451 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46452 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46453 192.168.0.1:3306 TIME_WAIT tcp 0 0 192.168.0.2:46454 192.168.0.1:3306 TIME_WAIT [root@httpd etc]# I remove a lot of them because of cpanel forums rules no more then 10000 characters
Posted by talkntickets, 10-05-2007, 09:59 PM
It is a bit hard to diagnose the problem based on the information provided but I would recommend that you switch on MySQL's slow log option (log-slow-queries) so that you can see if there are any faulty or inefficient MySQL queries which are causing the trouble. You might like to try using the max_join_size option in MySQL too and implement other resource limiting options in both MySQL and Apache.
Posted by 5need, 10-06-2007, 07:50 AM
After i enable log-slow-queries what i can do ? I do my best to optimze Apache and Mysql . Thanks , 5need
Posted by david510, 10-06-2007, 09:17 AM
There seems to be more TIME_WAIT connections. Try increasing the keepalive value inside /proc/sys/net/ipv4/tcp_keepalive_time
Posted by 5need, 10-06-2007, 03:41 PM
Hello David , In which server to increase it ? mysql or httpd ? and what to restart ater increase it ? Thanks
Posted by 5need, 10-06-2007, 05:18 PM
Hello All , I increase the tcp_keepalive_time from 1200 to 1800 and i see that the load go down from 3.0 to 1.0 and the browse get more speed what about if i put it to 5000 ? what will happen and also i edit the net.ipv4.tcp_fin_timeout = 30 from 25 to 30 i edit them from both /etc/sysctl.conf and from the files inside /proc/sys/net/ipv4/tcp_keepalive_time after that i restart the network service by the command/etc/rc.d/init.d/network restart My question what if i increase the tcp_keepalive_time for more what will happen ? Thanks
Posted by talkntickets, 10-06-2007, 05:59 PM
After you have made that change, restart MySQL. From then on, MySQL should write details of any long-running queries to the slow log, which you will usually find in your MySQL directory (e.g. /var/lib/mysql). It would normally be named something like hostname-slow.log (where 'hostname' is your server's hostname). The slow log contains a line like this in relation to each slow query: # Query_time: 11 Lock_time: 2 Rows_sent: 1 Rows_examined: 17637 If you spot any queries which have an excessive query time and/or are examining a very large number of rows, then those may be your problem and so you should scrutinize the SQL to see if it is faulty. Sometimes malformed SQL queries examine millions of rows unnecessarily.
Posted by 5need, 10-07-2007, 07:16 AM
Thanks talkntickets , I will see the log and i am watting for David answer . Regards, 5need
Posted by w-2-i, 10-08-2007, 06:14 AM
Watch the sql-queries with "mtop".
Posted by 5need, 10-08-2007, 07:12 AM
w-2-j i see the mysql queries , and i think there are everything normal because the large time was 2 secounds and all others is 1 , the mysql server not having load and the load of it always on 1 or little but the load is always up then 2 in httpd server . Thanks , 5need
Posted by gone-afk, 10-08-2007, 07:23 AM
check your key efficiency whilst you're in there. should be 99.9%+
Posted by 5need, 10-08-2007, 07:48 AM
Can you tell me please how to do this ? Thanks 5need
Posted by juangake, 10-08-2007, 08:47 AM
You can try Tuning Primer for MySQL http://forge.mysql.com/projects/view.php?id=44
Posted by 5need, 10-08-2007, 11:19 AM
juangake , My Mysql server not have a slow queris or long queries time and the load of it under1 always my problem is with my httpd server it always having load . Thanks . 5need
Posted by 5need, 10-09-2007, 03:42 PM
Help Please ....

Add to Favourites Print this Article