Time difference between Indodb and Maysam in Mysql

Question

I have a mysql table with over 30 million records that were originally saved with myisam. The following is a description of the table:

I would execute the following query on this table, which usually takes about 30 seconds. I would change @eid every time to avoid caching the database or disk.

select count(fact_data.id) from fact_data where fact_data.entity_id=@eid and fact_data.metric_id=1 

Then I converted this table to innoDB without any other changes, and after that the same query now returns in less than a second every time I run the query. Even when I accidentally installed @eid to avoid caching, the request returns in less than a second.

I studied the differences between the two types of storage in order to try to explain the dramatic performance improvement, but could not come up with anything. In fact, a lot of what I'm reading indicates that Myisam should be faster.

The queries that I run are connected to a local database, in which other processes do not get into the database during the tests.

Answer 1

This is an amazingly big difference in performance, but I can come up with a few things that can contribute.

MyISAM has historically been regarded as faster than InnoDB, but for the latest versions of InnoDB this is true for a much smaller set of use cases. MyISAM usually scans read-only tables faster. In most other use cases, I usually find that InnoDB is faster. Often many times faster. Stop locks are a deadly ring for MyISAM in most cases of using MySQL.

MyISAM caches indexes in its key buffer. You may have set up a too small key buffer for efficient index caching for your rather large table.

MyISAM is OS dependent for caching table data from .MYD files in the OS cache. If the OS runs low in memory, it will begin to flush its disk cache. This may cause it to continue reading from disk.

InnoDB caches both indexes and data in its own memory buffer. You can tell the operating system not to use its disk cache if you set innodb_flush_method to O_DIRECT, although this is not supported on OS X.

InnoDB typically buffers data and indexes on 16K pages. Depending on how you change the @eid value between requests, it may already cache data for one request due to the disk reading the previous request.

Make sure you create indexes the same way. Use the explanation to check if MySQL is using an index. Since you included description output instead of show create table or show indexes from, I cannot determine if entity_id is part of a composite index. If this is not the first part of a composite index, it will not be used.

If you are using a relatively modern version of MySQL, run the following command before running the query:

set profiling = 1;

This will enable query profiling for your session. After executing the request, run

show profiles;

This will show you a list of queries for which profiles are available. I think it saves the last 20 by default. Assuming your query was the first to run:

show profile for request 1;

Then you will see the duration of each stage of the query. This is extremely useful for determining what (for example, locking tables, sorting, creating temporary tables, etc.) causes a slow query.

Answer 2

My first suspicion would be that the original MyISAM table and / or indexes became fragmented over time, resulting in poor performance. The InnoDB table would not have the same problem, since you created it with all the data already in it (so that everything will be stored sequentially on disk).

You can test this theory by restoring the MyISAM table. The easiest way to do this is to use the null ALTER TABLE statement:

 ALTER TABLE mytable ENGINE = MyISAM; 

Then check the performance to make sure it is better.

Another possibility would be if the database itself were simply tuned for InnoDB performance rather than MyISAM. For example, InnoDB uses the innodb_buffer_pool_size parameter to know how much memory should be allocated to store cached data and indexes in memory. But MyISAM uses the key_buffer parameter. If your database has a large innodb buffer pool and a small key buffer, then InnoDB performance will be better than MyISAM performance, especially for large tables.

Answer 3

What are your definitions of indexes, there are ways in which you can create indexes for MyISAM in which your index fields will not be used if you think they will.