MySQL Memory Consumption and Open Prepare Statements

Today I read the nice post of Bhuvanesh on the memory leak issue they experienced with MySQL. In fact when you read the post, you realize that MySQL was just following the parameters that were setup (the default of max_prepare_stmt_count is 16382 even on 5.6).

I’m still convinced that the problem should be resolved in the application even if the use of ProxySQL for multiplexing is great.

In MySQL 8.0, we have enabled the memory instrumentation by default (see this post) and it’s possible to see the memory allocation for all threads having open prepare statements. I modified the query described in this post and added the memory consumption:

USE performance_schema;
SELECT t.thread_id, user,current_count_used, current_allocated, 
 current_avg_alloc, sp.count_star - sd.count_star open_sql_prepared, 
 cp.count_star - cc.count_star open_com_prepared
 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 
 sys.memory_by_thread_by_current_bytes t on t.thread_id = sp.thread_id
 WHERE sp.count_star - sd.count_star <> 0
   OR cp.count_star - cc.count_star <> 0;

And the result is the following:

+-----------+----------------+--------------------+-------------------+-------------------+-------------------+-------------------+
| thread_id | user           | current_count_used | current_allocated | current_avg_alloc | open_sql_prepared | open_com_prepared |
+-----------+----------------+--------------------+-------------------+-------------------+-------------------+-------------------+
|        58 | root@localhost |                207 | 1.74 MiB          | 8.62 KiB          |                 1 |                 0 |
|        61 | root@localhost |                 43 | 82.48 KiB         | 1.92 KiB          |                19 |                 0 |
|        62 | root@localhost |                 23 | 72.69 KiB         | 3.16 KiB          |                 6 |                 0 |
+-----------+----------------+--------------------+-------------------+-------------------+-------------------+-------------------+

As you can see, MySQL 8.0 instrumentation allows you to find almost everything you need ! If you have extra needs, please let us know 😉