Re: [PERFORM] MySQL+InnoDB vs. PostgreSQL test?

On Tue, 3 Feb 2004, Christopher Kings-Lynne wrote:

> > One more thing that annoyed me. If you started a process, such as a
> > large DDL operation, or heaven forbid, a cartesian join (what? I never
> > do that!).
>
> I believe InnoDB also has O(n) rollback time. eg. if you are rolling
> back 100 million row changes, it takes a long, long time. In PostgreSQL
> rolling back is O(1)...

Actually, it takes signifigantly longer to rollback than to roll forward,
so to speak, so that if you inserted for 10,000 rows and it took 5
minutes, it would take upwards of 30 times as long to roll back.

This is from the docs:

http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#InnoDB_tuning

Point 8:

# Beware of big rollbacks of mass inserts: InnoDB uses the insert buffer
to save disk I/O in inserts, but in a corresponding rollback no such
mechanism is used. A disk-bound rollback can take 30 times the time of the
corresponding insert. Killing the database process will not help because
the rollback will start again at the database startup. The only way to get
rid of a runaway rollback is to increase the buffer pool so that the
rollback becomes CPU-bound and runs fast, or delete the whole InnoDB
database.