What needs to be configured in mySQL immediately after installation?

Free translation of a rather old article from the MySQL Performance Blog that it’s better to set up immediately 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 in mySQL there are quite a few settings 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 setting, server performance increases noticeably.

• key_buffer_size is an extremely important setting when using MyISAM tables. Set it to about 30-40% of the available RAM if you use only MyISAM. The correct size depends on the size of the indexes, the data and the load on the server - remember that MyISAM uses the operating system (OS) cache to store data, so you need to leave enough space in the RAM for the data, and the data can take up much more space than the indexes. However, be sure to ensure 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 for the index cache, although the total size of all .MYI files does not exceed 1 GB. To do so completely useless, you just spend resources. If you practically do not have MyISAM-tables, then key_buffer_size should be set at about 16-32 MB - they will be used to store in memory indexes of temporary tables created on disk.
  
• innodb_buffer_pool_size is an equally important setting, but for InnoDB, be sure to pay attention to it, if you are going to use mostly InnoDB tables, since they are significantly more sensitive to buffer size than MyISAM tables. MyISAM tables in principle can work well even with a large amount of data and with the standard value of key_buffer_size , however, mySQL can “slow down” if the innodb_buffer_pool_size is incorrect. InnoDB uses its buffer for storing both indexes and data, so there is no need to leave memory for the OS cache — set innodb_buffer_pool_size to 70-80% of the available RAM (if, of course, only InnoDB tables are used). Regarding the maximum size of this option - similar to key_buffer_size - you should 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 mySQL performance, but I recommend leaving about 20 MB for InnoDB (or a bit more) for various internal needs.
  
• innodb_log_file_size is an extremely important setting in the conditions of databases with frequent write operations to tables, especially for large volumes. Larger sizes increase the speed, but be careful - data recovery time will increase. I usually set a value around 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 on your system, or you are actively working with BLOB data, I recommend to slightly increase the value of innodb_log_buffer_size . But do not overdo it - too much value will be a waste of memory: the buffer is reset 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 small bases it is even less.
  
• innodb_flush_log_at_trx_commit - complain that InnoDB is 100 times slower than MyISAM? You probably forgot about the innodb_flush_log_at_trx_commit setting. The default value “1” means that each UPDATE transaction (or a similar command outside the transaction) must flush the buffer to the disk, which is quite resource intensive. Most applications, especially previously used MyISAM tables, will work well with a value of “2” (i.e., “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 risk losing data even if mySQL server crashes, while setting the value of innodb_flush_log_at_trx_commit to “2” you lose data only when the entire operating system crashes.
  
• table_cache - opening tables can be very resource intensive. For example, MyISAM tables mark the headers of .MYI files as "currently in use." It is usually not advisable to open tables too often, so it’s best to keep the cache large enough to keep all your tables open. For this, a certain amount of OS resources and RAM is used; 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 can 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 a mySQL server with a table_cache value of 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 equal to 16. If your application can have jumps in the number of competitive connections and the variable Threads_Created shows a rapid increase in the number of threads, then you should increase the value of thread_cache . The goal is to prevent the creation of new threads in the normal operation of the server.
  
• query_cache_size - if your application reads data a lot and does not have a cache at the application level, this option can help a lot. Do not put too much value here, as maintaining a large query cache will be expensive in itself. The recommended value is from 32 to 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, that is, 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 server hardware and mySQL engines used, while session variables are usually customized specifically for specific tasks. If you mostly use simple queries, then there is no need to increase the sort_buffer_size value, even if you have an 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 calculated 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 best fits your server load.