Jakiś czas temu, natrafiłem na zagadnienie wyczerpywania limitu aktualnie aktywnych procesów w serwerze MySQL.

Serwer Apache podczas połączeń, pozostawiał zbyt wiele procesów w trybie oczekiwania, które były tworzone na zaś. Mimo że aplikacja działała w klastrze, było to jej wąskie gardło. Do tego ze względu na to że była dość rozbudowana, zamiast zmian w jej mechanice, należało ograniczyć liczbę procesów pozostających po wykonanym połączeniu.  

Zastosowanie limitu czasu oczekiwania na proces nie przynosiło rezultatu.

interactive_timeout=30
wait_timeout=30

Podjąłem więc decyzję stworzenia procedury, która po przekroczeniu pewnego progu ilości procesów, co 2 minuty usuwa najstarsze procesy (tylko w trybie sleep), by pozostawić zdefiniowaną ilość najnowszych procesów.

DROP PROCEDURE IF EXISTS KILL_PROCESS_PROCEDURE;

CREATE PROCEDURE KILL_PROCESS_PROCEDURE(
    IN  trigger_max_proces_count INT,
    IN  alive_max_sleep_proces_count INT
)
BEGIN
    DECLARE kill_done int;
    DECLARE process_count int;
    DECLARE EXIT HANDLER FOR NOT FOUND SET kill_done = 1;
    BEGIN DECLARE cursor_ID int;
    DECLARE cursor_i
        CURSOR FOR
        SELECT id FROM information_schema.PROCESSLIST
        WHERE id NOT IN(
            SELECT * FROM(
                             SELECT ID
                             FROM information_schema.PROCESSLIST
                             ORDER BY TIME ASC
                             LIMIT alive_max_sleep_proces_count) R
        )
          AND user = 'user'
          AND command = 'Sleep'
          AND ID != CONNECTION_ID() ORDER BY TIME ASC;

    IF (SELECT COUNT(*) FROM information_schema.PROCESSLIST) > trigger_max_proces_count THEN
        OPEN cursor_i;
        read_loop: LOOP
            FETCH cursor_i INTO cursor_ID;
            KILL CONNECTION cursor_ID;
            IF kill_done THEN
                LEAVE read_loop;
            END IF;
        END LOOP;
        CLOSE cursor_i;
    END IF;
    END;
END$
DELIMITER ;

Oto co się dzieje w kodzie:

1. Procedura pobiera z information_schema.PROCESSLIST listę wszystkich procesów.

2. Powyższa lista procesów jest limitowana, tylko do procesów w trybie sleep oraz wykluczanych jest z niej x najnowszych procesów.

3. Jeśli liczba procesów przekroczy wartość progową, wykonywane jest siłowe zamykanie listy procesów z pkt.2

Dodatkowo dodałem wyzwalacz, który co 2 minuty wykonuje całą procedurę.

DROP EVENT IF EXISTS KILL_PROCESS_EVENT;

DELIMITER $
CREATE DEFINER=`user`@`%` EVENT `KILL_PROCESS_EVENT`
    ON SCHEDULE
        EVERY 2 MINUTE
    ENABLE
    COMMENT 'param: (triggering number of processes, number of processes left in sleep mode)'
    DO BEGIN
    CALL `KILL_PROCESS_PROCEDURE`(50, 20);
END$
DELIMITER ;