2

I'm running MariaDB 11.2 in Docker Swarm in Ubuntu 20.04 and its memory usage keeps growing until database is killed by OOM killer.

Memory graph

According to MySQLTuner, max memory should be around 7.1GB, but after 1-2 days memory usage goes up to 30GB. So when new task is started on server and there are not enough memory, the OOM killer select mariadbd and kills it.

Server:

  • Ubuntu 20.04.1 (5.4.0-169-generic Kernel)
  • 64 GB RAM
  • Docker 24.0.6
  • MariaDB 11.2.3 (in container)
  • Also Apache 2 (in container)

Here's docker-compose.yaml:

version: "3.8"
services:
    database:
        image: mariadb:11.2
        deploy:
            restart_policy:
                condition: any
        networks:
            - database
        ports:
            - "3306:3306"
        ulimits:
            memlock: 2097152 # 2MB
        volumes:
            - "db_data:/var/lib/mysql"
            - "db_log:/var/log/mysql"
            - "/etc/localtime:/etc/localtime:ro"
            - "/etc/timezone:/etc/timezone:ro"
            - "./my.cnf:/etc/mysql/my.cnf"

networks:
    database:
        driver: overlay
        attachable: true
        name: database
...

Here's my.cnf:

[server]
table_definition_cache = 2048
innodb_buffer_pool_size = 6G
innodb_log_file_size = 1536M
innodb_log_buffer_size = 256M

log_error=/var/log/mysql/error.log

[client-server]
socket = /run/mysqld/mysqld.sock

!includedir /etc/mysql/mariadb.conf.d/
!includedir /etc/mysql/conf.d/

Includes are from my.cnf template.

Content of /etc/mysql/mariadb.conf.d/:

# 05-skipcache.cnf:
[mariadb]
host-cache-size=0
skip-name-resolve

# 50-server.cnf:
[mariadbd]
pid-file                = /run/mysqld/mysqld.pid
basedir                 = /usr

expire_logs_days        = 10

character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

Directory /etc/mysql/conf.d/ is empty.

What I tried?

Update database

I had same problem with 10.6. version. So I tried upgrading to 11.2. but that didn't fix the problem.

Performence schema

I enabled performence schema with memory instruments.

...
performance_schema = ON

performance-schema-instrument='memory/%=COUNTED'
...

Sum from sys.x$memory_global_by_current_bytes is around 7.1GB. But memory used by mariadbd process is much higher.

Incresse memlock in container to 2MB

I found warning in mysql/error.log:

2024-02-11 21:09:42 0 [Warning] mariadbd: io_uring_queue_init() failed with ENOMEM: try larger memory locked limit, ulimit -l, or https://mariadb.com/kb/en/systemd/#configuring-limitmemlock under systemd (262144 bytes required)
2024-02-11 21:09:42 0 [Warning] InnoDB: liburing disabled: falling back to innodb_use_native_aio=OFF

So I increesed memory lock limit to 2MB. Warning is gone, but memory problem still persists.


Update 1

I had to reduce innodb_buffer_pool_size to 1GB to reduce the number of crashes. I also adjusted innodb_log_file_size to 256MB.

MySQLTuner output when mariadb takes up 20GB of RAM (total system usage 48GB - 70%):

...

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2d 21h 59m 30s (142M q [564.348 qps], 336K conn, TX: 531G, RX: 107G)
[--] Reads / Writes: 80% / 20%
[--] Binary logging is disabled
[--] Physical Memory     : 62.8G
[--] Max MySQL memory    : 2.0G
[--] Other process memory: 0B
[--] Total buffers: 1.5G global + 2.9M per thread (151 max threads)
[--] Performance_schema Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.6G (2.59% of installed RAM)
[OK] Maximum possible memory usage: 2.0G (3.11% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (612/142M)
[OK] Highest usage of available connections: 25% (38/151)
[OK] Aborted connections: 0.00% (0/336034)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (113 temp sorts / 1M sorts)
[!!] Joins performed without indexes: 1341
[OK] Temporary tables created on disk: 4% (16K on disk / 358K total)
[OK] Thread cache hit rate: 99% (38 created / 336K connections)
[OK] Table cache hit rate: 99% (143M hits / 143M requests)
[OK] table_definition_cache (2048) is greater than number of tables (441)
[OK] Open file limit used: 0% (32/32K)
[OK] Table locks acquired immediately: 100% (468 immediate / 468 locks)

...

-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] General MyIsam metrics:
[--]  +-- Total MyISAM Tables  : 0
[--]  +-- Total MyISAM indexes : 0B
[--]  +-- KB Size :128.0M
[--]  +-- KB Used Size :23.3M
[--]  +-- KB used :18.2%
[--]  +-- Read KB hit rate: 0% (0 cached / 0 reads)
[--]  +-- Write KB hit rate: 0% (0 cached / 0 writes)
[--] No MyISAM table(s) detected ....

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB File per table is activated
[OK] InnoDB Buffer Pool size ( 1.0G ) under limit for 64 bits architecture: (17179869184.0G )
[!!] InnoDB buffer pool / data size: 1.0G / 34.4G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 256.0M * 1/1.0G should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk: 64 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.34% (42353558185 hits / 42633926388 total)
[OK] InnoDB Write Log efficiency: 91.45% (130133891 hits / 142298603 total)
[OK] InnoDB log waits: 0.00% (0 waits / 12164712 writes)

-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/2.6M
[OK] Aria pagecache hit rate: 99.9% (507M cached / 385K reads)

...

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    You are using an unsupported version for production environments
    Upgrade as soon as possible to a supported version !
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_join_buffer_size
    Performance schema should be activated for better diagnostics
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    performance_schema=ON
    innodb_buffer_pool_size (>= 34.4G) if possible.

Update 2

I found warning in log file:

[Warn] [Entrypoint]: /sys/fs/cgroup/pids:/docker/<conatiner_id>
11:net_cls,net_prio:/docker/<conatiner_id>
10:cpuset:/docker/<conatiner_id>
9:memory:/docker/<conatiner_id>
8:cpu,cpuacct:/docker/<conatiner_id>
7:hugetlb:/docker/<conatiner_id>
6:freezer:/docker/<conatiner_id>
5:devices:/docker/<conatiner_id>
4:blkio:/docker/<conatiner_id>
3:rdma:/docker/<conatiner_id>
2:perf_event:/docker/<conatiner_id>
1:name=systemd:/docker/<conatiner_id>
0::/docker/<conatiner_id>/memory.pressure not writable, functionality unavailable to MariaDB
3
  • seccomp filtering might be removing the syscall, though I'd expect a different error. What kernel version? Nothing recent has changed with regard to the implementation. Newer kernel versions don't quire locked memory.
    – danblack
    Feb 18 at 7:37
  • 1
    @WilsonHauck justpaste.it/g54o3 Feb 25 at 21:06
  • @WilsonHauck 1) my.cnf is complete (but 2 variables was updated => innodb_buffer_pool_size = 1G, innodb_log_file_size = 256M) 2) Added to justpaste.it/g54o3 Thanks Feb 27 at 10:35

2 Answers 2

0

Observation, Your OS ulimit -a report indicates Open Files of 1024. Your MariaDB open_files_limit is 32214 requested.

Dynamically your OS limit can be adjusted with ulimit -n 40000 and press Enter. If you need to reboot the OS, you can reset this limit to 40000 until such time as your can adjust you OS configuration for 40000 to persist across OS stop/start. This will help reduce Open Files thrashing to support your concurrent max_used_connections of 49.

Suggestions to consider for your my.cnf [mysqld] section

server_id=NULL  # you do not use replication, to avoid REPL overhead in instance.
innodb_buffer_pool_size=2G  # from 1G to reduce innodb_buffer_pool_reads RPS of 1,212.
innodb_log_file_size=512M  # from 256M to delay log rotation.
read_rnd_buffer_size=16K  # from 256K to reduce handler_read_rnd_next RPS of 28,095.
read_buffer_size=256K  # from 128K to reduce handler_read_next RPS of 50,202.
wait_timeout=3600  # from 28800 to reduce sleeping processes quicker.

(unless you have some VERY LOG running processes more than one hour).

With the 25 Sleeping processes reported recently that had max_memory_used SUM of 16M, we need to explore WHY these processes average sleeping time of 25 minutes per process. View profile, please.

2
  • Thanks. I updated values as suggested, but server_id=NULL caused error: [ERROR] mariadbd: Error while setting value 'NULL' to 'server_id'. Feb 28 at 10:26
  • Until MariaDB makes it possible as per their documentation, you will be required to use some REPLication management resources due their default value of 1. Feb 28 at 14:13
0

It looks like this instance has

version_malloc_library=system  

rather than jemalloc.

Has this problem been discovered in the past at these 2 url's?

https://stackoverflow.com/questions/78071133

https://stackoverflow.com/questions/60430448  

Just a thought.

In both cases use of jemalloc appears to manage RAM release better.

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.