MySQL InnoDB database performance practices-appropriate number of table records

In practice, some colleagues often asked: what is the appropriate number of records in the MySQL Innodb table?
Generally, the larger the table, the lower the performance. But how low is it? Is it a slow decline or a sharp decline? Is it a decline of 10 million or 0.1 billion?

To address these problems, I conducted a benchmark test. The benchmark test environment is as follows:
[Hardware configuration]

Hardware	Configuration
CPU	Intel (R) Xeon (R) CPU E5620 clock speed 2.40 GHz, 2 Physical CPUs, 16 logical CPUs
Memory	24G (6 blocks * 4G DDR3 1333 REG)
Hard Disk	300 GB * 3, SAS hard drive 15000 rpm, no RAID, RAID card, and write-back function
OS	RHEL5
MySQL	5.1.49/5.1.54

[MySQL configuration]

Configuration item	Configuration
Innodb_buffer_pool_size	18G
Innodb_log_file_size	200 M
Innodb_log_files_in_group	3
Sync_binlog	100
Innodb_flush_log_at_trx_commit	2

[Table configuration]

Configuration item	Configuration
Number of records	10 million, 20 million, 50 million, 0.1 billion
Storage Engine	Innodb
Row format	Compact

The performance test results are as follows:
1) For the same table with different records, the test results are as follows:

The analysis is as follows:
(1) When the table size is smaller than the Inndob buffer pool, the overall performance will slightly decrease as the number of table records increases, however, the performance of various operations is not significantly different (for example, 1KW/2KW is 12000TPS, and 5KW is 10000TPS, with a 16% difference ).

(2) When the table size is greater than Innodb buffer pool (10 kW), the Performance drops sharply (from 12000 to 1000), and the performance is close to the performance of high security configuration, at this time, disk I/O becomes the main factor affecting the performance.

Therefore, the number of table records has little impact on performance. The key is whether the table size is smaller than the Innodb buffer pool.

2) different tables with different records
In order to further verify the impact of table records on performance, another scenario is also verified: the number of records is large, but each record is short, compared with the two tables with a small number of records but a long record.

The performance of tables with a Value of 10 (Value10) and a Value of 250 (Value250) are compared. Innodb has the highest performance only when the buffer pool is greater than the table size. Therefore, to meet this condition, the number of table records for Value10 is 10 kW, the number of records in the Value250 table is 2KW.

It can be seen that even if the number of records in the Value10 table is five times the number of records in the Value250 table, the performance is about 16% higher. Therefore, compared with the number of table records, the row length has a greater impact on performance, and the longer the row, the lower the performance.

[Application recommendations]
Based on the above analysis, the critical point of the number of records must be considered for the number of table records, that is, the table size (data and index) after the number of records is expressed) the size of the Innodb buffer pool is exceeded. We recommend that you design a table with a small and refined row length as much as possible during design.

Note:
The test data is only used for comparison. It does not mean that the performance of MySQL is usually so high. For comparison, many preparations are made during the test, and the test operation is special.