After the MySQL service is started, run the showvariables and showstatus commands to view the static parameters and dynamic running status of the Mysql service. Showvariables is used to view MySQL parameters that are not changed after the database is started.
After the Mysql service is started, we can use the show variables and show status commands to view the static parameter values and dynamic running status information of the mysql service. Show variables is the value that cannot be changed after the database is started, such as the buffer size, character set, and data file name. Show status displays the dynamic changes of the database during operation, such as Lock wait and current connections. View the buffer parameter value on the server
View the running status of the buffer on the server
The key parameters that affect MySQL Performance are described in the key_buffer_size and table_cache of the MyISAM storage engine and some parameters starting with InnoDB _ using the innodb storage engine. 1. key_buffer_size this parameter is used to set the cache size of the Index block (Index Blocks), which is shared by the Index thread. this parameter only uses the MyISAM storage engine. In versions later than MySQL, you can cache the specified table index into the specified key_buffer, which can reduce competition between threads. Index cache overview the MyISAM storage engine, like many other database systems, uses a policy to store the most frequently accessed tables in memory. Corresponding to the index block, it maintains a structure called index cache (index buffer), which stores many frequently used Index block buffer blocks. For data blocks, Mysql mainly relies on the system's local file system cache. With the index buffer, threads no longer access the index cache serially. Multiple threads can access the index cache in parallel. You can set multiple index caches and specify the data table indexes to specific caches. Create an index cache
Set global cache index name. key_buffer_size = 100*1024;
Global is a global limitation, indicating that it is valid for every new session (connection.
Modifying an index cache is the same as creating an index cache. it is set global cache index name. key_buffer_size = 100*1024;
Put the indexes of the relevant table in the self-created index cache format: cache index table name 1, table name 2 in index cache put the indexes in table t1, t2, and t3 in the my_cache index cache
Because table t1 is an InnoDB table, table t2 and table t3 are MyISAM tables, only indexes in table t2 and table t3 can be stored in the my_cache cache.
You can use the load index into cache + table name to delete the index cache and set the index buffer size to 0. Note that you cannot delete the default key_buffer. Configure the mysql server to automatically load the index cache when it starts. add the following content to the MySQL configuration file (my. ini in Windows and my. cnf in Linux)
My_cache.key_buffer_size = 1G # specify the index cache size. init_file =/usr/local/mysql/init_index. SQL # specify the file to be loaded to the cache index.
The content of init_index. SQL is as follows:
Cache index t2 into my_cache;
Cache index t3 into my_cache;
2. table_cache Overview this parameter indicates the number of buffer tables opened by database users. table_cache is related to max_connections. When a connection accesses a table, MySQL checks the number of cached tables. if the table is already opened in the buffer, it directly accesses the cached table. if the table is cached, the current table is added to the cache and queried. Before caching, table_cache is used to limit the maximum number of cached tables. if the number of cached tables does not reach table_cache, a new table is added, mySQL releases the previously cached table based on the last query time and query rate rules of the cache table and adds a new table. Parameter optimization
Check the status variables open_tables and opend_tables of mysqld to determine the size of table_cache. Open_tables indicates the number of currently opened table caches. if you execute flush tables, the system will disable some table caches that are not currently in use, reducing the open_tables value. Opend_tables indicates the number of table caches that have been opened, which is accumulated all the time. the flush tables operation is not performed to reduce the number. For example, the changes are obvious.
After executing a query, we found that opend_table and open_table both increased by 1.
After executing the same query again, it is found that both opend_table and open_table do not change.
It can be seen that open_table is more valuable for setting table_cache
3. the Innodb_buffer_pool_size parameter defines the maximum memory cache size of table data and index data of the InnoDB storage engine. Different from the MyISAM storage engine, MyISAM's key_buffer_size only caches index keys, while Innodb_buffer_pool_size caches data blocks and index blocks. the higher the value, the less disk I/O required to access the data in the table. However, if the value is too large, physical memory competition will be too large. 4. the Innodb_flush_log_at_trx_commit parameter controls the operation time when data in the cache area is written to the log file and the log file data is refreshed to the disk. The default value is 1. The following values are available: 0: The log buffer is written to the log file once per second, and the log file is refreshed to the disk, but the transaction commit is not performed. 1: when each transaction is committed, the log buffer is written to the log file and the log file is refreshed to the disk. 2: when each transaction is committed, the log buffer is written to the log file, but the log file is not refreshed to the disk, and the log file is refreshed to the disk once per second. 5. the Innodb_additional_mem_pool_size parameter is used to store the memory pool size of the database structure and other internal data results. 6. innodb_log_buffer_size log cache size 7. the size of each log file in the innodb_log_file_size log group is 8. the innodb_lock_wait-timeoutMysql can automatically monitor the deadlock caused by the row lock and handle the corresponding, but the deadlock caused by the table lock, cannot be automatically detected, this parameter is mainly used for rollback when a row deadlock occurs after a specified time. 9. Whether the Innodb_support_xa setting supports distributed transactions. the default value is ON or 1, which indicates support.
