I. theory: 1. memory optimization principle: a allocates as much memory as possible to mysql for caching, but reserves enough memory for the operating system and other programs, otherwise, SWAP page switching will be generated, affecting the performance of bMyISAM data file reading relies on the operating system's own IO cache. Therefore, I. theory:
1. memory optimization principles:
A. allocate as much memory as possible to mysql for caching, but reserve enough memory for the operating system and other programs. otherwise, SWAP page switching will occur, affecting performance.
B. MyISAM data file reading depends on the operating system's own IO cache. Therefore, if there is a MyISAM table, more memory should be reserved for the operating system's IO cache.
C. the cache in the sorting area and connection area is allocated to each database session. the default value should be reasonably allocated based on the maximum number of connections. Cannot be set too large. Otherwise, the physical memory will be exhausted when the concurrent connection is high.
2. MyISAM memory optimization:
A. key_buffer_size determines the size of the cache area of the MyISAM index block, which directly affects the access efficiency of the MyISAM table. You can set the value of key_buffer_size in the mysql parameter file. for mysql databases, we recommend that you allocate at least 1/4 of the available memory to key_buffer_size.
The formula for calculating the key buffer usage is as follows:
1-(key_blocks_unused * key_cache_block_size)/key_buffer_size)
It should be around 80%. If the value is greater than 80%, the performance will be degraded due to insufficient index cache. if the value is smaller than 80%, the memory will be wasted.
B. use multiple index caches:
1) mysql improves the performance of MyISAM index access by sharing key buffer among sessions, but it cannot eliminate competition between key buffer sessions.
3. adjust the LRU policy:
A. adjust key_cache_pision_limit to control the proportion of cache used as warm sub-tables.
At the end:
N * key_cache_age_threshold/100 times that the cache has not been accessed, the database will be downgraded to the warm sub-table.
B. adjust read_buffer_size and read_md_buffer_size.
C. If the order by clause is used, you can increase the value of read_rnd_buffer_size. However, the value of read_rnd_buffer_size is also allocated by session.
4. InnoDB memory optimization:
A. InnoDB uses a memory area as the IO cache pool. this cache pool is used not only to cache index blocks of InnoDB, but also to cache data blocks of InnoDB. Different from MyISAM
B. Internally, the InnoDB cache pool is logically composed of free list, flush list, and LRU list. As the name suggests, free list is the list of idle cache blocks, and flush list is the list of cache blocks to be slowed down to the disk, while LRU list is the cache block currently used by InnoDB, it is the core of InnoDB buffer pool.
InnoDB uses the LRU algorithm similar to MyISAM's 'point insertion policy' LRU algorithm.
Dirty page refresh
C. you can adjust the InnoDB buffer pool size, change the allocation ratio of young sublist and old sublist, control dirty cache refreshing activities, and use multiple InnoDB cache pools to optimize InnoDB performance.
5. settings of innodb_buffer_pool_size:
A. innodb_buffer_pool_size determines the maximum cache size of table data and index data of the InnoDB storage engine. The innodb buffer pool provides data caching for both data blocks and index blocks. if the innodb_buffer_pool_size value increases, the higher the cache hit rate is, the less disk I/O required to access the InnoDB table, and the higher the performance.
You can use:
Mysqladmin-S/tmp/mysql. sock ext | grep-I innodb_buffer_pool
View the usage of the buffer pool
You can use the following formula to calculate the InnoDB cache pool hit rate:
(1-innodb_buffer_pool_reads/innodb_buffer_pool_read_request) * 100
If it is too low, the memory should be expanded and the innodb_buffer_pool_size value should be increased.
6. adjust innodb_old_blocks_time settings:
A. the innodb_old_blocks_time parameter determines the speed at which the cached data block is transferred from old sublist to young sublist. when a cached data block is inserted into the midpoint (old sublist, it is possible to be transferred to new sublist at least after the old sublist stays above innodb_old_blocks_time (MS.
You can adjust the value of innodb_old_blocks_time according to the output information of InnoDB Monitor. if non-youngs/s is low and young/s is high during table scanning, you should increase innodb_old_blocks_time appropriately, to prevent table scanning from eliminating real hot data, this value can be dynamically set
7. adjust the number of cache pools to reduce internal contention for the data structure of the cache pool:
A. The InnoDB cache system introduces the innodb_buffer_pool_instances configuration parameters. For a large cache pool, increasing the value of this parameter appropriately can reduce internal cache access conflicts caused by concurrency and improve performance. The InnoDB cache system splits the cache size specified by the innodb_buffer_pool_size parameter into innodb_buffer_pool_instances buffer pools.
8. control innodb buffer refresh, prolong data cache time, and reduce disk I/O
A. The refresh speed of the innodb buffer pool depends on two parameters.
1) innodb_max_dirty_pages_pct: Controls the maximum proportion of dirty pages in the cache pool. the default value is 75%. if the number of dirty pages reaches or exceeds this value, the innoDB background thread refreshes the cache.
2) innodb_io_capacity: To some extent, it indicates the number of I/O operations that can be completed on the disk per second. for disks with low rotation speed, innodb_io_capacity can be reduced. For Solid State Disks and arrays composed of multiple disks, the innodb_io_capacity value can be increased as appropriate.
3) if the cache pool cannot be increased, reduce the value of innodb_max_dirty_pages_pct and increase the value of innodb_io_capactity to speed up dirty page refreshing.
9. InnoDB doublewrite:
A. If an inconsistent page is found during recovery, InnoDB will use the corresponding copy of the system tablespace double buffer area to restore the data page.
B. since the buffer to be synchronized to doublewrite is sequential writes to the contiguous disk space, enabling dual writes has little impact on performance. For applications that require high performance and can tolerate data loss, innodb_doublewrite = 0 can be used to disable dual-write to meet performance requirements.
10. adjust the cache area for sorting user service threads:
A. If the sort_merge_passes value is large when you view show global status, you can adjust the sort_buffer_size parameter to adjust the sorting cache to improve the SQL performance of the order by clause or group clause.
B. You can adjust the join_buffer_size value to improve queries without indexes.
C. The best strategy is to set a smaller global join_buffer_size, and separately set join_buffer_size for complicated operation sessions.
11. InnoDB log mechanism and optimization:
A. settings of innodb_flush_log_at_trx_commit:
0: triggered once per second, meeting persistence requirements (the highest efficiency, but the least secure)
1: when each transaction is committed, the redo log in the cache is immediately written back to the log file, and the operating system fsync is called to refresh the IO cache (default value, the lowest efficiency, but the safest)
2: when each transaction is committed, InnoDB immediately writes the redo log back to the log file, but does not immediately call fsync to refresh the IO cache, instead, refresh the disk I/O cache only once per second (performance and data security are in the middle)
12. set the log file size to control the checkpoint:
A. You can use some methods to calculate the volume of innodb logs generated per hour and estimate the appropriate innodb_log_file_size value.
13. adjust innodb_log_buffer_size:
A. You can increase innodb_log_buffer_size to reduce log Write disk operations and improve transaction processing performance.
14. adjust max_connections to improve concurrent connections:
A. max_connections controls the maximum number of connections allowed to the mysql database. the default value is 151.
15. adjust back_log:
A. If the database needs to process a large number of connection requests in a short period of time, you can increase the value of back_log.
16. adjust table_open_cache:
A. If the value of the mysql state variable opened_tables is large without executing the flush tables command, it indicates that the value of table_open_cache is too small and should be increased as appropriate.
17. adjust thread_cache_size:
A. You can calculate the cache failure rate threads_created/connections to determine whether the value of thread_cached_size is appropriate. The closer this value is to 1, the lower the thread cache hit rate. increase the value of thread_cache_size as appropriate.
18. settings of innodb_lock_wait_timeout:
A. innodb_lock_wait_timeout can control the time for innodb transactions to wait for the row lock. the default value is 50 ms.
B. for interactive OLTP applications that require quick feedback, you can reduce the row Lock wait timeout to avoid long transaction suspension.
C. for batch processing operations running in the background, you can increase the row Lock wait timeout time to avoid large rollback operations.
II. practice:
mysql> use sakila;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show engine innodb status \G;*************************** 1. row *************************** Type: InnoDB Name: Status: =====================================151102 7:13:01 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 15 seconds-----------------BACKGROUND THREAD-----------------srv_master_thread loops: 113 1_second, 113 sleeps, 7 10_second, 47 background, 47 flushsrv_master_thread log flush and writes: 113----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 18, signal count 18Mutex spin waits 8, rounds 240, OS waits 7RW-shared spins 11, rounds 330, OS waits 11RW-excl spins 0, rounds 0, OS waits 0Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 0.00 RW-excl------------TRANSACTIONS------------Trx id counter B0APurge done for trx's n:o < 920 undo n:o < 0History list length 103LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 0, not startedMySQL thread id 28, OS thread handle 0x7f5dbdfe6700, query id 568 localhost rootshow engine innodb status--------FILE I/O--------I/O thread 0 state: waiting for i/o request (insert buffer thread)I/O thread 1 state: waiting for i/o request (log thread)I/O thread 2 state: waiting for i/o request (read thread)I/O thread 3 state: waiting for i/o request (read thread)I/O thread 4 state: waiting for i/o request (read thread)I/O thread 5 state: waiting for i/o request (read thread)I/O thread 6 state: waiting for i/o request (write thread)I/O thread 7 state: waiting for i/o request (write thread)I/O thread 8 state: waiting for i/o request (write thread)I/O thread 9 state: waiting for i/o request (write thread)Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0Pending flushes (fsync) log: 0; buffer pool: 0586 OS file reads, 55 OS file writes, 48 OS fsyncs0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s-------------------------------------INSERT BUFFER AND ADAPTIVE HASH INDEX-------------------------------------Ibuf: size 1, free list len 0, seg size 2, 0 mergesmerged operations: insert 0, delete mark 0, delete 0discarded operations: insert 0, delete mark 0, delete 0Hash table size 553229, node heap has 1 buffer(s)0.00 hash searches/s, 0.00 non-hash searches/s---LOG---Log sequence number 13476957Log flushed up to 13476957Last checkpoint at 134769570 pending log writes, 0 pending chkp writes32 log i/o's done, 0.00 log i/o's/second----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 274726912; in additional pool allocated 0Dictionary memory allocated 182244Buffer pool size 16383Free buffers 15829Database pages 553Old database pages 224Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 553, created 0, written 270.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 553, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue1 read views open inside InnoDBMain thread process no. 62305, id 140040552920832, state: waiting for server activityNumber of rows inserted 5, updated 0, deleted 0, read 120.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s----------------------------END OF INNODB MONITOR OUTPUT============================1 row in set (0.00 sec)ERROR: No query specifiedmysql> set global hot_cache.key_buffer_size = 128* 1024;Query OK, 0 rows affected (0.00 sec)mysql> set global hot_cache.key_buffer_size = 0;Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'key_buffer_size';+-----------------+----------+| Variable_name | Value |+-----------------+----------+| key_buffer_size | 33554432 |+-----------------+----------+1 row in set (0.00 sec)mysql> set global key_buffer_size = 0;ERROR 1438 (HY000): Cannot drop default keycachemysql> show warnings;+-------+------+------------------------------+| Level | Code | Message |+-------+------+------------------------------+| Error | 1438 | Cannot drop default keycache |+-------+------+------------------------------+1 row in set (0.00 sec)mysql> cache index sales,sales2 in hot_cache;+---------------+--------------------+----------+-------------------------------------+| Table | Op | Msg_type | Msg_text |+---------------+--------------------+----------+-------------------------------------+| sakila.sales | assign_to_keycache | Error | Table 'sakila.sales' doesn't exist || sakila.sales | assign_to_keycache | status | Operation failed || sakila.sales2 | assign_to_keycache | Error | Table 'sakila.sales2' doesn't exist || sakila.sales2 | assign_to_keycache | status | Operation failed |+---------------+--------------------+----------+-------------------------------------+4 rows in set (0.00 sec)mysql> set global key_cache_pision_limit = 70;Query OK, 0 rows affected (0.00 sec)mysql> set global hot_cache.key_cache_pision_limit = 70;Query OK, 0 rows affected (0.00 sec)mysql> show global variables like '%innodb_old_blocks_pct%';+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| innodb_old_blocks_pct | 37 |+-----------------------+-------+1 row in set (0.01 sec)mysql> show global variables like '%doublewrirte%';Empty set (0.00 sec)mysql> show global variables like '%doublewrite%';+--------------------+-------+| Variable_name | Value |+--------------------+-------+| innodb_doublewrite | ON |+--------------------+-------+1 row in set (0.01 sec)mysql> select @a1 := variable_value as a1;ERROR 1054 (42S22): Unknown column 'variable_value' in 'field list'mysql> select @a1 := variable_value as a1 -> from information_schema.global_status -> where variable_name = 'innodb_os_log_written' -> union all -> select sleep(60) -> union all -> select @a2 := variable_value as a2 -> from information_schema.global_status -> where variable_name = 'innodb_os_log_written';+------+| a1 |+------+| 9216 || 0 || 9216 |+------+3 rows in set (1 min 0.02 sec)mysql> select round((@a2-@a1)/1024/1024 / @@innodb_log_files_in_group) as MB;+------+| MB |+------+| 0 |+------+1 row in set (0.01 sec)
