MySQL optimization (1)

1. What can and should we optimize?

Hardware

Operating System/software library

SQL Server (set and query)

Application Programming Interface (API)

Applications

------------------------------------------------------------

Ii. Optimize hardware

If you need a huge database table (> 2 GB), you should consider using a 64-bit hardware structure, such as alpha, or the upcoming IA64. Because MySQL uses a large number of 64-bit integers internally, the 64-bit CPU will provide better performance.

For large databases, the optimized order is generally Ram, fast hard disk, and CPU capacity.

More memory by storing the most commonly used keycode pages in the memory, you can speed up key code updates.

If you do not use a transaction-safe table or a large table and want to avoid long file check, a UPS can safely shut down the system when a power failure occurs.

For a system with data inventory placed on a dedicated server, consider 1 GB
. Latency and throughput are equally important.

----------------------------------------------------------------

Iii. Disk Optimization

A dedicated disk is provided for the system, program, and temporary files. If a lot of modifications are made, update logs and transaction logs are placed on the dedicated disk.
Low track time is very important for database disks. For large tables, You can estimate that you will need log (number of rows)/log (index block length/3*2/(key code length + Data Pointer length )) + a row can be found only once. For a table with 500000 rows, You need to search for columns of the Mediun int type by log (500000)/log (1024/3*2/(3 + 2) + 1 = 4. The above index requires 500000*7*3/2 =
5.2 M space. In fact, most of the blocks will be cached, so we only need to find them one or two times.
However, for writing (as shown above), you will need four seek requests to find where to store the new key code. Generally, you need two seek requests to update the index and write a row.
For a very large database, your application will be limited by the disk seek speed. As the data volume increases, your application will increase at the N log N data level.
Divide databases and tables on different disks. In MySQL, you can use Symbolic Links for this purpose.
Disk (RAID 0) increases the Read and Write throughput.
A column with an image (RAID 0 + 1) is safer and increases the read throughput. The write throughput is reduced.
Do not use an image or RAID (except RAID 0) on the disk where temporary files or data that can be easily recreated are located ).
In Linux, run the hdparm-m16-d1 command on the disk during boot to enable simultaneous read/write of multiple sectors and DMA functions. This increases the response time by 5 ~ 50%.
In Linux, use async (default) and noatime to mount a disk ).
For some specific applications, memory disks can be used for some specific tables, but this is usually not required.

---------------------------------------------------------------

4. Optimize the Operating System

Do not swap areas. If the memory is insufficient, add more memory or configure your system to use less memory.
Do not use an NFS disk ).
Increase the number of files opened by the system and MySQL server. (Add ulimit-n # To the safe_mysqld script #).
Increase the number of processes and threads in the system.
If you have a relatively small number of large tables, tell the file system not to break files into different channels (Solaris ).
Use a file system (Solaris) that supports large files ).
Select the file system to use. On Linux, Reiserfs is very fast for opening, reading and writing. File Check takes only a few seconds.

----------------------------------------------------------------

5. Select the Application Programming Interface

PERL
It can be transplanted between different operating systems and databases.
Suitable for rapid prototyping.
You should use the DBI/DBD interface.
PHP
Easier to learn than PERL.
Use fewer resources than PERL.
You can get a faster speed by upgrading to PHP4.
C
Native Interface of MySQL.
Quickly and give more control.
Lower Layer, so you must pay more.
C ++
Higher level, giving you more time to write applications.
Still under development
ODBC
Run on Windows and Unix.
It can be transplanted between almost different SQL servers.
Slow. MyODBC is just a simple straight-through driver, which is 19% slower than native interfaces.
There are many ways to do the same thing. It is difficult to run as many ODBC drivers, and there are still different errors in different fields.
Problems. Microsoft occasionally changes the interface.
An unclear future. (Microsoft prefers OLE instead of ODBC)
JDBC
Theoretically, it is possible to port data between databases in different operating systems.
It can run on the web Client.
Python and others
It may be good, but we don't need them.

---------------------------------------------------------------

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.

---------------------------------------------------------------

7. Use portable applications

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 need a faster speed, 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.

-------------------------------------------------------------

9. 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.