Support

mistwang · #11 04-06-2008, 07:28 PM

Quote:

Originally Posted by PSS

If you run vbulletin let me know, I can assist a bit with optimizing that.

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

MarkPW · #12 04-15-2008, 10:18 AM

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.

mistwang · #13 04-15-2008, 10:24 AM

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%

MarkPW · #14 04-15-2008, 10:59 AM

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?

mistwang · #15 04-15-2008, 11:14 AM

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 · #16 04-17-2008, 06:01 AM

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