Solved

PHP/MYSQLi - how can I write an if statement for "if mysqli_connect too slow"?

Posted on 2014-09-07
11
346 Views
Last Modified: 2014-09-08
I want to digg proof my site as much as possible.

During super-high traffic times, my site slows down and eventually starts delivering 'mysql too busy' messages.

I've set up a caching program and have drastically reduced the number of mysql requests that happen on a per-page-load basis.

What I would like to do now is set it up so that if mysql is too busy or slow, there will be an alternate method to load the page without the info from the db.

Ok, so I'm testing this:

-------------------------------------------------

$mysqli = new mysqli("localhost", "me", "pass", "fstest");

if (mysqli_connect_errno()) {   //can't connect

//do this

}

else {  //db is working just fine

//do this

}

------------------------------

Is there a way I can set this up so that, in addition to using the alternate script if there is a mysql connection error, I can trigger the alternate script if the mysql connection is slow?

For example:

-----------------

if ((mysqli_connect_errno()) || ( *it's taking more than a quarter of a second to respond*) {

---------------

Thanks,

Chris
0
Comment
Question by:St_Aug_Beach_Bum
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 58

Assisted Solution

by:Gary
Gary earned 500 total points
ID: 40308592
http://php.net/manual/en/mysqli.construct.php

if ($mysqli->connect_error) {
    // oops
}
else{
    // connected
}

Open in new window

0
 
LVL 29

Expert Comment

by:becraig
ID: 40308601
Here is also some good reading from mysql on this.

Depending on your OS you may be able to increase the number of handles used by mysql:
http://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40308611
You can time almost any event.  Take the value of microtime() before and after.

You can use memcacheD
0
 

Author Comment

by:St_Aug_Beach_Bum
ID: 40308648
Right, but how can I time the connection attempt and use the alternate script if it takes too long?
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40308782
how can I time the connection attempt ...
I don't know.  I'm not even sure that's a good strategy.  

Have you used EXPLAIN SELECT to see what the many queries are doing and why they are running so slowly that they slow all of MySQL down?  Usually in circumstances like this the right approach is to denormalize the data base and throw money and machinery at the problem.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 58

Accepted Solution

by:
Gary earned 500 total points
ID: 40308786
Why not use MYSQLI_OPT_CONNECT_TIMEOUT (value in seconds) and then you can set how long to wait?

I would be looking at the db and seeing where I could optimize the queries, have you got the correct columns indexed (can turn a 10 sec query into a 1 sec query), are you doing things like select * etc.
0
 

Author Comment

by:St_Aug_Beach_Bum
ID: 40308913
Well, I don't have a generally slow mysql response time. The reason I'm rewriting everything it to better handle digg moments. When I get a 1000 visits in a minute.

Since rewriting my scripts and caching the pages, I've reduced it down to just two to three calls to the database that I need for each page load. The script runs in 0.0011570453643799 seconds when the server is under light load.

However, to further digg proof, I was thinking I would set it up so that if there was a slow connection, I'd default to an alternative script that doesn't require the database at all. It's not as good, but at least the visitor would get the info they are coming for.

Soo.... I don't quite understand everything I am reading about  MYSQLI_OPT_CONNECT_TIMEOUT that I am reading, but it looks like I can do this....

---------------

$mysqli = mysqli_init();

$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 1);

$mysqli = new mysqli("localhost", "me", "pass", "fstest");

if ($mysqli->connect_error) {   //can't connect

//do this

}

else {  //db is working just fine

//do this

}

------------------------------------

and if it took over 1 second to connect, it would error and the backup/non-db script would run, is that correct?

Would the:

 MYSQLI_OPT_CONNECT_TIMEOUT

accept a half a second you think, would that be ".5" or is there another way to write this?

Thank you everyone who has offered feedback so far, it got much more involved that I expected really fast :)

Chris
0
 
LVL 58

Expert Comment

by:Gary
ID: 40308923
You could do but anything less than 1 second would be pointless
0
 

Author Closing Comment

by:St_Aug_Beach_Bum
ID: 40309871
Thank you all!
0
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 40309940
I know this question is answered, but my $0.02: MySQL should be able to handle 16-17 visitors per second unless you have an underpowered or overwhelmed box. Usually if you're getting good speeds under normal loads but are seeing problems with high volume, you may want to check to see if you're doing any database writes for each of those hits.

For example, sometimes I see designs where hits are logged to a database table with visitor information. However, if you're using an engine like MyISAM for your tables, then those updates use table-level locking that can create a queue, causing each person to take significantly longer than they normally should.

I'd suggest running this query on your database:
SHOW STATUS LIKE 'Table%';

...and post the numbers back here. You should get two numbers, "immediate" and "waited" - the "immediate" number is good, the "waited" number is bad. The "waited" number indicates the number of times the script had to wait for another update-type (insert, delete, update) query to finish running before it could run. So you want a ratio where "immediate" is far higher than "waited".

If you're seeing a closer relationship between those two numbers, then you might want to revisit the design a bit or consider a different database engine like InnoDB (uses row-level locking instead of locking the entire table during a write). MyISAM is a good all-around engine and tends to be the default for most people but it's not always the best for scenarios that can see heavy traffic.

There are a few tips in this article that may also help on an overall database-performance level:
http://www.experts-exchange.com/Database/MySQL/A_1250-3-Ways-to-Speed-Up-MySQL.html

In particular, pay attention to the MySQLTuner output and the configuration options. Query caching can be essential in high-traffic scenarios, and you want to avoid designs that invalidate the cache frequently (which happens when you write to the table).
0
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 40309951
Also, something to consider as you grow - if you're seeing some fast growth rates, now is the best time to plan for the future. It's a LOT harder to change foundational infrastructure when you have regularly-high traffic. That said, if you have the budget to move to a clustered database like MySQL Cluster NDB (probably the simplest move if you're already on MySQL), then you will be on a database design that can scale very easily. However, a clustered setup needs a minimum of 3 separate servers, so it can be cost-prohibitive for some people.

But I'd first analyze your database writes and see if they are the main culprit in your case, and if your volume isn't regularly growing much, then InnoDB might be a good engine to change to in order to support the traffic spikes.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now