Solved

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

Posted on 2014-09-07
11
367 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
[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
  • 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 110

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 110

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
 
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 35

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 35

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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…
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.

726 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