MySQL Optimization

How to Optimize Mysql?
Optimization is a complex task because ultimately it requires understanding of the entire system to be optimized.The most important factor in making a system fast is its basic design.

The most common system bottlenecks are:

* 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 (or if it was already there), 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.

Optimizing hardware for MySQL

* If you need big tables ( > 2G), you should consider using 64 bit hardware like Alpha, Sparc or the upcoming IA64. As MySQL uses a lot of 64 bit integers internally, 64 bit CPUs will give much better performance.

* For large databases, the optimization order is normally RAM, Fast disks, CPU power.

* More RAM can speed up key updates by keeping most of the used key pages in RAM.

* If you are not using transaction-safe tables or have big disks and want to avoid long file checks, a UPS is good idea to be able to take the system down nicely in case of a power failure.

* For systems where the database is on a dedicated server, one should look at 1G Ethernet. Latency is as important as throughput.

Optimizing the application

* One should concentrate on solving the problem.
* When writing the application one should decide what is most important:
o Speed
o Portability between OS
o Portability between SQL servers
* Use persistent connections.
* Cache things in your application to lessen the load of the SQL server.
* Don't query columns that you don't need in your application.
* Don't use SELECT * FROM table_name...
* Benchmark all parts of your application, but put the most effort into benchmarking the whole application under the worst possible 'reasonable' load. By doing this in a modular fashion you should be able to replace the found bottleneck with a fast 'dummy module', you can then easily identify the next bottleneck (and so on).
* Use LOCK TABLES if you do a lot of changes in a batch; For example group multiple UPDATESDELETES together.

Optimizing MySQL

* Choose compiler and compiler options.
* Find the best MySQL startup options for your system.
* Scan the the MySQL manual and read Paul DuBois' MySQL book.
* Use EXPLAIN SELECT, SHOW VARIABLES, SHOW STATUS and SHOW PROCESSLIST.
* Learn how the query optimizer works.
* Optimize your table formats.
* Maintain your tables (myisamchk, CHECK TABLE, OPTIMIZE TABLE).
* Use MySQL extensions to get things done faster.
* Write a MySQL UDF function if you notice that you would need some function in many places.
* Don't use GRANT on table level or column level if you don't really need it.
* Pay for MySQL support and get help to solve your problem :)