Shulou(Shulou.com)05/31 Report--

This article mainly introduces what needs to be adjusted after MySQL installation, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to know about it.

In the face of MySQL's DBA or people who do MySQL performance-related work, my favorite question is what needs to be adjusted after the MySQL server is installed, assuming it is installed with the default settings.

I am surprised that so many people do not have a reasonable answer, and many MySQL servers are running in the default configuration.

Although you can adjust a lot of MySQL server variables, in general only a few variables are really important. After you have set these variables, changes to other variables usually result in relatively limited performance improvements.

Key_buffer_size-very important if you use the MyISAM table. If you use only the MyISAM table, set its value to 30 to 40 percent of the available memory. The right size depends on the number of indexes, the amount of data, and the load-remember that MyISAM uses the operating system's cache to cache data, so you also need to set aside memory for it, and the data is usually much larger than the index. However, you need to check to see if all key_buffer is always in use-it is not uncommon that the key_buffer is 4G while the .myi file has only 1G. That would be a bit of a waste. If you only use a small number of MyISAM tables, you want it to be smaller, but still set it to at least 16 to 32m for indexing temporary tables (hard disk-occupied).

Innodb_buffer_pool_size-is very important if you use the Innodb table. The Innodb table is more sensitive to the size of the buffer size than the MyISAM table. When dealing with large data sets (data set), using the default key_buffer_size and innodb_buffer_pool_size,MyISAM may work, while Innodb may be as slow as crawling. At the same time, Innodb buffer pool caches data and index pages, so you don't need space for the operating system's cache, which can be set to 70% to 80% of memory on an Innodb-only database server. The above key_buffer rules also apply-if you only have a small dataset and don't grow dramatically, don't set the innodb_buffer_pool_size too large. Because you can make better use of excess memory.

Innodb_additional_pool_size-this variable does not affect performance very much, at least in operating systems with decent (decent) memory allocation. But you still need to set it to at least 20MB (sometimes larger), which is allocated by Innodb to handle some chores.

Innodb_log_file_size-is very important for write-based workloads (workload), especially when the dataset is large. Higher values improve performance, but increase recovery time. So you need to be cautious. I usually set it to 64m to 512m depending on the server size (server size).

Innodb_log_buffer_size-the default value is sufficient for most load cases with a moderate number of writes and short transactions. If you have a lot of UPDATE or use a lot of blob, you may need to increase its value. Don't set it too much, or memory will be wasted-log buffer is refreshed at least once a second, and there is no need to use the memory needed for more than a second. 8MB to 16MB is usually sufficient. Smaller installations should use smaller values.

Innodb_flush_logs_at_trx_commit-crying that Innodb is 100 times slower than MyISAM? You may have forgotten to adjust this value. The default value is 1, which means that the log is flushed to disk every time the transaction is committed, which is very resource-consuming, especially when there is no battery backup for cache. Many applications, especially those migrated from MyISAM tables, should be set to 2. This means that the log is flushed only to the operating system's cache, not to disk. At this point, the log is still flushed to disk every second, so you usually don't lose more than 1-2 seconds of updates. Setting to 0 is faster, but less secure, and transactions are lost when the MySQL service crashes. Setting to 2 will only lose data when the operating system crashes.

Table_cache-opening tables is expensive (resource-consuming). For example, the MyISAM table marks the header of the MYI file to indicate which tables are in use. You don't want this to happen frequently, and it's usually best to resize your cache to meet the needs of most open tables. It uses some operating system resources and memory, but is usually not a problem for modern hardware levels. For an application that uses hundreds of tables, 1024 is an appropriate value (note that each join requires its own cache). If you have a lot of joins or tables, you need to increase its value. I have seen the use of values over 100000.

Thread_cache-thread creation / destruction is expensive and occurs every time you connect and disconnect. I usually set this value to at least 16. If the application sometimes has a large number of concurrent connections, and you can see the threads_created variable growing rapidly, I will increase its value. The goal is not to create threads in normal operations.

Query_cache-if your application is read-based, and you don't have application-level caching, then it will help a lot. Don't make it too large, because its maintenance may lead to performance degradation. It is usually set between 32m and 512m. After setting up, it will be checked after a period of time to see if it is appropriate. For certain workloads cache hit ratio is lower than would justify having it enabled. (this sentence cannot be translated)

Note: as you can see, the above are all global variables. These variables depend on the use of hardware and storage engines, while session-level variables (per session variables) are related to specific traffic (workload). If it's just a few simple queries, there's no need to add sort_buffer_size, even if you have 64 gigabytes of memory to waste. And doing so may also degrade performance. I usually put adjusting session-level variables in the second step, after I analyze the traffic (or load).

In addition, some examples of my.cnf files are included in the MySQL distribution, which can be used as a very good template. If you can choose one properly, it is usually better than the default.

Thank you for reading this article carefully. I hope the article "what needs to be adjusted after MySQL installation" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.