1

So I'm testing on our dev server to change MyISAM to InnoDB and am having a huge performance loss.

Here is my.cnf:

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_buffer_pool_instance=2
innodb_log_file_size=512M
key_buffer_size=24M
#70% of available RAM
innodb_buffer_pool_size=3072M
innodb_thread_concurrency=0
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=5000

I'm running a Amazon CentOS t2.medium instance (2 vCPU & 4 GB ram).

Here are a few tests I ran. I ran alot more but with the same theme. InnoDB takes way longer and loads the CPU more. enter image description here

Anyone have any thoughts? I've read if you have a lot of INSERTS / UPDATES with big data InnoDB is way better then MyISAM. I'm not sure what I'm missing here.

Community
  • 1
Marc Alexander
  • 147
  • 2
  • 8

2 Answers2

0

You said in your question

I've read if you have a lot of INSERTS / UPDATES with big data InnoDB is way better then MyISAM. I'm not sure what I'm missing here.

Your server class is too small for your InnoDB settings.

On Apr 14, 2011, I mentioned how the storage engines have distinct ways of caching.

  • MyISAM will only cache indexes in RAM. Data is always read from disk.
  • InnoDB caches data and indexes in the InnoDB Buffer Pool

I also noted how your innodb_read_io_threads and innodb_write_io_threads are maxed out. You only have 4GB of RAM or which you only have 1GB free (due to your 3GB or 3072M Buffer Pool). I would lower your threads from 64 to 8.

If you have the budget, you should go upgrade your server class to m3.large or m3.xlarge where you can have 4 CPU and 15 or 16G of RAM. You could then increase innodb_buffer_pool_size to 10G or 12G. Then, your remaining InnoDB settings would make sense. I would still lower your threads from 64 to 8.

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
-1

The structure of MyISAM and InnoDB are not the same:

MyISAM

When do dml(ex:insert) it's just store in memory and then push it after that.(bad when server crush)

InnoDB

need to syncs that data to disk until finished it cost the disk I/O.

Hint

  1. you can set you transaction to autocommit=0;
  2. https://dev.mysql.com/worklog/task/?id=6470
Community
  • 1
BongSey
  • 119
  • 11