i have a forum thats almost as busy as webhostingtalk.com but the server is so much loaded although its dual xeon 2gb ram dusl 73 scsi with 2gb swap

my my.cnf is:
[mysqld]
set-variable = query_cache_type=1
set-variable = query_cache_limit=10M
set-variable = query_cache_size=150M

set-variable = max_connections=512
set-variable = ft_max_word_len=30
set-variable = ft_max_word_len_for_sort=20
set-variable = ft_min_word_len=2
bind-address = 127.0.0.1
port = 3306

set-variable = delay_key_write=ON
set-variable = delayed_queue_size=10000
set-variable = delayed_insert_limit=50
set-variable = max_delayed_threads=8

set-variable = connect_timeout=10
set-variable = interactive_timeout=100
set-variable = wait_timeout=60

set-variable = low-priority-updates=ON
set-variable = key_buffer=550M
set-variable = table_cache=5500
set-variable = sort_buffer_size=5M
set-variable = join_buffer_size=5M
set-variable = read_buffer_size=5M
set-variable = read_rnd_buffer_size=5M
set-variable = tmp_table_size=400M
set-variable = myisam_sort_buffer_size=256M
set-variable = thread_cache=256
set-variable = thread_stack=256000
set-variable = thread_concurrency=4
server-id = 1
set-variable = max_allowed_packet=20M
set-variable = flush_time=9600

skip-bdb
skip-innodb



and using a script i found at rackshack forum's gave me this output:

Mon Aug 18 23:12:12 AST 2003


11:12pm up 7 days, 16:27, 1 user, load average: 4.98, 5.22, 5.14
167 processes: 157 sleeping, 5 running, 5 zombie, 0 stopped
CPU0 states: 70.0% user, 7.0% system, 0.0% nice, 22.0% idle
CPU1 states: 15.0% user, 16.1% system, 0.0% nice, 68.0% idle
CPU2 states: 37.0% user, 12.0% system, 0.0% nice, 50.0% idle
CPU3 states: 23.0% user, 10.0% system, 0.0% nice, 66.0% idle
Mem: 2064716K av, 1929012K used, 135704K free, 0K shrd, 14660K buff
Swap: 2048276K av, 39328K used, 2008948K free 1666168K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
3730 mysql 19 0 39260 38M 1396 R 67.6 1.9 0:25 mysqld
15997 nobody 16 0 1092 1092 828 R 13.6 0.0 0:00 top
3715 mysql 16 0 39252 38M 1396 R 12.8 1.9 0:40 mysqld
16006 nobody 17 0 5952 5952 3932 R 10.4 0.2 0:00 httpd
16024 nobody 16 0 5568 5568 3880 R 5.6 0.2 0:00 httpd


Http processes currently running = 47
Mysql processes currently running = 52

Netstat information summary
1 LAST_ACK
6 FIN_WAIT2
8 CLOSE_WAIT
8 SYN_RECV
12 CLOSING
14 FIN_WAIT1
22 LISTEN
31 ESTABLISHED
758 TIME_WAIT

+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 3574 |
| Aborted_connects | 0 |
| Bytes_received | 8608165 |
| Bytes_sent | 195190045 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 2704 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 477 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 1047 |
| Com_insert_select | 6 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 18 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 14032 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 8 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 7 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 7 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 4793 |
| Connections | 2738 |
| Created_tmp_disk_tables | 1378 |
| Created_tmp_tables | 2261 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 504 |
| Handler_read_first | 266 |
| Handler_read_key | 4190784 |
| Handler_read_next | 756128 |
| Handler_read_prev | 16619 |
| Handler_read_rnd | 100901 |
| Handler_read_rnd_next | 92540713 |
| Handler_rollback | 0 |
| Handler_update | 3856118 |
| Handler_write | 110205 |
| Key_blocks_used | 11318 |
| Key_read_requests | 1027180 |
| Key_reads | 11317 |
| Key_write_requests | 2427 |
| Key_writes | 2068 |
| Max_used_connections | 44 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 172 | 3% of table_cache in use
| Open_files | 215 |
| Open_streams | 0 |
| Opened_tables | 178 |
| Questions | 45157 |
| Qcache_queries_in_cache | 1126 |
| Qcache_inserts | 12164 |
| Qcache_hits | 21120 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1868 |
| Qcache_free_memory | 152655648 |
| Qcache_free_blocks | 381 |
| Qcache_total_blocks | 2713 |
| Rpl_status | NULL |
| Select_full_join | 108 |
| Select_full_range_join | 134 |
| Select_range | 1505 |
| Select_range_check | 0 |
| Select_scan | 2913 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 2 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 1040 |
| Sort_rows | 63239177 |
| Sort_scan | 3004 |
| Table_locks_immediate | 26625 |
| Table_locks_waited | 1437 |
| Threads_cached | 33 |
| Threads_created | 45 |
| Threads_connected | 12 |
| Threads_running | 9 |
| Uptime | 833 | 13 mins 53 secs
+--------------------------+-----------+


Key Reads/Key Read Requests = 0.011018 (Cache hit = 99.988982%)
Key Writes/Key Write Requests = 0.852081
Connections/second = 3.287 (/hour = 11832.893)
KB received/second = 10.091 (/hour = 36328.451)
KB sent/second = 228.830 (/hour = 823786.315)
Temporary Tables Created/second = 2.714 (/hour = 9771.429)
Opened Tables/second = 0.214 (/hour = 769.268)
Slow Queries/second = 0.002 (/hour = 8.643)
% of slow queries = 0.004%
Queries/second = 54.210 (/hour = 195156.303)
Next automatic buffers flush in 2 hrs 26 mins 7 secsMySQL Query Cache hits = 21120/35152(60%)

the code

<html><body><pre>
<?
// stat.php - Last modified: 03/18/02 13:10:00 P.Kelly

/ ************************************************** ************/
/* Set-up default parameters */
/* Override them using get vars eg. stats.php?ns=0 */
/* Or override the constant settings below */
/ ************************************************** ************/
$ns = isset($ns) ? $ns : 1; // allow netstat
$htp = isset($htp) ? $htp : 1; // allow http process count
$msp = isset($msp) ? $msp : 1; // allow mysql process count
$tp = isset($tp) ? $tp : 1; // allow top information
$mss = isset($mss) ? $mss : 1; // allow mysql status information
/ ************************************************** ************/

/***
Configure statistic gathering programs; specify path if necessary
***/

$netstat = 'netstat -a -n|grep -E "^(tcp)"| cut -c 68-|sort|uniq -c|sort -n';
$http = "ps -auxww | grep -c http";
$mysql = "ps -auxww | grep -c mysql";
$top = "top";
$uptime = "uptime";
$free = "free";
$mysqladmin = "mysqladmin";
$mysqllogin = "-uusername -ppassword";


function SecToDHMS($secs) {
$showSecs = $uptime == 0;
$s = "";
if ($secs >= 86400) {
$n = (int) ($secs/86400);
$s = $n." day".($n>1?"s":"");
$secs %= 86400;
}
if ($secs >= 3600) {
$n = (int) ($secs/3600);
$s .= " ".$n." hr".($n>1?"s":"");
$secs %= 3600;
}
if ($secs >= 60) {
$n = (int) ($secs/60);
$s .= " ".$n." min".($n>1?"s":"");
$secs %= 60;
}
if ($secs || $showSecs == 1)
$s .= " ".$secs." sec".($secs>1?"s":"");
return trim ($s);
}



/***
Display current date time
***/
system ("date");
echo "\n\n";




/***
Display "top" information if available; otherwise display similar information
***/
if($tp)
{
if (isset ($top) && $top != "")
{
$fp = popen ("$top -i -n 1 -b", "r");
$buffer = "";
while (!feof ($fp) && $buffer == "") // skip any blank header lines
$buffer = rtrim (fgets ($fp, 1024));

if (!feof ($fp) && $buffer != "")
{
echo "$buffer\n";
while (!feof ($fp) && $buffer != "")
{ // display all except CPU details
$buffer = rtrim (fgets ($fp, 1024));
if (substr ($buffer, 0, 11) != "CPU states:")
echo "$buffer\n";
}
}
// display details; filter to show only "nobody" and "mysql" users excluding "top" process
while (!feof ($fp))
{
$buffer = rtrim (fgets ($fp, 1024));
$user = rtrim (substr ($buffer, 6, 9));
if ($user == "USER")
$header = $buffer;
if (($user == "nobody" || $user == "mysql") && substr ($buffer, 67, 3) != "top")
{
if ($header != "")
{
echo "$header\n";
$header = "";
}
echo "$buffer\n";
}
}
pclose ($fp);
echo "\n";
}
else {
if (isset ($uptime) && $uptime != "")
system ("$uptime");
if (isset ($free) && $free != "")
system ("$free");
echo "\n";
}
}




/***
Display current number of http processes
***/
if($htp)
{
echo "\nHttp processes currently running = ";
system ($http);
}


/***
Display current number of http processes
***/
if($msp)
{
echo "Mysql processes currently running = ";
system ($mysql);
}

/***
Display current number of http processes
***/
if($ns)
{
echo "\nNetstat information summary\n";
system ($netstat);
echo "\n";
}



/***
Display MySQL extended-status (with special metric calculations to follow)
***/
if($mss)
{
if (isset($mysqladmin) && $mysqladmin != "") {

// Get MySql Variables.

exec("$mysqladmin $mysqllogin variables",$mysqlrawvar);
for($i = 0;$i < count($mysqlrawvar);$i++)
{
$line = array();
$line = explode("|",$mysqlrawvar[$i]);
if(count($line) > 2); $mysqlvar[trim($line[1])] = trim($line[2]);
// echo $mysqlrawvar[$i]."\n";
}

// echo "\n\nMySql Variables Array\n\n";
// while (list ($key, $val) = each ($mysqlvar)) echo "$key => $val<br>";

// Get MySql Status.

exec("$mysqladmin $mysqllogin extended-status",$mysqlrawstat);
for($i = 0;$i < count($mysqlrawstat);$i++)
{
$line = array();
$line = explode("|",$mysqlrawstat[$i]);
if(count($line) > 2);
{
$param = trim($line[1]);
$var = trim($line[2]);
$mysqlstat[$param] = $var;
$buffer = trim($mysqlrawstat[$i]);

if ($param == "Key_blocks_used" && isset ($mysqlvar[key_buffer])) {
$temp = sprintf ("%.0f", $var/($mysqlvar["key_buffer"]>>10)*100);
echo $buffer," Approx. $temp% of key_buffer in use";
}
else if ($param == "Max_used_connections" && $var == $mysqlvar["max_connections"])
echo $buffer," Max. connections reached\n";
else if ($param == "Open_tables" && isset ($mysqlvar["table_cache"])) {
$temp = sprintf("%.0f", $var/$mysqlvar["table_cache"]*100);
echo $buffer," $temp% of table_cache in use";
}
else if ($param == "Slow_queries" && isset ($mysqlvar["long_query_time"]))
echo $buffer." (execution time > ".$mysqlvar["long_query_time"]." secs)";
else if ($param == "Uptime")
echo $buffer." ".SecToDHMS($var);
else
echo $buffer;

echo "\n";
}
}


// echo "\n\nMySql Extended Status Array\n\n";
// while (list ($key, $val) = each ($mysqlstat)) echo "$key => $val<br>";

echo "\n\n";
if (isset ($mysqlstat[Key_read_requests]) && isset ($mysqlstat[Key_reads]) && $mysqlstat[Key_read_requests]) {
$temp = sprintf ("%.6f", $mysqlstat[Key_reads]/$mysqlstat[Key_read_requests]);
echo "Key Reads/Key Read Requests = $temp (Cache hit = ",100-$temp,"%)\n";
}
if (isset ($mysqlstat[Key_write_requests]) && isset ($mysqlstat[Key_writes]) && $mysqlstat[Key_write_requests]) {
$temp = sprintf ("%.6f", $mysqlstat[Key_writes]/$mysqlstat[Key_write_requests]);
echo "Key Writes/Key Write Requests = $temp\n";
}
if (isset ($mysqlstat[Uptime])) {
$uptime = $mysqlstat[Uptime];
if ($uptime) {
if (isset ($mysqlstat[Connections])) {
$temp = $mysqlstat[Connections]/$uptime;
echo "Connections/second = ",sprintf ("%.3f", $temp), " (/hour = ",sprintf ("%.3f", $temp*3600),")\n";
}
if (isset ($mysqlstat[Bytes_received])) {
$temp = ($mysqlstat[Bytes_received] >> 10)/$uptime;
echo "KB received/second = ",sprintf ("%.3f", $temp), " (/hour = ",sprintf ("%.3f", $temp*3600),")\n";
}
if (isset ($mysqlstat[Bytes_sent])) {
$temp = ($mysqlstat[Bytes_sent] >> 10)/$uptime;
echo "KB sent/second = ",sprintf ("%.3f", $temp), " (/hour = ",sprintf ("%.3f", $temp*3600),")\n";
}
if (isset ($mysqlstat[Created_tmp_tables])) {
$temp = $mysqlstat[Created_tmp_tables]/$uptime;
echo "Temporary Tables Created/second = ",sprintf ("%.3f", $temp)," (/hour = ",sprintf ("%.3f", $temp*3600),")\n";
}
if (isset ($mysqlstat[Opened_tables])) {
$temp = $mysqlstat[Opened_tables]/$uptime;
echo "Opened Tables/second = ",sprintf ("%.3f", $temp)," (/hour = ",sprintf ("%.3f", $temp*3600),")\n";
}
if (isset ($mysqlstat[Slow_queries])) {
$temp = $mysqlstat[Slow_queries]/$uptime;
echo "Slow Queries/second = ",sprintf ("%.3f", $temp)," (/hour = ",sprintf ("%.3f", $temp*3600),")\n";
}
if (isset ($mysqlstat[Questions])) {
echo "% of slow queries = ",sprintf ("%.3f%%", $mysqlstat[Slow_queries]/$mysqlstat[Questions]*100),"\n";
$temp = $mysqlstat[Questions]/$uptime;
echo "Queries/second = ",sprintf ("%.3f", $temp)," (/hour = ",sprintf ("%.3f", $temp*3600),")\n";
}
if (isset ($mysqlvar[flush_time]) && $mysqlvar[flush_time] != 0)
echo "Next automatic buffers flush in ",SecToDHMS($mysqlvar[flush_time]-($uptime % $mysqlvar[flush_time]));

# work out how much is served from the Query cache

if (isset ($mysqlstat[Qcache_hits])) {
$total = $mysqlstat[Qcache_inserts]+$mysqlstat[Qcache_hits]+$mysqlstat[Qcache_not_cached];
$percentage = ($mysqlstat[Qcache_hits]/$total)*100;
echo "MySQL Query Cache hits = $mysqlstat[Qcache_hits]/$total(",sprintf ("%.0f",$percentage), "%)\n";
}




}
}
}
}
?>

</pre></body></html>

can anyone recommend good way to optomize my.cnf for such loaded forum?
Thanks!

| Handler_read_key | 4190784 |
| Handler_read_next | 756128 |
| Handler_read_prev | 16619 |
| Handler_read_rnd | 100901 |
| Handler_read_rnd_next | 92540713 |

The configuration looks horribly overdone, but I think the primary issue is some large query is running without indexing, which is backlogging everything else.

A 'mysqladmin processlist' might point you toward the problem query.

yeah i saw so many queries, but whats the solution for it?

I need to edit something in this file for HiveMail but when I do pico my.cnf all it shows in there is this:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Where is the rest of the info? Is there something other then pico that I should be using?

Bashar: the solution is to hunt down the problem query and fix it. You can only do so much to plaster over bad code. It usually winds up being something doing a "SELECT * FROM table" to get a row count. Stupid PHPNuke.

js: your mysql database is using default values for most of its settings, which is usually fine. The MySQL docs should be able to tell you how to add new overrides.

hiryuu: its not a phpnuke its vbulletin

and i think yeah its doing select * from table which causing this

but the load never goes below 1.00 !

I run into a similar type situation where many, many PHP processes will sleep, instead of closing.
My solution at this point is to restart mySQL a couple times a day.

For yourself, run TOP, see how many processes are running. Then restart mySQL (thru WHM) and
watch how low your process are now.

If you do a # mysqladmin processlist
and see lots of processes sleeping, then you have the same problem I do -- usualy have 50 - 80 sleeping.

I have yet to find, besides a Cron job to restart mySQL, a good solution to this problem.
Although I use a very minimal "my.conf" file, I'm wondering if

set-variable = flush_time=9600

can close sleeping PHP/mySQL processes. If it does and 9600 refers to seconds,
I can see where a 300 setting would do wonders.


Edit: I thought VB was supposed to properly re-size posts, if one went way over?

Rob: It sounds like you have persistent connections turned on in PHP. In that case, you will have at least as many mysql threads as you have apache children, even if most of both are idle.

OTOH, that can be a good move for Bashar, since it removes some connection setup overhead, and most of his requests will be SQL-backed.

well still isn't that much good, load still above 4!

Wed Aug 20 01:59:59 AST 2003


2:00am up 1 day, 1:36, 0 users, load average: 4.45, 4.48, 4.89
294 processes: 289 sleeping, 5 running, 0 zombie, 0 stopped
CPU0 states: 47.8% user, 47.8% system, 0.0% nice, 4.0% idle
CPU1 states: 55.5% user, 40.4% system, 0.0% nice, 3.7% idle
CPU2 states: 60.0% user, 36.4% system, 0.0% nice, 3.3% idle
CPU3 states: 52.0% user, 45.1% system, 0.0% nice, 2.6% idle
Mem: 2064716K av, 2011080K used, 53636K free, 0K shrd, 7892K buff
Swap: 2048276K av, 217500K used, 1830776K free 1099796K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
1857 nobody 25 0 1172 1172 828 R 44.9 0.0 0:07 top
27044 mysql 25 0 626M 421M 1460 R 30.2 20.9 10:14 mysqld
1695 nobody 16 0 7236 7128 3780 R 4.4 0.3 0:11 httpd
755 mysql 15 0 628M 422M 1460 R 0.2 20.9 1:13 mysqld


Http processes currently running = 88
Mysql processes currently running = 144

Netstat information summary
7 FIN_WAIT2
7 LAST_ACK
10 CLOSING
10 SYN_RECV
16 FIN_WAIT1
17 CLOSE_WAIT
22 LISTEN
32 ESTABLISHED
1009 TIME_WAIT

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 17053 |
| Aborted_connects | 0 |
| Bytes_received | 590724885 |
| Bytes_sent | 839304017 |
| Com_admin_commands | 123920 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 191546 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 35732 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 76358 |
| Com_insert_select | 494 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 2380 |
| Com_replace_select | 64 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 963396 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 2 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 188 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 5 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 7 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 352308 |
| Connections | 75142 |
| Created_tmp_disk_tables | 98446 |
| Created_tmp_tables | 158618 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 42167 |
| Handler_read_first | 18503 |
| Handler_read_key | 246793296 |
| Handler_read_next | 60288097 |
| Handler_read_prev | 1514981 |
| Handler_read_rnd | 6606602 |
| Handler_read_rnd_next | 1854406223 |
| Handler_rollback | 0 |
| Handler_update | 227016844 |
| Handler_write | 7898878 |
| Key_blocks_used | 536069 |
| Key_read_requests | 62233854 |
| Key_reads | 987877 |
| Key_write_requests | 218348 |
| Key_writes | 189979 |
| Max_used_connections | 136 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 63 | 1% of table_cache in use
| Open_files | 83 |
| Open_streams | 0 |
| Opened_tables | 16350 |
| Questions | 3212942 |
| Qcache_queries_in_cache | 14795 |
| Qcache_inserts | 840179 |
| Qcache_hits | 1523904 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 123218 |
| Qcache_free_memory | 129004744 |
| Qcache_free_blocks | 6965 |
| Qcache_total_blocks | 36793 |
| Rpl_status | NULL |
| Select_full_join | 6354 |
| Select_full_range_join | 10402 |
| Select_range | 84037 |
| Select_range_check | 0 |
| Select_scan | 205695 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 271 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 63330 |
| Sort_rows | 4279350682 |
| Sort_scan | 211819 |
| Table_locks_immediate | 1864446 |
| Table_locks_waited | 104383 |
| Threads_cached | 83 |
| Threads_created | 137 |
| Threads_connected | 54 |
| Threads_running | 5 |
| Uptime | 54937 | 15 hrs 15 mins 37 secs
+--------------------------+------------+


Key Reads/Key Read Requests = 0.015874 (Cache hit = 99.984126%)
Key Writes/Key Write Requests = 0.870074
Connections/second = 1.368 (/hour = 4924.026)
KB received/second = 10.501 (/hour = 37802.654)
KB sent/second = 14.919 (/hour = 53710.163)
Temporary Tables Created/second = 2.887 (/hour = 10394.175)
Opened Tables/second = 0.298 (/hour = 1071.409)
Slow Queries/second = 0.005 (/hour = 17.759)
% of slow queries = 0.008%
Queries/second = 58.484 (/hour = 210542.825)
Next automatic buffers flush in 4 mins 23 secsMySQL Query Cache hits = 1523904/2487301(61%)

Queries/second = 58.484

If this is what happens on a regular basis, then a dedicated for the DB is what should be used. Or is it already on it's own server?

no its running db and httpd

you mean have 2 seperated boxes one for db and one for httpd?

That is correct. It's obvious from what you have shown the Forum is very heavly used. It has reached (and exceeded) the point where it needs dedicated resources.

Of course, that's just one man's opinion, but I am presuming the Server it is on now has a few (many popular) DB and other (possible resource intensive) processes going on and it's just too much for one Server to handle.

You could try bumping up RAM to 2 GB and using some acceleration methods I mention in another thread on this topic.

Using SCSI hard drive would also be a plus, but for the most part, although some hardware improvements (RAM & SCSI) might help on this server, you are probably better to setup the mentioned hardware on a new server dedicated to this Forum.

the box is dell dual xeon 2gb ram with dual 73gb scsi's

what more powerful than this would be!

Post new stats for these after it has run for 12/24 hours, can't really tell much in 13 minutes.

| Qcache_queries_in_cache | 1126 |
| Qcache_inserts | 12164 |
| Qcache_hits | 21120 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1868 |
| Qcache_free_memory | 152655648 |
| Qcache_free_blocks | 381 |
| Qcache_total_blocks | 2713 |

set-variable = table_cache=5500

wow thats 10 times my setting which is a xp2200 1g ram. according to the mysql manual this is one of the most important parameters that affect the mysql performance. any1 can explain a bit on how to tweak this???

set-variable = thread_cache=256

i think the my-huge.conf file that comes with mysql sets this to 8. now this one is 32 times bigger. any1 cares to explain this setting too?

set-variable = key_buffer=550M

now i think this could be set higher since the server has 2gb ram? i remember reading somewhere that this can be set to 50% of the server's total memory. according to the mysql manual this is another key parameter.

Wed Aug 20 12:15:57 AST 2003


12:16pm up 1 day, 11:51, 0 users, load average: 2.84, 2.30, 2.14
256 processes: 254 sleeping, 2 running, 0 zombie, 0 stopped
CPU0 states: 0.0% user, 0.2% system, 0.0% nice, 99.0% idle
CPU1 states: 12.0% user, 2.0% system, 0.0% nice, 85.1% idle
CPU2 states: 30.0% user, 26.2% system, 0.0% nice, 42.2% idle
CPU3 states: 3.1% user, 29.1% system, 0.0% nice, 66.1% idle
Mem: 2064716K av, 1994448K used, 70268K free, 0K shrd, 15036K buff
Swap: 2048276K av, 475608K used, 1572668K free 1399048K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
6814 nobody 24 0 1156 1156 828 R 54.2 0.0 0:02 top
26168 mysql 20 0 630M 189M 1800 R 41.0 9.4 16:33 mysqld


Http processes currently running = 55
Mysql processes currently running = 144

Netstat information summary
2 FIN_WAIT2
4 SYN_RECV
5 CLOSE_WAIT
7 CLOSING
11 FIN_WAIT1
14 ESTABLISHED
22 LISTEN
579 TIME_WAIT

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 27507 |
| Aborted_connects | 0 |
| Bytes_received | 869410754 |
| Bytes_sent | 2902646826 |
| Com_admin_commands | 180762 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 276131 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 54172 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 113901 |
| Com_insert_select | 761 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 3458 |
| Com_replace_select | 98 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 1409459 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 2 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 311 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 6 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 8 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 517050 |
| Connections | 108215 |
| Created_tmp_disk_tables | 144050 |
| Created_tmp_tables | 232776 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 65783 |
| Handler_read_first | 28530 |
| Handler_read_key | 350817139 |
| Handler_read_next | 90068634 |
| Handler_read_prev | 2151355 |
| Handler_read_rnd | 9620277 |
| Handler_read_rnd_next | 378540889 |
| Handler_rollback | 0 |
| Handler_update | 322292894 |
| Handler_write | 11208840 |
| Key_blocks_used | 536069 |
| Key_read_requests | 89727772 |
| Key_reads | 1589745 |
| Key_write_requests | 330484 |
| Key_writes | 285453 |
| Max_used_connections | 136 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 46 | 1% of table_cache in use
| Open_files | 65 |
| Open_streams | 0 |
| Opened_tables | 23763 |
| Questions | 4695898 |
| Qcache_queries_in_cache | 4556 |
| Qcache_inserts | 1232699 |
| Qcache_hits | 2226128 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 176761 |
| Qcache_free_memory | 142069296 |
| Qcache_free_blocks | 4845 |
| Qcache_total_blocks | 14193 |
| Rpl_status | NULL |
| Select_full_join | 9023 |
| Select_full_range_join | 15513 |
| Select_range | 121573 |
| Select_range_check | 0 |
| Select_scan | 301612 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 359 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 92712 |
| Sort_rows | 1988185169 |
| Sort_scan | 310534 |
| Table_locks_immediate | 2748578 |
| Table_locks_waited | 140173 |
| Threads_cached | 92 |
| Threads_created | 137 |
| Threads_connected | 45 |
| Threads_running | 3 |
| Uptime | 91876 | 1 day 1 hr 31 mins 16 secs
+--------------------------+------------+


Key Reads/Key Read Requests = 0.017717 (Cache hit = 99.982283%)
Key Writes/Key Write Requests = 0.863742
Connections/second = 1.178 (/hour = 4240.215)
KB received/second = 9.241 (/hour = 33267.870)
KB sent/second = 22.826 (/hour = 82173.186)
Temporary Tables Created/second = 2.534 (/hour = 9120.920)
Opened Tables/second = 0.259 (/hour = 931.111)
Slow Queries/second = 0.004 (/hour = 14.067)
% of slow queries = 0.008%
Queries/second = 51.111 (/hour = 184000.531)
Next automatic buffers flush in 3 mins 44 secsMySQL Query Cache hits = 2226128/3635588(61%)

Originally posted by qm8309
set-variable = table_cache=5500

wow thats 10 times my setting which is a xp2200 1g ram. according to the mysql manual this is one of the most important parameters that affect the mysql performance. any1 can explain a bit on how to tweak this???

set-variable = thread_cache=256

i think the my-huge.conf file that comes with mysql sets this to 8. now this one is 32 times bigger. any1 cares to explain this setting too?

set-variable = key_buffer=550M

now i think this could be set higher since the server has 2gb ram? i remember reading somewhere that this can be set to 50% of the server's total memory. according to the mysql manual this is another key parameter.

ok i changed the key_buffer to 1024mb now

about the rest, not sure what do they really mean, i saw them at one website

okay here is wut i found in mysql manual

Make sure that your operating system can handle the number of open file descriptors implied by the table_cache setting. If table_cache is set too high, MySQL may run out of file descriptors and refuse connections, fail to perform queries, and be very unreliable.

the complete doc regarding this is here
http://www.mysql.com/doc/en/Table_cache.html

thanks qm ill read it over :)

Seeing 400MB in swap makes me think going the other way would be better. (Less key cache, since the hit rate isn't improving much as you add it.) I think you're thrashing.

Try these values:
set-variable = max_connections=200
set-variable = key_buffer=200M
set-variable = table_cache=1000
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=16

The table cache should be (largest join * max connections + 1 per regularly used table). Thread cache is the max number of idle threads waiting for a new connection. If you have php persistent connections off, it should be pretty big (peak number of apache in use). If persistent connections are on, it should be very small.

Key cache basically keeps portions of the indicies in mysql's memory. It should be enough to hold all of the regularly used index blocks. On a dedicated SQL box, it can be very large. On a mixed box, it shouldn't be too big. Keep in mind the keys will also be cached by the OS, which can make smarter decisions about what to cache based on other demands within the system (like static images and other apache files).

Originally posted by hiryuu
Seeing 400MB in swap makes me think going the other way would be better. (Less key cache, since the hit rate isn't improving much as you add it.) I think you're thrashing.. I have to agree, secondly as you can see your Qcache_free_memory is very high and is being wasted. I suggest lowering it by a 64 - 100MB or until you start to see some Qcache_lowmem_prunes and then go up 8 -16mb.


| Qcache_queries_in_cache | 4556 |
| Qcache_inserts | 1232699 |
| Qcache_hits | 2226128 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 176761 |
| Qcache_free_memory | 142069296 |
| Qcache_free_blocks | 4845 |
| Qcache_total_blocks | 14193 |