MySQL Optimization (i)

One, we can and should optimize what?

Hardware

Operating system/Software Library

SQL Server (settings and queries)

Application Programming Interface (API)

Application

Second, optimize the hardware

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

For large databases, the order of optimization is generally ram, fast HDD, CPU capacity.

More memory can speed up the updating of key codes by storing the most commonly used key-code pages in memory.

If you do not use a transaction security (TRANSACTION-SAFE) table or have large tables and want to avoid long file checks, a single ups can shut down the system safely in the event of a power failure.

For a system that is stored in a dedicated server, 1G of Ethernet should be considered. Latency is just as important as throughput.

Third, optimize the disk

Provide a dedicated disk for systems, programs, and temporary files, and if there are many modifications, place the update and transaction logs on a dedicated disk.

Low seek time is important for database disks. For the large table, you can estimate that you will need log (line number)/log (index block length/3*2/(key code length + data pointer length)) + 1 times to find a row. For a table with 500000 rows, the index Mediun the type int column, which requires log (500000)/log (1024/3*2/(3 + 2)) +1=4 the secondary seek path. The above index requires 500000*7*3/2=5.2M space. In fact, most blocks will be cached, so it probably takes only 1-2 times to find the path.

However, for writing (as above), you will need 4 seek requests to find out where to store the new key code, and typically 2 times seek to update the index and write a line.

For a very large database, your application will be limited by disk seek speed, with the increase in the amount of data is n log n data level increment.

Divide the database and tables on separate disks. In MySQL, you can use symbolic links for this purpose.

The column disk (RAID 0) will improve read and write throughput.

A mirrored column (RAID 0+1) will be more secure and increase the throughput of the read. The throughput of writes will be reduced.

Do not use mirroring or RAID (except RAID 0) on the disk where the temporary files or data that can easily be rebuilt is located.

On Linux, use command hdparm-m16-d1 on the disk at boot time to enable multiple sectors and DMA capabilities to be read and write simultaneously. This can increase the response time 5~50%.

On Linux, mount the disk with the async (default) and Noatime mounts.

For some specific applications, you can use memory disks for certain tables, but you don't usually need them.