Test and Analysis of MySQL Performance

Test Environment

Operating System: Linux as Release 4, Kernel 2.6.9-42. elsmp #1 SMP

CPU: Intel (r) Xeon (TM) CPU 3.00 GHz, 4-core

Memory: 1 GB, mainly used to run MySQL services

Hard Disk: CCISS 37g

File System: ext3, supporting large files

MySQL: MySQL-5.0.51a

NIC: 100 m

Network Environment: M LAN

Test Method

The data size can be divided into 10 thousand, 0.1 million, 1 million, and 10 million records.

Execute 2000 random queries on each data scale, which can prevent the database from directly returning the query results from the cache, and bring the query results closer to the actual query conditions. Take the system time as the Random Seed and perform the query operation randomly within the existing record range.

Query types include single-Table select, double-Table select, insert, single-table delete, and double-Table Delete.

MyISAM and InnoDB are the two most used engines in MySQL and the most frequently used engines in our company at present. Therefore, comparing and testing are of reference value.

Each execution entry is the average execution time of a single query. The unit is subtle. If you want to view the overall execution time, multiply it by 2000.

Because the script inserts data slowly, the test efficiency using the mysql client tool is very low and it cannot ensure enough randomness, so the data insertion and test performance are completed by writing corresponding programs.

Enable binary Update log.

Test records
Test Data

Average execution time of MyISAM engine cross-host query operations (unit: microseconds)

Number of project records	Single Table select	Double table select	Insert	Update	Delete a single table	Double table delete
10 thousand	358	484	1188	269	298	464
0.1 million	375	505	1227	283	322	521
1 million	3174	5293	1242	4142	7981	17212
10 million	12302	19920	1261	12355	20449	39890

Average execution time of local query operations of MyISAM engine (unit: microseconds)

Number of project records	Single Table select	Double table select	Insert	Update	Delete a single table	Double table delete
10 thousand	178	247	694	141	170	345
0.1 million	195	271	705	157	186	585
1 million	3055	5164	746	4308	12373	22533
10 million	8665	15259	803	11033	17224	35485

After optimizing the configuration parameters of InnoDB, the update and insertion speed can be increased by more than 10 times.

Average execution time of a single query operation across hosts of the InnoDB Engine (unit: microseconds)

Number of project records	Single Table select	Double table select	Insert	Update	Delete a single table	Double table delete
10 thousand	387	521	1215	327	293	519
0.1 million	399	567	1231	338	322	663
1 million	2255	4950	1320	1988	3832	9550
10 million	10556	18702	1362	10823	19078	36666

Average execution time of a single local query operation in InnoDB Engine (unit: microseconds)

Number of project records	Single Table select	Double table select	Insert	Update	Delete a single table	Double table delete
10 thousand	180	264	709	165	156	312
0.1 million	226	340	754	204	205	590
1 million	1853	4530	746	1782	2771	9816
10 million	9275	17609	889	6941	14727	36282

Performance Comparison

Single Table select

Double table select

Insert operation

Update operation

Delete a single table

Double table delete

Performance Analysis

1. We can see that in MySQL 5.0, the performance of MyISAM and InnoDB Storage engines is not very different, which is basically close to the official saying.

2. Select, delete, or update operations of 10 thousand and 0.1 million are fast, less than 1 millisecond.

3. The performance of insert operations is slow, and the impact on data size is relatively small.

4. When the performance scale is increased from 0.1 million to 1 million, the performance decrease is obvious, from 1 million to 10 million.

5. the InnoDB engine has poor performance in the default parameter configuration.

6. We recommend InnoDB configuration (1 GB memory, mainly for MySQL servers ):

Innodb_buffer_pool_size = 600 m
Innodb_additional_mem_pool_size = 64 m
# Set .. _ log_file_size to 25% of buffer pool size
Innodb_log_file_size = 256 m
# Innodb_log_buffer_size = 8 m
Innodb_flush_log_at_trx_commit = 1

# Innodb_lock_wait_timeout = 50
Innodb_file_per_table

Among them, innodb_flush_log_at_trx_commit and innodb_file_per_table have the greatest impact on I/O performance.

7. files such as my-large.cnf are only parameters for optimizing the MyISAM engine, which is especially important when optimizing the configuration.

8. When the data size is large (more than 1 million records), the InnoDB engine has a comparative advantage in performance.

9. When the data size is small (less than 0.1 million entries), The MyISAM engine has a comparative advantage in performance.