MySQL optimization _linux of lamp server performance optimization techniques

Apache Server Optimization ,PHP optimization , mysql optimization

For program developers, the two most popular background databases are MySQL and SQL Server. The basic similarity between the two is the data storage and the query system. If you want to build a. NET Server system, which can access data from many different platforms and participate in database management, then you can choose SQL Server server. If you want to build a third-party dynamic site, from this site can read data from some clients, then MySQL will be a good choice.

1. Compile and install MySQL

By selecting the best possible compiler for your system, you can usually get 10-30% performance improvements. On the Linux/intel platform, compile MySQL with PGCC (GCC's Pentium chipset optimized edition). However, binary code will only run on Intel Pentium CPUs. 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 mysqld execution files (with--with-mysqld-ldflags=all-static) and uses the strip sql/mysqld to organize the final execution files. Note that since MySQL does not use C + + extensions, compiling MySQL without extended support will win tremendous performance gains.

2. Optimization table

MySQL has a rich set of types. You should try to use the most efficient type for each column. The analyse process can help you find the best type of table: SELECT * FROM table_name PROCEDURE analyse (). Using NOT NULL for columns that do not save null values is especially important for the columns you want to index. Change the table of type ISAM to MyISAM. Create a table in a fixed table format.

3, the correct use of the index

Indexes are used to quickly look for records with a specific value, and all MySQL indexes are saved as a B-tree. Without an index, MySQL must begin scanning all records of the entire table from the first record until a record is found that meets the requirements. If the table has 1000 records, the index lookup record is at least 100 times times faster than the sequential scan record.

Let's say we created a table named people:

 
  
  

  
   
   
CREATE TABLE people (Peopleid SMALLINT not NULL, name CHAR () is not null);
 
  
  

Then we completely randomly insert 1000 different name values into the People table.

But indexes also have drawbacks, first of all, the index takes up disk space. Usually, this problem is not very prominent. However, if you create an index for each possible combination of columns, the index file volume will grow much faster than the data file. If you have a large table, the size of the index file may reach the maximum file limit allowed by the operating system. If MySQL can estimate that it will probably be faster than scanning the entire table, then the index is not used. In addition, indexes reduce their speed for operations that need to write data, such as delete or update operations. This is because MySQL not only writes the change data to the data file, but it also writes the changes to the index file.

4, reduce the use of character set to compile MySQL

MySQL currently offers up to 24 different character sets (many language versions) to insert or view data from a table in their own language for global users. By default, MySQL installs the owner of these character sets, so the best option is to install one or two kinds you need on it (Chinese, English).

Summary: This article describes how to improve the performance of the system under the existing hardware conditions, network administrators fully understand their computers and networks, so as to find the real bottleneck. In today's budget crunch, understanding how to optimize system performance is more important than ever. Investing in hardware is not a way to get people to accept it, and it's not necessarily effective.

The above is the Lamp server performance optimization technique, the article ends here. Is your lamp server optimization good?