MySQL: How to decrease sleep process's time out?

Question

When I run 'show processlist;', then I get so many sleep processes.

I set wait_timeout and interactive_timeout to 60 in my.cnf. But, sleep process is not died when time 60 on processlist. I found that sleep process is died when time is 7900. What happend?? How can I decrease sleep process's time out?

If you need other information, I will give information as I possible.

UPDATE

I use root, read_only and deploy account on mysql. And I found root and read_only accounts run correct. Query -> Sleep -(60sec)-> release process.

But deploy account, using to connect between rails server and mysql, run wrong. Query -> Sleep -(7900sec)-> release. So, I think that deploy account or Rails is problem. However, I dont have any idea to fix this.

Answer 1

What does sleep mean?

MySQL process has completed the execution of query, but the client(Rails) doesn't exit.

Why does it happen?

Either puma or unicorn, they are using persistent connections to database, then it's normal to have lots of idle connection, the state of which is sleep.

Should I worry about it?

If those sleep connections come from Rails web application and less than the configuration of database connection pool size, there is nothing to worry.

How to fix it?

set global wait_timeout=XXX; // wait_timeout is for non-interactive sessions.
set global interactive_timeout=XXX;  // interactive_time is for interactive sessions

Reference

1. Should I worry about mysql sleep status process in processlist?

2. Mysql show processlist lists many processes sleep and info = null?

Answer 2

to check your idle process timeout you can connect to MySQL then execute the following command:

show variables like "%timeout%";

you need to check wait_timeout and interactive_timeout from the results based on those values you can execute the following commands from MySQL to set them:

set global wait_timeout=XXX; -- where XXX equal value less than the original one
set global interactive_timeout=XXX;  -- where XXX equal value less than the original one