# Configuring MariaDB for Optimal Performance This article will help you configure MariaDB for optimal performance. By default, MariaDB is configured to work on a desktop system and therefore use relatively few resources. To optimize installation for a dedicated server, you have to do a few minutes of work. For this article we assume that you are going to run MariaDB on a dedicated server. Feel free to update this article if you have more ideas. ## [my.cnf](https://mariadb.com/docs/server/server-management/install-and-upgrade-mariadb/configuring-mariadb/configuring-mariadb-with-option-files) Files MariaDB is normally configured by editing the [my.cnf](https://mariadb.com/docs/server/server-management/install-and-upgrade-mariadb/configuring-mariadb/configuring-mariadb-with-option-files) file. In the next section you have a list of variables that you may want to configure for dedicated MariaDB servers. ## [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) Storage Engine InnoDB is normally the default storage engine with MariaDB. * You should set [innodb\_buffer\_pool\_size](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-system-variables#innodb_buffer_pool_size) to about 80% of your memory. The goal is to ensure that 80 % of your working set is in memory. The other most important InnoDB variables are: * [innodb\_log\_file\_size](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-system-variables#innodb_log_file_size) * [innodb\_flush\_method](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-system-variables#innodb_flush_method) * [innodb\_thread\_sleep\_delay](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-system-variables#innodb_thread_sleep_delay) Some other important InnoDB variables: * [innodb\_max\_dirty\_pages\_pct\_lwm](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-system-variables#innodb_max_dirty_pages_pct_lwm) * [innodb\_read\_ahead\_threshold](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-system-variables#innodb_read_ahead_threshold) * [innodb\_buffer\_pool\_instances](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-system-variables#innodb_buffer_pool_instances). Deprecated and ignored from [MariaDB 10.5.1](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.5/10.5.1). * [innodb\_adaptive\_max\_sleep\_delay](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-system-variables#innodb_adaptive_max_sleep_delay). Deprecated and ignored from [MariaDB 10.5.5](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.5/10.5.5). * [innodb\_thread\_concurrency](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-system-variables#innodb_thread_concurrency). Deprecated and ignored from [MariaDB 10.5.5](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.5/10.5.5). ## [Aria](https://mariadb.com/docs/server/server-usage/storage-engines/aria/aria-storage-engine) Storage Engine * MariaDB uses by default the Aria storage engine for internal temporary files. If you have many temporary files, you should set [aria\_pagecache\_buffer\_size](https://mariadb.com/docs/server/server-usage/storage-engines/aria/aria-system-variables) to a reasonably large value so that temporary overflow data is not flushed to disk. The default is 128M. You can check if Aria is configured properly by executing: ```sql MariaDB [test]> show global status like "Aria%"; ``` ``` +-----------------------------------+-------+ | Variable_name | Value | +-----------------------------------+-------+ | Aria_pagecache_blocks_not_flushed | 0 | | Aria_pagecache_blocks_unused | 964 | | Aria_pagecache_blocks_used | 232 | | Aria_pagecache_read_requests | 9598 | | Aria_pagecache_reads | 0 | | Aria_pagecache_write_requests | 222 | | Aria_pagecache_writes | 0 | | Aria_transaction_log_syncs | 0 | +-----------------------------------+-------+ ``` If `Aria_pagecache_reads` is much smaller than `Aria_pagecache_read_request` and`Aria_pagecache_writes` is much smaller than Aria\_pagecache\_write\_request#, then your setup is good. If the [aria\_pagecache\_buffer\_size](https://mariadb.com/docs/server/server-usage/storage-engines/aria/aria-system-variables) is big enough, the two variables should be 0, like above. ## [MyISAM](https://mariadb.com/docs/server/server-usage/storage-engines/myisam-storage-engine) * If you don't use MyISAM tables explicitly (true for most [MariaDB 10.4](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.4/what-is-mariadb-104)+ users), you can set [key\_buffer\_size](https://mariadb.com/docs/server/server-usage/storage-engines/myisam-storage-engine/myisam-system-variables#key_buffer_size) to a very low value, like 64K. ## Using in memory temporary tables Using memory tables for internal temporary results can speed up execution.\ However, if the memory table gets full, then the memory table will be moved to\ disk, which can hurt performance. You can check how the internal memory tables are performing by executing: ```sql MariaDB [test]> show global status like "Created%tables%"; ``` ``` +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 1 | | Created_tmp_tables | 2 | +-------------------------+-------+ ``` `Created_tmp_tables` is the total number of internal temporary tables created as part of executing queries like SELECT.`Created_tmp_disk_tables` shows how many of these did hit the storage. You can increase the storage for internal temporary tables by setting [max\_heap\_table\_size](https://mariadb.com/docs/server/variables-and-modes/server-system-variables#max_heap_table_size) and [tmp\_memory\_table\_size](https://mariadb.com/docs/server/variables-and-modes/server-system-variables#tmp_memory_table_size) high enough. These values are per connection. ## Lots of Connections ### A Lot of Fast Connections + Small Set of Queries + Disconnects * If you are doing a lot of fast connections / disconnects, you should increase [back\_log](https://mariadb.com/docs/server/variables-and-modes/server-system-variables#back_log) and if you are running [MariaDB 10.1](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.1/changes-improvements-in-mariadb-10-1) or below [thread\_cache\_size](https://mariadb.com/docs/server/variables-and-modes/server-system-variables#thread_cache_size). * If you have a lot (> 128) of simultaneous running fast queries, you should consider setting [thread\_handling](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/buffers-caches-and-threads/thread-pool/thread-pool-system-status-variables) to `pool_of_threads`. ### Connecting From a Lot of Different Machines * If you are connecting from a lot of different machines you should increase [host\_cache\_size](https://mariadb.com/docs/server/variables-and-modes/server-system-variables#host_cache_size) to the max number of machines (default 128) to cache the resolving of hostnames. If you don't connect from a lot of machines, you can set this to a very low value! ## See Also * [MariaDB Memory Allocation](https://mariadb.com/docs/server/ha-and-performance/mariadb-memory-allocation) * [Full List of MariaDB Options, System and Status Variables](https://mariadb.com/docs/server/server-management/variables-and-modes/full-list-of-mariadb-options-system-and-status-variables) * [Server system variables](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables) * [mysqld options](https://mariadb.com/docs/server/server-management/starting-and-stopping-mariadb/mariadbd-options) * [Performance schema](https://mariadb.com/docs/server/reference/system-tables/performance-schema) helps you understand what is taking time and resources. * [Slow query log](https://mariadb.com/docs/server/server-management/server-monitoring-logs/slow-query-log) is used to find queries that are running slow. * [OPTIMIZE TABLE](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/optimizing-tables/optimize-table) helps you defragment tables. ## External Links * [what-to-tune-in-mysql-56-after-installation.html](https://www.tocker.ca/2013/09/17/what-to-tune-in-mysql-56-after-installation.html) * [optimizing-mysql-configuration-percona-mysql-university-montevideo](https://www.percona.com/resources/technical-presentations/optimizing-mysql-configuration-percona-mysql-university-montevideo) *This page is licensed: CC BY-SA / Gnu FDL* {% @marketo/form formId="4316" %}