GNU Info

(mysql.info)Optimize Overview

---

Next: Query Speed Prev: MySQL Optimization Up: MySQL Optimization

---

Enter node , (file) or (file)node

Optimization Overview
=====================

The most important part for getting a system fast is of course the basic
design. You also need to know what kinds of things your system will be
doing, and what your bottlenecks are.

The most common bottlenecks are:
   * Disk seeks.  It takes time for the disk to find a piece of data.
     With modern disks in 1999, the mean time for this is usually lower
     than 10ms, so we can in theory do about 1000 seeks a second. This
     time improves slowly with new disks and is very hard to optimize
     for a single table. The way to optimize this is to spread the data
     on more than one disk.

   * Disk reading/writing.  When the disk is at the correct position we
     need to read the data. With modern disks in 1999, one disk
     delivers something like 10-20Mb/s. This is easier to optimize than
     seeks because you can read in parallel from multiple disks.

   * CPU cycles.  When we have the data in main memory (or if it
     already were there) we need to process it to get to our result.
     Having small tables compared to the memory is the most common
     limiting factor. But then, with small tables speed is usually not
     the problem.

   * Memory bandwidth.  When the CPU needs more data than can fit in
     the CPU cache the main memory bandwidth becomes a bottleneck. This
     is an uncommon bottleneck for most systems, but one should be
     aware of it.

Design Limitations

MySQL Design Limitations/Tradeoffs

Portability

Portability

Internal use

What Have We Used MySQL For?

MySQL Benchmarks

The MySQL Benchmark Suite

Custom Benchmarks

Using Your Own Benchmarks