Description:
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
Note:
Query O/P: This select query returns heavy output and returns around ~400M size outfile for almost 2000 records.
Description: 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 Note: Query O/P: This select query returns heavy output and returns around ~400M size outfile for almost 2000 records.