The claim that InnoDB does not do a 'vacuum' of the database is wrong.
InnoDB does do garbage collection when the historical data (= delete-marked records) is no longer needed to serve a consistent read snapshot. The garbage collection (which is called purge) runs automatically as soon as it can clean up some records.
There have been a few reported cases where purge was not able to keep up with the updates or deletes to a table. To alleviate that problem we introduced a new startup option:
Note that the purge is an integral part of the processing of the database workload. If the purge cannot keep up, then the workload is too large for the combination InnoDB + hardware.
Actually, MySQL/InnoDB has two transaction logs. The write-ahead log in InnoDB's log files is used in crash recovery. MySQL's 'binlog' is used in point-in-time recovery from a backup.
Hi!
InnoDB does have a transaction log, and uses fsync() to write data to disk, like PostgreSQL and almost all transactional databases do. InnoDB is an 'ACID' database.
The problem in this case was that apparently fsync() did not write the data to disk, or to a non-volatile disk cache. There is not much a database can do in this situation.
Regards,
Heikki
Innobase Oy
It might be this bug which was reported recently and which I have now fixed in upcoming 3.23.57 and 4.0.12:
MySQL/InnoDB-3.23.56, March 17, 2003
* An outstanding bug: MySQL can erroneously return 'Empty set' if InnoDB estimates an index range size to 0 records though the range is not empty; MySQL also fails to do the next-key locking in the case of an empty index range.
I know that this bug can hit a table which has only one or a few rows. I am not sure if it can hit big tables. The reason for the bug was that MySQL assumed that the range cardinality estimate is always accurate.
Can you describe the index problem in more detail? Please send a bug report to mysql@lists.mysql.com.
- What MySQL version did you use?
- Did CHECK TABLE report the table ok?
- What kind of SELECT queries did you execute and what did they report?
- Are you aware that in the AUTOCOMMIT=0 mode you have to COMMIT your read transaction to advance the consistent read timepoint? Some users do not know this and then wonder why committed data is not visible in another connection. InnoDB serializes read-only transactions at a precise timepoint so that all consistent reads are really consistent with respect to each other. The default transaction isolation level is therefore called REPEATABLE READ.
Slashdot Mirror
Now you are underestimating other Innobase Oy engineers :). We have 4 and 1/2 top developers working here to make InnoDB even better.
Regards,
Heikki Tuuri
Localman,
Innobase Oy still does all the InnoDB Hot Backup sales through our website.
Do not be afraid to order :).
Best regards,
Heikki
Hi!
r t.html
The claim that InnoDB does not do a 'vacuum' of the database is wrong.
InnoDB does do garbage collection when the historical data (= delete-marked records) is no longer needed to serve a consistent read snapshot. The garbage collection (which is called purge) runs automatically as soon as it can clean up some records.
There have been a few reported cases where purge was not able to keep up with the updates or deletes to a table. To alleviate that problem we introduced a new startup option:
innodb_max_purge_lag
http://dev.mysql.com/doc/refman/5.0/en/innodb-sta
Note that the purge is an integral part of the processing of the database workload. If the purge cannot keep up, then the workload is too large for the combination InnoDB + hardware.
Regards,
Heikki Tuuri
Oracle Corp./Innobase Oy
Hi!
InnoDB has used WAL since I wrote it in mid-1990s. To PostgreSQL, WAL came later, around 2000.
Regards,Heikki
Innobase Oy
Actually, MySQL/InnoDB has two transaction logs. The write-ahead log in InnoDB's log files is used in crash recovery. MySQL's 'binlog' is used in point-in-time recovery from a backup.
Regards,Heikki
Innobase Oy
Hi! InnoDB does have a transaction log, and uses fsync() to write data to disk, like PostgreSQL and almost all transactional databases do. InnoDB is an 'ACID' database. The problem in this case was that apparently fsync() did not write the data to disk, or to a non-volatile disk cache. There is not much a database can do in this situation. Regards, Heikki Innobase Oy
Hi!
It might be this bug which was reported recently and which I have now fixed in upcoming 3.23.57 and 4.0.12:
MySQL/InnoDB-3.23.56, March 17, 2003
* An outstanding bug: MySQL can erroneously return 'Empty set' if InnoDB estimates an index range size to 0 records though the range is not empty; MySQL also fails to do the next-key locking in the case of an empty index range.
I know that this bug can hit a table which has only one or a few rows. I am not sure if it can hit big tables. The reason for the bug was that MySQL assumed that the range cardinality estimate is always accurate.
Best regards,
Heikki Tuuri
Innobase Oy
Hi!
Can you describe the index problem in more detail? Please send a bug report to mysql@lists.mysql.com.
- What MySQL version did you use?
- Did CHECK TABLE report the table ok?
- What kind of SELECT queries did you execute and what did they report?
- Are you aware that in the AUTOCOMMIT=0 mode you have to COMMIT your read transaction to advance the consistent read timepoint? Some users do not know this and then wonder why committed data is not visible in another connection. InnoDB serializes read-only transactions at a precise timepoint so that all consistent reads are really consistent with respect to each other. The default transaction isolation level is therefore called REPEATABLE READ.
Best regards,
Heikki Tuuri
Innobase Oy