A INSERT SQL is MySQL use memory too much

MySQL & MariaDB Percona Distribution for MySQL
1

when MySQL is running a insert data, the count of number is about 20w, and is about 100M but the memory useage is about more than 4G

image
image1193Ă—184 15 KB

the SQL memory from SELECT a.thread_id, a.event_name,sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED), sql_text FROM performance_schema.memory_summary_by_thread_by_event_name a,performance_schema.events_statements_current b where a.thread_id=b.thread_id ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC LIMIT 10; CURRENT_NUMBER_OF_BYTES_USED is about 3GB

why the SQL use so much memory

1 Like
2

@matthewb, would this be a similar issue to mine?

1 Like
3

Without seeing the entire SQL it is hard to make guesses. I suggest that you profile the query to determine if any buffers or other memory segments are allocated in order to complete the query.

1 Like
4

the SQL just a simple insert into table values(),(),()…;

1 Like
5

“simple” is a relative term. If you have 1000 “()” in your INSERT, that will require more memory than just 1 “()”.

1 Like
6

ok
insert into table values(),()
…
about 20 0000 lines number

memory use

*************************** 1. row ***************************
thread_id: 442974
event_name: memory/sql/THD::variables
sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED): 3.93 GiB
sql_text: INSERT INTO data

*************************** 2. row ***************************
thread_id: 442974
event_name: memory/sql/thd::main_mem_root
sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED): 1.06 GiB
sql_text: INSERT INTO data
the SQL is the same one

1 Like