A concise guide to MySQL optimization

mysql| optimization

Optimizing MySQL at compile time
If you install MySQL from the source code, note that the compilation process will have an important impact on the performance of future target programs, different compilation methods may get similar target files, but performance may vary greatly, therefore, in the compilation and installation of MySQL adapt carefully according to your application type choose the most likely good compilation options. This custom MySQL can provide the best performance for your application.

Tip: Use better compilers and better compiler options, which can improve performance 10-30%. (as the MySQL document says)

1.1. Use PGCC (Pentium GCC) compiler
The compiler (http://www.goof.com/pcg/) is optimized for programs running on the Pentium processor system and compiles MySQL source code with PGCC, which can improve overall performance by 10%. Of course, if your server is not using a Pentium processor, you will not need to use it, because it is designed for the Pentium system.

1.2, only use the character set you want to compile MySQL
MySQL currently offers up to 24 different character sets for global users to insert or view data in their own language. But save the case, MySQL installs owner these character sets, hot however, the best choice is to choose one you need. For example, prohibit all other character sets except the Latin1 character set:

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

1.3. Compile the mysqld into a static execution file
You can get better performance by compiling mysqld into a static execution file without having to share a library. You can statically compile mysqld by specifying the following options at configuration time.

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

1.4. Configuration Sample
The following configuration commands are often used to improve performance:

--------------------------------------------------------------------------------
%>cflags= "-o6-mpentiumpro-fomit-frame-pointer" CXX=GCC cxxflags= "-o6-mpentiumpro-fomit-frame-pointer- Felide-constructors-fno-exceptions-fno-rtti "./configure--prefix=/usr/local--enable-assembler-- With-mysqld-ldflags=-all-static--disable-shared
--------------------------------------------------------------------------------

Second, adjust the server
It is important to make sure that the correct compilation is used, but this is only the first step in the success, and the configuration of many MySQL variables is also critical to the normal operation of the server. You can assign these variables to a configuration file to make sure they work every time you start MySQL, which is the my.cnf file.

MySQL has provided a sample of several 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, and the scale description can be found in the system type header that describes the configuration file. If you run MySQL on a system with fairly little memory, and only occasionally, then MY-SMALL.CNF is ideal because it commands mysqld to use only the least resources. Similarly, if you plan to build an E-commerce supermarket and the system has 2G of RAM, you might want to use mysql-huge.cnf files.

To make use of one of these files, you need to copy a file that is best suited to your needs, renamed MY.CNF. You can choose to use a profile of three kinds of scopes:

Global: Copy the my.cnf file to the server's/etc directory, which makes the variables in the configuration file work globally, which is valid for MySQL database servers on all servers.
Local: Copy the my.cnf file to the [mysql-install-dir]/var/directory, making the MY.CNF work on a specific server. [Mysql-install-dir] Represents the MYSQL installation directory.
User: You can limit the my.cnf to a specific user, and copy the user's root directory.
How do you set these variables in the my.cnf? Further said, you can set which variable. Although the variables used are relatively generic to the MySQL server, each variable has a more specific relationship to some of the components of MySQL. If the variable max_connects is grouped under the mysqld category. You can know by executing the following commands:

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

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

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

You can then set the variables in the MY.CNF as follows:

--------------------------------------------------------------------------------
Set-variable = max_connections=100
--------------------------------------------------------------------------------

It sets the maximum number of concurrent connections for the MySQL server to 100. Be sure to insert the variable settings under the [Mysqld] heading in the my.cnf file.
Third, table type

Many MySQL users may be surprised that MySQL does provide users with 5 different table types, known as DBD, HEAP, ISAM, merge, and Myiasm. The DBD is classified as a transaction security class, while the other is a non transactional security class.

3.1. Transaction security

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

3.2. Non-transactional security

HEAP

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

ISAM

The ISAM table is the default table type for earlier versions of MySQL until MYIASM is developed. It is recommended that you do not use it again.

MERGE

The merge is an interesting new type that appears after 3.23.25. A merge table is actually a collection of identical MyISAM tables, combined into a single table, mainly for efficiency reasons. This can improve speed, search efficiency, repair efficiency, and save disk space.

Myiasm

This is the default table type for MySQL. It is based on Iasm code, but there are many useful extensions. Myiasm is better than the reason:

The Myiasm table is less than the Iasm table, so fewer resources are used.
The Myiasm table can be ported on different platforms with binary layers.
Larger key code sizes, larger key code caps.
3.3. Specify table Type

You can specify the type of table when creating a table. The following example creates a heap table:

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

Mysql>create TABLE email_addresses Type=heap (
->email char () not NULL,
->name char () not NULL,
->primary KEY (email));

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

The BDB table requires some configuration work, see http://www.mysql.com/doc/B/D/BDB_overview.html.

3.4. More table Types

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

4. Optimization Tools

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

4.1, show

You may be interested to know what the MySQL server is all about, and the following commands give a summary:

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

It gives a fairly long list of state variables and their values. Some variables contain the number of terminated customers abnormally, the number of exceptions terminated, the number of connection attempts, the maximum number of concurrent connections, and a large number of other useful information. This information is valuable for identifying system problems and inefficiencies.
Show can do more things. It can display valuable information about log files, specific databases, tables, indexes, processes, and permission tables. See the MySQL Manual for details.

4.2, EXPLAIN

When you face a SELECT statement, explain explains how the Select command is handled. This is helpful not only for deciding whether an index should be added, but also for deciding how a complex join can be handled by MySQL.

4.3, OPTIMIZE

The Optimize statement allows you to recover space and merge data file fragments, which is particularly important after a large number of updates and deletions are made to the table that contains the variable-length rows. Optimize currently works only on myiasm and BDB tables.