July 21, 2011, 2:21 p.m.
posted by blackhat
For some DBMSs, logging is an optional step. Nevertheless it's very rare that database administrators will decide to turn logging off for performance reasons. So it's safe to assume that logging will happen when you do a data change.
When will logging happen? Before data changes become permanent. The reasoning is that, if a database gets smashed, then one can recover from the log. If the data change were written before the log entry though, then the last entry in the log would be absent. So the DBMS always logs first.
What goes in the log? A copy of the new data, or a copy of the old data, or both. This point requires emphasis. You might think that the only necessary data in the log is the data-change statement itself—for example, the DBMS could just write this SQL statement into the log file:
INSERT INTO Table1 (column1) VALUES (1)
Well, it could—for instance, Oracle does just that (Oracle calls the SQL-statement log a journal). However, Oracle also writes a complete "before image" data copy into the log file, along with a complete "before image" index page if a data change also changes an index. And so will most other DBMSs.
Wow, isn't that expensive? Not as much as it appears. In the first place, the writing is sequential and therefore quite fast. In the second place, the log writing can be a parallel operation on a different disk drive from the main file, and the log file can be recycled so there are no allocation problems. In the third place, logging is cheap insurance.
The main thing you should know about logging is that it's one of the reasons your data-change statements are slow. The easiest way to speed them up is to turn logging off for a while. You can ask your DBA to do that in a few situations:
The Bottom Line: Logs
Assume logging will happen before data changes become permanent.
Logging is one of the reasons that data-change statements are slow. Speed them up by turning logging off (a) during a bulk INSERT, (b) for index updates/creations, and (c) during operations that copy from one database object to another. Don't turn logging off at any other time.