Solved

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

Posted on 2014-09-07
11
328 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
Comment Utility
http://php.net/manual/en/mysqli.construct.php

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

Open in new window

0
 
LVL 28

Expert Comment

by:becraig
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 58

Accepted Solution

by:
Gary earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
You could do but anything less than 1 second would be pointless
0
 

Author Closing Comment

by:St_Aug_Beach_Bum
Comment Utility
Thank you all!
0
 
LVL 34

Expert Comment

by:gr8gonzo
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

772 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

11 Experts available now in Live!

Get 1:1 Help Now