0

We need to call one of our PHP web service periodically (once every 5 secs) This web service has more than one select statements and no writes in it. It normally and mostly takes 200 ms to execute this PHP web service. But sometimes it is timed out or takes more than 10 sec. If this happens these timeouts happen consecutively. And after sometime it starts to run normal again. There is no visible problem in the web server, application server or DB server during this period.

We are using ClearDB (MySQL on cloud), we are new to cleardB and could not see any error logs other than the following MySQL status alerts.

Any ideas about the solution or any method for digging the problem more is appreciated.

Here is the MySQL status variables which are giving alerts:

Aborted clients 21.8 k

Aborted connects 49.1 k

Binlog cache disk use 14.2 k

Created tmp disk tables 1.2 M

Handler read rnd 163.1 M

Handler read rnd next 2.3 G

Innodb buffer pool pages dirty 6.8 k

Innodb buffer pool reads 8.6 M

Innodb row lock time avg 1.6 k

Innodb row lock time max 51.8 k

Innodb row lock waits 52.5 k

Opened tables 1.5 M

Qcache lowmem prunes 6.2 M

Select full join 123.6 k

Select range check 14.6 k

Slow queries 6.8 k

Sort merge passes 12.9 k

Table locks waited 7.6 k

Nermin Ozkiranartli
  • 59
  • 3
  • Maybe two issues: 1) Why it takes a long time sometimes. 2) starting the process again before it has finished the previous run? The second may be worthwhile checking? – Ryan Vincent May 22 '15 at 16:43
  • Can you show the query? Is it parametrised? You could have a bad case of parameter sniffing (at least that could be on sqlserver) http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx – Juan May 22 '15 at 16:47
  • For test purpose, we used static values for all the parameters, so this PHP always returns the same output now. Parameter sniffing is not possible since the query and query result is always same and there is no LIKE or string processing in it. From the client we are waiting for the http request to return in order to send another request, however after 10 sec. timeout we start sending another request – Nermin Ozkiranartli May 22 '15 at 17:04

1 Answers1

0

Microsoft Azure cloud server and ClearDB MySQL server does not work quite smooth and steady. If you work on Azure you have to choose MSSQL for DB. Or if you want to use MySQL then I recommend choosing another cloud server. After contacting to ClearDB support they even told us that we can not see MySQL error logs on clear db.

After moving our system to Amazon, we get rid of ClearDB and everything is now working steady.

Nermin Ozkiranartli
  • 59
  • 3