MySQL Memory Consumption and Open Prepare Statements

on

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 😉

4 thoughts on “MySQL Memory Consumption and Open Prepare Statements

  1. For sure, if prepared statements are not closed at all the problem is in the application and to me it didn’t become clear how max_prepare_stmt_count was set to it’s maximum value when there was a lot more reasonable default.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

recent

Last Tweets

Locations of visitors to this page
categories