10.16 How do I get MySQL to run at full speed?

Start by benchmarking your problem! You can take any program from the MySQL benchmark suite (normally found in the `sql-bench' directory) and modify it for your needs. By doing this, you can try different solutions to your problem and test which is really the fastest solution for you.

• Start mysqld with the correct options. More memory gives more speed if you have it. 10.1 Tuning server parameters.
• Create indexes to make your SELECT statements faster. MySQL indexes.
• Optimize your column types to be as efficient as possible. For example, declere columns to be NOT NULL if possible. 10.13 How to arrange a table to be as fast/small as possible.
• MySQL has two different levels of locks: Internal locks and external locks. The internal locks ensures that all update/retrieve operations are atomic (run without conflicts from other clients). The external locks allows multiple MySQL servers to run on the same data and allows you to use isamchk to check tables without taking down mysqld. The --skip-locking option disables external locking (file locking) between SQL requests. This gives greater speed but has the following consequences:
  • You MUST flush all tables with mysqladmin flush-tables before you try to check or repair tables with isamchk. (isamchk -d tbl_name is always allowed, since that simply displays table information.)
  • You can't run two MySQL servers on the same data files, if both are going to update the same tables.
  The --skip-locking option is on by default when compiling with MIT-pthreads, because flock() isn't fully supported by MIT-pthreads on all platforms. The only case when you can't use --skip-locking is if you run multiple MySQL SERVERS (not clients) on the same data (or run isamchk on the table without first flushing the mysqld server tables first). You can still use LOCK TABLES / UNLOCK TABLES even if you are using --skip-locking
• If updates are a problem, you can delay updates and then do many updates in a row later. Doing many updates in a row is much quicker than doing one at a time.
• On FreeBSD systems, if the problem is with MIT-pthreads, upgrading to FreeBSD 3.0 (or higher) should help, because you can use native threads. This makes it possible to use Unix sockets (with FreeBSD, this is quicker than connecting using TCP/IP with MIT-pthreads) and the threads package is much more integrated.
• GRANT checking on the table or column level will decrease performance.

If your problem is with some explicit MySQL function, you can always time this in the MySQL client:

mysql> select benchmark(1000000,1+1);
+------------------------+
| benchmark(1000000,1+1) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.32 sec)

The above shows that MySQL can execute 1,000,000 + expressions in 0.32 seconds on a simple PentiumII 400MHz.

All MySQL functions should be very optimized, but there may be some exceptions and the benchmark(loop_count,expression) is a great tool to find if this is a problem with your query.