Large message boards and MySQL logs of slow response

[beneluxrail]

Please do!! It would be nice to have a script to do so, and periodicly optimize the dbase as well.  

[RHiSC]

yeah, that would be great! nice suggestion...

[mediman]

i´m in the last tests and post the file here asap!

[RHiSC]

awesome!!! standing by...

[goosemoose]

I applied the query changes but I couldn't find the place in phpadmin to edit the variables that were suggested. Where is this hiding? Or do I put these in the query field as well? Thanks

[mediman]

try this script it makes all changes for you!

Code Select Expand

<?php

$db_name = "";
$db_user = "";
$db_passwd = "";
$db_server = "";
$db_prefix = "";

mysql_connect("$db_server","$db_user","$db_passwd");
$vers = mysql_get_server_info();
if(eregi("3.23",$vers)) {
echo "We found a MySQL Server with Version <b>$vers</b> installed!<br>";
echo "<font color ='red'>We change your tables to MyISAM type and analyze/optimze all tables also indices will be added!</font><br>";
typechanging();
optimize();
addindices();
} else {echo "We found a MySQL Server with Version <b>$vers</b> installed!<br>";
echo "<font color ='red'>tables will analyzed and optimzed and indices added will be added!</font><br>"; 
optimize();
addindices();
}


function typechanging() {
global $db_name;
mysql_select_db($db_name); 
$tables=mysql_list_tables($db_name);
while (list($bla)=mysql_fetch_array($tables)) {
$result = mysql_query("ALTER TABLE $bla TYPE=MyISAM")or die("Invalid query");
echo "Table <font color=blue>".$bla."</font> converted to MyISAM!<br>"; 
}
}

function optimize() {
global $db_name;
mysql_select_db($db_name); 
$tables=mysql_list_tables($db_name);
while (list($bla)=mysql_fetch_array($tables)) {
$result = mysql_query("OPTIMIZE TABLE $bla")or die("Invalid query");
echo "Table <font color=blue>".$bla."</font> analyzed and optimized!<br>"; 
}
}

function addindices() {
global $db_name,$db_prefix;
mysql_select_db($db_name); 
mysql_query("ALTER TABLE {$db_prefix}members add key(memberGroup(30)");
mysql_query("ALTER TABLE {$db_prefix}members add key(posts)");
mysql_query("ALTER TABLE {$db_prefix}members add key(memberName(30)");
mysql_query("ALTER TABLE {$db_prefix}members add key(dateRegistered)");
mysql_query("ALTER TABLE {$db_prefix}members add key(lastLogin)");
mysql_query("ALTER TABLE {$db_prefix}messages add key(posterTime)");
mysql_query("ALTER TABLE {$db_prefix}instant_messages add key (ID_MEMBER_FROM)");
mysql_query("ALTER TABLE {$db_prefix}instant_messages add key (ID_MEMBER_TO)");
mysql_query("ALTER TABLE {$db_prefix}instant_messages add key (deletedBy)");
mysql_query("ALTER TABLE {$db_prefix}instant_messages add key(fromName(30)");
mysql_query("ALTER TABLE {$db_prefix}instant_messages add key(toName(30)");
mysql_query("ALTER TABLE {$db_prefix}categories add key (catOrder)");
mysql_query("ALTER TABLE {$db_prefix}boards add key (ID_CAT)");
mysql_query("ALTER TABLE {$db_prefix}log_boards add key (ID_BOARD)");
mysql_query("ALTER TABLE {$db_prefix}log_boards add key (memberName(30)");
mysql_query("ALTER TABLE {$db_prefix}log_topics add key (membername(30)");
mysql_query("ALTER TABLE {$db_prefix}log_topics add key (logTime)");
mysql_query("ALTER TABLE {$db_prefix}log_topics add key (ID_TOPIC)");
mysql_query("ALTER TABLE {$db_prefix}log_activity add key (month)");
mysql_query("ALTER TABLE {$db_prefix}log_activity add key (day)");
mysql_query("ALTER TABLE {$db_prefix}log_activity add key (year)");
mysql_query("ALTER TABLE {$db_prefix}log_activity add key (mostOn)");
mysql_query("ALTER TABLE {$db_prefix}log_errors add key (logTime)");
mysql_query("ALTER TABLE {$db_prefix}log_errors add key (memberName(30)");
mysql_query("ALTER TABLE {$db_prefix}log_floodcontrol add key (ip(20)");
mysql_query("ALTER TABLE {$db_prefix}log_floodcontrol add key (logTime)");
mysql_query("ALTER TABLE {$db_prefix}log_karma add key (ID_TARGET)");
mysql_query("ALTER TABLE {$db_prefix}log_karma add key (ID_EXECUTOR)");
mysql_query("ALTER TABLE {$db_prefix}log_karma add key(logTime)");
mysql_query("ALTER TABLE {$db_prefix}log_mark_read add key (memberName(30)");
mysql_query("ALTER TABLE {$db_prefix}log_mark_read add key (ID_BOARD)");
mysql_query("ALTER TABLE {$db_prefix}log_mark_read add key (logTime)");
mysql_query("ALTER TABLE {$db_prefix}log_online add key (logTime)");
mysql_query("ALTER TABLE {$db_prefix}settings add key(variable(30)");
echo "<font color ='red'>Indices succesfully added!</font>";
}

?>

[Purple Raine]

Alright...

I *REALLY* need some help with this =)... as my board is only 2 months old, it doesn't really make sense to delete posts that are two months old...

yet my board is DISGUSTINGLY slow... I've had many people report that they've been waiting for 30+ seconds to see the board go...

I have the indices inserted into the code already, but still, it's slow as heck... and i mean SLOW...

on average, it takes about 9-11 seconds to load up the boardindex, which my host has told me is the main problem... each boardindex load has hammering the mysql queries and hence causing mucho problems on the server...

Can anyone think of any other means of making this work better / faster?

[mediman]

try this

Code Select Expand

<?php

$db_name = "";
$db_user = "";
$db_passwd = "";
$db_server = "";
$db_prefix = "";

mysql_connect("$db_server","$db_user","$db_passwd");
$vers = mysql_get_server_info();
if(eregi("3.23",$vers)) {
echo "We found a MySQL Server with Version <b>$vers</b> installed!<br>";
echo "<font color ='red'>We change your tables to MyISAM type and analyze/optimze all tables!</font><br>";
typechanging();
optimize();
} else {echo "We found a MySQL Server with Version <b>$vers</b> installed!<br>";
echo "<font color ='red'>tables will analyzed and Optimzed!</font><br>"; 
optimize();
}


function typechanging() {
global $db_name;
mysql_select_db($db_name); 
$tables=mysql_list_tables($db_name);
while (list($bla)=mysql_fetch_array($tables)) {
$result = mysql_query("ALTER TABLE $bla TYPE=MyISAM")or die("Invalid query");
echo "Table <font color=blue>".$bla."</font> wurde nach MyISAM konvertiert!<br>"; 
}
}

function optimize() {
global $db_name;
mysql_select_db($db_name); 
$tables=mysql_list_tables($db_name);
while (list($bla)=mysql_fetch_array($tables)) {
$result = mysql_query("OPTIMIZE TABLE $bla")or die("Invalid query");
echo "Table <font color=blue>".$bla."</font> analyzed and optimized!<br>"; 
}
}
?>

this will change your db to a more faster table type and sort and defrag the indices!

medi

[Delinquently insane]

awesome blazing fast  

[luisr]

Just curious about this topic.  My board has about 900 messages and 110 users.  Will I benefit from this?

[tore-]

Just curious about this topic.  My board has about 900 messages and 110 users.  Will I benefit from this?

yes

[Golden Tiger]

Ok, a suggestion than a question...

Suggestion to the administrators, add this file to the downloads section and send out a notification.  That way, it would truely be official.  Also, for people who switch over to SE expecting a faster board (like it says on the front page) and seeing a much slower one on their site... they will not be pleased!  This should be considered a bug fix, a SQL table bug fix, but a bug fix anyways.

Question, to mediman.  You posted two code snidbits...  do I need to run both, or only one, the first or the second?  Which one do I need to run to make my board faster?

[mediman]

the first one change the "table type" corresponding on your mysql version, add indices and optimize your table!

if you have already put the inidices, then use the second one!

if you only will make a optimizing (maybe once a month or so then use

Code Select Expand

<?

$db_name = "";
$db_user = "";
$db_passwd = "";
$db_server = "localhost";

mysql_connect("$db_server","$db_user","$db_passwd");
$vers = mysql_get_server_info();
echo "We found a MySQL Server with Version <b>$vers</b> installed!<br>";
optimize();

function optimize() {
global $db_name;
mysql_select_db($db_name);
$tables=mysql_list_tables($db_name);
while (list($bla)=mysql_fetch_array($tables)) {
$result = mysql_query("OPTIMIZE TABLE $bla")or die("Invalid query");
echo "Table <font color=blue>".$bla."</font> analyzed and optimized!<br>";
}
}

?>
cheers medi!

[Golden Tiger]

Also, one last thing, perhaps forward this to mod development, future development...  Put an option in the admin center telling you when you last optimized, and allowing you to optimize by clicking on a single link...

Just an idea!

[mediman]

good idea perhaps with a db checker

medi