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

• ypercubeᵀᴹ: Can you add the table's definition and all the innodn_xxx settings of your MySQL server?
• ypercubeᵀᴹ: Also check this blog article to get started with: Innodb Performance Optimization Basics. Default MySQL settings are usually not optimized for InnoDB use. Your buffer pool size is too low, to start with.
• Uday: please see the updated info.
• ypercubeᵀᴹ: I would start with innodb_buffer_pool_size. 8MB is too low. It really depends on your total server RAM and what other services/applications you run on the server but you could set it to many GB, if your total memory is high enough.
• Uday: Actually I tried with 64MB even the same results were observed. I will test this increasing this successively.

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).

• Uday: Yes this is true. But when I have the innodb_flush_log_at_trx_commit = 1, I observed 40% on the server. more over changing the innodb_flush_method to O_DIRECT or O_DSYNC improved the performance by 5 times. can you say anything about this 40% IO with innodb_flush_log_at_trx_commit = 1 ..?
• Haluk: As Baron's book also outlines. There is also the added benefit of concurrent inserts for MyISAM tables. On our production server we see inserts around 0.004 seconds for tables with no gaps, hence concurrent insert gets into action. However, on our tables with gaps, where concurrent insert is not possible, the insert speed reduces more than x10 times. We don't have any innodb tables so I cannot benchmark against them.

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...

• Uday: Yes. We cant compare. But this write speed single normal insert is taking 0.3 - 0.5 is not at all acceptable. this is horrible in fact. Its neither a big table nor a complex one. Its not even the live server. then why is this performance...?
• gbn: Why are your InnoDB setting so low for starters?
• Uday: I installed mysql with apt-get on my local. they are default setttings. But I cant accept these write performance. Do you feel any thing need to be improved..?

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.

• Jeutnarg: Late to the party, yes, but holy cow did that second paragraph help a ton. Furthermore, it's the only place I've seen that mentioned even after looking at over a dozen SO and DBA-SO posts on speeding up mysql inserts.

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/

• Uday: Hei... I know these conceptual stuff. my case is very simple. plain simple table insert with worse performance due to innodb. let me know if sm thing can change this. Regards -uday
• ypercubeᵀᴹ: "MyISAM faster than InnoDB on the whole". That's debatable, at least. There are articles that FKs can improve performance in certain cases. And even if it is true, a minor performance increase in some cases is not a good reason to lose integrity.
• user1822: @uday: Actually MyISAM will be slower if the workload shifts to a lot of concurrent reads and writes because of the locking that MyISAM will impose.
• Mahesh Patil: @uday set these values and try again ? innodb_log_buffer_size=16M for innodb_buffer_pool_size You can set this value to 70-80% of available memory for Innodb-only installations for innodb_flush_log_trx_commit=2 - writes to OS cache on each transaction commit. To disk every second.
• Uday: changing the innodb_flush_method from default to O_DSYNC or O_DIRECT has improved the performance 5 times better.

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.

• Richard: Not sure why my answer was downvoted. Please clarify in the comments or edit if the answer is unclear.