Ytukyg

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.

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.

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.

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'.