Efficiently inserting / updating 1000's records with mysql transaction help?

Question

I have to insert and then keep updating 1000's of records every minute, will mysql transaction help in the speed performance as it does in sqlite3.

I carried out a test (Inserting 1300 records) but showed the same result with InnoDB (for transaction) and MyISAM.

Using MySQLdb for Python

Answer 1

Try batch inserts. Can not say anything about consequent updates

Answer 2

Transactions aren't about performance; they're about data integrity and consistency.

I think that transactions will slow things down, because MySQL will have to maintain transaction logs and rollback segments. But the benefit is that it will ensure that each INSERT will keep its integrity.

InnoDB maintains referential integriry; MyISAM does not. If you observed no difference, I'm guessing that it's because you didn't have transaction boundaries set.

Answer 3

I don't know if this will help for MySQL, but for large-scale Oracle system I was involved with in a previous job, the inserts/updates were done in batches of 100's or 1000's to maximize throughput. A lot of the grunt work was done server-side in PL-SQL.