MySQL Bugs: #86821: Trigger allocates memory during SELECT

Bug #86821	Trigger allocates memory during SELECT
Submitted:	26 Jun 2017 4:06	Modified:	22 Aug 2024 22:30
Reporter:	Charly Batista	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.6, 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
We are select some data from Table_B during the insert into Table_A as fallow:
INSERT INTO Table_A (field_1, field_2, field_3,..., field_n) VALUES (value_1, (SELECT pk FROM Table_B where uk = 1), value_3, ..., value_n);

Table_B has triggers for AFTER and BEFORE INSERT, UPDATE and DELETE but Table_A doesn't have any trigger. It seems the variables inside the trigger are allocating memory even though none of the specified events were being triggered.

How to repeat:
I've reproduced this behavior using a simple test case. I'm attaching the files to reproduce the test. The files have descriptive names. The bash file just execute the SQL files into MySQL and loop 50 times calling the function memleaktest(). This function does only one insert into "table_a". I'm using big varchars (5000+) to make the memory usage grows fast.

Bash Script to run the test

Attachment: runtest.sh (application/octet-stream, text), 186 bytes.

SQL File to create tables and trigger

Attachment: create_structure.sql (application/sql, text), 23.48 KiB.

Stored procedure that inserts data into Table_A

Attachment: create_procedure.sql (application/sql, text), 147.57 KiB.

The SP helps to test but I've got very similar and consistent results running the plain insert without SP.

Running MySQL under valgrind gives more hints:

Detailed snapshots: [1, 11, 15, 21, 23, 25 (peak), 27, 39, 43, 56, 66]
--------------------------------------------------------------------------------
  n        time(i)         total(B)   useful-heap(B) extra-heap(B)    stacks(B)
--------------------------------------------------------------------------------
  0              0                0                0             0            0
  1  5,825,620,323    1,846,219,256    1,845,391,654       827,602            0
99.96% (1,845,391,654B) (heap allocation functions) malloc/new/new[], --alloc-fns, etc.
->75.18% (1,387,953,735B) 0xECFAE6: my_malloc (my_malloc.c:191)
| ->59.70% (1,102,175,800B) 0xECB92B: alloc_root (my_alloc.c:279)
| | ->10.53% (194,367,520B) 0xC2B934: sp_head::reset_lex(THD*) (sql_lex.h:3715)
| | | ->04.35% (80,373,728B) 0xDA28FD: MYSQLparse(THD*) (sql_yacc.yy:4108)
| | | | ->04.35% (80,373,728B) 0xCB1A62: parse_sql(THD*, Parser_state*, Object_creation_ctx*) (sql_parse.cc:7523)
| | | |   ->04.35% (80,373,728B) 0xD5C591: Trigger::parse(THD*) (trigger.cc:533)
| | | |     ->04.35% (80,373,728B) 0xD58036: Table_trigger_dispatcher::parse_triggers(THD*) (table_trigger_dispatcher.cc:689)
| | | |       ->04.35% (80,373,728B) 0xD581BA: Table_trigger_dispatcher::check_n_load(THD*, bool) (table_trigger_dispatcher.cc:442)
| | | |         ->04.35% (80,373,728B) 0xC521E5: open_table(THD*, TABLE_LIST*, Open_table_context*) (sql_base.cc:4396)
| | | |           ->04.35% (80,373,728B) 0xC58650: open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*) (sql_base.cc:5175)
| | | |             ->04.35% (80,373,728B) 0xC58F69: open_tables_for_query(THD*, TABLE_LIST*, unsigned int) (sql_base.cc:6568)
| | | |               ->04.35% (80,373,728B) 0xE1F4EB: Sql_cmd_insert::mysql_insert(THD*, TABLE_LIST*) (sql_insert.cc:464)
| | | |                 ->04.35% (80,373,728B) 0xE20370: Sql_cmd_insert::execute(THD*) (sql_insert.cc:3115)
| | | |                   ->04.35% (80,373,728B) 0xCAC1E5: mysql_execute_command(THD*, bool) (sql_parse.cc:3786)
| | | |                     ->04.35% (80,373,728B) 0xC2F57E: sp_instr_stmt::exec_core(THD*, unsigned int*) (sp_instr.cc:1021)
| | | |                       ->04.35% (80,373,728B) 0xC311B2: sp_lex_instr::reset_lex_and_exec_core(THD*, unsigned int*, bool) (sp_instr.cc:412)
| | | |                         ->04.35% (80,373,728B) 0xC31B59: sp_lex_instr::validate_lex_and_execute_core(THD*, unsigned int*, bool) (sp_instr.cc:712)
| | | |                           ->04.35% (80,373,728B) 0xC32ECE: sp_instr_stmt::execute(THD*, unsigned int*) (sp_instr.cc:908)
| | | |                             ->04.35% (80,373,728B) 0xC2AE92: sp_head::execute(THD*, bool) (sp_head.cc:790)
| | | |                               ->04.35% (80,373,728B) 0xC2E935: sp_head::execute_procedure(THD*, List<Item>*) (sp_head.cc:1526)
| | | |                                 ->04.35% (80,373,728B) 0xCAF61A: mysql_execute_command(THD*, bool) (sql_parse.cc:4854)
| | | |                                   ->04.35% (80,373,728B) 0xCB2353: mysql_parse(THD*, Parser_state*) (sql_parse.cc:5929)
| | | |                                     ->04.35% (80,373,728B) 0xCB2E98: dispatch_command(THD*, COM_DATA const*, enum_server_command) (sql_parse.cc:1493)
| | | |                                       ->04.35% (80,373,728B) 0xCB48E5: do_command(THD*) (sql_parse.cc:1021)
| | | |                                         ->04.35% (80,373,728B) 0xD796AE: handle_connection (connection_handler_per_thread.cc:312)
| | | |                                           ->04.35% (80,373,728B) 0x1208A22: pfs_spawn_thread (pfs.cc:2188)
| | | |                                             ->04.35% (80,373,728B) 0x4E31A9F: start_thread (in /lib64/libpthread-2.12.so)
| | | |                                               ->04.35% (80,373,728B) 0x6B85BCB: clone (in /lib64/libc-2.12.so)
<...cut...>

Also checking memory events on PS:

mysql> select event_name, current_alloc, high_alloc from memory_global_by_current_bytes where current_count > 0;
+--------------------------------------------------------------------------------+---------------+-------------+
| event_name                                                                     | current_alloc | high_alloc  |
+--------------------------------------------------------------------------------+---------------+-------------+
| memory/innodb/buf_buf_pool                                                     | 7.29 GiB      | 7.29 GiB    |
| memory/sql/sp_head::main_mem_root                                              | 3.21 GiB      | 3.62 GiB    |
| memory/innodb/hash0hash                                                        | 210.16 MiB    | 323.63 MiB  |
| memory/sql/TABLE                                                               | 183.82 MiB    | 190.28 MiB  |
| memory/sql/Query_cache                                                         | 128.02 MiB    | 128.02 MiB  |
| memory/mysys/KEY_CACHE                                                         | 64.00 MiB     | 64.00 MiB   |
| memory/innodb/log0log                                                          | 32.08 MiB     | 32.08 MiB   |
| memory/innodb/parallel_doublewrite                                             | 30.27 MiB     | 30.27 MiB   |
| memory/performance_schema/table_handles                                        | 27.19 MiB     | 27.19 MiB   |
| memory/innodb/mem0mem                                                          | 19.14 MiB     | 20.79 MiB   |
| memory/performance_schema/events_statements_history_long                       | 13.66 MiB     | 13.66 MiB   |
| memory/performance_schema/events_statements_summary_by_digest.tokens           | 9.77 MiB      | 9.77 MiB    |
<...cut...>

+------+-----------+-----------------------------------------------------+-------------+------------+---------------------------+-----------------------
| USER | HOST      | EVENT_NAME                                      | COUNT_ALLOC | COUNT_FREE | SUM_NUMBER_OF_BYTES_ALLOC | SUM_NUMBER_OF_BYTES_FREE |
+------+-----------+-----------------------------------------------------+-------------+------------+---------------------------+-----------------------
| root | localhost | memory/sql/sp_head::main_mem_root               |     5161270 |    5123106 |               90436226992 |              89652629360 |        
| root | localhost | memory/sql/sp_head::main_mem_root               |     9857844 |    9840114 |              172671752216 |             172288775328 |        
| root | localhost | memory/sql/sp_head::main_mem_root               |    15116325 |   15077409 |              264845838432 |             263971081760 |        
| root | localhost | memory/sql/sp_head::main_mem_root               |    20147715 |   20108371 |              353006440328 |             352085548776 |        
| root | localhost | memory/sql/sp_head::main_mem_root               |    21860573 |   21822776 |              383022467200 |             382119924096 |        
| root | localhost | memory/sql/sp_head::main_mem_root               |    25300477 |   25260725 |              443310326616 |             442343463704 |        
| root | localhost | memory/sql/sp_head::main_mem_root               |    30462708 |   30441464 |              533762286640 |             533118771504 |        
| root | localhost | memory/sql/sp_head::main_mem_root               |    35595979 |   35574369 |              623717978344 |             623028965448 |
<...cut...>

Hi,

Thanks for the bug report. I verified behavior as described. I am not sure this is a bug (I did not detect a memory leak, just memory allocation) but I'll let our dev team look at it further.

best regards
Bogdan

I can reproduce on 8.0.13. There's an even simpler test case, and I have some information that I don't know how to interpret.

Here's the simple structure that can illustrate the memory growth:

CREATE DATABASE `test_sp_head`;
DROP TABLE IF EXISTS `test_sp_head`;
CREATE TABLE `test_sp_head` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DELIMITER ;;
CREATE TRIGGER `test_sp_head` BEFORE UPDATE ON `test_sp_head` FOR EACH ROW begin signal sqlstate '45000' set message_text = 'triggered'; end ;;
DELIMITER ;

Then to display the continuous allocation of memory, run this command multiple times:
echo "select event_name, current_alloc, high_alloc from sys.memory_global_by_current_bytes where event_name like '%sp_head%'; explain select * from test_sp_head; select event_name, current_alloc, high_alloc from sys.memory_global_by_current_bytes where event_name like '%sp_head%'; " | mysql -A test_sp_head

What I see is that for the first 16 connections where we create even a query plan for any table that has triggers associated with it, new memory is allocated. After that memory growth halts. 

I have learned that mysql parses triggers and stored procedures for each thread/connection, and allocates memory for the compiled version. What I don't know is why that memory is not released when the thread exits, or the connection ends.

I'm guessing that there's some pool (though I don't have thread pooling enabled) that is being used for this?

>
> "What I see is that for the first 16 connections"
>

Tried to tweak table_open_cache_instances to affect this?

Yes, after changing that, the number of times it caches can be controlled. I had been looking for thread-specific caches, hadn't found that configuration. Thank you! For now, as far as I'm concerned this is not a bug.

This is fixed in MySQL 9.1 by BUG#44625 / WL#16455.

Closed.