3

I'm tuning my project MYSQL database, as many people I got suggestion to reduce

wait_timeout

, but it is unclear for me, does this session variable exclude query execution time, or it includes it? I have set it for 5 seconds, taking into account that I may have queries which are being executed for 3-5 seconds sometimes (yea that is slow there are few of them, but they still exist), so mysql connections have at least 1-2 seconds to be taken by PHP scripts before they are closed by MYSQL. In MySQL docs there is no clear explanation about how it starts counting that timeout and if it includes execution time. Perhaps your experience may help. Thanks.

Improve this question

3 Answers 3

Reset to default
2

Does the wait_timeout session variable exclude query execution time?

Yes, it excludes query time.

max_execution_time controls how long the server will keep a long-running query alive before stopping it.

Do you use php connection pools? If so 5 seconds is an extremely short wait_time. Make it longer. 60 seconds might be good. Why? the whole point of connection pools is to hold some idle connections open from php to MySQL, so php can handle requests from users without the overhead of opening connections.

Here's how it works.

  1. php sits there listening for incoming requests.
  2. A request arrives, and the php script starts running.
  3. The php script asks for a database connection.
  4. php (the mysqli or PDO module) looks to see whether it has an idle connection waiting in the connection pool. If so it passes the connection to the php script to use.
  5. If there's no idle connection php creates one and passes it to the php script to use. This connection creation starts the wait_timeout countdown.
  6. The php script uses the connection for a query. This stops the wait_timeout countdown, and starts the max_execution_time countdown.
  7. The query completes. This stops the max_execution_time countdown and restarts the wait_timeout countdown. Repeat 6 and 7 as often as needed.
  8. The php script releases the connection, and php inserts it into the connection pool. Go back to step 1. The wait_time is now counting down for that connection while it is in the pool.
  9. If the connection's wait_time expires, php removes it from the connection pool.

If step 9 happens a lot, then step 5 also must happen a lot and php will respond more slowly to requests. You can make step 9 happen less often by increasing wait_timeout.

(Note: this is simplified: there's also provision for a maximum number of connections in the connection pool.)

MySQL also has an interactive_timeout variable. It's like wait_timeout but used for interactive sessions via the mysql command line program.

What happens when a web user makes a request and then abandons it before completion? For example, a user might stop waiting for a report and go to another page. In some cases, the host language processor detects the closing of the user connection, kills the MySQL query, and returns the connection to the pool. In other cases the query either completes or hits the max_execution_timeout barrier. Then the connection is returned to the pool. In all cases the wait_timeout countdown only is active when a connection is open but has no query active on it.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Yes, and FWIW an "interactive session" is currently only a session run by the mysql CLI. That's the only code that sets the interactive option.
Thanks for detailed answer. Many tuning scripts suggest to reduce wait_timeout and to remove persistent connections. I configured my server to have persistent connections and made wait_timeout to 5 seconds. As it seems to me that should be enough to process long queries and to eliminate creation of new connection. Isn't this right? And yes, I use a pool of connections which is default config in many LAMP systems
Since you answered very detailed, may I ask you well that question which I asked a person below you answer: Doesn't those 60 seconds mean that is some query was made by a request which does not use it (a person left page, request was not finished properly whatever else but it is not used) it will be hanged for 60 seconds taking of precious connections? They are limited as well
See my edit. Coping with an abandoned query relates to max_execution_time, not wait_time. Any, you should know that the re-use of persistent connections is absolutely vital to the performance of high-traffic web sites, intertoobz advice to the contrary notwithstanding. Creating new dbms connections is expensive, and it's expensive for a shared resource (dbms server) rather that one that can be scaled out (web server)
Thank you, I have set it to 60 seconds, 'Threads_connected' did not change much since that moment, so it seems ok. max_execution_time is php parameter, in my case it is default 30 seconds, it still seems to me that 30 seconds of possible connection use could become performance bottleneck or even a reason of system instability, if some robots/parsers come and send many requests to site, it will use all connections very quick and server can't respond to normal requests. When they are limited to 5 seconds there is less possibility of such scenario... Am I wrong?
0

A MySQL server timeout can occur for many reasons, but happens most often when a command is sent to MySQL over a closed connection. The connection could have been closed by the MySQL server because of an idle-timeout; however, in most cases it is caused by either an application bug, a network timeout issue (on a firewall, router, etc.), or due to the MySQL server restarting.

It is clear from the documentation that it does not include the query execution time. It is basically the maximum idle-time allowed between two activities. If it crosses that limit, server closes the connection automatically.

The number of seconds the server waits for activity on a noninteractive connection before closing it.

From https://www.digitalocean.com/community/questions/how-to-set-no-timeout-to-mysql :

  • Configure the wait_timeout to be slightly longer than the application connection pool's expected connection lifetime. This is a good safety check.
  • Consider changing the waittimeout value online. This does not require a MySQL restart, and the waittimeout can be adjusted in the running server without incurring downtime. You would issue set global waittimeout=60 and any new sessions created would inherit this value. Be sure to preserve the setting in my.cnf. Any existing connections will need to hit the old value of waittimeout if the application abandoned the connection. If you do have reporting jobs that will do longer local processing while in a transaction, you might consider having such jobs issue set session wait_timeout=3600 upon connecting.
Improve this answer

2 Comments

Thanks for you answer, I have read it, but it was unclear what it counts as activity. That could be many factors, as it is, in my opinion execution time had to be excluded from that variable, or there must another timeout for 'clean kill' of abandon requests
@NIck you can very easily experiment it. Take for example a php code. Create a database connection. Fire some queries. Use sleep() to let application wait for some time. Keep on increasing this until you hit the wait time out
0

From the reference manual,

Time in seconds that the server waits for a connection to become active before closing it.

In elementary terms, How many SECONDS will you tolerate someone reserving your resources and doing nothing? It is likely your 'think time' before you decide which action to take is frequently more than 5 seconds. Be liberal. For a web application, some processes take more than 5 seconds to run a query and you are causing them to be terminated at 5 seconds. The default is 28800 seconds which is not reasonable. 60 seconds could be a reasonable time to expect any web based process to be completed. If your application is also used in a traditional workplace environment, a 15 minute break is not unreasonable. be liberal to avoid 'bad feedback'.

Improve this answer

2 Comments

Doesn't those 60 seconds mean that is some query was made by a request which does not use it (a person left page, request was not finished properly whatever else but it is not used) it will be hanged for 60 seconds taking of precious connections? They are limited as well
@nick Yes, 60 seconds means I will keep your connection for 60 seconds. SHOW GLOBAL STATUS LIKE 'threads_connected'; allows you to monitor the count of active connections and they are limited.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.