1

I have a problem of mysql processes staying alive.

SHOW PROCESSLIST; returns a list of processes. There are processes existing for hours - according to process ID, host (port).

It seems like the process performs a query (SELECT) than sleeps, performs the query again - and it happens all over again. The "time" shown in PROCESSLIST keeps resetting after each sleep.

How to make Mysql automatically kill such processes? I suppose that such a process stays alive after a PHP script timeout (it also performs a CURL task).

[EDIT - to answer comments]

The query: SELECT COUNT(*) FROM keywords WHERE updated < '2018-05-03 00:00:00' AND status = '0' AND active = '1;

columns: updated (DATETIME), status (INT) and active (BOOL) are INDEXED.

There is ~120K records in the table "keywords".

My PHP script is connecting to MySQL via PDO.

I have also set the following variables:

interactive_timeout = 180 wait_timeout = 180

Elliot B.
  • 17,060
  • 10
  • 80
  • 101
Hubert Owerczuk
  • 21
  • 2
  • you could kill them https://stackoverflow.com/questions/20048017/shell-script-to-auto-kill-mysql-sleep-processes or you could just fix them .. your call –  May 02 '18 at 23:12
  • I keep killing these processes every day... what should I fix? The query (I mean, columns) is properly indexed. What else should I try to do? – Hubert Owerczuk May 02 '18 at 23:15
  • There may be some useful info for you here: https://stackoverflow.com/questions/2407732/mysql-proccesslist-filled-with-sleep-entries-leading-to-too-many-connections – Don't Panic May 02 '18 at 23:17
  • what's the query? what's the db structure? what's the profile of the query? you really haven't provided enough information to even be able to guess at a solution. –  May 02 '18 at 23:17
  • Is whatever connects to your database using persistent connections? – Tim Fountain May 02 '18 at 23:20
  • are `status` and `active` indexed? –  May 02 '18 at 23:26
  • I have added additional info to my question. @smith, yes all columns after "WHERE" clause are indexed. – Hubert Owerczuk May 02 '18 at 23:27
  • That query must be in your code base somewhere. Your next step is to find it. The step after that is to read about persistent connections. – O. Jones May 02 '18 at 23:55
  • @O.Jones I know exatly there the code is. As far as I understand how PDO works, it keeps the connection alive until the script ends. I have the persistent connections option off. The script scrapes info via CURL from external pages and sometimes it takes too long (there are timeouts for both CURL and PHP). As I've written in my question post - I suppose the "persistent" connection problem appears when the CURL timeouts. What can I do to close the connection/process? – Hubert Owerczuk May 03 '18 at 00:27
  • What version of MySQL? Any "Proxy" in use between the client and server? – Rick James May 04 '18 at 06:15
  • Client and server are in the same datacenter and no proxy is between them. MySQL v. 5.7.22 – Hubert Owerczuk May 04 '18 at 10:21

1 Answers1

0

The behavior you have described is possible only with persistent connections. Persistent connections allow PHP to re-use a previous database connection when the credentials match. While there are downsides, persistent connections are not necessarily a bad thing and it is not necessary to manually kill them off.

If you want to disable persistent connections, then you must find the place in your code where the database connection is initialized. It should look something like this:

<?php
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass, array(
    PDO::ATTR_PERSISTENT => true
));
?>

Once you've located it, either remove the PDO::ATTR_PERSISTENT option or set it to false:

<?php
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass, array(
    PDO::ATTR_PERSISTENT => false
));
?>

After you've disabled persistent connections, it will be impossible for the database session to persist after the exit of the PHP script. As stated by the docs:

The connection remains active for the lifetime of that PDO object. To close the connection, you need to destroy the object by ensuring that all remaining references to it are deleted--you do this by assigning NULL to the variable that holds the object. If you don't do this explicitly, PHP will automatically close the connection when your script ends.

Elliot B.
  • 17,060
  • 10
  • 80
  • 101
  • Thanks for your answer. I've tried adding option disabling persistent connection but without any result. The long-lasting queries still appear "eating" all available CPU. In fact, there is no change compared to the situation before adding the option. – Hubert Owerczuk May 04 '18 at 23:23