Large message boards and MySQL logs of slow response

[Compuart]

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

both are not included in 1.4.1

[Will]

I can't run the script Medi wrote...I tried and the page loads for 10 minutes and nothing happens.

I tried to run the query in PHPAdmin but I got the following message error:
"Too many keys specified. Max 32 keys allowed"

What the hell is that? I'm very unlucky cuz since I tried to run the .PHP file, my forum is even slower (I can't open the threads anymore)...
I did a dump of my db, but how am I supposed to upload that? It's just a big .SQL file...

[VanGogh]

Ummm...... wow.

I am coming late to this gravy train, but that is incredible.  The speed difference is so noticable it's almost laughable.

Thank you very much!

VanGogh

[fungku]

Hi,

My MYSQL database is on a remote server.
When I run the first script it gives me an error:

Code Select Expand

Warning: MySQL Connection Failed: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111) in /home/sites/site12/web/OptimizeDatabase.php on line 9

Fatal error: Call to undefined function: mysql_get_server_info() in /home/sites/site12/web/OptimizeDatabase.php on line 10
Does anyone know what my problem could be?

I really want to speed up my YaBB SE 1.4.1, I am very ipatient and I hate waiting for my BoardIndex to load  

[mediman]

hi please use this

Code Select Expand

<?php

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

mysql_connect("$db_server","$db_user","$db_passwd");
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();

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

?>
medi

[mediman]

I can't run the script Medi wrote...I tried and the page loads for 10 minutes and nothing happens.

I tried to run the query in PHPAdmin but I got the following message error:
"Too many keys specified. Max 32 keys allowed"

What the hell is that? I'm very unlucky cuz since I tried to run the .PHP file, my forum is even slower (I can't open the threads anymore)...
I did a dump of my db, but how am I supposed to upload that? It's just a big .SQL file...

Please more Info! MySQL Version?

medi

[fungku]

Thanks medi  , but now I get this error:

Parse error: parse error in /home/sites/site12/web/odb.php on line 14


I looked on line 14, I know nothing about PHP or mysql, but is that right curly bracket } supposed to be there? There is no left one before it  

[mediman]

sorry i changed the script please try again!

i´m a little bit tired, is 5:51 a.m here in germany  

medi

[fungku]

Thanks a lot medi, worked great!

[mediman]

thx fungku! and from now on try

Code Select Expand

<?
/*****************************************************************************/
/* dboptimizer.php                                                         */
/*****************************************************************************/
/* Software Distributed by:    http://www.maincomm.de                        */
/* Support, News, Updates at:  [email protected]                            */
/* ========================================================================= */
/* Copyright (c) 2001-2002 The mainComm DEV Team                             */
/* The maincomm DEV Team is: Manju, Medizinmann, Mediman                     */
/* Software by: The maincomm Dev Team                                        */
/*****************************************************************************/
/* This program is free software; you can redistribute it and/or modify it   */
/* under the terms of the GNU General Public License as published by the     */
/* Free Software Foundation; either version 2 of the License, or (at your    */
/* option) any later version.                                                */
/*                                                                           */
/* This program is distributed in the hope that it will be useful, but       */
/* WITHOUT ANY WARRANTY; without even the implied warranty of                */
/* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General */
/* Public License for more details.                                          */
/*                                                                           */
/* The GNU GPL can be found in gpl.txt in this directory                     */
/*****************************************************************************/

//script for optimizing your database tables

  //your database connection
    //databasename, username, pwd, host
    $dbName = "";
    $dbUser = "";
    $dbPass = "";
    $dbHost = "";

  function getConnection($dbName, $dbUser, $dbPass, $dbHost){

    $db = mysql_connect ( $dbHost, $dbUser, $dbPass );
    mysql_select_db($dbName, $db) or die("Unable to select database");

    return $db;
  }

  function closeConnection($db){
    mysql_close($db);
  }

  $db = getConnection($dbName, $dbUser, $dbPass, $dbHost);

    $query = "SHOW TABLE STATUS FROM ".$dbName;
    $get_tables = mysql_query($query, $db);
    $num_tabs = mysql_num_rows($get_tables);

    echo "<font color=red>Your database contains $num_tabs! tables.<p></font>";

    if(0 < $num_tabs){
      echo "trying to optimize tables ... <p>";

      $k = 0;
      $i = 0;

      while($table = mysql_fetch_array($get_tables, $db)){
        $tableName = mysql_tablename($get_tables, $i);

        $datalength = $table['Data_length'];
        $indexlength = $table['Index_length'];
        $length = $datalength + $indexlength;
        $length = $length / 1024;
        $length = round ($length, 3);

        $freedata = $table['Data_free'];
        $freedata = $freedata / 1024;
        $freedata = round ($freedata, 3);

        //optimize
        $query = "OPTIMIZE TABLE ".$tableName;
        $resultat  = mysql_query($query, $db);

        if($freedata == 0){ $k = $k + 1; }
        else {
           echo "<font color=red>optimize:</font> $tableName .... <font color=green>$freedata kb optimized</font><br>";
           $opttab++;
        }

        $i++;
      }
      $cnt = $num_tabs - $k;

      if($cnt == 0){ echo "<p><font color=green>all tables are optimized</font>"; }
      else { echo "<p><font color=red>$opttab tables optimized</font>"; }

    } // end if

  closeConnection($db);


?>
thx medi

[PioneeR]

I used the following and got thiese errors

We change your tables to MyISAM type and analyze/optimze all tables also indices will be added!

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/***/public_html/yabbse/dboptimise.php on line 19

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/***/public_html/yabbse/dboptimise.php on line 29
Indices succesfully added!

hi please use this

Code Select Expand

<?php

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

mysql_connect("$db_server","$db_user","$db_passwd");
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();

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

?>
medi

[mediman]

can you please tell me you version of mysql / php ?
are you sure you put in the correct db name?

thx pioneer!

[wildgun]

test test

[complaints]

Cyber-sci.com are abusing

[Jeff Lewis]

Abusing what?