Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > ISPConfig 2 > General

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Reply
 
Thread Tools Display Modes
  #1  
Old 8th December 2006, 00:26
Jcorrea920 Jcorrea920 is offline
Member
 
Join Date: Feb 2006
Posts: 82
Thanks: 4
Thanked 1 Time in 1 Post
Unhappy Customer Web Site: index.php with mysql queries take 18 seconds to load!

Running:
Fedora Core 6 Perfect Install
Client Side Web Site Versions:
ISPConfig 2.2.8
Apache 2.2.3
MySQL 5.0.22
PHP 5.1.6 & 5.0.4-fast-cgi
Jetbox CMS

I noticed the latency with two of my customer's websites when I made the switch from Fedora Core 4 to Fedora Core 6. Latency on the new software was about 33 seconds. I wasn't sure if it was the change in kernel or the change in php version so when I installed version 5.0.4 in parallel to 5.1.6 in brought down the latency from 33 seconds to about 12 seconds. And after running a "yum update" the latency went to about 18 seconds. So just so that I don't loose the clients I created a simple HTML page with the same CSS with "Click Here to Enter Site" while the website loads just to give them something to do while the latency takes its time. The HTML page loads immediately. There is another client that stayed on the Fedora Core 4 Server with php 5.0.4 and his index.php page loads immediately. The only difference is that Fedora Core 4 is behind a generic firewall provided by our ISP and the Fedora Core 6 system is behind IPCOP 1.4.11 & Copfilter add-on with HTTP antivirus scan/filter enabled.

Should I create a flash movie that lasts about 15 seconds and loops to add to the HTML page?

Could it be the mysql queries?

Code:
tail /var/www/web3/log/error.log

[Thu Dec 07 07:44:36 2006] [error] [client] File does not exist: /var/www/web3/web/favicon.ico

Trying to reduce massive latency.
Help is greatly appreciated.
Reply With Quote
Sponsored Links
  #2  
Old 8th December 2006, 11:18
till till is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 37,015
Thanks: 840
Thanked 5,652 Times in 4,461 Posts
Default

This sounds to be a problem with your mysql query. If the database is not several GB large, then there are some indices missing in the database. make sure you have indicies on all database columns that you use in select and join statements.
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
  #3  
Old 9th December 2006, 03:20
Jcorrea920 Jcorrea920 is offline
Member
 
Join Date: Feb 2006
Posts: 82
Thanks: 4
Thanked 1 Time in 1 Post
Question Indexes exist...

The first SELECT statement in index.php:

Code:
        // Login and registration is handeled by webuser.php
        // All login & registration function can be found in /includes/f_jetstream_core_one.inc.php
        // Check out webuser.php for more info if something goes wrong

        // Check for front-end session
        if ($_COOKIE[session_name()]) {
                session_start();
                if ($_SESSION["uid"] && $_SESSION["type"]=="frontend") {
                        $uresult=mysql_prefix_query("SELECT * FROM webuser WHERE
 uid='".$_SESSION["uid"]."'") or die(mysql_error());
                        if (mysql_num_rows($uresult)>0) {
                                $uarray=mysql_fetch_array($uresult);
                        }
                }
        }
}
Webuser (column/table) seems to have a healthy index "uid". There is no data in the table but the index takes up 1024 bytes. There are not any rows in this column and the next autoindex is 1.

The next SELECT statement:

Code:
        $sql2="SELECT * FROM navigation WHERE view_name='".$view."'";
        $r2 = mysql_prefix_query($sql2) or die(mysql_error()." q: ".$sql2."<br /
> Line: ".__LINE__." <br/>File: ".__FILE__);
        if ($ra2 = mysql_fetch_array($r2)){
                //echo $ra2["file_name"];
                include($ra2["file_name"]);
        }
        else{
                $dodefaultpage=true;
        }
}
Navigation Column seems to have a healthy index as well "nav_id".

And the last SELECT Statement is the struct Column which also seems to be healthy.

Code:
elseif (isset($item) && is_numeric($item)){
        //All items with workflow have a unique id in the struct db table
        //Check what type off item it is.
        //The container_id is the id Jetstream CMS uses to registrate containers
        $dodefaultpage=false;
        $primq = "SELECT struct.*, UNIX_TIMESTAMP(struct.ondate) AS uondate, UNI
X_TIMESTAMP(struct.offdate) AS uoffdate, struct.id AS struct_id FROM struct WHER
E struct.id=".$item;
        $primr = mysql_prefix_query($primq) or die(mysql_error());
        if ($primarray = mysql_fetch_array($primr)){
                switch ($primarray["container_id"]){
                        case 11:
There are not any JOIN statements in the file. I do admin there are a few columns that don't have indexes in the database but they are not called in the index.php and the other customer (on Fedora Core 4 Server) has the same database configuration and load in less than 3 seconds. What could it be? Thanks in advanced Till.
Reply With Quote
  #4  
Old 9th December 2006, 07:33
Jcorrea920 Jcorrea920 is offline
Member
 
Join Date: Feb 2006
Posts: 82
Thanks: 4
Thanked 1 Time in 1 Post
Lightbulb Enableling loggin for slow queries might help

Till,
I will enable logging and post my log later this weekend. If anyone is interested in enableling logging for slow mysql queries:

http://www.ducea.com/2006/11/06/iden...-slow-queries/
Reply With Quote
  #5  
Old 29th December 2006, 04:38
Craig Craig is offline
Member
 
Join Date: Dec 2006
Posts: 56
Thanks: 1
Thanked 3 Times in 1 Post
 
Default

You have :
Code:
$sql2="SELECT * FROM navigation WHERE view_name='".$view."'";
but then only mention an index on "nav_id". Shouldn't there be an index on "view_name" or did you intend to type "view_name" and ended up typing "nav_id"?

Logging slow queries as well as logging joins for which there were no indexes to use are VERY powerful and I often use but there is one thing to remember, if the MySQL server is heavily loaded, queries that should take single digit miliseconds to complete can all of a sudden start taking seconds so it is good to take the data in the long query log with a grain of salt.

One thing that I have found is that the more joins there are in a given query, the more chances there are for a given table that the joins depends on will be locked at the time of the query, due to the record or table, depending on table type, being updated. In other words, start with the ugliest queries and either optimize or split them up as needed so they no longer show up in the long query log and you'll likely find that unrelated queries that showed up in the log previously are no longer there.

In the case of multi-table joins, although they are elegant and in most cases more efficient, they basically expose one to a greater chance of running into locked table conditions.

But, all that said, if the only change you made was OS rev. level and although optimizing MySQL whenever possible is good, it is even better to resist the urge to apply a band-aid to one's elbow because their knee is smashed and search out the root cause itself.

One last thing, it is also important to make sure that one isn't delving into the world of quantum physics where the outcome of an experiment changes simply by observing the experiment. I can't count the number of times I started searching for the root of a MySQL problem that I just discovered only to find that the "problem" had always been there, I just hadn't been in the right situation to observe it before.

That's not to say that there isn't a problem or that it is not happening but instead, make sure that you are observing an average of load times over the course of a few days to a week to make sure that you aren't just seeing things at their worst.

By the way, do you use MySQL Administrator? Using the real time stats and graphs really really helps get a better idea of what is going on. I've gotten to the point where I can tell you, within a couple of dozen visitors, how many totally visitors are active on a given site that normally has up to 20k visitors in a 30 minute period of time. That's not due to my expertise, that's due to MySQL Administrator making it so easy.

I apologize if nothing I've written is new to you as I'm no expert either but what I have learned, came at a GREAT cost and I'd like to share it to get more bang for my buck!
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Static Web Site Configurations christopher Installation/Configuration 8 18th November 2006 15:43
Virtual Users And Domains With Postfix, Courier And MySQL (mail not relaying) RinoM1 Server Operation 1 12th November 2006 16:31
Site with MySQL problem?! edge Server Operation 2 13th August 2006 20:57
Howto suggestion suse PhP ver 4 + Ver 5 wwparrish Suggest HOWTO 11 7th August 2006 14:29
Mandriva 10.2 Perfect Setup Install Problems... ctroyp Installation/Configuration 12 30th December 2005 17:04


All times are GMT +2. The time now is 03:06.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.