MySQL Forums
Forum List  »  Newbie

Re: Deleted table file behavior!
Posted by: Rick James
Date: May 05, 2012 01:30PM

You are approaching the logic 'incorrectly'. Think of the whole process this way...
1. The data & indexes must be persisted to disk (unless you are using MEMORY or BLACKHOLE engines).
2. Always reading/writing the disk immediately would make things slow. So...
3. How can RAM be used to "cache" things to cut down on I/O?
4. The integrity constraints of InnoDB (not MyISAM) add wrinkles (iblog, etc) to the processing and the ordering of events.

> I still need to know where this "hidden/default binary log" is stored on windows for MyISAM files. I Know that by default for innodb is on ib_logfile0 and ib_logfile1 but I dont know how it works on MyISAM

There is not necessarily any hidden place for things -- wouldn't that be a waste of effort?

MyISAM indexes are updated via read-modify-write through the key_buffer and the .MYI file. The read is skipped if the block is already in the key_buffer; the write is delayed until MySQL feels like writing it.

MyISAM data is updated via read-modify-write to the .MYD file, with the OS doing the caching.

InnoDB has both data and indexes in 16KB blocks. The blocks are aggressively cached in the buffer_pool. For ROLLBACK, UNDO, etc, the iblog files are also involved. InnoDB uses either ibdata1 or a .ibd file (depending on the setting of innodb_file_per_table at the time of the CREATE TABLE) for persisting the data and indexes.

Turning on the binlog is one step in setting up Replication or certain flavors of backup. It is optional; the binlog may never be written to. So, it is orthogonal to the .MYD, .ibd, etc files.

Options: ReplyQuote

Written By
Re: Deleted table file behavior!
May 05, 2012 01:30PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.