Hello all. I am having an issue with my production db in that it’s experiencing high disk i/o after an upgrade from 5.5 to 5.6. I haven’t changed any of the my.cnf settings so I am not sure what is going on with it. The only difference I made with the upgrade, besides the 5.6 upgrade, was to add a larger volume with more iops. I am running on Ec2 with SSD backed EBS with 1500 iops. Pasting my output here which you can see the reads are very high:
BUFFER POOL AND MEMORY
Total memory allocated 9890168832; in additional pool allocated 0
Total memory allocated by read views 3824
Internal hash tables (constant factor + variable factor)
Adaptive hash index 1317985696 (152997176 + 1164988520)
Page hash 1196152 (buffer pool 0 only)
Dictionary cache 39547537 (38251184 + 1296353)
File system 903016 (812272 + 90744)
Lock system 23916344 (23906984 + 9360)
Recovery system 0 (0 + 0)
Dictionary memory allocated 1296353
Buffer pool size 589816
Buffer pool size, bytes 9663545344
Free buffers 8188
Database pages 510523
Old database pages 188292
Modified db pages 89
Pending reads 1
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 9178749, not young 494214786
22.76 youngs/s, 1514.97 non-youngs/s
Pages read 271218867, created 63453, written 3272378
1362.39 reads/s, 0.12 creates/s, 15.71 writes/s
Buffer pool hit rate 990 / 1000, young-making rate 0 / 1000 not 12 / 1000
Pages read ahead 0.90/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 510523, unzip_LRU len: 0
I/O sum[543824]:cur[9560], unzip sum[0]:cur[0]
From what it looks like to me high disk i/o is reading from disk into the buffer pool which I don’t understand. My disk is being maxed right now around those 1500 levels. My my.cnf file as well.
[root@db1 ~]# cat /etc/my.cnf
[mysql]
CLIENT
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
datadir=/var/lib/mysql
user=mysql
GENERAL
user = mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid
bind-address = 0.0.0.0
performance_schema = 0
DATA STORAGE
datadir = /var/lib/mysql/
CACHES AND LIMITS
auto_increment_increment = 1
auto_increment_offset = 1
key_buffer_size = 256M
long_query_time = 10
max_allowed_packet = 256M
max_connections = 10000
max_connect_errors = 10000
port = 3306
read_buffer_size = 2M
read_rnd_buffer_size = 8M
query_cache_size = 256M
server-id = 561
sort_buffer_size = 2M
thread_cache = 32
thread_concurrency = 2
default_storage_engine = innodb
group_concat_max_len = 131072
max_heap_table_size = 256M
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 4096
tmpdir = /dev/shm
INNODB
innodb_flush_method = O_DIRECT
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size = 9G
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 20
innodb_log_buffer_size = 8M
#innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_file_per_table
innodb_data_file_path=ibdata1:10M:autoextend
LOGGING
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
log-queries-not-using-indexes = OFF
REPLICATION
log-bin = /var/lib/mysql/binlog
log-slave-updates
binlog_format = MIXED
relay-log = /var/lib/mysql/relay-bin
replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
skip-slave-start
ADJUSTMENTS
sql_mode = NO_ENGINE_SUBSTITUTION
Any help with this would be appreciated. My only option if I can figure this out is to either downgrade or get more iops from a larger provisioned disk.
A bit more info from innotop:
_____________________________ Buffer Pool _____________________________
Size Free Bufs Pages Dirty Pages Hit Rate Memory Add’l Pool
575.99k 7684 511967 201 867 / 1000 9.21G 0
_______________________ Page Statistics _______________________
Reads Writes Created Reads/Sec Writes/Sec Creates/Sec
271605219 3276799 63497 1471.53 0.00 0.00
______________________ Insert Buffers ______________________
Inserts Merged Recs Merges Size Free List Len Seg. Size
0 0 1 28290 28292
_________________ Adaptive Hash Index __________________
Size Cells Used Node Heap Bufs Hash/Sec Non-Hash/Sec
381.62 2231.77