Monty says MySQL optimization (1)

This article is one of Monty's speeches at the o'reilly Open Source Convention 2000 conference, this is a detailed MySQL optimization document covering the following aspects: what we can optimize, hardware optimization, disk optimization, operating system optimization, and Application Programming Interface selection.

I,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.

1G Ethernet should be considered for systems with data inventory placed on a dedicated server. 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 = Mb 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.

IV,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.

V,Select 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)
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.