MySQL Optimization Concise Guide http://clyan.hongnet.com 子 整 ((2001-05-16 05:15:00)
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 document is said) 1.1, using the PGCC (Pentium GCC) compiler (http://www.goof.com/pcg/) Optimize the program running on the Pentium processor system, compile MYSQL with PGCC Source code, overall performance can be increased by 10%. Of course, if your server is not using a Pentium processor, you don't have to use it because it is designed for the Pentium system. 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-chars = none [- Her-Configuration-Options] ---------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------- 1.3, put mysqld Compiling into a static execution file to compile mysqld into static execution file without having to share libraries 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, the following configuration commands are often used to improve performance: --- -------------------------------------------------- ---------------------------%> cflags = "- O6 -MPENTIUMPRO -FOMIT-FRAME-POINTER" CXX = GCC CXXFLAGS = "- O6 -MPENTIUMPRO -FOMITORS-FNO-Exceptions -fno-rtti "./configure --prefix = / usr / local --enable-assembler --with-mysqld-ldflags = -ll-static --Disable-shared ------------------------------------------------------------------------------------------------------- ----------------------------------- Second, adjust the server to ensure that the correct compilation is important, but this is just The first step in success, configuring a large number of mysql variables, which works on the normal operation of 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 scope of the configuration file: Global: Copy the My.cnf file to the server / etc directory, which makes the variables in the configuration file to the global, namely the mysql database server on all servers. effective. 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 commands you can know: ----------------------------------------- -------------------------------------%> / usr / local / mysql / libexec / mysqld - -help ---------------------------------------------------------------------------------------------------------------------------- ------------------------------- It shows a lot 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 those in my.cnf as follows Variable: ------------------------------------------------ ------------------------------- 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, the type type Many mysql users may be surprised that mysql does provide five 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 The Transaction Security 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 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. The ISAM 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 the default table type of MySQL. It is based on IASM code, but there are many useful extensions. MyiaSM is a better reason: MyiaSM table is less than IASM table, so less resources are used. 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 table when you create a table.
