How to keep database connect alive?

Question

I'm using ajax, and quite often if not all the time, the first request is timed out. In fact, if I delay for several minutes before making a new request, I always have this issue. But the subsequent requests are all OK. So I'm guessing that the first time used a database connect that is dead. I'm using MySQL.

Any good solution?

Answer 1

chances are that you problem is NOT opening the connection, but actually serving the request. subsequent calls are fast because of the mysql query cache.

what you need to do is to look for slow mysql queries, for example by turning on the slow query log, or by looking at the server at real time using mytop or "SHOW PROCESSLIST" to see if there is a query that takes too long. if you found one, use EXPLAIN to make sure it's properly indexed.

Answer 2

Can you clarify:

• are you trying to make a persistent connection?
• do basic MySQL queries work (e.g. SELECT 'hard-coded' FROM DUAL)
• how long does the MySQL query take for your ajax call (e.g. if you run it from a mysql command-line or GUI client.)
• how often do you write to the MySQL tables used in your AJAX query?

Answering those questions should help rule-out other problems that have nothing to do with making a persistent connection: basic database connectivity, table indexing / slow-running SQL, MySQL cache invalidation etc.