MySQL Optimization Guide

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.
III. Table type

Many MySQL users may be surprised that MySQL does provide users with 5 different table types, such as DBD, HEAP, ISAM, MERGE, and MyIASM. DBD is classified as transaction security, while others are non-transaction security.

3.1. Transaction security

DBD
The Berkeley DB (DBD) table is a table that supports transaction processing, developed by Sleepycat (http://www.sleepycat.com. It provides the long-awaited MySQL feature-transaction control. Transaction control is a very valuable feature in any database system, because they ensure that a group of commands can be successfully executed.

3.2 Non-transaction security

HEAP

The HEAP table is the fastest table to access data in MySQL. This is because they use a hash index stored in dynamic memory. Another key point is that if MySQL or the server crashes, data will be lost.

ISAM

ISAM tables are the default table types of earlier MySQL versions until MyIASM is developed. We recommend that you do not use it any more.

MERGE

MERGE is an interesting new type that appears after 3.23.25. A merge table is actually a set of identical MyISAM tables and is merged into a table mainly for efficiency reasons. This improves speed, search efficiency, repair efficiency, and saves disk space.

MyIASM

This is the default table type of MySQL. It is based on IASM code, but there are many useful extensions. Why is MyIASM better:

MyIASM tables are smaller than IASM tables, so fewer resources are used.
MyIASM tables can be transplanted at the binary layer on different platforms.
Larger key size, larger key size limit.
3.3 specify the table type

You can specify the table type when creating a table. In the following example, create a HEAP table:


--------------------------------------------------------------------------------

Mysql> create table email_addresses TYPE = HEAP (
-> Email char (55) not null,
-> Name char (30) not null,
-> Primary key (email ));


--------------------------------------------------------------------------------

BDB tables require some configuration work. For more information, see http://www.mysql.com/doc/ B /d/bdb_overview.html.

3.4. More table types.

To make MySQL management more interesting, MySQL 4.0 will provide two new table types, Innobase and Gemeni.

4. Optimization tools

The MySQL server itself provides several built-in commands to help with optimization.

4.1. SHOW

You may be interested in knowing what the MySQL server actually has. The following command provides a summary:


--------------------------------------------------------------------------------
Mysql> show status;
--------------------------------------------------------------------------------

It provides a fairly long list of state variables and their values. Some variables include the number of customers with exceptional termination, the number of connections with exceptional termination, the number of connection attempts, the maximum number of concurrent connections, and a large amount of other useful information. This information is of great value for identifying system problems and inefficiency.
SHOW can do more. It can display valuable information about log files, specific databases, tables, indexes, processes, and permission tables. For more information, see the MySQL manual.

4.2. EXPLAIN

When you are facing a SELECT statement, EXPLAIN how the SELECT command is processed. This is not only helpful for deciding whether to add an index, but also for determining how a complicated Join is processed by MySQL.

4.3 OPTIMIZE

The OPTIMIZE statement allows you to restore space and merge data file fragments. This is especially important after a large number of updates and deletions are performed on tables that contain long rows. OPTIMIZE currently only works in MyIASM and BDB tables