Solved

mysql authentication takes forever

Posted on 2014-10-20
6
319 Views
Last Modified: 2014-10-26
We have a website URL(php on apache), which opens up a login page. While the login page loads fine, after entering the username and password, it takes ages to login. It looks like the php-mysql authentication is taking a lot of time, but I can't figure out why it's taking so much time.

I have also tested this on localhost, so dns issue is ruled out. Can someone suggest how to drill down further to understand what's causing the slowness?


Thanks,
0
Comment
Question by:Starquest321
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 7

Assisted Solution

by:Thomas Wheeler
Thomas Wheeler earned 100 total points
ID: 40393726
What type of login page is it? What is the query being used? Is it slow to login to myself via the command line?
0
 

Author Comment

by:Starquest321
ID: 40393739
This is a standard login ...using mysql normal standard connections. What can we check in order to trouble shoot? Is there some log?
0
 
LVL 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 100 total points
ID: 40393771
Can't be a "standard login".  The time it takes to submit a username and password and check it against a MySQL database table is on the order of milliseconds, faster than you can blink.  Can you write a simple 'select' routine to get data from the MySQL server?  That's what I would do to check the actual response from the server.
0
WordPress Tutorial 2: Terminology

An important part of learning any new piece of software is understanding the terminology it uses. Thankfully WordPress uses fairly simple names for everything that make it easy to start using the software.

 
LVL 7

Assisted Solution

by:Stampel
Stampel earned 100 total points
ID: 40393804
Activate full mysql query log in your /etc/my.cnf file using this 2 lines :
general_log_file=/var/log/mysql-query.log
general_log=0

Restart MySQL, refresh your apache and during the same time place a tail to see the log coming.
[root@www ~]$ tail -f /var/log/mysql-query.log

If you are under windows, use mTail software to "spy" the logfile

This way you will now when it looses time.
0
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 100 total points
ID: 40394234
If you use the design pattern shown in this article, login will be instantaneous.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_2391-PHP-login-logout-and-easy-access-control.html

If you're suspicious of the MySQL query, you might want to try EXPLAIN SELECT.
http://dev.mysql.com/doc/refman/5.7/en/using-explain.html

If you post the CREATE TABLE statements and show us the PHP code you're using we may be able to do something more than guess about your situation, but without seeing that information all we can give you is generalization.

If you want to attach instrumentation to the process to see where the time is being lost, this little class will enable that process.  There are examples of usage at the end of the script.

<?php // demo/class_Stopwatch.php
error_reporting(E_ALL);

// A SCRIPT TIMER FOR ALL OR PART OF A SCRIPT PHP 5+
// MAN PAGE http://php.net/manual/en/function.microtime.php
class StopWatch
{
    protected $a; // START TIME
    protected $s; // STATUS - IF RUNNING
    protected $z; // STOP TIME

    public function __construct()
    {
        $this->a = array();
        $this->s = array();
        $this->z = array();
    }

    // A METHOD TO PROVIDE A FINAL READOUT, IF NEEDED
    public function __destruct()
    {
        $ret = $this->readout();
        if (!$ret) return FALSE;
        echo
          __CLASS__
        . '::'
        . __FUNCTION__
        . '() '
        ;
        echo "<b>$ret</b>";
        echo PHP_EOL;
    }

    // A METHOD TO REMOVE A TIMER
    public function reset($name='TIMER')
    {
        // RESET ALL TIMERS
        if ($name == 'TIMER')
        {
            $this->__construct();
        }
        else
        {
            unset($this->a[$name]);
            unset($this->s[$name]);
            unset($this->z[$name]);
        }
    }

    // A METHOD TO CAPTURE THE START TIME
    public function start($name='TIMER')
    {
        $this->a[$name] = microtime(TRUE);
        $this->z[$name] = $this->a[$name];
        $this->s[$name] = 'RUNNING';
    }

    // A METHOD TO CAPTURE THE END TIME
    public function stop($name='TIMER')
    {
        $ret = NULL;

        // STOP ALL THE TIMERS
        if ($name == 'TIMER')
        {
            foreach ($this->a as $name => $start_time)
            {
                // IF THIS TIMER IS STILL RUNNING, STOP IT
                if ($this->s[$name])
                {
                    $this->s[$name] = FALSE;
                    $this->z[$name] = microtime(TRUE);
                }
            }
        }

        // STOP ONLY ONE OF THE TIMERS
        else
        {
            if ($this->s[$name])
            {
                $this->s[$name] = FALSE;
                $this->z[$name] = microtime(TRUE);
            }
            else
            {
                $ret .= "ERROR: CALL TO STOP() METHOD: '$name' IS NOT RUNNING";
            }
        }

        // RETURN AN ERROR MESSAGE, IF ANY
        return $ret;
    }

    // A METHOD TO READ OUT THE TIMER(S)
    public function readout($name='TIMER', $dec=3, $m=1000, $t = 'ms', $eol=PHP_EOL)
    {
        $str = NULL;

        // GET READOUTS FOR ALL THE TIMERS
        if ($name == 'TIMER')
        {
            foreach ($this->a as $name => $start_time)
            {
                $str .= $name;

                // IF THIS TIMER IS STILL RUNNING UPDATE THE END TIME
                if ($this->s[$name])
                {
                    $this->z[$name] = microtime(TRUE);
                    $str .= " RUNNING ";
                }
                else
                {
                    $str .= " STOPPED ";
                }

                // RETURN A DISPLAY STRING
                $lapse_time = $this->z[$name] - $start_time;
                $lapse_msec = $lapse_time * $m;
                $lapse_echo = number_format($lapse_msec, $dec);
                $str .= " $lapse_echo $t";
                $str .= $eol;
            }
            return $str;
        }

        // GET A READOUT FOR ONLY ONE TIMER
        else
        {
            $str .= $name;

            // IF THIS TIME IS STILL RUNNING, UPDATE THE END TIME
            if ($this->s[$name])
            {
                $this->z[$name] = microtime(TRUE);
                $str .= " RUNNING ";
            }
            else
            {
                $str .= " STOPPED ";
            }

            // RETURN A DISPLAY STRING
            $lapse_time = $this->z[$name] - $this->a[$name];
            $lapse_msec = $lapse_time * $m;
            $lapse_echo = number_format($lapse_msec, $dec);
            $str .= " $lapse_echo $t";
            $str .= $eol;
            return $str;
        }
    }
}


// DEMONSTRATE THE USE -- INSTANTIATE THE STOPWATCH OBJECT
$sw  = new Stopwatch;

// SET A STOPWATCH NAME THAT REFLECTS THE PARTS OF THE SCRIPT WE WANT TO TIME
$g_timer = 'GOOGLE TIMER';

// START A TIMER TO GET ELAPSED TIME FOR A CALL TO GOOGLE
$sw->start($g_timer);

// PERFORM SOME ACTIVITY THAT YOU WANT TO TIME (READS GOOGLE WEB PAGE)
$page = 'https://www.google.com';
$html = file_get_contents($page);

// GET A READOUT OF THE TIMER WHILE IT IS STILL RUNNING
echo nl2br($sw->readout($g_timer));
echo "<br/>" . PHP_EOL;

// PERFORM SOME OTHER ACTIVITY (READS GOOGLE WEB PAGE AGAIN)
$page = 'https://www.google.com';
$html = file_get_contents($page);

// STOP THE TIMER AND GET A READOUT WITH SHORT DECIMALS
$x = $sw->stop($g_timer);
echo nl2br($sw->readout($g_timer, 1));
echo "<br/>" . PHP_EOL;




// START A SECOND TIMER
$y_timer = 'YAHOO TIMER';
$sw->start($y_timer);

// PERFORM SOME OTHER ACTIVITY THAT YOU WANT TO TIME
$page = 'http://yahoo.com/';
$html = file_get_contents($page);

// REPORT THE STOPWATCHES CONTENT (ONE IS STOPPED AND ONE IS STILL RUNNING)
echo nl2br($sw->readout());
echo "<br/>" . PHP_EOL;

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";

// STOP ALL OF THE STOPWATCHES
$sw->stop();

// REPORT THE STOPWATCHES CONTENT AGAIN
echo nl2br($sw->readout());
echo "<br/>" . PHP_EOL;

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";



// TRY TO STOP A TIMER THAT IS NOT RUNNING
$x = $sw->stop($g_timer);
var_dump($x);
echo "<br/>" . PHP_EOL;
echo "<br/>" . PHP_EOL;



// START THIS TIMER OVER AGAIN
$sw->start($y_timer);

// PERFORM SOME OTHER ACTIVITY THAT YOU WANT TO TIME
$page = 'http://weather.yahoo.com/';
$html = file_get_contents($page);

// REPORT THE STOPWATCHES CONTENT
echo nl2br($sw->readout());
echo "<br/>" . PHP_EOL;

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";



// REMOVE ONE OF THE STOPWATCHES
$sw->reset($g_timer);

// REPORT THE STOPWATCHES CONTENT
echo nl2br($sw->readout());
echo "<br/>" . PHP_EOL;

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";



// REMOVE ALL OF THE STOPWATCHES
$sw->reset();
echo "ALL STOPWATCHES HAVE BEEN REMOVED";

// REPORT THE STOPWATCHES CONTENT (SHOWS NOTHING)
echo nl2br($sw->readout());

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";

// SHOW THE DESTRUCTOR IN ACTION
$sw->start('Foo');
echo "<pre>";
unset($sw);
echo "</pre>";

Open in new window

0
 
LVL 19

Accepted Solution

by:
alextoft earned 100 total points
ID: 40402322
Don't be so quick to rule DNS out. A 5 second-ish delay is frequently associated with failure to perform a reverse lookup on the connecting IP. You could try running tcpdump during a connection, load it up in Wireshark and have a look in there for outbound UDP/53 packets, or make sure there's a hosts record in for the interface you're connecting from.

If you're really stuck, maybe run the server in interactive mode using strace whilst you connect. It'll explode in your terminal, but it can be surprisingly easy to see what's hanging a process.

All this assumes you're using Linux instead of that other fluffy thing which has start buttons and stuff (forget what it's called off the top of my head) :)
0

Featured Post

Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question