High load problems - please help

Discussion in 'Install/Configuration' started by MarkPW, Apr 4, 2008.

  1. MarkPW

    MarkPW New Member

    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):

    Here's some stats from one the quietest periods, during a spike:

    Top stats a few mins later:

    Many thanks
  2. anewday

    anewday Moderator

    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: Apr 5, 2008
  3. mistwang

    mistwang LiteSpeed Staff

    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. MarkPW

    MarkPW New Member

    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:

    DB:

    Here's the result of netstat. To answer your question, the server does have a dedicated GB connection.

    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...

    Last edited: Apr 5, 2008
  5. mistwang

    mistwang LiteSpeed Staff

    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.
  6. Matthew_K

    Matthew_K New Member

    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.
  7. mistwang

    mistwang LiteSpeed Staff

    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.
  8. Matthew_K

    Matthew_K New Member

    OK. I've setup the variables as recommended. I'll let you know if I run into any trouble. Thanks.

    Matt.
  9. anewday

    anewday Moderator

    Does this apply for most cases? The default config has both together.
    Last edited: Apr 6, 2008
  10. PSS

    PSS Member

    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.
  11. mistwang

    mistwang LiteSpeed Staff

    It will be great if you could write a wiki entry on vB optimization, especially on tuning MySQL DB. :)
  12. MarkPW

    MarkPW New Member

    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: Apr 15, 2008
  13. mistwang

    mistwang LiteSpeed Staff

    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%
  14. MarkPW

    MarkPW New Member

    It is a private GB link and retransmission is around 0.00032%. Is there anything I could tweak in the lsphp5 config that I haven't already?
  15. mistwang

    mistwang LiteSpeed Staff

    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. ;)
  16. PSS

    PSS Member

    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: Apr 17, 2008

Share This Page