How to fix high memory usage of MariaDB?

Question

I am using MariaDB(10.1.21) with following storage engines:

• InnoDB
• TokuDB
• Spider Engine

System Configuration is :

• 3GB RAM
• Dual Core Processor

I have tried pt-mysql-summary tool to identify the memory usage and it shows 90% of innodb buffer pool size is filled,when no process is running on server.

I have also tried restarting the MariaDB server and this reduces memory for about 1-2 hours only and after that it eats up memory.

Any clue why it is consuming too much memory?

Sharing my configurations below:

My.cnf

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#performance_schema=ON
tmp_table_size=16M
max_heap_table_size=16M
skip-log-bin
#table_open_cache                =16384
#table_definition_cache          =16384

#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages     = en_US
skip-external-locking


#bind-address           = 127.0.0.1
#skip-networking


# SAFETY #
max_allowed_packet= 16M
max-connect-errors = 1000000
skip-name-resolve
#sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048


# LOGGING #
log_warnings            = 2
slow_query_log          =1
log-output              = TABLE
long_query_time = 5
log_slow_verbosity      = query_plan
log-error               =/var/log/mysql/system_error.err


# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 128M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 1720M

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completion

[isamchk]
key_buffer              = 16M

TokuDB.cnf

plugin-load-add=ha_tokudb.so
tokudb_data_dir=/var/lib/mysql/toku_db_data
tokudb_read_block_size=64k
tokudb_row_format=tokudb_zlib
tokudb_directio=on

Spider.cnf

[mariadb]
#spider_internal_limit          =1
spider_direct_order_limit       =1
spider_skip_default_condition   =1
spider_casual_read              =1
spider_bgs_mode                 =2
spider_direct_dup_insert        =1
spider_auto_increment_mode      =2
#optimizer_switch='engine_condition_pushdown=on'
#optimizer_switch='mrr=on,mrr_sort_keys=off'

Answer 1

Your buffer pool is set to 1720M and TokuDB uses by default half of the server memory. 1.7G + 1.5G = 3.2G, which is already more than the total physical memory.

Set both values to something lower and leave at least 1G available for the OS. for example:

innodb_buffer_pool_size = 1G
tokudb_cache_size = 1G

Also, the reason why you're seeing the buffer pool filling up to 90% is that some queries might run at some point, even a backup with mysqldump could do that. Once the buffer pool is full, it will not empty even if there is no server activity.

Answer 2

It's not critical So don't worry It's a normal Behaviour of Innodb Engine.

If your data size is bigger than or equal the Buffer pool size, Innodb will fill all of buffer size at warm up time. So when you start MySQL after some hours buffer pool will full (Only Buffer pull Not all Ram's capacity)

You'are using multiply Engines in your system. It can complicate config, because you have to controll important variables (such as Memory Configs) of each Engine.

Do you have a reason to use the TokuDB? I think it's better to use one of these engines (InnoDB or TokuDB)

So if you prefer to use innodb, you can set buffer pool size to about 70% of RAM unless your data/index size is small