关于mysql 5.6.20 innodb 对 read_buffer_size参数进行了测试
结论如下
mysql 5.6.20官方版本没有数据块多块读的功能,与是否设置 read_buffer_size参数无关。对大表扫描该参数基本没啥效果。
测试过程如下
关闭数据库
mysqladmin shutdown
关闭系统缓存
echo 1 > /proc/sys/vm/drop_caches
重启数据库
mysqld_safe &
查看read_buffer_size参数值为128K
mysql> show variables like '%read%';
+-----------------------------------------+---------------------------+
| Variable_name | Value |
+-----------------------------------------+---------------------------+
| innodb_purge_threads | 1 |
.......................................................
| pseudo_thread_id | 1 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
.......................................................
+-----------------------------------------+---------------------------+
23 rows in set (0.00 sec)
查看执行计划
mysql> explain select count(*) from user_info4 where user_name='TTS';
+----+-------------+------------+------+---------------+------+---------+------+-----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+-----------+-------------+
| 1 | SIMPLE | user_info4 | ALL | NULL | NULL | NULL | NULL | 158311631 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+-----------+-------------+
1 row in set (0.00 sec)
执行SQL
mysql> select count(*) from user_info4 where user_name='TTS';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (8 min 56.92 sec)
SQL执行过程中的IO状态计算得出每个IO的大小是8K,数据库的块大小也是设置为8K的,从这些可以看出全表扫描的过程也是单块读。
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
fiob 0.00 0.00 9599.01 0.00 76792.08 0.00 16.00 0.00 0.18 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
fiob 0.00 0.00 10003.00 0.00 80024.00 0.00 16.00 0.00 0.19 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
fiob 0.00 0.00 9616.00 0.00 76928.00 0.00 16.00 0.00 0.18 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
fiob 0.00 0.00 10354.00 0.00 82832.00 0.00 16.00 0.00 0.21 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
fiob 0.00 0.00 9865.00 0.00 78920.00 0.00 16.00 0.00 0.19 0.00 0.00
关闭数据库
mysqladmin shutdown
关闭系统缓存
echo 1 > /proc/sys/vm/drop_caches
重启数据库
mysqld_safe &
设置read_buffer_size为1MB
mysql> set read_buffer_size=1024*1024;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%read%';
+-----------------------------------------+---------------------------+
| Variable_name | Value |
+-----------------------------------------+---------------------------+
...............................................
| innodb_read_only | OFF |
...................................
| read_buffer_size | 1048576 |
| read_only | OFF |
......................................
+-----------------------------------------+---------------------------+
23 rows in set (0.00 sec)
查看执行计划
mysql> explain select count(*) from user_info4 where user_name='TTS';
+----+-------------+------------+------+---------------+------+---------+------+-----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+-----------+-------------+
| 1 | SIMPLE | user_info4 | ALL | NULL | NULL | NULL | NULL | 155067540 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+-----------+-------------+
1 row in set (0.00 sec)
执行SQL
mysql> select count(*) from user_info4 where user_name='TTS';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (8 min 57.05 sec)
SQL执行过程中的IO状态计算得出每个IO的大小是8K,数据库的块大小也是设置为8K的,从这些可以看出全表扫描的过程也是单块读。
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
fiob 0.00 0.00 9890.00 0.00 79120.00 0.00 16.00 0.00 0.19 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
fiob 0.00 0.00 9121.00 0.00 72968.00 0.00 16.00 0.00 0.16 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
fiob 0.00 0.00 9695.00 0.00 77560.00 0.00 16.00 0.00 0.19 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
fiob 0.00 0.00 9550.00 0.00 76400.00 0.00 16.00 0.00 0.17 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
fiob 0.00 0.00 10012.00 0.00 80096.00 0.00 16.00 0.00 0.18 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
fiob 0.00 0.00 9702.00 0.00 77616.00 0.00 16.00 0.00 0.19 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
fiob 0.00 0.00 9683.00 0.00 77464.00 0.00 16.00 0.00 0.17 0.00 0.00
从2次执行SQL所消耗的时间看,都是8分57秒的样子基本无差,得出read_buffer_size参数对大表扫描的性能基本没有影响。
测试环境如下
mysql版本5.6.20 数据库引擎innodb,测试表大小1.6亿占用系统物理空间45G+,服务器内存够大保证所有数据全部被加入到内存中。
mysql的基本配置如下
[root@hpdl580-176 ~]# cat /etc/my.cnf
[client]
socket = /app/mysql/mysql/run/mysql.sock
[mysqld]
user=mysql
port = 3306
bind-address = 0.0.0.0
max_connections = 3000
max_connect_errors = 30
back_log = 600
socket = /app/mysql/mysql/run/mysql.sock
pid-file = /app/mysql/mysql/run/mysql.pid
basedir = /app/mysql/mysql
datadir = /app/mydb
log-error = /app/mysql/mysql/log/mysql_err.log
log-warnings = 1
log-bin=/app/mysql/mysql/data/log-bin
expire_logs_days=5
max_binlog_size = 1024M
binlog_format=mixed
long_query_time=2
slow-query-log-file = /app/mysql/mysql/log/mysql_slowquery.log
#general_log=OFF
#general_log_file=/app/mysql/mysql/log/mysql_query.log
explicit_defaults_for_timestamp=true
open_files_limit = 10240
table_open_cache = 4096
#max_allowed_packet = 32M
binlog_cache_size = 256M
max_heap_table_size = 128M
sort_buffer_size = 16M
join_buffer_size = 16M
thread_cache_size = 300
query_cache_size = 128M
query_cache_limit = 4M
ft_min_word_len = 8
character-set-server = utf8
#character-set-client = utf8
thread_stack = 512K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 128M
sync_binlog=10
innodb_thread_concurrency=120
innodb_use_sys_malloc = 64M
innodb_buffer_pool_size = 128G
innodb_buffer_pool_instances = 64
innodb_flush_method = O_DIRECT
innodb_sync_spin_loops=15
innodb_spin_wait_delay=12
innodb_page_size=8192
query_cache_size=0
innodb_data_home_dir = /app/mysql/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_write_io_threads = 32
innodb_read_io_threads = 32
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 512M
innodb_log_file_size = 512M
innodb_log_files_in_group = 4
innodb_flush_method = O_DIRECT
innodb_log_group_home_dir = /mylog/
innodb_max_dirty_pages_pct = 77
innodb_lock_wait_timeout = 100
innodb_flush_neighbors = 0
innodb_doublewrite=1
innodb_io_capacity=6600
innodb_io_capacity_max=20000
query_cache_type = 0
large_pages=1
max_length_for_sort_data=1M
max_sort_length=1M
系统内存512G
One Response