Mysql: Insert performance INNODB vs MYISAM

Question

I am inserting into a simple small table with 5 attributes and 1000 rows.

I observed when the engine is INNODB, each insert is taking 0.03 - 0.05 seconds. I changed the engine to MYISAM, then the inserts are faster. it is taking 0.001 - 0.003.

What is the problem. innodb_flush_log_trx_commit = 1 by default. I was this setting as it is. Here are my innodb setting.

innodb_log_buffer_size : 1MB
innodb_log_file_size   : 5MB
innodb_buffer_pool_size: 8MB
innodb_flush_log_trx_commit = 1

mysql> desc table ;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(10) | YES  |     | NULL    |       |
| count | int(10) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

VARIABLES

mysql> show variables like 'innodb%' ;
+-----------------------------------------+------------------------+
| Variable_name                           | Value                  |
+-----------------------------------------+------------------------+
| innodb_adaptive_hash_index              | ON                     |
| innodb_additional_mem_pool_size         | 1048576                |
| innodb_autoextend_increment             | 8                      |
| innodb_autoinc_lock_mode                | 1                      |
| innodb_buffer_pool_size                 | 8388608                |
| innodb_checksums                        | ON                     |
| innodb_commit_concurrency               | 0                      |
| innodb_concurrency_tickets              | 500                    |
| innodb_data_file_path                   | ibdata1:10M:autoextend |
| innodb_data_home_dir                    |                        |
| innodb_doublewrite                      | ON                     |
| innodb_fast_shutdown                    | 1                      |
| innodb_file_io_threads                  | 4                      |
| innodb_file_per_table                   | OFF                    |
| innodb_flush_log_at_trx_commit          | 1                      |
| innodb_flush_method                     |                        |
| innodb_force_recovery                   | 0                      |
| innodb_lock_wait_timeout                | 50                     |
| innodb_locks_unsafe_for_binlog          | OFF                    |
| innodb_log_buffer_size                  | 2097152                |
| innodb_log_file_size                    | 5242880                |
| innodb_log_files_in_group               | 2                      |
| innodb_log_group_home_dir               | ./                     |
| innodb_max_dirty_pages_pct              | 90                     |
| innodb_max_purge_lag                    | 0                      |
| innodb_mirrored_log_groups              | 1                      |
| innodb_open_files                       | 300                    |
| innodb_rollback_on_timeout              | OFF                    |
| innodb_stats_method                     | nulls_equal            |
| innodb_stats_on_metadata                | ON                     |
| innodb_support_xa                       | ON                     |
| innodb_sync_spin_loops                  | 20                     |
| innodb_table_locks                      | ON                     |
| innodb_thread_concurrency               | 8                      |
| innodb_thread_sleep_delay               | 10000                  |
| innodb_use_legacy_cardinality_algorithm | ON                     |

+-----------------------------------------+------------------------+ 36 rows in set (0.00 sec)

I could not able to figure out what went wrong with this. Thanks in advance. Regards, UDAY

Answer 1

The reason is very simple. When you insert a row into MyISAM, it just puts it into the server's memory and hopes that the server will flush it to disk at some point in the future. Good luck if the server crashes.

When you insert a row into InnoDB it syncs the transaction durably to disk, and that requires it to wait for the disk to spin. Do the math on your system and see how long that takes.

You can improve this by relaxing innodb_flush_log_at_trx_commit or by batching rows within a transaction instead of doing one transaction per row.

I highly recommend reading High Performance MySQL 3rd Edition (I am the author).

Answer 2

InnoDB will be slightly slower because it is ACID compliant, has MVCC and does useful things like actually check foreign keys etc.

As an example, Oracle's own whitepaper for MyISAM vs InnoDB they actually say

These benchmarks were run with relaxed ACID constraints to provide a more comparable analysis with MyISAM...

Another, from Percona's "Should you move from MyISAM to Innodb?" (my bold)

Performance

... while on the drawbacks side we see significantly large table size (especially if data size is close to memory size), generally slower writes, slower blob handling, concurrency issues, problems dealing with very large number of tables, slow data load and ALTER TABLE and others. Another big one is COUNT(*) without where clause which is often the show stopper for them move until it is worked around.

You can't compare like-for-like write speed...

Answer 3

When you are using InnoDB, you have row-level lock, and in MyISAM you have table-level lock. Which does not make sense in a benchmark with only 1 connection to the DB. But if you run 100 simulatenous scripts to insert, then the difference will be obvious.

Also, keep in mind, that InnoDB stores data phisically in the order of the primary key. If it's not autoincrement, and the inserts produce somehow random values for the primary key, you will hit the I/O limits because of random writes. This is visible in a benchmark when the table size is bigger then the buffer pool.

Answer 4

MyISAM in most cases will be faster than InnoDB for run of the mill sort of work. Selecting, updating and inserting are all very speedy under normal circumstances. InnoDB is more strict in data integrity while MyISAM is loose. MyISAM has full-text search index while InnoDB has not.

Advantage of MyISAM:

• Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.

• Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources. Full-text indexing.

• Especially good for read-intensive (select) tables.

MySIAM Slower than InnoDB for tables that are frequently being inserted to or updated, because the entire table is locked for any insert or update.

Advantages of Innodb:

• InnoDB should be used where data integrity comes a priority because it inherently takes care of them by the help of relationship constraints and transactions.
• Faster in write-intensive (inserts, updates) tables because it utilizes row-level locking and only hold up changes to the same row that’s being inserted or updated.

Conslusion: The comparison is pretty straightforward. InnoDB is more suitable for data critical situations that require frequent inserts and updates. MyISAM, on the other hand, performs better with applications that don’t quite depend on the data integrity and mostly just select and display the data.

http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/

Answer 5

There are subtle differences in INDEXing between the engines. Until you check this out, you can have mysterious cases where one engine is faster than the other -- either direction. Even after checking the indexes, there will be cases where either is faster than there other. This covers all the cases I know of: http://mysql.rjweb.org/doc.php/myisam2innodb

An, as others have said, do tune to the engine. See http://mysql.rjweb.org/doc.php/memory

Answer 6

I'm the creator of FlightPath (open-source) and CEO of FlightPath Academics (SaaS company). FlightPath is web-based software for managing student success and therefore keeps up with a lot of data.

So here's my two cents:

We use InnoDB as the default table structure. However, we frequently import large amounts of data from our client schools, usually in the form of a nightly routine, and usually from CSV files.

For tables where we read from throughout the day, and only ever write to overnight from these imports, we set those to MyISAM for the sake of the routine not taking 5 hours or longer to run. (It's much faster to write to in our experience).

Here's a practical example of what I mean:

• The table where were keep track of student activity (that gets lots of reads and writes throughout the day) is InnoDB.
• The table that keeps a record of all a student's completed courses and grades (that gets only reads throughout the day) is MyISAM. This is the type of table we would update in nightly routines when no one is reading from it, so table-level locks are not such a big deal.

I hope this advice helps others; your mileage may vary of course.