Monty says MySQL optimization (2)

This article is the second Monty speech at the o'reilly Open Source Convention 2000 conference, this article describes how to optimize applications, use portable applications, speed up, optimize MySQL, compile and install MySQL.

VI,Optimize applications

Focus on solving the problem.
When writing an application, you should decide what is the most important:
Speed
Portability between operating systems
Portability between SQL servers
Use Continuous connections ..
Cache data in the application to reduce the load on the SQL server.
Do not query unnecessary columns in the application.
Do not use SELECT * FROM table_name...
Test all parts of an application, but focus most of the effort on testing the overall application under the potentially worst-case load. By using a modular approach, you should replace the bottleneck with a fast "dumb module", and easily mark the next bottleneck.
If you make a large number of modifications in a batch, use lock tables. For example, you can combine multiple UPDATES or DELETES.

VII,Portable applications should be used

Perl DBI/DBD
ODBC
JDBC
Python (or other languages with common SQL interfaces)
You should only use SQL structures that exist in all target SQL servers or can easily use other constructor to simulate SQL structures. The Crash-me page on www.mysql.com can help you.
Write a package program for the operating system or SQL server to provide missing functions.

8. If you needFaster, You should:

Identify bottlenecks (CPU, disk, memory, SQL Server, operating system, API, or application) and focus on solving them.
It provides you with faster speed/flexibility scaling.
Gradually understand the SQL server so that you can use the fastest possible SQL structure for your problems and avoid bottlenecks.
Optimize table layout and query.
Use replication for faster select speeds.
If you have a slow network connection to the database, use the compressed Client/Server protocol.
Don't be afraid that the first version of the application cannot be transplanted perfectly. When you solve the problem, you can always optimize it later.

IX,Optimize MySQL

Select the compiler and compilation options.
Find the best startup options for your system.
Read MySQL reference manual and Paul DuBios's MySQL book. (The Chinese version already exists)
Use explain select, show variables, show status, and show processlist.
Understand how the query optimizer works.
Optimize the table format.
Maintain your TABLE (myisamchk, check table, optimize table)
Use the MySQL extension function to complete everything quickly.
If you notice that you will need some functions in many cases, write the MySQL UDF function.
Do not use table-level or column-level GRANT unless you do need it.
Purchase MySQL technical support to help you solve the problem :)

10,Compile and install MySQL

By selecting the best possible compiler for 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.
Generally, native compilers for a specific CPU (such as the Sun Workshop of the iSCSI) should provide better performance than gcc, but this is not always the case.
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.
If the operating system supports native threads, use native threads (instead of mit-pthreads ).
Use the MySQL benchmark to test the final binary code.