Does Mysql (MariaDB) wait_timeout exclude query execution time?

Question

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.

Answer 1

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.