Should I ping mysql server before each query?

So I was wondering whether I should or should not ping the mysql server (mysqli_ping) to ensure that the server is always alive before runnin开发者_运维技巧g query?

You shouldn't ping MySQL before a query for three reasons:

1. Its not a reliable way of checking the server will be up when you attempt to execute your query, it could very well go down in the time between the ping response and query.
2. Your query may fail even if the server is up.
3. As the amount traffic to your website scales up, you will be adding a lot of extra overhead to the database. Its not uncommon in enterprise apps that have used this method to see a huge amount of the database's resources getting wasted on pings.

The best way to deal with database connections is error handling (try/catch), retries and transactions.

More on this on the MySQL performance blog: Checking for a live database connection considered harmful

In that blog post you'll see 73% of the load on that instance of MySQL was caused by applications checking if the DB was up.

I don't do this. I rely on the fact that I'll have a connection error if the server's gone and I try to do something.

Doing the ping might save you a bit of time and appear to be more responsive to the user, but a fast connection error isn't much better than waiting a few seconds followed by a connection error. Either way, the user can't do anything about it.

No.

Do you ping SO before you navigate there in a browser, just to be sure the server is running?

So I was wondering whether I should or should not ping the mysql server (mysqli_ping) to ensure that the server is always alive before running query?

Not really. If it is not live, you will come to know through the error messages coming through your queries or when connecting to the database. You can get mysql error with:

mysql_error()

Example:

mysql_connect(......) or die(mysql_error());

This is not the standard way of dealing with it... If there's an exception, you'll deal with it then.

It's somewhat similar to the difference between checking that a file exists before trying to open it, or catching the file-not-found exception when it occurs... If it's a very, very common and likely error it may be worth it to check before, but usually execution should try to go normally and exceptions should be caught and handled when they occur.

Generally speaking, no.

However, if you have a long-running script, for example some back-end process that's called as a cron job where that may be a time span between connecting and subsequent queries, mysqli_ping() maybe useful.

Setting mysqli.reconnect to true in php.ini is useful in this case.

No.

Just because the ping succeeds doesn't mean the query will. What if the server becomes unavailable between the time you ping it and the time you execute the query?

For this reason, you'll have to have proper error-catching around the query anyway. And if you do, you might as well simply rely on this as your primary error trap.

Adding the ping just adds unnecessary round-trips, ultimately slowing down your code.

The only time I can think of to do this is if the database is
1. non-critical to the functioning of your app, and,
2. it has a tendency to be offline.

Other than that, no.

The only time in which it would be worthwhile to use ping would be if you were implementing your own db connection pooling system. Even in that case, I wouldn't ping before every query, just on each "connect" / checkout from the pool.