Welcome, Guest. Please Login or Register.
November 24, 2017, 04:52:14 PM
Home Help Search Login 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  |  Topic: Large message boards and MySQL logs of slow response « previous next »
Pages: 1 ... 7 8 [9] 10 Reply Ignore Print
Author Topic: Large message boards and MySQL logs of slow response  (Read 63106 times)
Compuart
Quality, Quality, Quality!
YaBB God
*****
Posts: 1283



WWW
Re:Large message boards and MySQL logs of slow response
« Reply #120 on: September 16, 2002, 12:37:56 PM »
Reply with quote


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
Logged

Will
Noobie
*
Posts: 41


Let me be me...


WWW
Re:Large message boards and MySQL logs of slow response
« Reply #121 on: October 10, 2002, 09:28:46 AM »
Reply with quote

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

Welcome To The crappity smackin'Jungle!!

http://www.GNRFrance.net
VanGogh
Noobie
*
Posts: 7


I'm a llama!


Re:Large message boards and MySQL logs of slow response
« Reply #122 on: October 18, 2002, 01:27:17 PM »
Reply with quote

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
Logged
fungku
Noobie
*
Posts: 23


alcanime.net


Re:Large message boards and MySQL logs of slow response
« Reply #123 on: December 10, 2002, 11:02:59 AM »
Reply with quote

Hi,

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

Code: [Select]
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  ;)
Logged
mediman
Support Team
YaBB God
*****
Posts: 2858



WWW
Re:Large message boards and MySQL logs of slow response
« Reply #124 on: December 10, 2002, 10:32:43 PM »
Reply with quote

hi please use this

Code: [Select]
<?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
« Last Edit: December 10, 2002, 11:53:49 PM by mediman » Logged

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



WWW
Re:Large message boards and MySQL logs of slow response
« Reply #125 on: December 10, 2002, 10:35:29 PM »
Reply with quote


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
Logged

mainComm Dev Team
fungku
Noobie
*
Posts: 23


alcanime.net


Re:Large message boards and MySQL logs of slow response
« Reply #126 on: December 10, 2002, 11:39:39 PM »
Reply with quote

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  :P
« Last Edit: December 10, 2002, 11:45:01 PM by fungku » Logged
mediman
Support Team
YaBB God
*****
Posts: 2858



WWW
Re:Large message boards and MySQL logs of slow response
« Reply #127 on: December 10, 2002, 11:55:30 PM »
Reply with quote

sorry i changed the script please try again!

im a little bit tired, is 5:51 a.m here in germany  ::)

medi
Logged

mainComm Dev Team
fungku
Noobie
*
Posts: 23


alcanime.net


Re:Large message boards and MySQL logs of slow response
« Reply #128 on: December 11, 2002, 12:00:38 AM »
Reply with quote

Thanks a lot medi, worked great!
Logged
mediman
Support Team
YaBB God
*****
Posts: 2858



WWW
Re:Large message boards and MySQL logs of slow response
« Reply #129 on: December 11, 2002, 12:13:44 AM »
Reply with quote

thx fungku! and from now on try

Code: [Select]
<?
/*****************************************************************************/
/* dboptimizer.php                                                         */
/*****************************************************************************/
/* Software Distributed by:    http://www.maincomm.de                        */
/* Support, News, Updates at:  yabbse@maincomm.de                            */
/* ========================================================================= */
/* 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
« Last Edit: December 11, 2002, 12:29:57 AM by mediman » Logged

mainComm Dev Team
PioneeR
Llama Hunter
YaBB God
*****
Posts: 767



Re:Large message boards and MySQL logs of slow response
« Reply #130 on: December 11, 2002, 09:32:41 PM »
Reply with quote

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]
<?php<br><br>$db_name "";<br>$db_user "";<br>$db_passwd "";<br>$db_server "";<br>$db_prefix "";<br><br>mysql_connect("$db_server","$db_user","$db_passwd");<br>echo "<font color ='red'>We change your tables to MyISAM type and analyze/optimze all tables also indices will be added!</font><br>";<br>typechanging();<br>optimize();<br>addindices();<br><br>function typechanging() {<br>global $db_name;<br>mysql_select_db($db_name); <br>$tables=mysql_list_tables($db_name);<br>while (list($bla)=mysql_fetch_array($tables)) {<br>$result mysql_query("ALTER TABLE $bla TYPE=MyISAM")or die("Invalid query");<br>echo "Table <font color=blue>".$bla."</font> converted to MyISAM!<br>"; <br>}<br>}<br><br>function optimize() {<br>global $db_name;<br>mysql_select_db($db_name); <br>$tables=mysql_list_tables($db_name);<br>while (list($bla)=mysql_fetch_array($tables)) {<br>$result mysql_query("OPTIMIZE TABLE $bla")or die("Invalid query");<br>echo "Table <font color=blue>".$bla."</font> analyzed and optimized!<br>"; <br>}<br>}<br><br>function addindices() {<br>global $db_name,$db_prefix;<br>mysql_select_db($db_name); <br>mysql_query("ALTER TABLE {$db_prefix}members add key(memberGroup(30)");<br>mysql_query("ALTER TABLE {$db_prefix}members add key(posts)");<br>mysql_query("ALTER TABLE {$db_prefix}members add key(memberName(30)");<br>mysql_query("ALTER TABLE {$db_prefix}members add key(dateRegistered)");<br>mysql_query("ALTER TABLE {$db_prefix}members add key(lastLogin)");<br>mysql_query("ALTER TABLE {$db_prefix}messages add key(posterTime)");<br>mysql_query("ALTER TABLE {$db_prefix}instant_messages add key (ID_MEMBER_FROM)");<br>mysql_query("ALTER TABLE {$db_prefix}instant_messages add key (ID_MEMBER_TO)");<br>mysql_query("ALTER TABLE {$db_prefix}instant_messages add key (deletedBy)");<br>mysql_query("ALTER TABLE {$db_prefix}instant_messages add key(fromName(30)");<br>mysql_query("ALTER TABLE {$db_prefix}instant_messages add key(toName(30)");<br>mysql_query("ALTER TABLE {$db_prefix}categories add key (catOrder)");<br>mysql_query("ALTER TABLE {$db_prefix}boards add key (ID_CAT)");<br>mysql_query("ALTER TABLE {$db_prefix}log_boards add key (ID_BOARD)");<br>mysql_query("ALTER TABLE {$db_prefix}log_boards add key (memberName(30)");<br>mysql_query("ALTER TABLE {$db_prefix}log_topics add key (membername(30)");<br>mysql_query("ALTER TABLE {$db_prefix}log_topics add key (logTime)");<br>mysql_query("ALTER TABLE {$db_prefix}log_topics add key (ID_TOPIC)");<br>mysql_query("ALTER TABLE {$db_prefix}log_activity add key (month)");<br>mysql_query("ALTER TABLE {$db_prefix}log_activity add key (day)");<br>mysql_query("ALTER TABLE {$db_prefix}log_activity add key (year)");<br>mysql_query("ALTER TABLE {$db_prefix}log_activity add key (mostOn)");<br>mysql_query("ALTER TABLE {$db_prefix}log_errors add key (logTime)");<br>mysql_query("ALTER TABLE {$db_prefix}log_errors add key (memberName(30)");<br>mysql_query("ALTER TABLE {$db_prefix}log_floodcontrol add key (ip(20)");<br>mysql_query("ALTER TABLE {$db_prefix}log_floodcontrol add key (logTime)");<br>mysql_query("ALTER TABLE {$db_prefix}log_karma add key (ID_TARGET)");<br>mysql_query("ALTER TABLE {$db_prefix}log_karma add key (ID_EXECUTOR)");<br>mysql_query("ALTER TABLE {$db_prefix}log_karma add key(logTime)");<br>mysql_query("ALTER TABLE {$db_prefix}log_mark_read add key (memberName(30)");<br>mysql_query("ALTER TABLE {$db_prefix}log_mark_read add key (ID_BOARD)");<br>mysql_query("ALTER TABLE {$db_prefix}log_mark_read add key (logTime)");<br>mysql_query("ALTER TABLE {$db_prefix}log_online add key (logTime)");<br>mysql_query("ALTER TABLE {$db_prefix}settings add key(variable(30)");<br>echo "<font color ='red'>Indices succesfully added!</font>";<br>}<br><br>?>

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



WWW
Re:Large message boards and MySQL logs of slow response
« Reply #131 on: December 11, 2002, 11:27:29 PM »
Reply with quote

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

thx pioneer!
Logged

mainComm Dev Team
wildgun
Noobie
*
Posts: 1


I'm a llama!


Re:Large message boards and MySQL logs of slow response
« Reply #132 on: December 14, 2002, 01:50:57 AM »
Reply with quote

test test
Logged
complaints
Noobie
*
Posts: 1


I'm a llama!


Re:Large message boards and MySQL logs of slow response
« Reply #133 on: January 16, 2003, 04:59:13 AM »
Reply with quote

Cyber-sci.com are abusing
Logged
Jeff Lewis
Global Moderator
YaBB God
*****
Posts: 10149


I'm a llama!


WWW
Re:Large message boards and MySQL logs of slow response
« Reply #134 on: January 16, 2003, 08:38:17 AM »
Reply with quote

Abusing what?
Logged

Pages: 1 ... 7 8 [9] 10 Reply Ignore Print 
YaBB SE Community  |  English User Help  |  FAQ  |  Topic: 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.0.12 | SMF © 2016, Simple Machines
Valid XHTML 1.0! Valid CSS!
Page created in 0.095 seconds with 19 queries.