First, optimize mysql when compiling if you install MySQL from source code, pay attention to the compilation process has important impact on future target programs, and different compilation methods may get similar target files, but performance may differ. Therefore, selecting the most likely compiled options based on your application type in compiling installation mysql. This customized MySQL can provide optimal performance for your application.
Tips: Use better compilers and better compiler options, which can improve performance 10-30%. (MySQL documentation is said)
1.1, use the PGCC (Pentium GCC) compiler (http://www.goof.com/pcg/) to run?.
1.2, using only the character sets you want to use Mysql MySQL currently available up to 24 different character sets, inserting or viewing data in their own language in their own language. So far, MySQL installs the owner of these character sets, and the best choice means that choosing a kind of you need. For example, all other character sets other than the Latin1 character set are prohibited:
-------------------------------------------------- ------------------------------%> ./ Configure -with-extra-charsets = none [- ot--configuration- Options] ------------------------------------------------ --------------------------------
1.3, compile MySQLD into a static execution file to compile mysqld into static execution file without having to share the library can also achieve better performance. You can still compile mysqld by specifying the following options when configuring the following options.
-------------------------------------------------- ------------------------------%> ./ Configure -with-mysqld-ldflags = -all-static [- oter -configuration-options] --------------------------------------------- -----------------------------------
1.4, Configuring Samples The following configuration commands are often used to improve performance:
-------------------------------------------------- ------------------------------%> cflags = "- o6 -mpenuration -fomit-frame-point" CXX = GCC CXXFLAGS = "- O6 -MPENTIUMPRO -FOMITORS-FNO-Exceptions -fno-RTTI" ./configure --prefix = / usr / local --enable-assembler --with-mysqld-ldflags = - All-static --disable-shared -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Second, the adjustment server ensures that the correct compilation is important, but this is just the first step in success, configuring a large number of MySQL variables, the same role in running the server. You can exist of these variables in a configuration file to make sure they work every time my mysql is started, this configuration file is my.cnf file. MySQL has provided samples for 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 the system that only quite less memory, but only occasionally use, My-Small.cnf is ideal because it commands Mysqld only uses the least resource. Similarly, if you plan to build an e-commerce supermarket, and the system has 2G memory, then you may have to use the mysql-huge.cnf file.
In order to use one of these files, you need to copy a file that best sufficient demand, rename my.cnf. You can choose one of the three scopes of configuration file:
Global: Copy the My.cnf file into the server / etc directory, which makes the variables in the configuration file to the global, which is valid for the MySQL database server 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. [MySQL-INSTALL-DIR] represents the mysql installation directory. User: You can restrict the use of specific users, copy my.cnf to the user's root directory. How to set these variables in my.cnf? Furthermore, which variable you can set. Although the variables used are relatively universal to the MySQL server, each variable has a more specific relationship with some components of MySQL. If the variable max_connects is at MySQLD category. Execute the following command to know:
-------------------------------------------------- ------------------------------%> / usr / local / mysql / libexec / mysqld --help ----- -------------------------------------------------- -------------------------
It shows a large number of options and variables associated with MySQLD. You can easily find the variable under the 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 concurrent connection of the MySQL server is 100. To ensure that the variable settings are inserted under the [MySQLD] header in my.cnf file. Third, table type
Many MySQL users may be surprised that mysql does provide users with 5 different table types, called DBD, HEAP, ISAM, MERGE, and Myiasm. DBD is classified as a transaction security class, while others are non-transaction security classes. 3.1, transaction security
The DBD Berkeley DB (DBD) table is a table that supports transaction processing, developed by Sleepycat Software (http://www.sleepycat.com). It provides a long-awaited feature-transaction control in Mysql users. Transaction Control is a very valuable feature in any database system because they ensure that a set of commands can be executed successfully.
3.2, non-transaction safety
HEAP
The HEAP table is the fastest table of data in MySQL. This is because they use a hash index stored in dynamic memory. Another point is if the mysql or server crashes, the data will be lost.
ISAM
The ISAM table is the default table type of the early mysql version until myiaSM is developed. It is recommended not to use it anymore.
Merge
Merge is an interesting new type, which appears after 3.23.25. A MERGE table is actually a collection of same Myisam tables, combined into a table, mainly for efficiency. This improves speed, search efficiency, repair efficiency and saves disk space.
Myiasm
This is my mysql's default table type. It is based on IASM code, but there are many useful extensions. Reason for MyIASM:
The Myiasm table is less than the IASM table, so use fewer resources. The Myiasm table can be portable on the binary layer on different platforms. A larger key code size, a larger key code upper limit. 3.3, specify table type
You can specify the type of the table when you create a table. The following example creates a HEAP table:
-------------------------------------------------- ------------------------------
MySQL> CREATE TABLE Email_Addresses Type = Heap (-> Email Char (55) Not NULL, -> Name Char (30) Not Null, -> Primary Key (email);
-------------------------------------------------- ------------------------------
The BDB table requires some configuration, see http://www.mysql.com/doc/b/d/bdb_overview.html.
3.4, more table type
In order to make MySQL management more interesting, the upcoming Mysql 4.0 will provide two new table types, called INNOBASE and GEMENI.
4, optimization tool
The MySQL server itself provides several built-in commands to help optimize.
4.1, show
You may be interested in knowing what the mysql server is more, the following command gives a summary:
-------------------------------------------------- ------------------------------ MySQL> show status; --------------- -------------------------------------------------- ---------------
It gives a quite long state variable and a list of its values. Some variables contain an abnormality to terminate the number of customers, the number of abnormal termination connections, the number of connected attempts, the maximum concurrent connection, and a large number of other useful information. This information is to find system problems and inefficient value. SHOW can make more things. It can display valuable information about log files, specific databases, tables, index, process, and permission tables. See the mysql manual for details.
4.2, explain
When you face the SELECT statement, Explain explains how the select command is processed. This is not only helping to decide whether to add an index, but also how to determine how complex Join is handled by MySQL. 4.3, Optimize
The Optimize statement allows you to recover space and merge data file debris, which is especially important to make a lot of updates and delete the table containing the growing line. Optimize is currently only working in Myiasm and BDB tables.
