Home  >  Article  >  Backend Development  >  MySQL Optimization Guide - Server Parameter Adjustment_PHP Tutorial

MySQL Optimization Guide - Server Parameter Adjustment_PHP Tutorial

WBOY
WBOYOriginal
2016-07-13 17:00:58786browse

Correct compilation method is important, but it is only part of the work to improve the performance of MySQL server. Many parameters of the MySQL server will affect the performance of the server, and we can save these parameters to the configuration file so that these parameters will automatically take effect every time the MySQL server starts. This configuration file is my.cnf.
 
MySQL server provides several examples of my.cnf files, which can be found in the /usr/local/mysql/share/mysql/ directory, and their names are my-small.cnf and my-medium. cnf, my-large.cnf and my-huge.cnf. The size specification in the file name describes the type of system to which the configuration file applies. For example, if the system running the MySQL server does not have a lot of memory and MySQL is only used occasionally, then using the my-small.cnf configuration file is ideal. This configuration file tells the mysqld daemon to use the least amount of system resources. On the other hand, if the MySQL server is used to support a large-scale online shopping mall and the system has 2G of memory, then using mysql-huge.cnf is most appropriate.

To use the above example configuration file, we should first copy a configuration file that best suits the requirements and name it my.cnf. This copied configuration file can be used in the following three ways:
Globally: Copy this my.cnf file to the /etc directory of the server. At this time, the parameters defined in the file will be globally valid, that is, on the server All MySQL database servers running are valid.

Partial: Copying this my.cnf file to [MYSQL-INSTALL-DIR]/var/ will make the file only valid for the specified server, where [MYSQL-INSTALL-DIR] represents the directory where MySQL is installed. .

User: Finally, we can also limit the scope of the file to specified users. This simply requires copying the my.cnf file to the user's root directory.

So, how to set the parameters in the my.cnf file? Or furthermore, what parameters can we set? All of these parameters have a global impact on the MySQL server, but at the same time each parameter is closely related to a specific part of MySQL. For example, the max_connections parameter belongs to the mysqld category. So how do you know this? To do this, just execute the following command:
% >/usr/local/mysql/libexec/mysqld --help

  This command will display various options and parameters related to mysqld. It is very convenient to find these parameters, because these parameters are after the line "Possible variables for option --set-variable (-O) are". After finding these parameters, we can set all these parameters in the my.cnf file as follows:
set-variable = max_connections=100

The effect of this line of code is: connect to the MySQL server at the same time The maximum number of connections is limited to 100. Don’t forget to add a set-variable directive in the [mysqld] section of the my.cnf file. For details, see the example

in the configuration file.

www.bkjia.comtruehttp: //www.bkjia.com/PHPjc/631192.htmlTechArticleThe correct compilation method is important, but it is only part of the work to improve the performance of MySQL server. Many parameters of the MySQL server will affect the performance of the server, and we can put this...
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn