2018-02-28, 21:01
When using MariaDB, the default values work very well. However, there is a single value that will speed up your queries by a factor between 5 and 10! I'm talking about optimizer_search_depth which defaults to 62. A value of 1 is much better, and you can also try values 0 or 5. The other recommendation is to run your database off a SSD. CPU power is not that important. Anyway, here are my optimizations for my.ini in a concise form for systems with 4GB RAM:
For systems with 2GB RAM, use
innodb_buffer_pool_size=1G
innodb_buffer_pool_instances=1
aria_pagecache_buffer_size=128M; default value
For systems with 8GB RAM, use
innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=4
aria_pagecache_buffer_size=1G
Starting from MariaDB 10.2.2, use this instead (example for 8GB RAM):
# innodb_buffer_pool_size=4G
innodb_buffer_pool_chunk_size=1G
innodb_buffer_pool_instances=4
aria_pagecache_buffer_size=1G
UPDATE 2018-11-30
As of MariaDB 10.3 the following options will have no effect and even prevent MariaDB from starting on Windows:
UPDATE 2019-01-20
Changing the recommended value for optimizer_search_depth to "0". That means, the actual value is automatically determined by MariaDB. This will probably lead to a value around 7 for Kodi, which is safer than 1. 1 is the fastest option but may lead to premature ending of queries because of lack of search depth, thus having to run the query multiple times.
UPDATE 2019-03-11
UPDATE 2019-03-14
If possible, you should run MariaDB 10.2.2 or higher as more and more values are being automatically optimized when they are not specified! Also, older versions than 10 will lose Kodi core support at some point.
Quote:innodb_buffer_pool_size=2G;MariaDB recommends using 80% of your available memory but the default 1G might be enough. Always set to multiples of 1G.
innodb_buffer_pool_instances=2;When innodb_buffer_pool_size is > 1G, MariaDB will split the pool in instances. They should be even dividers of 1G.
innodb_file_format=Barracuda;This is an optimized storage engine not available in MySQL
innodb_log_block_size=4096;Default is 512, when using an SSD or 4k drives, block size should be 4096
innodb_adaptive_hash_index=OFF;For Kodi no benefit is gained from additional indexes. This should only be used with large databases.
character_set_server=utf8;I really hope you all used this in MySQL already.This avoids potential pitfalls with text in exotic codepages.
key_buffer_size=16k;When not using MyISAM as storage engine, this buffer can be reduced to a minimum
skip_name_resolve=1;Avoids name resolving in the local network, thus reducing overhead. That also means that all connections are done by IP only.
optimizer_search_depth=0;Here we are, this is winner of the year.
aria_pagecache_buffer_size=512M;MariaDB uses Aria, not MyISAM for temporary files. Whatever doesn't fit into memory will be written to disk temporarily. Set this to a reasonable amount to avoid disk flushing.
innodb_buffer_pool_dump_at_shutdown=ON
innodb_buffer_pool_load_at_startup=ON;use from MariaDB 10.0 upwards to preload buffer on startup
innodb_doublewrite=0; small speed-up at the expense of safety. Don't use in production system!
For systems with 2GB RAM, use
innodb_buffer_pool_size=1G
innodb_buffer_pool_instances=1
aria_pagecache_buffer_size=128M; default value
For systems with 8GB RAM, use
innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=4
aria_pagecache_buffer_size=1G
Starting from MariaDB 10.2.2, use this instead (example for 8GB RAM):
# innodb_buffer_pool_size=4G
innodb_buffer_pool_chunk_size=1G
innodb_buffer_pool_instances=4
aria_pagecache_buffer_size=1G
UPDATE 2018-11-30
As of MariaDB 10.3 the following options will have no effect and even prevent MariaDB from starting on Windows:
- innodb_log_block_size
- innodb_file_format; (is always Barracuda)
UPDATE 2019-01-20
Changing the recommended value for optimizer_search_depth to "0". That means, the actual value is automatically determined by MariaDB. This will probably lead to a value around 7 for Kodi, which is safer than 1. 1 is the fastest option but may lead to premature ending of queries because of lack of search depth, thus having to run the query multiple times.
UPDATE 2019-03-11
- Amended some values in the first post
- Added value for Aria pagecache
- Added recommendations for different system RAM
UPDATE 2019-03-14
- Added info for dynamic buffer
- Added info for dumping and pre-loading buffer
- Added info to turn off double-buffer writes
If possible, you should run MariaDB 10.2.2 or higher as more and more values are being automatically optimized when they are not specified! Also, older versions than 10 will lose Kodi core support at some point.
