What needs to be configured in mySQL right after installation?
- Transfer
A free translation of a rather old article from the MySQL Performance Blog that it is better to immediately configure it after installing the basic version of mySQL.
It's amazing how many people install mySQL on their servers and leave it with the default settings.
Despite the fact that there are quite a lot of settings in mySQL that you can change, there is a set of really very important characteristics that you definitely need to optimize for your own server. Usually after such a small setup, server performance increases markedly.
As you can see, these are global settings. These variables depend on the hardware of the server and the mySQL engines used, while session variables are usually configured specifically for specific tasks. If you mainly use simple queries, then there is no need to increase the value of sort_buffer_size , even if you have extra 64 GB of RAM. Moreover, large cache values can only degrade server performance. Session variables are best left for later to fine tune the server.
PS: mySQL installation comes with several pre-installed my.cnf files designed for different loads. If you have no time to configure the server manually, then it is usually better to use them than the standard configuration file, choosing the one that suits the load of your server more.
It's amazing how many people install mySQL on their servers and leave it with the default settings.
Despite the fact that there are quite a lot of settings in mySQL that you can change, there is a set of really very important characteristics that you definitely need to optimize for your own server. Usually after such a small setup, server performance increases markedly.
- key_buffer_size is an extremely important setting when using MyISAM tables. Set it equal to about 30-40% of the available RAM, if you use only MyISAM. The correct size depends on the size of the indexes, data and server load - remember that MyISAM uses the operating system cache (OS) to store data, so you need to leave enough space in RAM for the data, and the data can take up much more space than the indexes. However, be sure to check that all the space allocated by the key_buffer_size directive for the cache is constantly used - you can often see situations where 4 GB is allocated to the index cache, although the total size of all .MYI files does not exceed 1 GB. To do so is completely useless, you will only spend resources. If you have practically no MyISAM tables, thenkey_buffer_size should be set to about 16-32 MB - they will be used to store indexes of temporary tables created on disk in memory.
- innodb_buffer_pool_size is an equally important setting, but for InnoDB, be sure to pay attention to it if you intend to use InnoDB tables mainly, because they are significantly more sensitive to buffer size than MyISAM tables. MyISAM tables, in principle, can work well even with large amounts of data and with the standard key_buffer_size value , however mySQL can greatly “slow down” if the innodb_buffer_pool_size value is incorrect . InnoDB uses its own buffer to store both indexes and data, so there is no need to leave memory for the OS cache - set innodb_buffer_pool_size70-80% of available RAM (unless, of course, only InnoDB tables are used). Regarding the maximum size of this option - similarly to key_buffer_size - do not get carried away, you need to find the optimal size, find the best use of available memory.
- innodb_additional_mem_pool_size - this option has almost no effect on the performance of mySQL, but I recommend that you leave about 20 MB (or a little more) for InnoDB for various internal needs.
- innodb_log_file_size is an extremely important setting in a database environment with frequent write operations to tables, especially for large volumes. B of lshie size increases performance, but be careful - and increase recovery time. I usually set the value to about 64-512 MB depending on the size of the server.
- innodb_log_buffer_size - the standard value of this option is quite suitable for most systems with an average number of write operations and small transactions. If there are bursts of activity in your system, or you are actively working with BLOB data, then I recommend slightly increasing the value of innodb_log_buffer_size . However, do not overdo it - too much value will be a waste of memory: the buffer is flushed every second, so you do not need more space than is required during this second. The recommended value is about 8-16 MB, and for smaller databases - even less.
- innodb_flush_log_at_trx_commit - complain that InnoDB runs 100 times slower than MyISAM? You probably forgot about setting innodb_flush_log_at_trx_commit . The default value of “1” means that each UPDATE transaction (or a similar command outside the transaction) must flush the buffer to disk, which is quite resource intensive. Most applications, especially those that previously used MyISAM tables, will work well with a value of "2" (ie, "do not flush the buffer to disk, only to the OS cache"). The log, however, will still be flushed to the disk every 1-2 seconds, so in case of an accident you will lose a maximum of 1-2 seconds of updates. A value of “0” will improve performance, but you run the risk of losing data even if the mySQL server crashes, while setting it toinnodb_flush_log_at_trx_commit in "2" You will lose data only if the entire operating system crashes.
- table_cache - opening tables can be quite resource intensive. For example, MyISAM tables mark the headers of .MYI files as “currently used”. It is usually not recommended to open tables too often, so it is best that the cache is large enough to keep all your tables open. A certain amount of OS resources and RAM are used for this, however this is usually not a significant problem for modern servers. If you have several hundred tables, then the starting value for the table_cache option may be “1024” (remember that each connection requires its own descriptor). If you have even more tables or a lot of connections - increase the value of the parameter. I saw mySQL server with table_cache value equal to 100,000.
- thread_cache - creating / destroying threads is also a resource-intensive operation that occurs every time a connection is established and every connection is broken. I usually set this option to 16. If your application can have a jump in the number of concurrent connections and the Threads_Created variable shows a rapid increase in the number of threads, then increase the thread_cache value . The goal is to prevent the creation of new threads in the normal functioning of the server.
- query_cache_size - if your application reads a lot of data often and you don’t have a cache at the application level, this option can help a lot. Do not put too much importance here, as servicing a large request cache will be expensive in itself. The recommended value is between 32 and 512 MB. Do not forget to check how well the query cache is used - in some conditions (with a small number of hits in the cache, i.e. when the same data is practically not selected) using a large cache can degrade performance.
As you can see, these are global settings. These variables depend on the hardware of the server and the mySQL engines used, while session variables are usually configured specifically for specific tasks. If you mainly use simple queries, then there is no need to increase the value of sort_buffer_size , even if you have extra 64 GB of RAM. Moreover, large cache values can only degrade server performance. Session variables are best left for later to fine tune the server.
PS: mySQL installation comes with several pre-installed my.cnf files designed for different loads. If you have no time to configure the server manually, then it is usually better to use them than the standard configuration file, choosing the one that suits the load of your server more.