MySQL 5.6 Innodb data page 16 K vs 8 K Performance Comparison Test

From MySQL5.6, a new parameter innodb_page_size can be set to 8 K and 4 K for Innodb data pages. The default value is 16 K. This parameter must be added to my. cnf at the beginning of initialization. If a table has been created and modified, an error will be reported when MySQL is started.

Reference manual:

650) this. width = 650; "border = 0>

Next I will perform a stress test for 16 K and 8 K.

Hardware: R710, 72 GB memory, 6 300 GB, 15000 forwarded raid 10, XFS partition.

My. cnf parameters:

 
 

  
  
innodb_buffer_pool_size = 48G 
  
  
innodb_buffer_pool_instances = 8 
  
  
innodb_flush_method = O_DIRECT 
  
  
innodb_file_per_table = 1
  
  
innodb_read_io_threads = 16 
  
  
innodb_write_io_threads = 16 
  
  
innodb_io_capacity = 2000 
  
  
innodb_log_files_in_group = 3 
  
  
innodb_flush_log_at_trx_commit = 0 
  
  
innodb_log_file_size = 1024M 
  
  
innodb_max_dirty_pages_pct = 90 
 
 

1. Sysbench parameters (read/write ):

 
 

  
  
sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=100000000 --max-requests=1000000 --num-threads=100  
  
  
--mysql-host=192.168.110.121  --mysql-port=3306 --mysql-user=admin --mysql-password=123456 --mysql-db=test  
  
  
--oltp-table-name=sbtest --mysql-socket=/tmp/mysql.sock run 
 
 

The sbtest table contains 0.1 billion records and the file size is 24 GB.

The following is a 16 K performance diagram:

 
 

  
  
innodb_page_size = 16k 
 
 

650) this. width = 650; "border = 0>

650) this. width = 650; "border = 0>

The following figure shows the 8 K performance:

 
 

  
  
innodb_page_size = 8k 
 
 

650) this. width = 650; "border = 0>

650) this. width = 650; "border = 0>

 
 

  
  
Conclusion: The Peak page is 16 K, which has a low CPU pressure, with an average of 20%


  
  
8 K page, CPU pressure is 30% ~ 40%, but the select throughput is higher than 16 K
 
 

2. Sysbench parameters (read-only ):

 
 

  
  
sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=10000000 --max-requests=0 --num-threads=100  
  
  
--oltp-read-only=on --mysql-host=192.168.110.121  --mysql-port=3306 --mysql-user=admin --mysql-password=123456 --mysql-db=test 
  
  
 --oltp-table-name=sbtest --mysql-socket=/tmp/mysql.sock run 
 
 

The sbtest table contains 0.1 billion records and the file size is 24 GB.

The following figure shows the performance of 16 K and 8 K:

650) this. width = 650; "border = 0>

650) this. width = 650; "border = 0>

Conclusion: 16 K on the left and 8 K on the right. The difference is not obvious. Therefore, for read/write frequency, 16 K is currently a good performance.