1

I was using MySQL version 5.1.52 for my application but recently I have updated it to 5.6.26, but now I am facing very high disk IO.

Following are system configuration:

  • OS : Linux 2.6.39.4
  • RAM : 2GB
  • Disk : 30GB Compact Flash.

My O.S is same just version of MySQL is changed. I had taken IO observation with MySQL versions 5.1.52 and 5.6.26 using iotop utility, same queries are executed on both versions, reading are number of writes on disk. Following are observations:

5.1.52 MySQL Version:

Number of Queries     All Innodb Tables          All MyISAM Tables
900                     12.45M                        192K
1800                    23.76M                        424K
2700                    36.02M                        680K
3600                    46.33M                        924K

5.6.26 MySQL Version:

Number of Queries     All Innodb Tables          All MyISAM Tables
900                      75.04M                       212K
1800                    158.03M                       472K
2700                    245.93M                       756K
3600                    346.66M                      1016K

I can compromise with the ACID properties but not with the IO.

Why IO is increased in MySQl 5.6.26?

How I can reduce disk IO?

Is it fine to use MyISAM engine instead of InnoDB engine with MySQL 5.6.26?

my.cnf for MySQL 5.6.26:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
default-storage-engine=MyISAM
general_log=on
general_log_file=/dev/shm/mysql.logs
thread_cache_size=9
table_open_cache=2000
table_definition_cache=1400
sort_buffer_size=2097152
query_cache_size=1048576
max_allowed_packet=4194304
key_buffer_size=8388608
join_buffer_size=262144
back_log=80
query_cache_type=OFF
open_files_limit=5000
event_scheduler=OFF
innodb_thread_concurrency=0
innodb_sync_spin_loops=30
innodb_stats_on_metadata=OFF
innodb_max_dirty_pages_pct=90
innodb_file_per_table=ON
innodb_concurrency_tickets=5000
innodb_autoextend_increment=8
innodb_open_files=2000
innodb_log_file_size=67108864
innodb_log_buffer_size=67108864
innodb_additional_mem_pool_size=1048576
innodb_log_buffer_size=1048576
innodb_max_dirty_pages_pct=90
innodb_max_dirty_pages_pct_lwm=90
innodb_adaptive_flushing_lwm=70
innodb_adaptive_flushing=0
innodb_io_capacity=2000
innodb_io_capacity_max=6000
innodb_lru_scan_depth=2000
innodb_write_io_threads=1
innodb_read_io_threads=1
innodb_buffer_pool_instances=1
innodb_buffer_pool_size=20971520
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=2

[mysqld_safe]
pid-file=/var/run/mysqld/mysqld.pid

Thanks in advance

Michael Hampton
  • 244,070
  • 43
  • 506
  • 972
purvesh pardeshi
  • 11
  • 3
  • 1
    It depends on the InnoDB/Myisam database spread balance, on the memory consumption parameters configured, and on stuff like that. There just isn't enough info provided. We need at least some data about your mysql server memory setup. But in general - no, you should probably stick to the InnoDB engine, as it's a main engine in mysql for now. – drookie Jan 13 '16 at 07:48
  • Why do you have so many non-default values? I doubt you can justify one write thread. Configs like this not only confusing, but also error prone and hard to work with. – akuzminsky Jan 14 '16 at 15:45

0 Answers0