Configuration items I have adjusted to improve mysql Performance

Configuration items I have adjusted to improve mysql Performance
Skip-external-locking: skips the external lock. To understand this parameter, you must first understand external-locking (external locking is used to lock the MYISAM data table under multi-process [multiple services share the same database Directory] access ), in most cases, our mysql service is a single-process service. From the mysql official website, the skip-external-locking parameter is ON by default,

mysql> show variables like '%skip%';  +------------------------+-------+  | Variable_name          | Value |  +------------------------+-------+  | skip_external_locking  | ON    |  

 

Enable this parameter in the configuration file [mysqld. Key_buffer_size = 256 M: Enable index cache for MYISAM Data Tables shared by threads. In our project, the data table basically uses the INNODB engine, so this parameter is not adjusted for the moment max_allowed_packet = 16 M: maximum data packet size allowed by the server. When this configuration item is not adjusted, the default value of the server is 4 MB. Of course, this parameter is related to the data packet size in mysql (16 MB by default) and mysqldump (24 MB by default, I have adjusted it to 16 Mb). Generally, it can be 1 MB, we recommend that you adjust this parameter when using a blog or a larger string. In general, the database will be initialized to net_buffer_length (minimum 1024 bytes, maximum 1 M, the default value is 16 kb. Table_open_cache = 512: number of tables opened by all threads (the default size is 1000 ). If opened_tables is large and flush tables is not frequently used, we recommend that you increase the size of this parameter. This value is not as large as possible. You need to adjust sort_buffer_size = 512 K according to the actual situation of open_tables and opened_tables: the cache size of the session to be sorted is for each connection, this value will not increase, the better. The default size is KB. A large configuration will consume more memory. I personally have not tested read_buffer_size = 512 K: Specify the cache for the MYISAM data table thread that requires full table scan. this parameter is also for each connection. I do not need to pay too much attention for the moment.

Each thread that does a sequential scan for a MyISAM table allocates a buffer of this size (in bytes)  for each table it scans. If you do many sequential scans, you might want to increase this value, which  defaults to 131072. The value of this variable should be a multiple of 4KB. If it is set to a value that is not  a multiple of 4KB, its value will be rounded down to the nearest multiple of 4KB.  This option is also used in the following context for all search engines:  • For caching the indexes in a temporary file (not a temporary table), when sorting rows for ORDER BY.  • For bulk insert into partitions.  • For caching results of nested queries.  and in one other storage engine-specific way: to determine the memory block size for MEMORY tables.  The maximum permissible setting for read_buffer_size is 2GB.  For more information about memory use during different operations, see Section 8.11.4.1, “How MySQL  Uses Memory”.  

 

Read_rnd_buffer_size = 1 M: first, this variable can be used by any storage engine. When reading a row from a sorted key value table, it is obtained from the buffer and not from the disk. The default value is 256 kb.

This variable is used for reads from MyISAM tables, and, for any storage engine, for Multi-Range Read  optimization.  When reading rows from a MyISAM table in sorted order following a key-sorting operation, the rows are  read through this buffer to avoid disk seeks. See Section 8.2.1.15, “ORDER BY Optimization”. Setting  the variable to a large value can improve ORDER BY performance by a lot. However, this is a buffer  Server System Variables  627  allocated for each client, so you should not set the global variable to a large value. Instead, change the  session variable only from within those clients that need to run large queries.  The maximum permissible setting for read_rnd_buffer_size is 2GB.  

 

Thread_cache_size = 18: Number of threads available for service cache.

How many threads the server should cache for reuse. When a client disconnects, the client's threads  are put in the cache if there are fewer than thread_cache_size threads there. Requests for threads  are satisfied by reusing threads taken from the cache if possible, and only when the cache is empty is  a new thread created. This variable can be increased to improve performance if you have a lot of new  connections. Normally, this does not provide a notable performance improvement if you have a good  thread implementation. However, if your server sees hundreds of connections per second you should  normally set thread_cache_size high enough so that most new connections use cached threads. By  examining the difference between the Connections and Threads_created status variables, you can  see how efficient the thread cache is. For details, see Section 5.1.6, “Server Status Variables”.  The default value is based on the following formula, capped to a limit of 100:  8 + (max_connections / 100)  This variable has no effect for the embedded server (libmysqld) and as of MySQL 5.7.2 is no longer  visible within the embedded server.  

 

Query_cache_size = 8 M: memory size allocated to the query cache. To be used with query_cache_type, It is disabled by default. From the surface of this parameter, it seems that the larger the value is set, the better the effect will be. However, please note that after a select statement is used to query the cache, the database caches the query results to the cache. If the query results remain unchanged after the same select query, the database returns the cache results, if the queried data tables are added, deleted, modified, and deleted, the cache will be invalid during the change period, and the data will be updated. For addition, deletion, and modification, it takes a lot of time, so we have to make a trade-off. I will sort out the relevant test data in the future.

By default, the query cache is  disabled. This is achieved using a default value of 1M, with a default for query_cache_type of  0. (To reduce overhead significantly if you set the size to 0, you should also start the server with  query_cache_type=0.  The permissible values are multiples of 1024; other values are rounded down to the nearest multiple.  Note that query_cache_size bytes of memory are allocated even if query_cache_type is set to 0.  See Section 8.9.3.3, “Query Cache Configuration”, for more information.  The query cache needs a minimum size of about 40KB to allocate its structures. (The exact size  depends on system architecture.) If you set the value of query_cache_size too small, a warning will  occur, as described in Section 8.9.3.3, “Query Cache Configuration”.

Query_cache_type = indicates that all queries are cached. 2 indicates that select SQL _cache queries are cached. See the following content.

0 or OFF Do not cache results in or retrieve results from the query cache. Note that  this does not deallocate the query cache buffer. To do that, you should set  query_cache_size to 0.  1 or ON Cache all cacheable query results except for those that begin with SELECT  SQL_NO_CACHE.  2 or DEMAND Cache results only for cacheable queries that begin with SELECT SQL_CACHE.  

 

Set global max_connections = 500: note that the maximum number of connections is set through the command line, not configured in the configuration file, because I tried to modify it in the configuration file, after the mysql service is restarted, it does not take effect. After this parameter is set, it still does not take effect after the service is restarted. If a friend knows the reason, please let us know. If your project uses the spring connection pool, I think spring connections correspond to this connection. Depends on your project needs. Log-bin = mysql-bin: enables binary logs, and the log name is generated in sequence according to mysql-bin. But I have always been wondering whether the binary log is the transaction log of innodb. Is it a transaction log that is traditionally called? Binlog_format = mixed: the binary log format is mixed. In this mode, the combination of statement and row modes explains the problems and solutions we encountered in setting binary logs, if you encounter a similar problem, it will be helpful.

In MySQL 5.7, the default format is STATEMENT.  You must have the SUPER privilege to set either the global or session binlog_format value.  The rules governing when changes to this variable take effect and how long the effect lasts are the same  as for other MySQL server system variables. See Section 13.7.4, “SET Syntax”, for more information.  When MIXED is specified, statement-based replication is used, except for cases where only row-based  replication is guaranteed to lead to proper results. For example, this happens when statements contain  user-defined functions (UDF) or the UUID() function. An exception to this rule is that MIXED always  uses statement-based replication for stored functions and triggers.  

 

Innodb_buffer_pool_size = 512 M: innodb cache table and index memory space. We suggest setting it to 50-80% of the physical memory on the official website, but please note that it is a machine that focuses on data service, if your operating system contains databases and other services, consider the memory occupied by other services. For Linux, this parameter will occupy the size of the swap file.

The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data.  The default value is 128MB. The maximum value depends on the CPU architecture; the maximum  is 4294967295 (232-1) on 32-bit systems and 18446744073709551615 (264-1) on 64-bit systems.  On 32-bit systems, the CPU architecture and operating system may impose a lower practical  maximum size than the stated maximum. When the size of the buffer pool is greater than 1GB, setting  innodb_buffer_pool_instances to a value greater than 1 can improve the scalability on a busy  server.  The larger you set the innodb_buffer_pool_size value, the less disk I/O is needed to access the  same data in tables more than once. On a dedicated database server, you might set this to up to 80% of  the machine physical memory size. 

 

Innodb_additional_mem_pool_size = 20 M: memory pool size used to set innodb data directory information and other internal data structures.

The size in bytes of a memory pool InnoDB uses to store data dictionary information and other internal  data structures. The more tables you have in your application, the more memory you allocate here. If  InnoDB runs out of memory in this pool, it starts to allocate memory from the operating system and  writes warning messages to the MySQL error log. The default value is 8MB.  This variable relates to the InnoDB internal memory allocator, which is unused if  innodb_use_sys_malloc is enabled.  

 

Innodb_log_file_size = 128 M: used to set the size of each log file in the memory pool. However, note that "once this parameter is set, you need to delete the original ib_logfile backup; otherwise, an error will occur when the mysql service is restarted. "I have not personally tested it.

The size in bytes of each log file in a log group. The combined size of log files  (innodb_log_file_size * innodb_log_files_in_group) cannot exceed a maximum value that  is slightly less than 512GB. A pair of 255 GB log files, for example, would allow you to approach the limit  InnoDB System Variables  1945  but not exceed it. The default value is 48MB. Sensible values range from 1MB to 1/N-th of the size of  the buffer pool, where N is the number of log files in the group. The larger the value, the less checkpoint  flush activity is needed in the buffer pool, saving disk I/O. Larger log files also make crash recovery  slower, although improvements to recovery performance in MySQL 5.5 and higher make the log file size  less of a consideration. For general I/O tuning advice, see Section 8.5.7, “Optimizing InnoDB Disk I/O”.  

 

Innodb_log_buffer_size = 16 M: The cache size of log files refreshed by innodb to the disk, which can reduce disk IO.

The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is  8MB. A large log buffer enables large transactions to run without a need to write the log to disk before  the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making  the log buffer larger saves disk I/O. For general I/O tuning advice, see Section 8.5.7, “Optimizing InnoDB  Disk I/O”.  

 

Innodb_flush_log_at_trx_commit = 2: set the time point when binary logs refresh the disk.
Innodb_lock_wait_timeout = 20: The time when the transaction waits for timeout. The default value is 50 seconds.

The length of time in seconds an InnoDB transaction waits for a row lock before giving up. The  default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB  InnoDB System Variables  1941  transaction waits at most this many seconds for write access to the row before issuing the following  error:  ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction  When a lock wait timeout occurs, the current statement is rolled back (not the entire transaction). To  have the entire transaction roll back, start the server with the --innodb_rollback_on_timeout  option. See also Section 14.19.4, “InnoDB Error Handling”.  

 

When I configure mysql in Linux, the mysql service cannot be started. It is very strange and no reason is found.