- Documentation Library
- Table of Contents
- MySQL 6.0 Reference Manual
- MySQL 5.1 Reference Manual
- MySQL 5.0 Reference Manual
- MySQL 3.23/4.0/4.1 Manual
- Table of Contents
[+/-]
The most important factor in making a system fast is its basic design. You must also know what kinds of processing your system is doing, and what its bottlenecks are. In most cases, system bottlenecks arise from these sources:
Disk seeks. It takes time for the disk to find a piece of data. With modern disks, the mean time for this is usually lower than 10ms, so we can in theory do about 100 seeks a second. This time improves slowly with new disks and is very hard to optimize for a single table. The way to optimize seek time is to distribute the data onto more than one disk.
Disk reading and writing. When the disk is at the correct position, we need to read the data. With modern disks, one disk delivers at least 10–20MB/s throughput. 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, we need to process it to get our result. Having small tables compared to the amount of memory is the most common limiting factor. But with small tables, speed is usually not the problem.
Memory bandwidth. When the CPU needs more data than can fit in the CPU cache, main memory bandwidth becomes a bottleneck. This is an uncommon bottleneck for most systems, but one to be aware of.
MySQL Enterprise. For instant notification of system bottlenecks subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
User Comments
"Having small tables compared to the amount of memory is the most common limiting factor. But with small tables, speed is usually not the problem."
This sentence is too vague. What issues would somebody have with small tables if not speed (it wouldn't be capacity...)? And why would having substantially more memory than one needs be a problem? I'm not saying that this documentation is wrong, but that it needs to be elucidated.
Small tables probably fit totally in the page/block cache or have a better hit rate. Data access to/from disk is limiting in terms of latency.
If data fits in memory, then there are no/minimal reads to disk. The writes to disk will be during logging at commits (e.g. in InnoDB) and checkpointing (writing dirty data and index pages to disk).
As most MySQL installations with internal disks (limited to 2-5) in a compute node, it is important to not the disks be the bottleneck, as they will limit the throughput by increasing the latency on each transaction.
Add your own comment.