MySQL Optimization Guide

Simple Guide to MySQL optimization (1. optimizing MySQL during compilation

If you distribute and install MySQL from the source code, note that the compilation process will have an important impact on the future target program. different compilation methods may obtain similar target files, however, the performance may vary greatly. Therefore, when compiling and installing MySQL, you must carefully select the most likely compilation option based on your application type. This custom MySQL can provide the best performance for your applications.

Tip: select a good compiler and a good compiler option, so that the application can improve performance by 10-30%. (MySQL document)

1.1 Use the pgcc (Pentium GCC) compiler

The compiler (http://www.goof.com/pcg/) optimizes programs running on the Pentium processor system and compiles MySQL source code with pgcc, improving the overall performance by 10%. Of course, if your server does not use a Pentium processor, you do not need to use it because it is designed for the Pentium system.

1.2 compile MySQL using only the character set you want to use

MySQL currently provides up to 24 different character sets for global users to insert or view data in tables in their own language. However, the best choice is to select a character set that you need. For example, disable all character sets except Latin1:

%>./Configure-with-extra-charsets = none [-- other-configuration-options]

1.3 Compile mysqld into a static execution file.

Compiling mysqld into a static execution file can achieve better performance without sharing the database. You can statically compile mysqld by specifying the following options during configuration.

%>./Configure-with-mysqld-ldflags =-all-static [-- other-configuration-options]

1.4 configuration samples

The following configuration commands are commonly used to improve performance:

%> CFLAGS = "-O6-mpentiumpro-fomit-frame-pointer" CXX = gcc CXXFLAGS = "-O6-mpentiumpro-fomit-frame-pointer-felide-constructors-fno-restrictions tions -fno-rtti ". /configure -- prefix =/usr/local -- enable-validator -- with-mysqld-ldflags =-all-static -- disable-shared

II. server adjustment

It is important to ensure correct compilation, but this is only the first step of success. configuring many MySQL variables also plays a key role in the normal operation of the server. You can assign values to these variables in a configuration file to make sure they take effect every time you start MySQL. this configuration file is my. cnf.

MySQL has provided several samples of my. cnf files, which can be found in the/usr/local/mysqld/share/mysql/directory. These files are named my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf, which can be found in the title of the system type that describes the configuration file to apply. If you run MySQL on a system with only a small amount of memory and only use it occasionally, the my-small.cnf would be ideal because it commands mysqld to use only the fewest resources. Similarly, if you plan to build an e-commerce supermarket and the system has 2 GB of memory, you may need to use mysql-huge.cnf files.

To use one of these files, you need to copy the most suitable file and change it to my. cnf. You can choose one of the following three scopes:

Global: Copy the my. cnf file to the/etc directory of the server, so that the variables in the configuration file apply to the Global environment, that is, they are valid for MySQL database servers on all servers.

Local: copy the my. cnf file to the [MYSQL-INSTALL-DIR]/var/directory so that my. cnf acts on a specific server. The [MYSQL-INSTALL-DIR] represents the MySQL installation directory.

User: you can restrict specific users to copy my. cnf to the User's root directory.

How can I set these variables in my. cnf? Further, you can set the variable. Although the variables used are relatively common to MySQL servers, each variable has a more specific relationship with some components of MySQL. For example, the variable max_connects belongs to the mysqld category. Run the following command:

%>/Usr/local/mysql/libexec/mysqld -- help

It displays a large number of options and variables related to mysqld. You can easily find the variable under this line of text:

Possible variables for option -- set-variable (-O) are

Then you can set the variables in my. cnf as follows:

Set-variable = max_connections = 100

It sets the maximum number of concurrent connections of the MySQL server to 100. Make sure to insert variable settings under the [mysqld] title in the my. cnf file.
I. optimizing MySQL during compilation

If you distribute and install MySQL from the source code, note that the compilation process will have an important impact on the future target program. different compilation methods may obtain similar target files, however, the performance may vary greatly. Therefore, when compiling and installing MySQL, you must carefully select the most likely compilation option based on your application type. This custom MySQL can provide the best performance for your applications.

Tip: select a good compiler and a good compiler option, so that the application can improve performance by 10-30%. (MySQL document)

1.1 Use the pgcc (Pentium GCC) compiler

The compiler (http://www.goof.com/pcg/) optimizes programs running on the Pentium processor system and compiles MySQL source code with pgcc, improving the overall performance by 10%. Of course, if your server does not use a Pentium processor, you do not need to use it because it is designed for the Pentium system.

1.2 compile MySQL using only the character set you want to use

MySQL currently provides up to 24 different character sets for global users to insert or view data in tables in their own language. However, the best choice is to select a character set that you need. For example, disable all character sets except Latin1:

%>./Configure-with-extra-charsets = none [-- other-configuration-options]

1.3 Compile mysqld into a static execution file.

Compiling mysqld into a static execution file can achieve better performance without sharing the database. You can statically compile mysqld by specifying the following options during configuration.

%>./Configure-with-mysqld-ldflags =-all-static [-- other-configuration-options]

1.4 configuration samples

The following configuration commands are commonly used to improve performance:

%> CFLAGS = "-O6-mpentiumpro-fomit-frame-pointer" CXX = gcc CXXFLAGS = "-O6-mpentiumpro-fomit-frame-pointer-felide-constructors-fno-restrictions tions -fno-rtti ". /configure -- prefix =/usr/local -- enable-validator -- with-mysqld-ldflags =-all-static -- disable-shared

II. server adjustment

It is important to ensure correct compilation, but this is only the first step of success. configuring many MySQL variables also plays a key role in the normal operation of the server. You can assign values to these variables in a configuration file to make sure they take effect every time you start MySQL. this configuration file is my. cnf.

MySQL has provided several samples of my. cnf files, which can be found in the/usr/local/mysqld/share/mysql/directory. These files are named my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf, which can be found in the title of the system type that describes the configuration file to apply. If you run MySQL on a system with only a small amount of memory and only use it occasionally, the my-small.cnf would be ideal because it commands mysqld to use only the fewest resources. Similarly, if you plan to build an e-commerce supermarket and the system has 2 GB of memory, you may need to use mysql-huge.cnf files.

To use one of these files, you need to copy the most suitable file and change it to my. cnf. You can choose one of the following three scopes:

Global: Copy the my. cnf file to the/etc directory of the server, so that the variables in the configuration file apply to the Global environment, that is, they are valid for MySQL database servers on all servers.

Local: copy the my. cnf file to the [MYSQL-INSTALL-DIR]/var/directory so that my. cnf acts on a specific server. The [MYSQL-INSTALL-DIR] represents the MySQL installation directory.

User: you can restrict specific users to copy my. cnf to the User's root directory.

How can I set these variables in my. cnf? Further, you can set the variable. Although the variables used are relatively common to MySQL servers, each variable has a more specific relationship with some components of MySQL. For example, the variable max_connects belongs to the mysqld category. Run the following command:

%>/Usr/local/mysql/libexec/mysqld -- help

It displays a large number of options and variables related to mysqld. You can easily find the variable under this line of text:

Possible variables for option -- set-variable (-O) are

Then you can set the variables in my. cnf as follows:

Set-variable = max_connections = 100

It sets the maximum number of concurrent connections of the MySQL server to 100. Make sure to insert variable settings under the [mysqld] title in the my. cnf file.