MySQL database optimization

MySQL database optimization 1.4 MySQL benchmark suite

This chapter would have included the technical description of the MySQL benchmark suite (and crash-me), but it has not yet been written. Now, you can have a better idea by viewing the code and results in the MySQL release source code 'SQL-encoding' directory.

The benchmark suite is to tell the user what kind of SQL query performance is better or worse.

Note that this benchmark is single-threaded, so it measures the minimum operation execution time. We plan to add a benchmark suite for multi-threaded testing in the future.

To use the benchmarking suite, you must meet the following requirements:

The reference kit is available in the source code of MySQL release. You can download the release from the http://dev.mysql.com/downloads/ or use the existing Development code Tree (see "2.3.3 Installing from the Development Source Tree" for details ").

The benchmark script is written in Perl. it uses the Perl DBI module to connect to the database. Therefore, the DBI module must be installed. In addition, each server to be tested must have a specific BDB driver. For example, to test MySQL, PostgreSQL, and DB2, you must install the DBD: mysql, DBD: Pg, and DBD: DB2 modules. For details, see "2.7 Perl Installation Note ".
After obtaining the MySQL distribution source code, you can see the reference suite in the 'SQL-quota' directory. To run these benchmark tests, first set up the service, enter the 'SQL-detail' directory, and run the run-all-tests script:

Shell> cd SQL-example
Shell> perl run-all-tests -- server = server_name

Server_name can be any available service. To list all available options and supported services, call the following command:

Shell> perl run-all-tests -- help

The crash-me script is also stored in the 'SQL-encoding' directory. Crash-me tries to determine what features, performance, and restrictions the database supports by executing real queries. For example, it can determine:

Supported field types
Supported indexes
Supported functions
How many queries are supported
What types of VARCHAR fields are supported?
The results of different database crash-me can be found on the http://dev.mysql.com/tech-resources/crash-me.php. For more information, visit http://dev.mysql.com/tech-resources/benchmarks.

1.5 Use your own benchmark

Make sure to benchmark your database or application to discover their bottlenecks. After solving this bottleneck (or using a fake module instead), you can easily find the next bottleneck. Even if the current overall performance of the application is acceptable, you should at least make a good plan to locate each bottleneck. Maybe one day you want the application to have better performance.

You can find a portable and portable benchmark test program from the MySQL benchmark suite. For details, see "7.1.4 The MySQL Benchmark Suite ". You can modify any program in the benchmarking suite to suit your needs. You can solve the problem in different ways and know which program is the fastest.

Another benchmark suite is an open source database benchmark that can be found on the http://osdb.sourceforge.net.

When the system load is very heavy, problems usually occur. We have many customers who contact us to say that they have a (tested) production system that has also encountered load problems. In many cases, performance problems are caused by factors such as the basic design of the database (for example, poor performance of scanning data tables under high loads), operating system, or library. Many times, these problems are easier to solve before they are officially used for production.

To avoid this problem, we recommend that your application perform a benchmark test under the worst possible load! You can use Super Smack, which can be found in the http://jeremy.zawodny.com/mysql/super-smack. From the meaning of its name, you can think of it as long as you want, it will let your system die, so be sure to only test on the development system
2. optimize SELECT statements and other queries

First, one factor that affects all statements is that the more complex your permission settings are, the higher the overhead is.

Using simple GRANT statements allows MySQL to reduce the overhead of permission checks when executing statements on the client. For example, if no table-level or field-level permissions are set, the server does not need to check the records of tables_priv and columns_priv tables. Similarly, if no resource limit is set for the account, the server does not need to perform resource usage statistics. If a large number of queries exist, it is worthwhile to spend some time planning a simple authorization mechanism to reduce the overhead of server permission check.

If the problem lies in some specific MySQL expressions or functions, you can use the BENCHMARK () function in the mysql client to perform a regular test. Its syntax is: BENCHMARK (loop_count, expression ). For example:

Mysql> select benchmark (+ 1 );
+ ------------------------ +
| BENCHMARK (+ 1) |
+ ------------------------ +
| 0 |
+ ------------------------ +
1 row in set (0.32 sec)

The above results are obtained by running on the Pentium II MHz system. It tells us that MySQL can execute 0.32 simple addition operations within 1,000,000 seconds on this system.

All MySQL functions should be optimized, but there are still some function exceptions. BENCHMARK () is a very good tool used to check whether there is a problem in the query statement.