Bug #114438 high memory usage on a MySQL server during query
Submitted: 21 Mar 10:53 Modified: 22 Apr 6:14
Reporter: Ashik Ibrahim Email Updates:
Status: No Feedback Impact on me:
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.33 OS:Linux
Assigned to: MySQL Verification Team CPU Architecture:x86
Tags: json, MySQL, ram usage adviser

[21 Mar 10:53] Ashik Ibrahim
Problem Statement: I have been frequently facing high memory usage on a MySQL db server.
 Whenever the following query is running, We are facing a memory violation shoot up on the server which has json functions with more columns and sometimes causing restart of MySQL.

Total RAM                 - 62G
CPU                       - 8
MySQL                     - 8.0.33
bulk_insert_buffer_size   - 8M
innodb_buffer_pool_size   - 32G
innodb_change_buffer_max_size - 25
innodb_log_buffer_size    - 16M
innodb_sort_buffer_size   - 1M
join_buffer_size          - 256K
key_buffer_size           - 8M
myisam_sort_buffer_size   - 8M
preload_buffer_size       - 32K
read_buffer_size          - 128K
read_rnd_buffer_size      - 256K
sort_buffer_size          - 256K
tmp_table_size            - 16M
max_connections           - 500
max_heap_table_size       - 16M
innodb_log_file_size      - 512M

How to repeat:
The select statement from the below query impacts the memory even if the table size is very less. Can identify the memory violation when the JSON_ARRAYAGG(JSON_OBJECT function is used for more number of columns.

INSERT INTO report_compute_storage SELECT CompanyCode AS CmpCode, ProcessName AS ProcessCode, tmp.JsonKey AS JsonKey, tmp.JsonValue AS JsonValue, tmp.ModDt AS ModDt FROM (SELECT CONCAT(rpt.cmpCode, '-', rpt.SalesHier1Code) AS JsonKey, JSON_ARRAYAGG(JSON_OBJECT('currentLevelUserCode', rpt.SalesHier1Code, 'currentLevelUserName', rpt.SalesHier1Name, 'nextLevelUserCode', IF((rpt.SalesHier2Code IS NOT NULL AND rpt.SalesHier2Code != ''), rpt.SalesHier2Code, rpt.distrCode), 'nextLevelUserName',  IF((rpt.SalesHier2Name IS NOT NULL AND rpt.SalesHier2Name != ''), rpt.SalesHier2Name, rpt.DistrName), 'cmpCode', rpt.CmpCode, 'distrCode',  rpt.DistrCode, 'distrName', rpt.DistrName, 'salesmanCode', rpt.SalesmanCode, 'salesmanName', rpt.SalesmanName,  'routeCode', rpt.RouteCode, 'routeName', rpt.RouteName,  'customerCode', rpt.CustomerCode, 'customerName',  rpt.CustomerName, 'l3MonthSales', rpt.l3MonthSales, 'mtdValTarget', rpt.MTDValTarget, 'mtdVolTarget', rpt.MTDVolTarget,  'mtdSales', rpt.MTDSales, 'mtdVolume', rpt.MTDVolume,  'mtdNoOfInvoice', rpt.MTDNoOfInvoice, 'l3mAvgSales',  rpt.L3MAvgSales, 'totalMonthCoverage', rpt.TotalMonthCoverage, 'actualMonthCoverage', rpt.ActualMonthCoverage, 'actualMonthProductivity', rpt.ActualMonthProductivity, 'totalCRBills', rpt.TotalCRBills, 'totalCRValue',  rpt.TotalCRValue, 'sov', rpt.SOV,'displayCode1',rpt.DisplayCode1,  'displayName1',rpt.DisplayName1, 'displayCode2',rpt.DisplayCode2, 'displayName2',rpt.DisplayName2, 'displayCode3',rpt.DisplayCode3, 'displayName3',rpt.DisplayName3, 'displayCode4',rpt.DisplayCode4, 'displayName4',rpt.DisplayName4, 'displayCode5',rpt.DisplayCode5,  'displayName5',rpt.DisplayName5, 'displayCode6',rpt.DisplayCode6, 'displayName6',rpt.DisplayName6, 'displayCode7',rpt.DisplayCode7, 'displayName7',rpt.DisplayName7,  'displayCode8',rpt.DisplayCode8, 'displayName8',rpt.DisplayName8, 'displayCode9',rpt.DisplayCode9, 'displayName9',rpt.DisplayName9, 'displayCode10',rpt.DisplayCode10, 'displayName10',rpt.DisplayName10)) AS JsonValue, NOW() AS ModDt FROM retailerdashboard rpt WHERE rpt.cmpCode = CompanyCode AND rpt.SalesHier1Code IS NOT NULL AND rpt.SalesHier1Code != '' GROUP BY JsonKey) tmp GROUP BY tmp.JsonKey ON DUPLICATE KEY UPDATE ModDt = NOW()

Error that we are facing during the restart:
  kernel: Out of memory: Killed process 531457 (mysqld) total-vm:84460396kB, anon-rss:63030676kB, file-rss:0kB, shmem-rss:0kB, UID:1007 pgtables:164752kB oom_score_adj:0

Please find the memory utilization (During this query, JSON utilizes heavy memory):
event_name      current_alloc
memory/innodb/buf_buf_pool      34.78 GiB
memory/sql/JSON                 5.29 GiB

 Query O/P: This select query returns heavy output and returns around ~400M size outfile for almost 2000 records.
[22 Mar 6:14] MySQL Verification Team
Hello Ashik Ibrahim,

Thank you for taking the time to report a problem.  Unfortunately you are not using a current version of the product you reported a problem with, 8.0.33 is year old version(2023-04-18, General Availability) and many bugs fixed since then -- the problem might already be fixed. Please download a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with current GA 8.0.36, please change the version on this bug report to the version you tested and change the status back to "Open", provide logical dump of the table(structure, subset of data, query which reproduces issue) to reproduce issue at our end.  Again, thank you for your continued support of MySQL.

Alternatively, you can attach the test case here and I can confirm at my end. Thank you.

[23 Apr 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".