Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

MySQL 8.0 Certification Exam > InnoDB Variables > Flashcards

InnoDB Variables Flashcards

1
Q

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

A

innodb_buffer_pool_size

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Best practice for setting innodb_buffer_pool_size on a server running all tables with innodb storage engine

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Best practice for setting innodb_buffer_pool_size on a shared server

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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

A

innodb_log_file_size

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Two reasons why the undo and redo functions are important database system

A

Rolling back a transaction (that’s the Undo)

Replaying committed transactions in case of a database crash (and that’s Redo)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What does it mean to have a small redo log size?

A

Small Log files = small buffer

small log files makes writes slower and crash recovery faster

small buffer and flushing needs to happen often

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What does it mean to have a large redo log size?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Best practice for setting innodb_log_file_size

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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.

A

innodb_flush_log_at_trx_commit

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

innodb_flush_log_at_trx_commit option settings

A

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)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

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)

A

sync_binlog

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

sync_binlog variable options

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What variable is this?

global, not dynamic

default: fsync or 0 (Unix), unbuffered (Windows)

chance to avoid double buffering

A

innodb_flush_method

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

True or False:

Setting innodb_flush_method to 0_DIRECT increases performance?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Options for innodb_flush_method: O_DIRECT or 4

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Options for innodb_flush_method: O_DSYNC or 1

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Options for innodb_flush_method: littlesync or 2

A

littlesync or 2: This option is used for internal performance testing and is currently unsupported. Use at your own risk.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Options for innodb_flush_method: nosync or 3

A

nosync or 3: This option is used for internal performance testing and is currently unsupported. Use at your own risk.

19
Q

Options for innodb_flush_method: O_DIRECT_NO_FSYNC

A

O_DIRECT_NO_FSYNC: InnoDB uses O_DIRECT during flushing I/O, but skips the fsync() system call after each write operation.

20
Q

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

A

innodb_buffer_pool_instances

21
Q

Best practices for setting innodb_buffer_pool_instances

A

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

22
Q

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

A

innodb_thread_concurrency

23
Q

Best practice for setting innodb_thread_concurrency

A

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.

24
Q

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

A

innodb_concurrency_tickets

25
Q

Setting innodb_concurrency_tickets considerations

A

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.

26
Q

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.

A

innodb_thread_sleep_delay:

27
Q

Setting innnodb_thread_concurrency

A

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.

28
Q

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

A

innodb_io_capacity

29
Q

Best practice for setting innodb_io_capacity

A

Best solution – measure random write throughput of your storage and set it to 50% to 75%.

30
Q

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.

A

innodb_io_capacity_max

31
Q

Setting innodb_io_capacity_max

A

Best solution – measure random write throughput of your storage and set innodb_io_capacity_max to the maximum you could achieve

32
Q

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

A

innodb_stats_on_metadata

When disabled, InnoDB does not update statistics during these operations.

33
Q

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.

A

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.)

34
Q

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

A

innodb_adaptve_hash_index_parts

35
Q

What variable is this?

global, dynamic

Specifies how to generate and verify the checksum stored in the disk blocks of InnoDB tablespaces.

Default crc32

A

innodb_checksum_algorithm

36
Q

Options for innodb_checksum_algorithm: crc32

A

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

37
Q

Describe innodb_checksum_algorithm: strict_crc32

A

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.
38
Q

Describe innodb_checksum_algorithm: innodb

A

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.
39
Q

Describe innodb_checksum_algorithm: strict_innodb

A

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.
40
Q

Options for innodb_checksum_algorithm: none

A

when writing generates constant checksum number

permitted when reading: of the checksums generated by none, innodb, or crc32.

41
Q

Describe innodb_checksum_algorithm: strict_none

A

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.

42
Q

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)

A

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.
43
Q

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)

A

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

MySQL 8.0 Certification Exam (9 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions