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.

 

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("[B][COLOR="red"]SELECT[/COLOR][/B] * 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="[B][COLOR="red"]SELECT[/COLOR][/B] * 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 = "[B][COLOR="Red"]SELECT[/COLOR][/B] 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.

 

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/identifying-mysql-slow-queries/

 

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!