On 01/09/13 20:20, Leyne, Sean wrote: > Dmitry, > >>> I understand that i can get more reads becouse of "small" cache but >>> why writes? >> Every read into a cache full of dirty pages (modified by sweep) implies a >> page >> write, because the engine needs to reuse some page buffer for reading. > That is reasonable, but many times more writes are occurring. > > Let's assume that the cache is full of dirty pages before the start of the > sweep. > > The sweep would end up reading all of the pages in the database. > > So the number of individual pages read would equal the number pages in the > database, which means that the number of dirty pages writes should be less > than or equal the number db pages.
Sean, take into account that for each deleted record version which changes (deletes) index key we should modify an index block. If records are not sorted in database (I know that in Karol's case they seem to be sorted, but now I talk about generic case) for each deleted record version we need another index block. We must find it in a cache and if it's missing (which is quite possible if indexes of current table have size 4 times bigger than cache), we must read it from disk. But if we have only dirty pages in cache (which is also very likely in such a case), some page should be written. With indexes size N times bigger than cache only 1/N of deleted record versions will not cause page write in case of random records distribution in database. Certainly, this is just an estimation, but I'm afraid it's not too far from reality. If records are not ideally ordered in Karol's sample only about 100M record versions should be deleted to have mentioned number of bytes written to disk (once again just an estimation). I suppose that for 50Gb database this number is not too big provided we talk about 'favorite' table - it's just about 10 Gb of data pages for 100byte records, even not taking into an account compression and delta records. WIth them taken into an account we sooner get 3-5Gb of cleared space on data pages. ------------------------------------------------------------------------------ Master Java SE, Java EE, Eclipse, Spring, Hibernate, JavaScript, jQuery and much more. Keep your Java skills current with LearnJavaNow - 200+ hours of step-by-step video tutorials by Java experts. SALE $49.99 this month only -- learn more at: http://p.sf.net/sfu/learnmore_122612 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
