MySQL optimization method summary (2) [figure] _ MySQL

Summary of MySQL optimization methods (2) [figure] bitsCN.com

To obtain the graphic data, we also wrote a simple C language tool for executing SQL queries and making the results into GIF images. This tool is also dynamically executed after Perl scripts parse Web pages.

In many cases, you only need to copy the existing script and simply modify the SQL query statement in it to generate a new report. Sometimes, you need to add more fields to the existing accumulative table or create a new one. This operation is very simple, because we store all the transaction tables on the disk (a total of about 50 GB transaction tables and 20 GB of other customer data ).

We also allow customers to directly access the cumulative table through ODBC. in this way, advanced users can use the data for their own experiments. The system works well and there is no problem processing data on a moderate Sun Ultra iSCSI Workstation (2x200 MHz. Finally, the system is transplanted to Linux.

1.4 MySQL benchmark suite

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 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?

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.

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.

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:

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.

BitsCN.com