MySQL config options / performance
Config Options
https://mariadb.com/resources/blog/10-database-tuning-tips-for-peak-workloads/
Check an option:
- SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
[mysqld]
innodb_buffer_pool_size
https://mariadb.com/kb/en/innodb-system-variables/#innodb_buffer_pool_size
Default: 128MB, adjusted dynamically starting with mariadb 10.2.2?
- Up to 80% of available RAM with dedicated MySQL Server
- https://dba.stackexchange.com/a/27341
- Calculate with 60% margin:
- SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB') A;
- => e.g. 4 GB
innodb_log_file_size
Default: 48MB < mariadb 10.4
Max with 10.4: 512GB
Recommended: 0.25 - 0.5 the size of innodb_buffer_pool_size e.g. 500M
innodb_log_buffer_size
https://mariadb.com/kb/en/innodb-system-variables/#innodb_log_buffer_size
Recommended: 64MB
innodb_flush_log_at_trx_commit
https://mariadb.com/kb/en/innodb-system-variables/#innodb_flush_log_at_trx_commit
1 The default, the log buffer is written to the InnoDB redo log file and a flush to disk performed after each transaction. This is required for full ACID compliance.
0 Nothing is done on commit; rather the log buffer is written and flushed to the InnoDB redo log once a second. This gives better performance, but a server crash can erase the last second of transactions.
2 The log buffer is written to the InnoDB redo log after each commit, but flushing takes place once a second. Performance is slightly better, but a OS or power outage can cause the last second's transactions to be lost.
Recommended with battery: 2
With non-critical data like monitoring: 0
innodb_flush_log_at_timeout
Reduce flush/write frequency
Default: 1
Lower performance hit for non-critical data: 5
innodb_io_capacity
https://mariadb.com/kb/en/innodb-system-variables/#innodb_io_capacity
Default: 200 or 1000?
Recommended: 4000? Low value for old HDDs like 100?
To determine the appropriate setting for you, we recommend benchmarking your storage. By default innodb_io_capacity is set to 1000, but it should be set to approximately the maximum number of IOPS the underlying storage can handle.
https://www.percona.com/blog/2019/12/18/give-love-to-your-ssds-reduce-innodb_io_capacity_max/
innodb_doublewrite
https://mariadb.com/kb/en/innodb-system-variables/#innodb_doublewrite
If set to 1, the default, to improve fault tolerance InnoDB first stores data to a doublewrite buffer before writing it to data file. Disabling will provide a marginal peformance improvement.
innodb_doublewrite = 0
OLD
ullright uses "modern" InnoDB tables with transactions, foreign keys etc.
- vi /etc/fstab
-
add noatime,data=writeback (no data-writeback on virtual machines!)
-
Examing slow queries
http://www.techfounder.net/2008/10/15/optimizing-or-union-operations-in-mysql/
Explain
http://dev.mysql.com/doc/refman/5.1/de/explain.html
- EXPLAIN SELECT ...
Join types from good to bad:
- eq_ref
- ref
key: the used key(s)
rows: the number of rows scanned. the lower - the better!
Show Index
- SHOW INDEX FROM tablename;
Links
sounds good: http://devblog.policystat.com/run-mysql-from-a-ram-disk-in-ubuntu
speed up table creation: http://stackoverflow.com/questions/2025011/increase-speed-for-mysql-table-creation-in-django
http://www.koopman.me/2008/11/using-tmpfs-for-mysql-tmpdir-setting/
http://icephoenix.us/stuff/how-to-move-mysql-storage-to-ramfs-or-tmpfs-partition/
tune mysql http://www.stereoplex.com/blog/speeding-up-django-unit-test-runs-with-mysql
my.conf
### CUSTOM BY KLEMENS
skip-sync-frm = OFF
#innodb_flush_method = fdatasync
# The number of open tables for all threads. Increasing this value
# increases the number of file descriptors that mysqld requires.
# Therefore you have to make sure to set the amount of open files
# allowed to at least 4096 in the variable "open-files-limit" in
# section [mysqld_safe]
table_open_cache = 2048
# The size of the cache to hold the SQL statements for the binary log
# during a transaction. If you often use big, multi-statement
# transactions you can increase this value to get more performance. All
# statements from transactions are buffered in the binary log cache and
# are being written to the binary log at once after the COMMIT. If the
# transaction is larger than this value, temporary file on disk is used
# instead. This buffer is allocated per connection on first update
# statement in transaction
binlog_cache_size = 1M
# Maximum allowed size for a single HEAP (in memory) table. This option
# is a protection against the accidential creation of a very large HEAP
# table which could otherwise use up all memory resources.
max_heap_table_size = 64M
# Size of the buffer used for doing full table scans.
# Allocated per thread, if a full scan is needed.
read_buffer_size = 2M
# When reading rows in sorted order after a sort, the rows are read
# through this buffer to avoid disk seeks. You can improve ORDER BY
# performance a lot, if set this to a high value.
# Allocated per thread, when needed.
read_rnd_buffer_size = 16M
# Sort buffer is used to perform sorts for some ORDER BY and GROUP BY
# queries. If sorted data does not fit into the sort buffer, a disk
# based merge sort is used instead - See the "Sort_merge_passes"
# status variable. Allocated per thread if sort is needed.
sort_buffer_size = 8M
# This buffer is used for the optimization of full JOINs (JOINs without
# indexes). Such JOINs are very bad for performance in most cases
# anyway, but setting this variable to a large value reduces the
# performance impact. See the "Select_full_join" status variable for a
# count of full JOINs. Allocated per thread if full join is found
join_buffer_size = 8M
# How many threads we should keep in a cache for reuse. When a client
# disconnects, the client's threads are put in the cache if there aren't
# more than thread_cache_size threads from before. This greatly reduces
# the amount of thread creations needed if you have a lot of new
# connections. (Normally this doesn't give a notable performance
# improvement if you have a good thread implementation.)
thread_cache_size = 8
# This permits the application to give the threads system a hint for the
# desired number of threads that should be run at the same time. This
# value only makes sense on systems that support the thread_concurrency()
# function call (Sun Solaris, for example).
# You should try [number of CPUs]*(2..4) for thread_concurrency
thread_concurrency = 8
# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again. Having the query
# cache enabled may result in significant speed improvements, if your
# have a lot of identical queries and rarely changing tables. See the
# "Qcache_lowmem_prunes" status variable to check if the current value
# is high enough for your load.
# Note: In case your tables change very often or if your queries are
# textually different every time, the query cache may result in a
# slowdown instead of a performance improvement.
query_cache_size = 64M
# Only cache result sets that are smaller than this limit. This is to
# protect the query cache of a very large result set overwriting all
# other query results.
query_cache_limit = 2M
# Minimum word length to be indexed by the full text search index.
# You might wish to decrease it if you need to search for shorter words.
# Note that you need to rebuild your FULLTEXT index, after you have
# modified this value.
ft_min_word_len = 4
# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.
tmp_table_size = 64M
# Additional memory pool that is used by InnoDB to store metadata
# information. If InnoDB requires more memory for this purpose it will
# start to allocate it from the OS. As this is fast enough on most
# recent operating systems, you normally do not need to change this
# value. SHOW INNODB STATUS will display the current amount used.
innodb_additional_mem_pool_size = 16M
# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system. Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
innodb_buffer_pool_size = 750M
# Number of threads allowed inside the InnoDB kernel. The optimal value
# depends highly on the application, hardware as well as the OS
# scheduler properties. A too high value may lead to thread thrashing.
innodb_thread_concurrency = 16
# The size of the buffer InnoDB uses for buffering log data. As soon as
# it is full, InnoDB will have to flush it to disk. As it is flushed
# once per second anyway, it does not make sense to have it very large
# (even with long transactions).
innodb_log_buffer_size = 8M