MySQL Forums
Forum List  »  InnoDB

Very slow insert with Innodb
Posted by: Vikram Rao
Date: December 30, 2008 09:12PM

Hi,

Recently we changed table types of few of our tables which have very frequent inserts, updated and deletes to Innodb from MyISAM. The issue with MyISAM was table lock during querying and we experienced crashes with few tables.

After moving to Innodb the inserts into these tables have become very slow under load. We are trying to hit with 1000 users (max connections in MySQL is set to 1000). We are using MySQL C API to coonect from our C++ services.
Some specifications:

1. The target table has around 1 million records.
2. We removed the auto_increment field from the table. (but it didn't help)
3. The table has big_int fields and one date time field.
4. We have index on 3 fields apart from the PK.
5. Multiple clients will be trying to insert into this table simultaneously.
6. MySQL version 5.060.
7. innodb_thread_concurrency is 0(no limit)
8. innodb_buffer_size is 1GB
9. We are not using any explicit transactions at this point in time.
10. We are inserting multiple records with single query (Insert into Table1 values (,,,), (,,,,))
11. innodb_table_locks is set to OFF
12. table_lock_wait_timeout is set to 50. We usually get table lock wait time out exceeded. Try another transaction error.

The inserts at peak load take about 2 minutes for 40 inserts.

Do we need to make any changes in innodb setting to improve the performance?

Thanks in advance.

Options: ReplyQuote


Subject
Views
Written By
Posted
Very slow insert with Innodb
7010
December 30, 2008 09:12PM
2920
December 30, 2008 11:01PM
2905
January 05, 2009 08:28AM
2633
January 05, 2009 07:08PM
2668
January 05, 2009 11:46PM
2401
January 06, 2009 09:45PM
2864
January 07, 2009 03:37AM
2418
January 11, 2009 10:47PM
2297
January 17, 2009 09:44AM
2383
January 17, 2009 11:58PM
2712
January 18, 2009 09:09AM
2333
January 18, 2009 05:01PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.