关于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


    还没有评论!
1  

Leave your comment

请留下您的姓名(*)

请输入正确的邮箱地址(*)

请输入你的评论(*)


感谢开源 © 2016. All rights reserved.&3Q Open Source&^_^赣ICP备15012863号-1^_^
乐于分享共同进步 KreativeThemes