| Bug #107173 | A INSERT SQL is MySQL use memory too much | ||
|---|---|---|---|
| Submitted: | 2 May 2022 5:37 | Modified: | 2 May 2022 14:48 |
| Reporter: | 问问 jack | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | MySQL 8.0.19 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[2 May 2022 5:50]
问问 jack
the Inert number is 100MB , but the memory use is more then 3GB
[2 May 2022 12:07]
MySQL Verification Team
Hi Mr. jack, Thank you for your bug report. Your bug report is a bit imprecise, so we truly do not know what are you asking about. From what you wrote, you are running an INSERT which inserts 100 million rows in one table. That thread is using circa more than 3 Gb in order to insert 100 million rows. You also wrote that count of number is about 20w, which is totally confusing and it is not possible to understand what that means at all. Regarding the insertion of 100 million rows in a table, that operation can indeed use 4 Gb. The average size of the row can be quite big, then there are indices, transaction and DML logs and other entities that use memory. If you have set InnoDB 's buffer pool high and have other buffers configured , then it is entirely expected behaviour.
[2 May 2022 13:47]
问问 jack
thank you The average size of the row is not big. the count(*) of the insert data is about 20w ,and the total size of the insert data is only 100MB ,but it uses 3GB memory. where I can find indices, transaction and DML logs and other entities that use memory.
[2 May 2022 14:48]
MySQL Verification Team
Hi, It is all described in our Reference Manual, which you can find on http://dev.mysql.com. We also recommend that you upgrade to the latest 8.0 release available, which is 8.0.29. This is a forum for bugs with fully repeatable test cases and not a forum for asking questions ........ Not a bug.
Description: 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 How to repeat: 2022-04-30 01:00:59 *************************** 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 ................. 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