LAMP server performance optimization tips for Mysql optimization

Apache server optimization, PHP optimization, Mysql optimization

The two most popular backend databases for application developers are MySQL and SQL Server. The fundamental similarity between the two is the data store and the query system. If you want to build a.NET server system that can access data from many different platforms and participate in database management, then you can use SQL Server server. If you want to build a third-party dynamic web site from which data can be read from several clients, MySQL is a good choice.

1, compile and install MySQL

By picking the best possible compiler for your system, you can typically get a 10-30% performance improvement. On the Linux/Intel platform, MySQL is compiled with pgcc (pentium chip optimized version of gcc). However, the binary will only run on Intel pentium CPU. For a particular platform, use the optimizations recommended in the MySQL reference manual. Compile MySQL with the character set you will use. Static compilation generates the mysqld execution file (with -- with-mysqld-ldflags = all-static) and the strip sql/mysqld collates the final execution file. Note that since MySQL does not use C++ extensions, compiling MySQL without extension support will gain a huge performance boost.

2, optimization table

MySQL has a rich set of types. You should try to use the most efficient type for each column. The ANALYSE procedure helps you find the optimal type of table: SELECT * FROM table_name PROCEDURE ANALYSE(). Use NOT NULL for columns that don't store NULL values, which is especially important for columns you want to index. Change the table of type ISAM to MyISAM. Create a table in a fixed table format.

3. Use the index

correctly

Indexes are used to quickly find records with specific values, and all MySQL indexes are stored as B-trees. If there is no index, MySQL must scan all records of the entire table from the first record when the query is executed until it finds the one that meets the requirements. If the table has 1000 records, finding records through the index is at least 100 times faster than sequential scanning.

Suppose we create a table called people:

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

We then inserted 1000 different name values into the people table at completely random.

But indexes have their drawbacks. First, indexes take up disk space. Usually, this problem is not very prominent. However, if you create indexes for every possible combination of columns, the volume of index files will grow much faster than that of data files. 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, indexes are not used. In addition, for operations that require data to be written, such as DELETE and UPDATE, the index slows them down. This is because MySQL not only writes the changes to the data file, but it also writes the changes to the index file.

4, reduce the use of character set compilation MySQL

MySQL currently provides up to 24 different character sets (in many languages) for users worldwide to insert or view data in tables in their own languages. By default, MySQL installs the owner of these character sets, so the best option is to install one or two that you need (Chinese, English).

Summary: this article introduces how to improve the system performance under the existing hardware conditions, network administrators fully understand their own computers and networks, so as to find the real bottleneck. In these days of budget shortfalls, it is more important than ever to understand how to optimize system performance. Hardware investment alone is not an acceptable solution, and it may not work.

That's the end of the LAMP server performance tuning tip. Is your LAMP server optimized yet?