Welcome, Guest. Please Login or Register.
March 28, 2024, 11:37:53 PM
Home Help Search Log in Register
News: If you are still using YaBB SE, please consider upgrading to SMF as soon as possible.

YaBB SE Community  |  English User Help  |  FAQ  |  Large message boards and MySQL logs of slow response « previous next »
Pages: 1 2 3 [4] 5 6 ... 10 Reply Ignore Print
Author Topic: Large message boards and MySQL logs of slow response  (Read 248212 times)
beneluxrail
Noobie
*
Posts: 17


Re:Large message boards and MySQL logs of slow response
« Reply #45 on: April 16, 2002, 05:01:10 PM »
Reply with quote

Please do!! It would be nice to have a script to do so, and periodicly optimize the dbase as well.  :D
Logged
RHiSC
Jr. Member
**
Posts: 96


I'm a llama!

Re:Large message boards and MySQL logs of slow response
« Reply #46 on: April 17, 2002, 01:11:05 AM »
Reply with quote

yeah, that would be great! nice suggestion...
Logged
mediman
Support Team
YaBB God
*****
Posts: 2858


WWW
Re:Large message boards and MySQL logs of slow response
« Reply #47 on: April 17, 2002, 01:17:51 AM »
Reply with quote

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

mainComm Dev Team
RHiSC
Jr. Member
**
Posts: 96


I'm a llama!

Re:Large message boards and MySQL logs of slow response
« Reply #48 on: April 17, 2002, 03:33:30 AM »
Reply with quote

awesome!!! standing by...
Logged
goosemoose
Sr. Member
****
Posts: 256


I'm a llama!

Re:Large message boards and MySQL logs of slow response
« Reply #49 on: April 18, 2002, 07:35:50 PM »
Reply with quote

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
Logged

MobileYaBB v.3 [75% done]
All in One MemberList 1.0
Integrated Chat v 2.0
mediman
Support Team
YaBB God
*****
Posts: 2858


WWW
Re:Large message boards and MySQL logs of slow response
« Reply #50 on: April 18, 2002, 07:42:52 PM »
Reply with quote

try this script it makes all changes for you!
<?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>";
}

?>
« Last Edit: June 26, 2002, 09:46:14 PM by mediman » Logged

mainComm Dev Team
Purple Raine
Beta Tester
Full Member
***
Posts: 141


Who's Yo Daddy?

WWW
Re:Large message boards and MySQL logs of slow response
« Reply #51 on: April 19, 2002, 08:39:29 PM »
Reply with quote

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?

Logged
mediman
Support Team
YaBB God
*****
Posts: 2858


WWW
Re:Large message boards and MySQL logs of slow response
« Reply #52 on: April 19, 2002, 08:50:59 PM »
Reply with quote

try this

<?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

Logged

mainComm Dev Team
Delinquently insane
Foolish One
YaBB God
*****
Posts: 529


The One Surpassed By God!!!!

Re:Large message boards and MySQL logs of slow response
« Reply #53 on: April 20, 2002, 01:14:36 AM »
Reply with quote

awesome blazing fast   :)
Logged

ALL MODS Cancelled projected terminated
luisr
Full Member
***
Posts: 120


Left blank to save space.

Re:Large message boards and MySQL logs of slow response
« Reply #54 on: April 25, 2002, 02:46:38 AM »
Reply with quote

Just curious about this topic.  My board has about 900 messages and 110 users.  Will I benefit from this?
Logged
tore-
Sr. Member
****
Posts: 291


I love YaBB SE!

Re:Large message boards and MySQL logs of slow response
« Reply #55 on: April 25, 2002, 05:09:29 AM »
Reply with quote

Quote from: luisr on April 25, 2002, 02:46:38 AMJust curious about this topic.  My board has about 900 messages and 110 users.  Will I benefit from this?

yes
Logged

Star wars rules!
Golden Tiger
Jr. Member
**
Posts: 74


Lost all reality...

ICQ - 5689654 WWW
Re:Large message boards and MySQL logs of slow response
« Reply #56 on: April 28, 2002, 08:29:37 PM »
Reply with quote

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?
Logged

mediman
Support Team
YaBB God
*****
Posts: 2858


WWW
Re:Large message boards and MySQL logs of slow response
« Reply #57 on: April 28, 2002, 08:42:15 PM »
Reply with quote

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

<?

$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!
« Last Edit: June 26, 2002, 09:44:02 PM by mediman » Logged

mainComm Dev Team
Golden Tiger
Jr. Member
**
Posts: 74


Lost all reality...

ICQ - 5689654 WWW
Re:Large message boards and MySQL logs of slow response
« Reply #58 on: April 28, 2002, 08:55:33 PM »
Reply with quote

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!
Logged

mediman
Support Team
YaBB God
*****
Posts: 2858


WWW
Re:Large message boards and MySQL logs of slow response
« Reply #59 on: April 28, 2002, 08:58:35 PM »
Reply with quote

good idea ;) perhaps with a db checker ???

medi
Logged

mainComm Dev Team
Pages: 1 2 3 [4] 5 6 ... 10 Reply Ignore Print 
YaBB SE Community  |  English User Help  |  FAQ  |  Large message boards and MySQL logs of slow response « previous - next »
 


Powered by MySQL Powered by PHP YaBB SE Community | Powered by YaBB SE
© 2001-2003, YaBB SE Dev Team. All Rights Reserved.
SMF 2.1.4 © 2023, Simple Machines
Valid XHTML 1.0! Valid CSS

Page created in 0.026 seconds with 19 queries.