10

I have a table with around 1 Billion rows, and its 98% read intensive.

I tried tuning the database, with different storage engines (MyISAM and InnoDB)

Then ran a few test to view the performance

In the where clause I had a primary key ID, and it seemd that since MyISAM Key Cache stores loads all the index in its buffer, using MyISAM seemed to be pretty fast, around 2 times faster than InnoDB

But for InnoDB, it seemd slower!! Is it that InnoDB does not use any buffer to pre-load the indexes?

Akash
  • 229
  • 3
  • 7
  • maybe any of the trigger happy moderators voting to close the question can elaborate on their motivations? – pQd Aug 13 '12 at 19:17
  • Can you give us some idea of the size of the database and table in question? Total size on disk would be useful. Also, what spec is the machine you are running on? – Dave Rix Aug 14 '12 at 07:52

5 Answers5

6

Before you decide upon MyISAM or InnoDB you will have to look over both Storage Engines in terms of how what each caches

MyISAM

When read, a MyISAM table's indexes can be read once from the .MYI file and loaded in the MyISAM Key Cache (as sized by key_buffer_size). How can you make a MyISAM table's .MYD faster to read? With this:

ALTER TABLE mytable ROW_FORMAT=Fixed;

I wrote about this in my past posts

InnoDB

OK, what about InnoDB? Does InnoDB do any disk I/O for queries? Surprisingly, yes it does !! You are probably thinking I am crazy for saying that, but it is absolutely true, even for SELECT queries. At this point, you are probably wondering "How in the world is InnoDB doing disk I/O for queries?"

It all goes back to InnoDB being an ACID-complaint Transactional Storage Engine. In order for InnoDB to be Transactional, it has to support the I in ACID, which is Isolation. The technique for maintaining isolation for transactions is done via MVCC, Multiversion Concurrency Control. In simple terms, InnoDB records what data looks like before transactions attempt to change them. Where does that get recorded? In the system tablespace file, better known as ibdata1. That requires disk I/O.

COMPARISON

Since both InnoDB and MyISAM do disk I/O, what random factors dictate who is faster?

  • Size of Columns
  • Column Format
  • Character Sets
  • Range of Numeric Values (requiring large enough INTs)
  • Rows Being Split Across Blocks (Row Chaining)
  • Data Fragmentation caused by DELETEs and UPDATEs
  • Size of Primary Key (InnoDB has a Clustered Index, requiring two key lookups)
  • Size of Index Entries
  • the list goes on...

EPILOGUE

Thus, in a heavy-read environment, it is possible for a MyISAM table with a Fixed Row Format to outperform InnoDB reads out of the InnoDB Buffer Pool if there is enough data being written into the undo logs contained within ibdata1 to support the transactional behavior imposed on the InnoDB data. Plan your data types, queries, and storage engine real carefully. Once the data grows, it might become very difficult to move data around.

By the way, I wrote something like this 5 days ago : How do I assign a memory limit for mySQL?

Community
  • 1
RolandoMySQLDBA
  • 16,364
  • 3
  • 47
  • 80
  • does innodbe really generate any disk reads when all the data is already in the buffer pool and there are no concurrent data modification requests, just reads? – pQd Aug 13 '12 at 19:16
  • Its my guess that as the asker has 1 billion rows in his db, then he is unlikely to have it all cached in RAM in the buffer pool - hence there will be reads required to get to the data outside the buffer pool and on disk? – Dave Rix Aug 14 '12 at 07:51
3

MyISAM will always run a lot faster than innodb when there is no contention for the data. Start adding multiple sessions trying to update the same tablse, and innodb very quickly gets the performance advantage.

How you tune the system for the 2 engines is very different.

The reason that different engines exist is because different workloads / access patterns exist.

symcbean
  • 19,931
  • 1
  • 29
  • 49
2

you have to 'warm up' innodb. eg by re-playing access logs or running some smart queries that will touch each value from index.

take a look here or here.

i hope you dont use default mysql settings for innodb - they were suitable for hardware from ~2000.

Community
  • 1
pQd
  • 29,561
  • 5
  • 64
  • 106
  • I did make changes in the default config, also ran the query multiple times, around 30 times, but produced nearly the same results. It was faster after a few tries, but remained slower than MYISAM, also used MariaDB (latest version) – Akash Aug 13 '12 at 10:36
1

Check this site, it has very useful information:

http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/

http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

You can also tune your file system. I have a good performance results on XFS with optimal sunit and swidth values (of course if you use RAID)

klocek
  • 562
  • 5
  • 11
0

After further tuning InnoDB on MariaDB, I increased the innodb_buffer_pool_size to my InnoDB database size, since doing so, InnoDB has started fetching rows faster

I suppose tuning InnoDB is quite important according to your database needs

Akash
  • 229
  • 3
  • 7