MariaDB uses more RAM than MySQL?

Asked by zingaburga on 2011-06-25

Just trying out MariaDB on a small webserver with 1GB RAM and I noticed that MariaDB seems to be consuming more memory than a similar MySQL setup.

On this "clean" install (only 1 connection (me) and no data in the database and no additional plugins than those supplied), it's consuming around 200MB of RAM. I'd expect MySQL, with similar parameters and configuration to take around 50MB of RAM (though I haven't exactly verified this).
I'm running Debian 6 32-bit, and MariaDB 5.2.7 was installed through apt (using http://kb.askmonty.org/v/installing-mariadb-deb-files). My MySQL experience is based off a different server also running Debian 6 32-bit and MySQL being installed through apt as well.

I don't particularly have a memory usage target, however, I find the large difference in memory usages between the two rather surprising - is this intentional?

Here's my.cnf for my MariaDB install: http://pastebin.com/QG9dZm5R

There is nothing special in /etc/mysql/conf.d/ - only /etc/mysql/conf.d/mysqld_safe_syslog.cnf which was included in the stock install and hasn't been modified.

So would anyone have an idea why MariaDB would be using so much more RAM than MySQL?

Thanks!

Question information

Language:
English Edit question
Status:
Solved
For:
MariaDB Edit question
Assignee:
No assignee Edit question
Solved by:
Sergei Golubchik
Solved:
2011-06-26
Last query:
2011-06-26
Last reply:
2011-06-26
Kurt von Finck (mneptok) said : #1

I would be very surprised if MySQL used 1/4 the memory on the same hardware with same configuration and same datasets. I strongly suggest you test the supposition that MySQL would use ~50MB in the *exact* same situation.

zingaburga (zingaburga) said : #2

Thanks a lot for the very fast response Kurt!
That sounds fair enough - I thought you guys might happen to know something before I went in and set some testing thing up, perhaps a different default configuration option, or perhaps MariaDB does some sort of buffering in free memory that MySQL does...

Anyway, I've got another spare VPS server to test with. Details:
RAM: 4GB (2GB guaranteed, 4GB burst)
OS: Debian 6 64-bit

I'm installing MySQL and MariaDB on the same above server, but not together (ie, only ever have one of them installed at a time). I'm using the *exact same* my.cnf for both MySQL and MariaDB installs, so obviously, the only difference between the two would be the stuff in the [mariadb] section. You can find my my.cnf file here: http://pastebin.com/KumY4mkj
Again, there's no data in either database (beyond the stock installed mysql/information_schema databases) and the only user ever used it is me, so no more than 1 connection at any time.

MySQL: installed from apt
# mysql --version
mysql Ver 14.14 Distrib 5.1.57, for debian-linux-gnu (x86_64) using readline 6.1

After putting in the above my.cnf:

root@vps:/etc/mysql# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for tables which need an upgrade, are corrupt or were
not closed cleanly..
root@vps:/etc/mysql# ps uUmysql
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
mysql 26473 1.0 0.3 90980 14136 pts/0 Sl 07:24 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/m
root@vps:/etc/mysql# pmap -x 26473
[ find the output here: http://pastebin.com/Gk6pzKZJ ]
root@vps:/etc/mysql# free
             total used free shared buffers cached
Mem: 4194304 83508 4110796 0 0 0
-/+ buffers/cache: 83508 4110796
Swap: 0 0 0
root@vps:/etc/mysql# /etc/init.d/mysql stop
Stopping MySQL database server: mysqld.
root@vps:/etc/mysql# free
             total used free shared buffers cached
Mem: 4194304 27904 4166400 0 0 0
-/+ buffers/cache: 27904 4166400
Swap: 0 0 0

According to the difference in 'free', usage is around 54MB. I'm not sure how accurate this is, but it should at least be some indication.
Also, if it helps:

root@vps:/etc/mysql# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.1.57-1~dotdeb.1 (Debian)

[...]

mysql> show plugins;
+------------+----------+----------------+---------+---------+
| Name | Status | Type | Library | License |
+------------+----------+----------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | DISABLED | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | DISABLED | STORAGE ENGINE | NULL | GPL |
| CSV | DISABLED | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | DISABLED | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | DISABLED | STORAGE ENGINE | NULL | GPL |
+------------+----------+----------------+---------+---------+
10 rows in set (0.00 sec)

Okay, so we've got that for MySQL, time to remove it and install MariaDB. Removal simply done by 'apt-get autoremove mysql-server mysql-client'
MariaDB installed simply via 'apt-get install mariadb-server mariadb-client'

# mysql --version
mysql Ver 14.16 Distrib 5.2.7-MariaDB, for debian-linux-gnu (x86_64) using readline 6.1

After putting in the above my.cnf:

root@vps:/etc/mysql# /etc/init.d/mysql restart
Stopping MariaDB database server: mysqld.
Starting MariaDB database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..
root@vps:/etc/mysql# ps uUmysql
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
mysql 9534 1.4 0.7 247724 30404 pts/0 Sl 06:33 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/m
root@vps:/etc/mysql# pmap -x 9534
[ output here: http://pastebin.com/u88dGzmM ]
root@vps:/etc/mysql# free
             total used free shared buffers cached
Mem: 4194304 226020 3968284 0 0 0
-/+ buffers/cache: 226020 3968284
Swap: 0 0 0
root@vps:/etc/mysql# /etc/init.d/mysql stop
Stopping MariaDB database server: mysqld.
root@vps:/etc/mysql# free
             total used free shared buffers cached
Mem: 4194304 24408 4169896 0 0 0
-/+ buffers/cache: 24408 4169896
Swap: 0 0 0

Difference in 'free' shows about 197MB usage.
Also:

root@vps:/etc/mysql# mysql -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 30
Server version: 5.2.7-MariaDB-mariadb101~squeeze (MariaDB - http://mariadb.com/)

[...]

MariaDB [(none)]> show plugins;
+--------------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+--------------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | DISABLED | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | DISABLED | STORAGE ENGINE | NULL | GPL |
| CSV | DISABLED | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| Maria | ACTIVE | DAEMON | NULL | GPL |
| Aria | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | DISABLED | STORAGE ENGINE | NULL | GPL |
| PBXT | DISABLED | STORAGE ENGINE | NULL | GPL |
| PBXT_STATISTICS | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| InnoDB | DISABLED | STORAGE ENGINE | NULL | GPL |
| INNODB_RSEG | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_PAGES | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_PAGES_INDEX | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_PAGES_BLOB | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TRX | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLE_STATS | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| INNODB_INDEX_STATS | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| XTRADB_ADMIN_COMMAND | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_STATS | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| XTRADB_ENHANCEMENTS | DISABLED | INFORMATION SCHEMA | NULL | GPL |
+--------------------------------+----------+--------------------+---------+---------+
34 rows in set (0.00 sec)

Again, I'm not sure whether my method of determining memory usage is accurate, so I've posted the other metrics here. I'm also not sure whether disabling all the MariaDB plugins is what I'm supposed to do, but it just seems that MariaDB bundles and activates a lot more by default (so I tried disabling stuff I don't need for some RAM savings, if that's what it does). It doesn't seem that 'skip-aria' works in the configuration file, so I can't disable the Aria storage engine.

I hope the information here is useful. Please do feel free to ask for any more info or point out the flaws in my methodology etc.

Thanks again :)

Best Sergei Golubchik (sergii) said : #3

You were right up to the very end.

More active plugins means that you need more memory, and disabling unused plugins is one of the ways to get the memory usage down.

You cannot disable Aria engine because it is used internally, for temporary tables (used by optimizer for GROUP BY, DISTINCT, etc). If you'd like to disable Aria, you need to build MariaDB yourself (and use --without-aria-tmp-tables option to ./configure). But then many such queries may get noticeably slower.

Now, this is one last step that you haven't done. After figuring out that this memory is likely consumed by the Aria engine, you could've run "show variables like 'aria%'" to see all configuration variables of the Aria engine.

There is aria_pagecache_buffer_size variable, which is 128M by default.

Philip Stoev (pstoev-askmonty) said : #4

You may also wish to disable PBXT, it is known to use 64-128MB of memory just for being loaded, even if no PBXT tables are created.

zingaburga (zingaburga) said : #5

Thanks again for the responses!

D'oh! I wonder why I never thought about tweaking the aria variables.
Added this to the [mariadb] section of my.cnf:

aria_pagecache_buffer_size = 32m
aria_sort_buffer_size = 32m

And MariaDB is consuming ~100MB less RAM.

Thanks for the explanation Sergei - that makes a lot of sense. I don't really want to disable Aria, was just commenting it for testing purposes :) So in the end, it was Aria consuming the memory, and tweaking the variables pretty much fixes this.

@Philip: I actually did disable PBXT (I dunno if the above plugins list came out correctly, but it says disabled in there) - thanks for the hint though!

PS: do you have any tips for tweaking aria_pagecache_buffer_size? I do want to try out Aria, and I've typically relied on mysqltuner to see if I've set a variable too low.
Thanks again! :D

zingaburga (zingaburga) said : #6

Thanks Sergei, that solved my question.

Michael Widenius (monty) said : #7

About aria_pagecache_buffer_size:

In most cases this is similar to key_buffer_size (the buffer MyISAM uses for keys).

The rules of thumbs are:

- If you are not using MyISAM and don't plan to use Aria
  - Set key_buffer_size to something very low (16K ?) as it's not used.
  - Set aria_pagecache_buffer_size to what you think you need for handling internal tmp tables that didn't fit in memory.
    - Normally this is what you before had set for key_buffer_size (at least 1M).

- If you are using MyISAM and not planning to use Aria:
   Set aria_pagecache_buffer_size to what you think you need for handling internal tmp tables that didn't fit in memory.

- If you are planning to use Aria, you should set aria_pagecache_buffer_size to something that fits a big part of your normal data + overflow temporary tables.

zingaburga (zingaburga) said : #8

Great answer Michael - really useful! Thanks for that!