You are missing our premiere tool bar navigation system! Register and use it for FREE!

NukeCops  
•  Home •  Downloads •  Gallery •  Your Account •  Forums • 
Readme First
- Readme First! -

Read and follow the rules, otherwise your posts will be closed
Modules
· Home
· FAQ
· Buy a Theme
· Advertising
· AvantGo
· Bookmarks
· Columbia
· Community
· Donations
· Downloads
· Feedback
· Forums
· PHP-Nuke HOWTO
· Private Messages
· Search
· Statistics
· Stories Archive
· Submit News
· Surveys
· Theme Gallery
· Top
· Topics
· Your Account
Who's Online
There are currently, 270 guest(s) and 0 member(s) that are online.

You are Anonymous user. You can register for free by clicking here
How to proceed when MySQL is slow

25.8. How to proceed when MySQL is slow

It is of course too general a complain to say "MySQL is slow! What is wrong with my pages?", but nonetheless, there exist a couple of things you can check if this happens to you (see slow page load and mysql errors):

  • First obvious question: did your host upgrade or make changes to MySQL?

  • Second, any new or modified modules added?

  • Third, does your host have 'log slow queries' enabled for mysql so you can isolate which ones are causing the problems? When started with the --log-slow-queries[=file_name] option, mysqld writes a log file containing all SQL commands that took more than long_query_time seconds to execute. See The Slow Query Log.

  • Does this happen randomly or during certain time periods throughout the day?

  • Maybe you need to rebuild your indexes. To rebuild your indexes, the easiest way to do so for a MyISAM table is to use the following statement, which rebuilds the index file:

    mysql> REPAIR TABLE tbl_name QUICK;
    

    If you don't have access to the MySQL prompt through a terminal, you can write a small PHP script that does the same. Copy the following code into a file and save it as, say, repair.php:

    <?php
    require("config.php");
    $host = $dbhost;
    $database = $dbname;
    $username = $dbuname;
    $password = $dbpass;
    mysql_connect($host, $username, $password);
    @mysql_select_db($database);
    $res = mysql_query("repair table ".$prefix."_XXXX");
    if ($res) {
        echo $res;
    } else {
        print (mysql_error());
    }
    ?>
    

    Dont't forget to replace "XXXX" with the actual name (without the prefix!) of the table you are suspecting of causing you trouble. Read the table definitions in the nuke.sql file that came with your PHP-Nuke package under the sql directory (you used nuke.sql during installation to populate the database, see Section 3.2.4 and Section 3.4.5).

    Upload repair.php to the same directory on your web server where also config.php is in - and point your browser to it. Of course, you should delete it after you are done.

  • The phpBB software used for the standard Forum in PHP-Nuke, comes with a table, nuke_bbsearch_wordlist, that is used to store words that occur in forum posts. Due to the large size of your database, the nuke_bbsearch_wordlist table may have grown very large. By default phpBB does not include an index for that table. Lack of an index on nuke_bbsearch_wordlist can make your forum slow down. Running the SQL command below will increase the size of the nuke_bbsearch_wordlist table (could double it), but a performance increase is expected with the index (see How to Increase your Forums Performance):

    ALTER TABLE nuke_bbsearch_wordmatch ADD INDEX post_id (post_id);
    

    Run this during a time on your site when it is least busy. More records on that table means longer run time.

    If you find that this doesn't work for you, you can remove the index with:

    ALTER TABLE nuke_search_wordmatch DROP INDEX post_id;
    
Tip Large Slow Query Logs
 

The slow query log can be used to find queries that take a long time to execute and are thus candidates for optimization . With a large log, that can become a difficult task. You can pipe the slow query log through the mysqldumpslow command to get a summary of the queries which appear in the log.

Powered by TOGETHER TEAM srl ITALY http://www.togetherteam.it - DONDELEO E-COMMERCE http://www.DonDeLeo.com - TUTTISU E-COMMERCE http://www.tuttisu.it
Web site engine's code is Copyright © 2002 by PHP-Nuke. All Rights Reserved. PHP-Nuke is Free Software released under the GNU/GPL license.
Page Generation: 0.151 Seconds - 409 pages served in past 5 minutes. Nuke Cops Founded by Paul Laudanski (Zhen-Xjell)
:: FI Theme :: PHP-Nuke theme by coldblooded (www.nukemods.com) ::