LAMP server performance optimization tips-Mysql Optimization

Apache server optimization, PHP optimization, and Mysql Optimization

For program developers, the two most popular backend databases are MySQL and SQL Server. The most basic similarity between the two is data storage and query system. If you want to establish a. NET Server System, which allows you to access data from multiple different platforms and manage databases, you can select the SQL Server. If you want to create a third-party dynamic website from which you can read data from some clients, MySQL will be a good choice.

1. Compile and install MySQL

By selecting the best possible compiler in your system, you can generally achieve a performance improvement of 10-30%. On Linux/Intel Platform, use pgcc (gcc Pentium chip optimized version) to compile MySQL. However, binary code can only run on Intel Pentium CPU. For a specific platform, use the optimization options recommended in the MySQL reference manual. Compile MySQL with the character set you will use. Static compilation generates the mysqld execution file (use -- with-mysqld-ldflags = all-static) and uses strip SQL/mysqld to organize the final execution file. Note that since MySQL does not use C ++ extensions, compilation of MySQL without extensions won huge performance improvements.

2. Optimization table

MySQL has a set of rich types. Try to use the most effective type for each column. The ANALYSE process helps you find the optimal table Type: SELECT * FROM table_name procedure analyse (). Not null is used for columns that do NOT store NULL values. This is especially important for the columns you want to index. Change the ISAM type table to MyISAM. Use a fixed table to create a table.

3. Correct Use of Indexes

Indexes are used to quickly search for records with specific values. All MySQL indexes are saved as B-trees. If no index exists, MySQL must scan all the records of the entire table from the first record until the required records are found. If the table has 1000 records, the index search records should be at least 100 times faster than the Sequential Scan records.

Suppose we have created a table named "people:

 
 

  
  
CREATE TABLE people ( peopleid SMALLINT NOT NULL, name CHAR(50) NOT NULL ); 
 
 

Then, we randomly insert 1000 different name values into the people table.

However, indexes also have disadvantages. First, indexes occupy disk space. Generally, this problem is not very prominent. However, if you create an index for each possible combination of columns, the size of the index file will grow much faster than that of the data file. If you have a large table, the size of the index file may reach the maximum file size allowed by the operating system. If MySQL can predict that it will be faster than scanning the entire table, no index will be used. In addition, for operations that require data writing, such as DELETE and UPDATE operations, indexes will reduce their speed. This is because MySQL not only writes the changed data to the data file, but also writes the changes to the index file.

4. Reduce the use of character sets to compile MySQL

MySQL currently provides up to 24 different character sets (many language versions), allowing global users to insert or view data in tables in their own language. By default, MySQL installs the character sets of the owner, so the best option is to install either of the two types you need (Chinese and English ).

Summary: This article describes how to improve system performance with existing hardware conditions. network administrators fully understand their computers and networks to find the real bottleneck. In today's budget shortage, understanding how to optimize system performance is more important than ever. Blindly investing in hardware is not a way to make people accept it, and it does not take effect.

The above is the LAMP server performance optimization technique. This article ends. Is your LAMP server optimized?