Very detailed MySQL database performance optimization cache parameter settings

The website runs on the Ali Cloud, 1G memory, Php7+php-fpm+nginx+mariadb+redis all installs on a server, but the website visit Quantity Day also has 500IP, not many, but also caused a little pressure, just put up a few days database will often hang out, This is why you need to set some parameters to review the performance optimization of the database.

In either case, caching technology is a key technology to improve database performance, and the access speed of physical disks will always be the same as the speed of memory access is never an order of magnitude. Caching technology can greatly improve the overall performance of the database both in reading and writing.

MARIADB Configuration file Modification

Configuration file path:/ETC/MY.CNF.D/SERVER.CNF, modify the following, add Innodb_buffer_pool_size and max_allowed_packet two parameters, temporarily resolved the database will be dead.

# This is the Mysqld standalone daemon
[Mysqld]
Log_error=/var/log/mariadb_error.log
innodb_buffer_pool_size=164m
Max_allowed_packet = 10M
Innodb_buffer_pool_size parameters

The biggest difference between the caching mechanism of the INNODB storage engine and the MyISAM is that InnoDB not only caches the index, but also caches the actual data. So, the exact same database, using the InnoDB storage engine, can use more memory to cache database-related information, assuming there is enough physical memory. This is certainly an attractive feature in an era of declining memory prices.

The innodb_buffer_pool_size parameter is used to set the InnoDB most important buffer (Innodb_buffer_pool) size, which is the most important cache space for caching user tables and indexed data, and has the greatest impact on InnoDB overall performance. Whether it's an official MySQL manual or a lot of people on the Web sharing InnoDB optimization advice, if it's a dedicated server, it can be set up between the 50%~80% of the entire system's physical memory.

Max_allowed_packet parameters

If an SQL statement is very long or contains BLOB data, it can cause a memory overflow. Add or modify the following variables in the above configuration file:

Max_allowed_packet = 10M (You can also set the size you want)

The function of the Max_allowed_packet parameter is to control the maximum length of its communication buffer.

Some of the other configuration parameters

Innodb_buffer_pool_size

If you use InnoDB, then this is an important variable. Compared to MyISAM, InnoDB is more sensitive to buffer size. Mysiam may also be fine with the default key_buffer_size for large amounts of data, but InnoDB feel like they're crawling with the default values when it comes to large amounts of data. The InnoDB buffer pool caches data and indexes, so there is no need to leave room for the system's cache, which can be set to 70%-80% of memory, if only with InnoDB. The same as Key_buffer, if the amount of data is small and not increase, then do not set this value too high can improve memory usage.

Innodb_additional_pool_size

The effect is not obvious, at least when the operating system is able to allocate memory properly. But you may still need to set it to 20M or more to see how much memory InnoDB will allocate for other purposes.

Innodb_log_file_size

It's important to write a lot, especially when it comes to large amounts of data. Note that large files provide higher performance, but will take more time to recover from the database. I usually use 64m-512m, depending on the server space.

Innodb_log_buffer_size

The default value is available for most middle write operations and for short transaction use. You should increase this value if you often make updates or use a lot of BLOB data. But too big is also a waste of memory, because 1 seconds will always flush (the word in Chinese how to say?) ) Once, so there is no need to set the requirement to more than 1 seconds. 8m-16m generally should be enough. Small use can be set to a smaller point.

Innodb_flush_log_at_trx_commit (This works well)

Complaining that InnoDB is 100 times times slower than MyISAM? So you probably forgot to adjust the value. The default value of 1 means that every instruction outside the transaction commit or transaction needs to write the log to the (flush) hard disk, which is time consuming. Especially when using battery-powered caching (Battery backed up cache). Set to 2 for many applications, especially from the MyISAM table, it means that the system cache is written without writing to the hard disk. The log still flush to the hard drive every second, so you won't normally lose more than 1-2 seconds of updates. Set to 0 will be faster, but the security aspect is poor, even if MySQL hangs may lose the transaction data. A value of 2 only loses data when the entire operating system hangs.