0

Please help with MariaDB server configuration. I see that buffer pool is only 38% of RAM and I think it has to be changed. I want to migrate all Aria and MyISAM tables to InnoDB engine. Will I have enougth RAM and MariaDB server will work correctly and without crash? If I incrase Buffer pool to 10GB will that be enough?

RAM:

free -h
             total       used       free     shared    buffers     cached
Mem:           15G        15G       607M         4K       138M       7.5G
-/+ buffers/cache:       7.5G       8.2G
Swap:         1.0G       135M       888M

InnoDB configuration:

> SELECT VARIABLE_NAME, SESSION_VALUE, GLOBAL_VALUE FROM   INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE   VARIABLE_NAME IN ('innodb_buffer_pool_size','innodb_additional_mem_pool_size','innodb_log_buffer_size','tmp_table_size','max_connections','sort_buffer_size','read_buffer_size','join_buffer_size','binlog_cache_size');
+-------------------------+---------------+--------------+
| VARIABLE_NAME           | SESSION_VALUE | GLOBAL_VALUE |
+-------------------------+---------------+--------------+
| BINLOG_CACHE_SIZE       | NULL          | 32768        |
| MAX_CONNECTIONS         | NULL          | 300          |
| JOIN_BUFFER_SIZE        | 262144        | 262144       |
| SORT_BUFFER_SIZE        | 2097152       | 2097152      |
| READ_BUFFER_SIZE        | 131072        | 131072       |
| TMP_TABLE_SIZE          | 16777216      | 16777216     |
| INNODB_LOG_BUFFER_SIZE  | NULL          | 16777216     |
| INNODB_BUFFER_POOL_SIZE | NULL          | 6442450944   |
+-------------------------+---------------+--------------+
8 rows in set (0,16 sec)

Data with indexes:

+--------+---------+----------+----------+------------+
| ENGINE | Data GB | Index GB | Total GB | Num Tables |
+--------+---------+----------+----------+------------+
| NULL   |    NULL |     NULL |     NULL |          8 |
| Aria   |    14.3 |      3.9 |     18.3 |          7 |
| InnoDB |    67.1 |     50.6 |    117.7 |        141 |
| MyISAM |    65.2 |     21.0 |     86.3 |         16 |
+--------+---------+----------+----------+------------+
4 rows in set (0,40 sec)
2

1 Answer 1

1

How much of your InnoDB/ Aria / MyISAM data is actively used by MariaDB in queries? The show global status below will show you.

show global status like 'innodb_buffer_pool%', 100% usage of Innodb_buffer_pool_pages_data actually ok, look at innodb_buffer_pool_read_requests vs innodb_buffer_pool_reads - reads should be low (<1% of innodb_buffer_pool_read_requests) after a warmup and running for a while.

Under allocation of innodb buffer pool will just mean more requests go to disk. Overallocation of innodb buffer pool will result in OOM once it fills up.

innodb_buffer_pool_size can be changed at runtime with set global innodb_buffer_pool_size=value (note can't use 10G as value with G as a suffix, but you can do 10 * 1024 * 1024 * 1024). So start small, increment until the read requests drop down to a small amount, and don't forget to put the final value in the configuration file so that a restart will restore to this value on startup.

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.