CODEWITHSUNDEEP
mysqlperformancememorymariadb
Owow
Owow

Reputation: 347

Mysqld reserving more and more memory over time (query cache disabled)

After a few weeks running, mysqld (mariadb) is reserving more and more memory as you can see on the screenshot below

htop screenshot

Some answers says that disabling query caching will help, so i did add to /etc/mysql/my.cnf

[mysqld]
query_cache_size = 0
query_cache_type = 0

and tried to restart mysqld but the reserved memory is still the same after the restart. I didn't touch anything else on the my.conf and using both myisam and innodb tables

Also RESET QUERY CACHE on selected db does nothing at all

Upvotes: 1

Views: 205

Answers (2)

Rick James
Rick James

Reputation: 142248

Some things that are allocated in RAM grow up to some limit, then stick with that setting. The buffer_pool (cf innodb_buffer_pool_size) is the main memory allocation. When you start mysqld, only a little space is allocated for it. As you process data, tables are opened, etc, and the buffer_pool grows. This is probably what you are seeing.

The Query cache is generally useless and should be left off. (There are rare exceptions.) Even if you use it, it would probably not explain what you are seeing. It quickly fills up, fragments and remains (I think) stuck at query_cache_size.

If you need the memory for other apps, then do decrease innodb_buffer_pool_size. With only 170MB of data (including indexes?), a setting of 250M is probably more than adequate. Possibly 100M would work OK, but I would not go that low without adequate need for the RAM elsewhere.

The buffer_pool is used for a variety of things, but it is a "cache". That is, to handle all your data, it needs to be more then 170M, but being a cache, it is find to set it less than that. (But understand that may lead to more I/O.)

The Htop shows that not all of your 8GB of RAM is in use.

Also, lower max_connections; this will decrease the per-connection memory allocations. For most users 20 is sufficient. (Then watch for error messages something like 'cannot get a connection'.)

Upvotes: 1

Gordan Bobić
Gordan Bobić

Reputation: 1858

This is normal, and 209MB of RAM is very small for a typical MySQL deployment. Did you change any settings away from defaults? That memory consumption looks about right if you haven't. How big is your data?

Upvotes: 1

Related Questions