• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 440
  • Last Modified:

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

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
St_Aug_Beach_Bum
Asked:
St_Aug_Beach_Bum
  • 3
  • 3
  • 2
  • +2
2 Solutions
 
GaryCommented:
http://php.net/manual/en/mysqli.construct.php

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

Open in new window

0
 
becraigCommented:
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
 
Ray PaseurCommented:
You can time almost any event.  Take the value of microtime() before and after.

You can use memcacheD
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
St_Aug_Beach_BumAuthor Commented:
Right, but how can I time the connection attempt and use the alternate script if it takes too long?
0
 
Ray PaseurCommented:
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
 
GaryCommented:
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
 
St_Aug_Beach_BumAuthor Commented:
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
 
GaryCommented:
You could do but anything less than 1 second would be pointless
0
 
St_Aug_Beach_BumAuthor Commented:
Thank you all!
0
 
gr8gonzoConsultantCommented:
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
 
gr8gonzoConsultantCommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now