Solved

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

Posted on 2014-09-07
11
376 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
Webinar: MariaDB® Server 10.2: The Complete Guide

Join Percona’s Chief Evangelist, Colin Charles as he presents MariaDB Server 10.2: The Complete Guide on Tuesday, June 27, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

695 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