> Hello, Linux People! > > after short consultation, i have come to this conclusion about which > filesystem i should use on my database box (or server) > the winner is: ex2 linux extended filesystem, yes, lassies & lads > > - "why not ext3/Reiser's ?" > - "because journalling is already implemented in the DBMS."
The DBMS journal and the FS journal serve two distinct purposes. The DBMS journal is there to make sure the database transactions either finish successfully or disappear altogether like they never happened (commit or roll-back in DB-speak). The log is also kept until the next backup, so that the database objects (like a database table) can actually be reset to a specific point in time, as long as in that point there are no open transactions (actually it can be restored to a point in time when there are open transactions but the open transactions would not have any effect on the database - they would have been 'rolled back'). To sum it up, the role of the DB journal in recovery is to undo/redo database operations. The database assumes that the journal itself is okay. The FS journal is kind of the same thing, but the database objects are files, and the transaction is a write() operation. This log is used to maintain the file system integrity in case of a failure. When a failure occurs the log is traversed and any transaction that can be completed is completed, while the FS data structures are maintained. Once the file system reaches some point when the disk representation of the file system is consistent enough, the log is deleted (this can be thought of a database full backup) and everything starts from scratch. If in your imaginary setup a power failure occurs, the file system will lose consistency (because an ext2 file system saves parts of its internal data structures in memory. Every FS does). If you are lucky, you can fsck it back to life, long fsck time applies. After you do that, and only if you are successful, you can proceed to the database restoration section, where the log will be examined by your DBMS, and will be compared to the database status. Any completed transaction will be written, and incomplete transactions will be rolled back. Only then can you take the database online again. If you opt to use the ext2, more power to you. Remember that you will stand longer recovery times and a higher likelihood of data corruption. If you manage to corrupt your database file, you will have to restore it from backup and re-apply the log file (you did backup the file, and keep all the logs from the backup until present time, right?). If you loose the log file... may the deity you believe in have mercy on whatever concept of soul you may posses, as your database will be at an unknown state. If you have points of quiescence, and your DBMS supports this feature, you may be able to recover to that point. Otherwise your latest backup (which I trust does exist) is your only resort, and you will lose whatever data you have written to your database since the latest backup. To sum it up, the integrity of your log files is of utmost importance. When you use a raw device for your database, the DBMS manages it, in effect creating its own file system. Whether it's good or bad for recoverability is left as an exercise to the reader. -- Arik ================================================================To unsubscribe, send mail to [EMAIL PROTECTED] with the word "unsubscribe" in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
