Mysql high performance 2

42. working principle of mysql configuration [code snippet] 43. side effects caused by variables: [code snippet] 44. the glibc library of the [code snippet] system may also limit the size of memory allocated each time. innodb_buffer_pool

42. working principle of mysql configuration [code snippet] 43. side effects caused by variables: [code snippet] 44. the glibc library of the [code snippet] system may also limit the size of memory allocated each time. innodb_buffer_pool

42. how mysql configuration works

View the path of the configuration file used: which mysqld-verbose-help | grep-A 1 'default options' variable query_cache_size the global variable sort_buffer_size is global by Default, but each thread can also set join_buffer_size to the global value, however, each thread can also set sort_buffer_size = value (partial) separately; set GLOBAL sot_buffer_size = value (GLOBAL) table_cache: Number of table caches key_buffer_size: bytes

43. Side effects caused by variables:

Key_buffer_size: the specified space can be allocated to the key buffer at a time. table_cache_size is allocated only when the table is opened by the next thread. tableNum 44. memory usage

1. determine the upper limit of memory available 2. Determine the memory used for each connection to mysql 3. Determine the memory used by the operating system 4. submit all the remaining memory to the mysql Cache

The glibc library of the system may also limit the size of memory allocated each time. innodb_buffer_pool <= glibc

45. Cache

The operating system cache of Innodb log files and myisam data in the innodb buffer pool myisam key cache query cache cannot receive the cache configured by work: the operating system cache of binary logs and table definition files

46. innodb buffer pool (innotop monitoring buffer)

Cache, index, row data, adaptive hash index, insert buffer, lock, and other large data structure buffer pools may take a long time to restart, you can use the percona server function to re-load the pages of the buffer pool to save time (or perform full table scan and index scan). init_file enables full table scan and index scan; the file contains multiple SQL commands, each of which is a single line. comments are not allowed.

46. myisam key cache (key cache)

Before allocating key_buffer_size, You Need To Know How Much disk space myisam actually occupies select sum (index_length) from information_schema.tables where engine = 'misaim '; or du-sch 'Find/path/mysql/data/directory/-name '*. MYI "'key _ buffer_size should not exceed the total index size or not exceed 25%-50% of the total memory retained by the operating system cache. Indexes of myisam with better performance should be cached in the key cache, you can create multiple named key_buffer_1.key_buffer_size = 1Gkey_buffer_2.key_buffer_size = 1 Gcache index t1, t2 in key_buffer_1; | load index into cache t1, t2; t1, t2 is cached in key_buffer_1 for any index that does not explicitly specify the key buffer to be mapped to. It is accessed for the first time in mysql. myi files are allocated to the default buffer zone.

Calculate the buffer usage:

100-(key_block_unused * key_cache_block_size) * 100/key_buffer_size)

Cache hit rate per second:

Key_reads: mysqladmin extended-status-r-I 10 | grep key_readskey_reads/uptime

Myisam uses the system cache to cache data files. Generally, the BBS of data files are too large. Therefore, it makes sense to keep more memory for the operating system.

Key block size (key cache block)

The block size affects myisam, operating system cache, and interaction between file systems.

Myisam_block_size, key_block_size

48. thread cache

Thread_cache_size, which is currently not associated with the connection but is prepared for the next new connection service, specifies the number of threads that mysql can save in the cache. Observe threads_connected and set thread_cache_size.

49. table cache

The table cache can reuse resources. The table cache separation is: Open the table cache (table_open_cache), and the table definition cache (table_definition_cache) opened_tables status variable is large or growing, this may be because the table cache is not large enough and table_cache can be added (this will not stop increasing during table creation and deletion of temporary tables)

Inndb. data dictionary)

The number of files opened by innodb to. ibd is also limited. innodb_open_files (the setting is large enough)

There is no direct exercise between opening a table in innodb and opening a file. A single global file descriptor is used for each. ibd file.

Myisam, which uses the table cache to hold the file descriptor of the opened table

Innodb Transaction log:

Use logs to reduce the overhead for transaction submission

Innodb uses logs to change random I/O to sequential I/O, while Innodb logs are written in a circular manner,
When it is written to the end of the log, it will jump to the beginning again to continue writing, but will not overwrite the log records that have not yet applied the moral Data File

Log File Size: innodb_log_file_size; innodb_log_files_in_group

Modify the file size:

Completely shut down mysql, move the old log file to another place for saving, then reconfigure the parameters, restart, and ensure that mysql is completely shut down

The innodb_recovery_stats of percona server can help you understand the server's workload from the standpoint of performing crash recovery.

Innodb_log_buffer_size log buffer (1 M-8 M) innodb_ OS _log_written view how much data innodb has written to log files: control the frequency of log buffer refreshing (Session-level variable, UShi global variable) 0, the log buffer is written to the log file and refreshed every second, but nothing is done when the transaction is committed. 1. The log buffer is written to the log file, every transaction commit is refreshed to the persistent storage 2' log buffer to write to the log file, not refreshed, (, difference: If the mysql process is down, 2 will not lose anything, if the entire server crashes, some transactions may still be lost ),