Large message boards and MySQL logs of slow response

[Mostmaster]

In addition to the database changes listed above, I also took out MostMaster's image resizer from the forum code. I don't need this particular function on my forum, and it has greatly increased in speed, especially on threads with many posts (and many sigs).

You don't need to remove the code because you can easily turn on/off the option in your admincenter. And it only works for sigs and normal posts, not for avatars
It can slow down your board if it tries to get the size of an non-existing image or an image on a slow server. That's the only disadvantage of this method.

[Mach8]

Mostmaster: normal people would do that, but since I've edited every source file on my forum, removing stuff I don't use I thought I'd do it with that too

It's a good piece of code, nothing wrong with it, the only flaw as you've said is that if the server replies slowly, or the file doesn't exist then you're bound to experience speed problems.

[Webby]

How exactly can I see whether my database is up-to-date or not ?  And if so, using the script will ruin it, or is it always "safe" to use it.

I can certainly make a backup first, but want to know this for my own peace of mind

The second script I have to run every month or so you say ?!

[Egg]

Mediman


can you tell me how and where to save / run the script??

just a bit new to this game

thanks

Egg

[mediman]

How exactly can I see whether my database is up-to-date or not ?  And if so, using the script will ruin it, or is it always "safe" to use it.

I can certainly make a backup first, but want to know this for my own peace of mind

The second script I have to run every month or so you say ?!

if you need info how uptodate is your mysql 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();
echo "Version: $vers";
?>

The scripts have no code that will in any form be a risk of you db...
the script is so written, that the optimizing part depents on your mysql version... only if you have a version 3.23.xx the table type will be changed to MyISAM!

the optimize script can you run so often you want, i use it twice a month, but once a month is also ok!

medi

if you have any questions...

[mediman]

Mediman


can you tell me how and where to save / run the script??

just a bit new to this game

thanks

Egg

copy & paste the code in a textfile, rename it to optidb.php (or what ever you want) and upload it anywhere to your server. run it and thats it!

medi

[Webby]

The scripts have no code that will in any form be a risk of you db...
the script is so written, that the optimizing part depents on your mysql version... only if you have a version 3.23.xx the table type will be changed to MyISAM!

the optimize script can you run so often you want, i use it twice a month, but once a month is also ok!

medi

if you have any questions...

Version: 3.23.49a-log

So I have to check if it is " MyISAM " ?

[Coyote]

OMG  

I have a small board of 179 members and 30,000 posts a month - and I thought the board was pretty slick as it was - I did not expect this to database change to make much difference. BUT IT DOES!!!!

Thanks guys!!!!

[Jan Houtsma]

Mediman


can you tell me how and where to save / run the script??

just a bit new to this game

thanks

Egg

php -f <filename> works as well but you will see the html tags in the responses.

[Jan Houtsma]

Strange. My server is RH7.2 with mysql-3.23.41-1.

According to the script it should add indices and change the type to MyISAM. But everything already was MyISAM and all the indices were already there without running the script at all.

Jan

if you have ever had a 3.23 db then it is normal that your table type was already MyISAM, not a problem!
it´s just a secure feature to change anotherone to MyISAM!

the other thing (you have already had all indices is litte bit strange to me... but cool for you

well, but you can use the second script to optimize (defrag) your tables from time to time!

medi

[Webby]

Woohoo....it's a lot faster now !

[WedgeAntilles250]

Ok, this is confusing.  Three different scripts to do the same thing....optimize the db.

Which one do I use?

[mediman]

Ok, this is confusing.  Three different scripts to do the same thing....optimize the db.

Which one do I use?

if you never has optimized your db then use the 1st one! this change your table types to MyIsam, add indices and defrag, analyze your tables!

if you have already added the indices (all indices) then use the script without the "addindices" code!

both script you have to run once! (if yu run it more then once it will only not make any effect!

for your monthly optimize job, use then this

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("ANALYZE TABLE $bla")or die("Invalid query");
echo "Table <font color=blue>".$bla."</font> analyzed and optimized!<br>";
}
}

?> but if you have a mysql version 3.23.xx table only will optimized if you have table type MyISAM!

Sorry, i will make an "all in one" script to make it litte bit more usefull...

medi

[David]

For Boardnation I built the optimization into a new page I added to the Admin center.  Maybe the code to optimize the db should be called each time someone visits the Admin center or a new page created that deals with db utility stuff, backing up, optimizing . . .

[mediman]

this is a kewl idea!  

medi