2

I am experiencing very high memory usage on MariaDB server. I am not sure if it is due to misconfiguration or a bug.

Server:                 MariaDB
Server version:         10.5.4-MariaDB MariaDB Server

We are running MariaDB 10.5.4

Our server is configured to use 140G for bugger and 9G for log.

innodb_buffer_pool_size=140G
innodb_log_file_size=9G
max_heap_table_size            = 8G
tmp_table_size                 = 8G

However, our server is currently using 225G without any load on server, and frequently spikes over 240G during queries.

All tables are InnoDB. Memory buffer usage according to information schema is the following:

MariaDB [db]> SELECT  ENGINE,         ROUND(SUM(data_length) /1024/1024, 1) AS "Data MB",         ROUND(SUM(index_length)/1024/1024, 1) AS "Index MB",         ROUND(SUM(data_length + index_length)/1024/1024, 1) AS "Total MB",                                                                                         COUNT(*) "Num Tables"     FROM  INFORMATION_SCHEMA.TABLES     WHERE  table_schema not in ("information_schema", "PERFORMANCE_SCHEMA", "SYS_SCHEMA", "ndbinfo")     GROUP BY  ENGINE;
+--------+-----------+----------+-----------+------------+
| ENGINE | Data MB   | Index MB | Total MB  | Num Tables |
+--------+-----------+----------+-----------+------------+
| NULL   |      NULL |     NULL |      NULL |         20 |
| Aria   |       0.8 |      0.3 |       1.1 |         24 |
| CSV    |       0.0 |      0.0 |       0.0 |          2 |
| InnoDB | 1952761.3 | 284144.6 | 2236905.9 |       2517 |
| MEMORY |       0.0 |      0.0 |       0.0 |          3 |
+--------+-----------+----------+-----------+------------+


Total large memory allocated 151397597184
Dictionary memory allocated 899109080
Buffer pool size   9019360
Free buffers       1024
Database pages     9018336
Old database pages 3329014
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 47473690, not young 10534657191
0.00 youngs/s, 0.00 non-youngs/s
Pages read 192376965, created 62530810, written 146872114
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 9018336, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

Server memory usage starts out normally, but greatly increases during couple days until it eventually consumes all of server's resources and restarts.

We began experiencing this issue shortly after upgrading from an ancient version of MariaDB. (do not recall which one, but it was very dated).

How can I troubleshoot this issue further, and what can I do to ensure that memory usage does not go above 160G?

4
  • What else is running on this server? How do you know it's MariaDB that "consumes all of server's resources"? How do you control the number of database connections?
    – mustaccio
    Nov 11, 2020 at 3:00
  • @mustaccio Nothing except for MariaDB runs on this server. I have full control over database configuration. Server is 'configured' to use ~160G of RAM, but uses 250G+ of RAM after a couple days. Database usually handles around 100-200 for short amount of time, and but usually floats around 5-10.
    – Dimi
    Nov 11, 2020 at 6:09
  • @Dimi any update? Have a similar issue
    – edo1
    Apr 30, 2023 at 12:22
  • @edo1 After many month of back-n-forth troubleshooting, MariaDB identified the root cause of this issue and fixed it. We didn't have any issues with newer versions of MariaDB
    – Dimi
    May 12, 2023 at 14:03

2 Answers 2

2

POSSIBLE MEMORY LEAK ISSUES

There have been three releases since version 10.5.4

  • 03 Nov 2020 : MariaDB 10.5.7
  • 07 Oct 2020 : MariaDB 10.5.6
  • 10 Aug 2020 : MariaDB 10.5.5

There have been some memory issues fixed since version 10.5.4

Memory Issues Fixed in 10.5.5

Revision #d1bb7f9143

  • 2020-06-24 12:51:55 +0300
  • Fixed memory leak in item_sum.cc::report_cut_value_error()

Revision #572e53d8cc

  • 2020-06-19 15:24:16 +0530
  • MDEV-22931: mtr_t::mtr_t() allocates some memory

Memory Issues Fixed in 10.5.6

None (just branch merges from previous versions)

Memory Issues Fixed in 10.5.7

Revision #0d581562d8

  • 2020-10-22 17:09:18 +0300
  • Fix memory leak on Alter_drop allocation

Revision #d98ccbe1e1

  • 2020-08-21 11:54:16 +0300
  • MDEV-23526 InnoDB leaks memory for some static objects

WHAT CAN YOU DO NEXT

You might need to update to MariaDB 10.5.7 on a Staging Server, load data into it.Once you do that, you can do some benchmarking.

  • Perhaps you can mysqlbinlog dump everything from the binaries logs in the 10.5.4 server and import the SQL generated into the Staging Server running 10.5.7.
  • You could also import the slow log from 10.5.4 server into 10.5.7 Staging Server.

See if memory bloats up or not.

If memory does not bloat, then you should upgrade the prod server to 10.5.7.

1
max_heap_table_size            = 8G
tmp_table_size                 = 8G

One chunk of 8GB of RAM, or even more than one, can be allocated per connection. This depends on how complex your SELECTs and how big your tables are.

Do not set those to more than, say, 1% of RAM.

If you would like more analysis, please provide RAM size, GLOBAL STATUS and VARIABLES: http://mysql.rjweb.org/doc.php/mysql_analysis

innodb_log_file_size is a file size; it does not impact RAM usage.

There are some other settings that may impact RAM usage; the above link will help discover them.

6
  • 1
    '8G' statement is a little ambiguous. Can you clarify if that applies to max_heap or to tmp? I was under impression that max_heap_table_size is the heap/temp memory usage cap for the entire server, and tmp_table_size is a limit for a single table.
    – Dimi
    Nov 11, 2020 at 14:55
  • @Dimi - for temp tables created (by mysql) in support of a complex SELECT, min(max_heap_table_size, tmp_table_size) is used. (Most people raise/lower both, so it is not a problem. max_heap_table_size also controls how big a Engine=MEMORY table is allowed to be. Do you have a lot of MEMORY tables? (Or is that side of the discussion irrelevant?)
    – Rick James
    Nov 11, 2020 at 17:12
  • @Dimi - Rolando's Answer may be the 'right' answer'. But my recommendation still stands -- lower at least tmp_table_size.
    – Rick James
    Nov 11, 2020 at 17:16
  • Thank you for clarification. We used to have memory tables in the past, but not anymore. Will reduce tmp_table_size to be 1% of RAM (3G) and see if that changes anything. Testing Rolando's answer on dev now.
    – Dimi
    Nov 11, 2020 at 17:45
  • @RickJames These variables shouldn't affect the memory consumption of the InnoDB engine, should they? But in the original question, we see intensive memory usage by the InnoDB engine.
    – edo1
    Apr 30, 2023 at 12:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.