6.0.0-beta1
7/17/25

[#12656] Horde pings SQL server with each database query
Summary Horde pings SQL server with each database query
Queue Horde Framework Packages
Queue Version Git master
Type Enhancement
State Resolved
Priority 1. Low
Owners jan (at) horde (dot) org
Requester arjen+horde (at) de-korte (dot) org
Created 09/04/2013 (4334 days ago)
Due
Updated 12/17/2013 (4230 days ago)
Assigned
Resolved 09/10/2013 (4328 days ago)
Milestone
Patch No

History
12/17/2013 11:36:24 AM Git Commit Comment #7 Reply to this comment
Changes have been made in Git (master):

commit 0100bfdd7cff2a994be76fd0831c3aafb567e2c7
Author: Jan Schneider <jan@horde.org>
Date:   Tue Sep 10 14:50:52 2013 +0200

     [jan] Don't ping server before each query in PDO drivers (Bug #12656).

  framework/Db/lib/Horde/Db/Adapter/Pdo/Base.php |    2 --
  framework/Db/package.xml                       |    2 ++
  2 files changed, 2 insertions(+), 2 deletions(-)

http://git.horde.org/horde-git/-/commit/0100bfdd7cff2a994be76fd0831c3aafb567e2c7
09/10/2013 12:55:51 PM Jan Schneider Comment #6
Assigned to Jan Schneider
State ⇒ Resolved
Reply to this comment
I actually simply removed the ping, we don't ping in other backends 
than PDO either. Retrying on connection issues is a nice touch, and we 
may want to add this at some point. But this needs to be done across 
all backends then.
09/10/2013 12:51:09 PM Git Commit Comment #5 Reply to this comment
Changes have been made in Git (master):

commit 6473f788bd06787aecea9fbce3cde8ea6f3491ea
Author: Jan Schneider <jan@horde.org>
Date:   Tue Sep 10 14:50:52 2013 +0200

     [jan] Don't ping server before each query in PDO drivers (Bug #12656).

  framework/Db/lib/Horde/Db/Adapter/Pdo/Base.php |    2 --
  framework/Db/package.xml                       |    2 ++
  2 files changed, 2 insertions(+), 2 deletions(-)

http://git.horde.org/horde-git/-/commit/6473f788bd06787aecea9fbce3cde8ea6f3491ea
09/05/2013 10:58:19 AM arjen+horde (at) de-korte (dot) org Comment #4
New Attachment: only-ping-sql-on-error-2.diff Download
Reply to this comment
By the way, the subject should really be "Horde pings MySQL server 
with each database query", since this happens only for the 'MySQL / 
PDO' and 'MySQL (mysqli)' backends. I'm not sure if something similar 
happens with the 'PostgreSQL' and 'SQLite' backends.

Attached a slightly modified patch, which doesn't ping the server the 
last time if the number of retries is exceeded already and an 
exception will be thrown anyway.
09/04/2013 05:40:39 PM arjen+horde (at) de-korte (dot) org Comment #3
New Attachment: only-ping-sql-on-error.diff Download
Reply to this comment
My first shot at trying to reduce the number of pings to the SQL database.

Assume the connection to the database is good (which will generally be 
the case) and send queries straight away. Only ping the SQL database 
if an exception occurs (much less common). If the server doesn't 
respond, reconnect and try again (up to three times). If does respond 
to the ping, assume something else was wrong and throw exception as 
usual.

The logic might be improved by interpreting $e->getMessage(). If it 
contains 'SQLSTATE[08', there is a definitly a connection problem and 
we should reconnect. But I'm not sure if all supported SQL databases 
support this status code, hence the (slightly less efficient) ping.
09/04/2013 10:53:07 AM arjen+horde (at) de-korte (dot) org Comment #2 Reply to this comment
Not surprisingly, I'm not the first to note such behaviour:

http://www.mysqlperformanceblog.com/2010/05/05/checking-for-a-live-database-connection-considered-harmful/

For what it's worth...
09/04/2013 07:17:28 AM arjen+horde (at) de-korte (dot) org Comment #1
Priority ⇒ 1. Low
Type ⇒ Enhancement
Summary ⇒ Horde pings SQL server with each database query
Queue ⇒ Horde Framework Packages
Milestone ⇒
Patch ⇒ No
State ⇒ New
Reply to this comment
While monitoring my MySQL server stats, I noticed that half of the 
queries performed are 'SELECT 1' (ping) requests. While it may be a 
good idea to ping the SQL server if we haven't heard from it for a 
while, sending 150 pings in quick succession every 
$conf[activesync][ping][waitinterval] for each ActiveSync connection 
is may be a overkill.

Not that I'm worried about the SQL server load (the load for a 'SELECT 
1' is probably negliable compared to the 'real' queries doing the 
actual work), but sending a ping for each SQL query effectively 
doubles the round trip delay to the server. While this may not be much 
of an issue if the webserver and database are on the same (physical) 
machine, it may add up if they are not.

Idealy, a timestamp should be stored with the connection so that a 
ping is only sent if the last time we heard from the server on this 
connection was more than 'X' seconds ago (with X probably a hardcoded 
value of maybe 10 to 30 seconds). I can see if I can come up with a 
patch to do this, but my PHP skills are mediocre at best, so please 
bear with me.

Saved Queries