Large message boards and MySQL logs of slow response

[Jeff Lewis]

If you're using 1.4, use BoardIndex.php from 1.3 until 1.4.1 is released.

[djMostly]

tried that, but now i'm struggling with database user access problems.  ugh.  not sure if it's the "patch" or a problem with my host.

[mediman]

When using the convert php i get this?

Fatal error: Call to undefined function: mysql_get_server_info() in /files/local/home/earthdome/forum/convert.php on line 10

I'm running version  3.22.32 of MySql

I have since read that i need version 3.23.xx.
Time to hassle my host.

your installation does not support get_server_info, so delete this part from the script!

mediman

[mephisto_kur]

Very small board..  But 8 guys post 1000 times a week...  (125 topics)...

Even so small, its noticably faster.

[djMostly]

how would one uninstall this patch?

[Snowman30]

Im totally lost with all this.

I have 1.4 which was built up from 1.3 as an upgrade.

I have MySQL 3.23.45

now my board is slow and i want to speed it up, which of these files should i be running??? Ive downloaded the zip of the files and installed them on the server but i dont know which one i should use???

Couls someone please shed some light on this for me please.

Thanks

[mephisto_kur]

Use Convert.php first, then use Optimze.php regularly.  Convert adds the indexes and Optimize cleans 'em.

[Snowman30]

excellent...thanks for that...

Now is there anything else i can do to get a faster load time....im on a server that gives most of my sites viewers a ping of around 300ms so anything i can do to squueze performance out of it will help.

[Frodo]

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>";
}

?>

What do I do with this? I am using tripod.co.uk and at first it was really fast, but now with like 30 topics and 200 posts, members are complaining how slow it is. I have defianately seen an increase in speed also. I tried running the first one in this topic in phpmyadmin as a query, it worked, no difference. Then I tried running your code mediman, and i get an error in my db name where there is no error! What do I do?

[Daniel D.]

1. Copy the code into notepad.
2. Insert your datas in the first lines (db password, db name etc.).
3. Save it as 'optimize.txt'.
4. Upload it to your FTP server in the YaBB SE directory and rename it to 'optimize.php'.
5. Run it in your browser - that's it !

[Stud Muffin]

Do you need to put the board in maint mod before you run the optimize scipt?

Code Select Expand

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>";
}
}


[Compuart]

In case the 1.4.1 boardindex still isn't going fast enough, here's a mod that reduces the number of queries 3 * number_of_boards times.

[medizinmann]

Do you need to put the board in maint mod before you run the optimize scipt?

No, because mysql will lock your tables during optimizing!

@compuart nice script

[medizinmann]

we updatet the dboptimizer.php

please download it here http://www.yabb.info/community/attachments/dbmain2.zip

both scripts will be includet in adminsecion (as a mod)

m.

[goosemoose]

Is this included in 1.4.1 then, or should I run this and compuart's mod?