Impact of InnoDB pagesize on Performance

According to percona's blog, the Percona-XtraDB-9.1 added support for different page sizes, So we tested the impact of different page sizes on the performance of an InnoDB,
Because the Percona-XtraDB-9.1 only supports 4, 8, 16 size page size, because the page size is too small, it does not make sense, so we tested the pagesize = 8 K and 16 K performance. The following is a test of 4 K, 8 K, 16 K page.

Percona-XtraDB-9.1 please click http://www.percona.com/downloads/Percona-Server-5.5/Percona-Server-5.5.23-25.3/binary/linux/x86_64/Percona-Server-5.5.23-rel25.3-240.Linux.x86_64.tar.gz download:

It is easy to modify the page size. You only need to specify the size in the configuration file. If it is 8 K, write it in the configuration file.
Innodb_page_size = 8 K.
Note that percona-xtradb cannot be larger than 16 K. If 32 K is defined, the default 16 K is used.
Mysqld. log contains such a record:

 1   120517   10 : 03 : 31 [Warning] Option'  InnoDB-page-size  ' : Unsigned Value 32768 Adjusted 16384  2   120517   10 : 03 : 31 [Note] plugin '  Federated  '   Is  Disabled. 3   120517   10 : 03 : 31 InnoDB: the InnoDB memory heap Is Disabled

Of course you can modify the source code re-compilation, see here http://www.mysqlab.net/blog/2009/09/innodb%E8%A1%A8%E7%A9%BA%E9%97%B4page-size%E7%9A%84%E9%80%89%E6%8B%A9/

Pagesize cannot be set when there is data in MySQL. It can only be set when MySQL is initialized. Different page sizes may cause incompatible data records.
In InnoDB, each page stores at least two records. Because only one record is saved, the entire index tree is a linked list, which means no meaning is available. Therefore, each record can be 8 KB at most, if the value is greater than 8 KB, the row overflows, and the large field overflows to another page.
Even if the page size is 8 K, the maximum size of each record is 4 K.

When everything is done, go to MySQL and check the system parameters. You will find that the page size is changed to 8 K.

 1 Mysql> show variables like'  % PAGE %  '  ;  2 + ----------------------------- + ------- + 3 | Variable_name | value | 4 + ----------------------------- + ------- + 5 | Innodb_flush_neighbor_pages | area | 6 | Innodb_max_dirty_pages_pct | 75 | 7 | Innodb_page_size | 8192 |8 | Innodb_stats_sample_pages | 8 | 9 | Large_page_size | 0 | 10 | Large_pages | off | 11 + ----------------------------- + ------- +

Test environment:

1Mysql>SelectVersion ();2+ -------------------- +3| Version () |4+ -------------------- +5|5.5.23-Rel25.3-Log |6+ -------------------- +7 1RowIn Set(0.00Sec)
 

The test data is 1000 million. the test data is about 40 Gb when the page size is 16 K. However, when the page size is 8 K, the test data is 46 GB, 6 GB more, we can see that there is a waste of space when the page size is 8 K.

MySQL buffer pool is 10 Gb, 4 times the data volume.
The testing tool is an internal testing tool. You can try the TPS changes at the time of the reactors.

The test result is as follows:

From the test results, 8 K is 20% better than 16 K, which is somewhat unexpected. Although 8 K is more frequent and better than 16 K, however, I did not expect that the performance could be improved much.

The following is the result of the 4 K pagesize test:
When 4 K, we found that the size of the data file changed to a greater value. This time, it became 52 GB, which is 12 GB larger than when 16 K.

We can see that the smaller the value, the better. 8 K is a good value (for OLTP applications ).