MySQL performance query and Configuration Overview

MySQL performance query and Configuration Overview

View MySQL database performance:
Show proceelist view the database connection status and total number of connections
Show globalstatus like 'max _ used_connections '; view the maximum response connection of the database. The maximum number of connections to mysql under 10% is high.
Show processlist view connection count status
Showglobal status like 'thread % '; view process usage
Show globalstatus like 'qcache % '; view Cache Usage
Show statuslike 'key _ read % 'ratio key_reads/key_read_requests should be as low as possible, at least or
Show statuslike "open %"

Currently, databases on the production line generally occupy a large amount of memory, mainly because of the improper configuration of parameters related to the database configuration file, resulting in a large read cache space:
For related parameters, refer to the configuration below:
Important parameters for Database Configuration:
[Mysqld]
Skip-external-locking // Skip the external lock, used to lock the myisam data table under multiple processes
Key_buffer_size = 384 M // specify the index buffer size. 4G memory indexes are generally 512 M or M. For this value, you can check the status value key_read_request to check whether the configuration is reasonable, the ratio is about and.
Table_open_cache = 2048 // mysql reads some data to table_open_cachede when it opens the database table. mysql reads data from the database only when it cannot be found in the database, this can reduce the number of times the file is opened and closed, and check whether the configuration is reasonable. For this number of configurations, check the number of opened tables. Use show global status like 'open % _ tables 'to check the number of opened tables. We recommend that you set the number of opened tables to open_tables/opened_tables> = 0.85.
Read_buffer_size = 2 M // size of the cache used for read queries. this parameter is only valid for myisam tables.
Read_rnd_buffer_size = 8 M // limited to all storage engines
Myisam_sort_buffer_size = 64 M // re-index the maximum file size
Thread_concurrency = 4 // This value is generally double the number of cpu cores. This value belongs to an important configuration object of Version 5.5 or later. This value is invalid.
User = mysql
Pid-file =/var/run/mysqld. pid
Socket =/var/run/mysqld. sock
Port = 3306
Basedir =/usr
Datadir =/var/lib/mysql
Tmpdir =/tmp
Lc_messages_dir =/usr/share/mysql
Lc_messages = en_US
Skip-name-resolve
Lower_case_table_names // # ignore the case sensitivity of the table name
Connect_timeout = 15 // Default Value
Wait_timeout = 600 // Default Value
Max_allowed_packet = 16 M // The default value is 16 M, but when a large field prompts that the information package is too large, modify it. Generally, it is increased to 32 M.
Thread_cache_size = 128 // Number of stored threads in the cache. 8 GB memory is usually set to 64 or 128, 3 GB is 32, and 2 GB is 16.
Sort_buffer_size = 8 M // sort buffer 100 connection will consume GB of memory
Bulk_insert_buffer_size = 16 M // The default cache size for batch inserts is 8 M.
Tmp_table_size = 256 M // 32 M by default
Max_heap_table_size = 256 M
Query_cache_limit = 0 # Do not use data cache. Generally, memcache external cache is used. The default value is 1 MB.
Query_cache_size = 0 # is generally used for mysqlisam optimization. We recommend that you disable this function.
Log_bin =/var/log/mysql/mariadb-bin // note the size of the storage space of log-bin.
Log_bin_index =/var/log/mysql/mariadb-bin.index
Expire_logs_days = 10 // time period for storing log-bin
Max_binlog_size = 100 M // The maximum binlog size. You do not need to set it.
Innodb_buffer_pool_size = 16384 M // It is generally set to 50%-80% of the system memory.
Innodb_log_buffer_size = 32 M // determine the memory used by the log file
Innodb_file_per_table = 1
Innodb_open_files = 400
Innodb_io_capacity = 400
Innodb_flush_method = O_DIRECT
Innodb_log_file_size = 512 M // determine the size of the data file. Large settings can improve performance, but also increase the time required to recover the faulty database.
 
Two important parameters in innode configuration are innodb_buffer_pool_size and innodb_log_file_size.
In mysql official documents, we generally recommend that you set innodb_buffer_pool_sixe to about 50% to 80% of the memory.
Show status like 'innodb _ buffer_pool _ % '; view innode Performance
 
 
[Mysqldump]
Quick // do not use mydqldump if you need to enable it when exporting a huge table. Generally, do not use mydqldump for backup after 20 GB.

This article permanently updates the link address: