InnoDB Variables Flashcards
What variable is this?
Scope: global
Dynamic
most important variable for memory optimization:
a cache for read data, indexes, and modified (dirty, data that hasn’t been flushed) data
want it big enough to avoid swapping (causes queries to lock), but not too big that the OS doesn’t have room for caching and memory structures
innodb_buffer_pool_size
Best practice for setting innodb_buffer_pool_size on a server running all tables with innodb storage engine
set the innodb buffer pool size to 75-80% of total MySQL memory
you want to leave room for OS cache (binary logs, relay logs, innodb transaction logs) and OS memory structures
Best practice for setting innodb_buffer_pool_size on a shared server
check the total size of tables using innodb and if it fits in 50% of total OS RAM
–> if it fits, you can set innodb_buffer_pool_size to the total size
→ if not, consider increasing it
What variable is this?
This sets the size of the InnoDB’s redo log files which, in MySQL world, are often called simply transaction logs.
making room for MySQL redo log
Default: 50331648 (48M)
global
not dynamic
innodb_log_file_size
Two reasons why the undo and redo functions are important database system
Rolling back a transaction (that’s the Undo)
Replaying committed transactions in case of a database crash (and that’s Redo)
What does it mean to have a small redo log size?
Small Log files = small buffer
small log files makes writes slower and crash recovery faster
small buffer and flushing needs to happen often
What does it mean to have a large redo log size?
Large log files = big buffer
large log files make writes faster and crash recovery slower
a lot more work needs to be done to restore the database to the consistent state (redo operation)
big buffer, flushing can be more streamlined
Best practice for setting innodb_log_file_size
If innodb_dedicated_server is enabled, the innodb_log_file_size value is automatically configured if it is not explicitly defined.
If not, check that the total size of your redo logs fit in 1-2 hours worth of writes during your busy period. If it doesn’t it needs to be adjusted to do so.
What variable is this?
Controls the balance between strict ACID compliance for commit operations and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value but then you can lose transactions in a crash.
global, dynamic
Default: 1 (required for ACID compliance)- durability over performance
Instructs InnoDB to flush AND sync after EVERY transaction commit.
innodb_flush_log_at_trx_commit
innodb_flush_log_at_trx_commit option settings
Default: 1 (required for ACID compliance)- durability over performance
If you have it set to 0 or 2, sync is performed once a second instead. Not ideal for financial institutions like banks.
0 (flush, no sync) – the faster than 1, least reliable.
Means FLUSH to disk, but DO NOT SYNC (no actual IO is performed on commit)
- performance difference b/t 0 and 2 so 0 is a better choice than 2
2 (neither flush or syncing) – better performance than 1, worse reliability than 1
- means DON’T FLUSH and DON’T SYNC (again no actual IO is performed on commit)
What variable is this?
Controls how often the MySQL server synchronizes the binary log to disk.
global, dynamic
Default: 1 (should be 1 if innodb_flush_log_at_trx_commit = 1)
sync_binlog
sync_binlog variable options
Default: 1 (should be 1 if innodb_flush_log_at_trx_commit = 1)
- Enables synchronization of the binary log to disk before transactions are committed.
- Safest setting but can have a negative impact on performance due to the increased number of disk writes
0 (no slaves/backups or don’t mind losing events in the binary logs)- better performance
- Disables synchronization of the binary log to disk by the MySQL server.
N (N is another value than 0 or 1) - better performance than 1
- The binary log is synchronized to disk after N binary log commit groups have been collected.
What variable is this?
global, not dynamic
default: fsync or 0 (Unix), unbuffered (Windows)
chance to avoid double buffering
innodb_flush_method
True or False:
Setting innodb_flush_method to 0_DIRECT increases performance?
True
For increase performance, set to O_DIRECT to enable direct IO
InnoDB uses O_DIRECT or 4 (or directio() on Solaris) to open the data files, and uses fsync() to flush both the data and log files. This option is available on some GNU/Linux versions, FreeBSD, and Solaris.
Without direct IO, double-buffering happens because all database changes are first written to OS cache and then they are synced to disk – end up with the same data in InnoDB buffer pool AND in OS cache.
means in a write-intensive environment you could be losing up to almost 50% of memory, especially if your buffer pool is capped at 50% of total memory
server could end up swapping due to high pressure on the OS cache.
Options for innodb_flush_method: O_DIRECT or 4
O_DIRECT or 4 –> opens the data files, and uses fsync() to flush both the data and log files. This option is available on some GNU/Linux versions, FreeBSD, and Solaris.
Options for innodb_flush_method: O_DSYNC or 1
O_DSYNC or 1: InnoDB uses O_SYNC to open and flush the log files, and fsync() to flush the data files. InnoDB does not use O_DSYNC directly because there have been problems with it on many varieties of Unix.
Options for innodb_flush_method: littlesync or 2
littlesync or 2: This option is used for internal performance testing and is currently unsupported. Use at your own risk.
Options for innodb_flush_method: nosync or 3
nosync or 3: This option is used for internal performance testing and is currently unsupported. Use at your own risk.
Options for innodb_flush_method: O_DIRECT_NO_FSYNC
O_DIRECT_NO_FSYNC: InnoDB uses O_DIRECT during flushing I/O, but skips the fsync() system call after each write operation.
What variable is this?
The number of regions that the InnoDB buffer pool is divided into.
global, not dynamic
For systems with buffer pools in the multi-gigabyte range
dividing the buffer pool into separate instances can improve concurrency
reducing locking contention as different threads read and write to cached pages
innodb_buffer_pool_instances
Best practices for setting innodb_buffer_pool_instances
default: 8 (or 1 if innodb_buffer_pool_size < 1GB)
Otherwise, if innodb_buffer_pool_size > 1.3GB, the default for innodb_buffer_pool_instances is innodb_buffer_pool_size/128MB
What variable is this?
Defines the maximum number of threads permitted inside of InnoDB. Threads waiting for locks are not counted in the number of concurrently executing threads.
global, dynamic
innodb_thread_concurrency
Best practice for setting innodb_thread_concurrency
Setting innodb_thread_concurrency
keep default ( 0 ), if you have a light or moderate workload (ie 32 CPU cores and 4 requests- request run in parallel)
only increase it gradually (by 10) if you see that the server keeps hitting that limit while hardware is under-utilized.
What variable is this?
controls how many operations are executed when thread concurrency is met
Determines the number of threads that can enter InnoDB concurrently
global, dynamic
default: 5000
innodb_concurrency_tickets
Setting innodb_concurrency_tickets considerations
With a nonzero innodb_thread_concurrency value, you may need to adjust the innodb_concurrency_tickets value up or down to find the optimal balance,
With a small innodb_concurrency_tickets value
- -> small transactions that only need to process a few rows compete fairly with larger transactions that process many rows.
- disadvantage: large transactions must loop through the queue many times before they can complete, which extends the amount of time required to complete their task.
With a large innodb_concurrency_tickets value –> large transactions spend less time waiting for a position at the end of the queue (controlled by innodb_thread_concurrency) and more time retrieving rows.
Large transactions also require fewer trips through the queue to complete their task.
disadvantage: large innodb_concurrency_tickets value is that too many large transactions running at the same time can starve smaller transactions by making them wait a longer time before executing.
What variable is this?
How long InnoDB threads sleep before joining the InnoDB queue, in microseconds.
global, dynamic
Default: 10000
InnoDB automatically adjusts it up or down depending on the current thread-scheduling activity.
Helps the thread scheduling mechanism to work smoothly during times when the system is lightly loaded and when it is operating near full capacity.
innodb_thread_sleep_delay:
Setting innnodb_thread_concurrency
You can set the configuration option innodb_adaptive_max_sleep_delay to the highest value you would allow for innodb_thread_sleep_delay, and InnoDB automatically adjusts innodb_thread_sleep_delay up or down depending on the current thread-scheduling activity.
What variable is this?
global, dynamic
the number of I/O operations per second (IOPS) available to InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the change buffer.
For writes (not reads), MySQL has background flushing cycles and during each cycle it checks how much data needs to be flushed.
Default: 200
innodb_io_capacity
Best practice for setting innodb_io_capacity
Best solution – measure random write throughput of your storage and set it to 50% to 75%.
What variable is this?
cap InnoDB IO usage
global, dynamic
controls how many write IOPS will MySQL do flushing the dirty data when it’s under stress.
innodb_io_capacity_max
Setting innodb_io_capacity_max
Best solution – measure random write throughput of your storage and set innodb_io_capacity_max to the maximum you could achieve
What variable is this?
global, dynamic
option only applies when optimizer statistics are configured to be non-persistent. Optimizer statistics are not persisted to disk when innodb_stats_persistent is disabled or when individual tables are created or altered with STATS_PERSISTENT=0.
default OFF
innodb_stats_on_metadata
When disabled, InnoDB does not update statistics during these operations.
True or False:
When innodb_stats_on_metadata is enabled, it can improve access speed for schemas that have a large number of tables or indexes and also improve the stability of execution plans for queries that involve InnoDB tables.
False
default OFF
- commands like show table status and queries against INFORMATION_SCHEMA will be instantaneous instead of taking seconds to run and causing additional disk IO.
- When disabled, InnoDB does not update statistics during these operations.
- can improve access speed for schemas that have a large number of tables or indexes
- can also improve the stability of execution plans for queries that involve InnoDB tables.
When innodb_stats_on_metadata is enabled,
- InnoDB updates non-persistent statistics when metadata statements such as SHOW TABLE STATUS or when accessing the INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS tables. (These updates are similar to what happens for ANALYZE TABLE.)
What variable is this?
global, not dynamic
Partitions the adaptive hash index search system. Each index is bound to a specific partition, with each partition protected by a separate latch.
Default: 8
innodb_adaptve_hash_index_parts
What variable is this?
global, dynamic
Specifies how to generate and verify the checksum stored in the disk blocks of InnoDB tablespaces.
Default crc32
innodb_checksum_algorithm
Options for innodb_checksum_algorithm: crc32
improve the speed of calculating the InnoDB checksums significantly.
- Checksums are calculated every single time a page (or log entry) is read or written
- value crc32 uses an algorithm that is faster than ‘innodb’ to compute the checksum for every modified block, and to check the checksums for each disk read.
- It scans blocks 32 bits at a time
Versions of MySQL Enterprise Backup up to 3.8.0 do not support backing up tablespaces that use CRC32 checksums
Describe innodb_checksum_algorithm: strict_crc32
strict_crc32 (somewhat faster)
- they do not need to compute all checksum values during disk reads.
- reports an error if it encounters a valid but non-matching checksum value in a tablespace
- recommended only use strict settings in a new instance, to set up tablespaces for the first time.
Describe innodb_checksum_algorithm: innodb
innodb
- backward-compatible with earlier versions of MySQL
- A checksum calculated in software, using the original algorithm from InnoDB.
- permitted when reading: any of the checksums generated by none, innodb, or crc32.
Describe innodb_checksum_algorithm: strict_innodb
strict_innodb
- checksum calculated in software, using the original algorithm from InnoDB.
- permitted when reading: any of the checksums generated by none, innodb, or crc32. InnoDB prints an error message if a valid but non-matching checksum is encountered.
Options for innodb_checksum_algorithm: none
when writing generates constant checksum number
permitted when reading: of the checksums generated by none, innodb, or crc32.
Describe innodb_checksum_algorithm: strict_none
when writing generates constant checksum number
permitted when reading: any of the checksums generated by none, innodb, or crc32. InnoDB prints an error message if a valid but non-matching checksum is encountered.
What variable is this?
represents the number of open tables cache instances
To improve scalability by reducing contention among sessions, the open tables cache can be partitioned into several smaller cache instance
global, not dynamic
default: 16 (min 1; max 64)
table_open_cache_instances
Advantages:
- permitting higher performance for operations that use the cache when there are many sessions accessing tables
- A session needs to lock only one instance to access it for DML statements.
- DDL statements still require a lock on the entire cache, but such statements are much less frequent than DML statements.
- A value of 8 or 16 is recommended on systems that routinely use 16 or more cores.
What variables are these?
look here if server is extremely write-heavy and disk IO is the bottle-neck
global, not dynamic
only used for the background activity, such as checkpointing (flushing dirty pages to disk), change buffer merge operations and sometimes, read-ahead activity.
default 4 (max 64)
innodb_read_io_threads - The number of I/O threads for read operations in InnoDB
innodb_write_io_threads- The number of I/O threads for write operations in InnoDB.
don’t need to think about this variable much unless the server is write heavy and disk IO is the bottle
because MySQL is already using Asynchronous IO
