Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Can you add the table's definition and all the innodn_xxx settings of your MySQL server? –  ypercube Apr 12 '12 at 8:32
    
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. –  ypercube Apr 12 '12 at 8:34
    
please see the updated info. –  Uday Apr 12 '12 at 8:41
    
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. –  ypercube Apr 12 '12 at 8:56
    
Actually I tried with 64MB even the same results were observed. I will test this increasing this successively. –  Uday Apr 12 '12 at 10:16
show 1 more comment

5 Answers

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

share|improve this answer
    
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 ..? –  Uday Apr 13 '12 at 4:59
1  
I hope I see you here more here. your book is awesome. –  john.locke Apr 15 '12 at 16:45
    
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. –  Haluk Jul 11 '12 at 20:32
add comment

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

share|improve this answer
    
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...? –  Uday Apr 12 '12 at 7:17
    
Why are your InnoDB setting so low for starters? –  gbn Apr 12 '12 at 7:22
    
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..? –  Uday Apr 12 '12 at 7:30
add comment

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/

share|improve this answer
    
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 –  Uday Apr 12 '12 at 7:45
    
"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. –  ypercube Apr 12 '12 at 8:48
1  
@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. –  a_horse_with_no_name Apr 12 '12 at 8:53
    
@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. –  Mahesh Patil Apr 12 '12 at 12:43
    
Thanq mahesh. Its working fine with innodb_flush_log_at_trx_commit = 2. But i am expecting better performance than this with innodb_flsuh_log_at_trx_commit = 1. I will run a test with ur suggested configs and see.. –  Uday Apr 12 '12 at 13:16
show 1 more comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.