Who’s leaking prepared statements?

In my last post, I described a specific problem with prepared statements into which PERFORMANCE_SCHEMA can give visibility.  That made me wonder whether PERFORMANCE_SCHEMA can also be used to identify other areas where prepared statements run into problems.  The most significant problem tends to be leakage of prepared statements.  This can inflate memory usage, both on the server and application side, and it’s not uncommon to find applications which fail to close prepared statements.

So the question is, what can PERFORMANCE_SCHEMA tell us about how connections close (or more importantly, fail to close) prepared statements?

At the most basic level, one can check the number of PREPARE statements executed compared to DEALLOCATE PREPARE, and you can do that using global status variables.  You’re shooting for general equality between (Com_prepare_sql + Com_stmt_prepare) and (Com_stmt_close + Com_dealloc_sql).  I’m using session status below to make it easy to follow, but obviously global status variables will be most interesting in any review of prepared statement leakage:

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SESSION STATUS LIKE 'com_%prepare%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Com_prepare_sql    | 0     |
| Com_stmt_prepare   | 0     |
| Com_stmt_reprepare | 0     |
| Com_xa_prepare     | 0     |
+--------------------+-------+
4 rows in set (0.00 sec)

mysql> PREPARE stmt FROM 'SELECT 1';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SHOW SESSION STATUS LIKE 'com_%prepare%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Com_prepare_sql    | 1     |
| Com_stmt_prepare   | 1     |
| Com_stmt_reprepare | 0     |
| Com_xa_prepare     | 0     |
+--------------------+-------+
4 rows in set (0.00 sec)

mysql> SHOW SESSION STATUS LIKE 'Com_stmt_close';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Com_stmt_close | 0     |
+----------------+-------+
1 row in set (0.00 sec)

mysql> SHOW SESSION STATUS LIKE 'Com_dealloc_sql';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Com_dealloc_sql | 0     |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SESSION STATUS LIKE 'Com_stmt_close';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Com_stmt_close | 1     |
+----------------+-------+
1 row in set (0.00 sec)

mysql> SHOW SESSION STATUS LIKE 'Com_dealloc_sql';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Com_dealloc_sql | 1     |
+-----------------+-------+
1 row in set (0.00 sec)

That’s useful as a start to identify whether a problem exists, but it doesn’t help isolate the source very well. PERFORMANCE_SCHEMA can take it the next step.

The PERFORMANCE_SCHEMA counters are smarter than the status variables, and we can count the number of prepared statements that have been opened (statement/sql/prepare_sql event for normal statements, statement/com/Prepare event for connections using COM_STMT_PREPARE protocol command) and the number closed (statement/sql/dealloc_sql event for normal statements, statement/com/Close stmt event for COM_STMT_CLOSE command).  Once can write a query which computes the difference between the prepares and closes:

SELECT sp.thread_id, t.processlist_user user, t.processlist_host host,
sp.count_star - sd.count_star open_com_query_ps,
cp.count_star - cc.count_star open_com_prepare_ps
FROM
( SELECT COUNT_STAR,
THREAD_ID
FROM events_statements_summary_by_thread_by_event_name
WHERE event_name = 'statement/sql/prepare_sql' ) sp
JOIN
( SELECT COUNT_STAR,
THREAD_ID
FROM events_statements_summary_by_thread_by_event_name
WHERE event_name = 'statement/com/Prepare' ) cp
ON (cp.THREAD_ID = sp.THREAD_ID)
JOIN
( SELECT COUNT_STAR,
THREAD_ID
FROM events_statements_summary_by_thread_by_event_name
WHERE event_name = 'statement/sql/dealloc_sql' ) sd
ON (sd.THREAD_ID = sp.THREAD_ID)
JOIN
( SELECT COUNT_STAR,
THREAD_ID
FROM events_statements_summary_by_thread_by_event_name
WHERE event_name = 'statement/com/Close stmt' ) cc
ON (cc.THREAD_ID = sp.THREAD_ID)
JOIN threads t ON (t.thread_id = sp.thread_id)
ORDER BY GREATEST(open_com_query_ps, open_com_prepare_ps) DESC;

That’s nice, and it gives us output like the following:

+-----------+------+-----------+-------------------+---------------------+
| thread_id | user | host      | open_com_query_ps | open_com_prepare_ps |
+-----------+------+-----------+-------------------+---------------------+
|        22 | root | localhost |                 3 |                   0 |
+-----------+------+-----------+-------------------+---------------------+
1 row in set (0.08 sec)

Of course, there are still ways in which this isn’t 100% accurate. For example, one can do stuff like this:

+-----------+------+-----------+-------------------+---------------------+
| thread_id | user | host      | open_com_query_ps | open_com_prepare_ps |
+-----------+------+-----------+-------------------+---------------------+
|        22 | root | localhost |                 3 |                   0 |
+-----------+------+-----------+-------------------+---------------------+
1 row in set (0.08 sec)

mysql> PREPARE stmt FROM 'SELECT 1';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> PREPARE stmt FROM 'SELECT 2';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SELECT sp.thread_id, t.processlist_user user, t.processlist_host hos
-> sp.count_star - sd.count_star open_com_query_ps,
-> cp.count_star - cc.count_star open_com_prepare_ps
-> FROM
->  ( SELECT COUNT_STAR,
->     THREAD_ID
->    FROM events_statements_summary_by_thread_by_event_name
->    WHERE event_name = 'statement/sql/prepare_sql' ) sp
->  JOIN
->  ( SELECT COUNT_STAR,
->     THREAD_ID
->    FROM events_statements_summary_by_thread_by_event_name
->    WHERE event_name = 'statement/com/Prepare' ) cp
->  ON (cp.THREAD_ID = sp.THREAD_ID)
->  JOIN
->  ( SELECT COUNT_STAR,
->     THREAD_ID
->    FROM events_statements_summary_by_thread_by_event_name
->    WHERE event_name = 'statement/sql/dealloc_sql' ) sd
->  ON (sd.THREAD_ID = sp.THREAD_ID)
->  JOIN
->  ( SELECT COUNT_STAR,
->     THREAD_ID
->    FROM events_statements_summary_by_thread_by_event_name
->    WHERE event_name = 'statement/com/Close stmt' ) cc
->  ON (cc.THREAD_ID = sp.THREAD_ID)
->  JOIN threads t ON (t.thread_id = sp.thread_id)
->  WHERE sp.count_star - sd.count_star <> 0
->   OR cp.count_star - cc.count_star <> 0
-> ORDER BY GREATEST(open_com_query_ps, open_com_prepare_ps) DESC;
+-----------+------+-----------+-------------------+---------------------+
| thread_id | user | host      | open_com_query_ps | open_com_prepare_ps |
+-----------+------+-----------+-------------------+---------------------+
|        22 | root | localhost |                 5 |                   0 |
+-----------+------+-----------+-------------------+---------------------+
1 row in set (0.09 sec)

In the above, I prepared the same statement handler twice without closing it.

I know Marc has some ideas for further PERFORMANCE_SCHEMA monitoring of prepared statements, but what’s implemented today is a good step forward. It’s easier to tie a potential prepared statement leak to specific accounts, and to isolate the leakage to protocol-based prepared statements (drivers using COM_PREPARE) or the more generic COM_QUERY interface used by the mysql command-line interface.

2 thoughts on “Who’s leaking prepared statements?

  1. So is it safe to reuse single handler multiple times and deallocate it only once?
    I.e.:

    PREPARE stmt FROM ‘SELECT 1’;
    PREPARE stmt FROM ‘SELECT 2’;
    DEALLOCATE PREPARE stmt;

    Thank you.

    1. Hi Slava,

      It is documented to be safe, yes:

      If a prepared statement with the given name already exists, it is deallocated implicitly before the new statement is prepared. This means that if the new statement contains an error and cannot be prepared, an error is returned and no statement with the given name exists.

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.