Subject Re: [firebird-support] Re: Best way to delete millions of rows
Author Ann Harrison
On Nov 22, 2016, at 6:56 AM, kragh.thomas@... [firebird-support] <> wrote:
> Pagesize is 16384 and pagebuffers is 256. Servermode is superclassic.
> If I drop the primary key/index everything works as expected.

Interesting. GUIDs produce really fat indexes. Firebird uses prefix compression and GUIDs vary in the first bytes so they don't compress well. Keys generated sequentially will be on different index leaf pages. Since records are deleted and garbage collected in the order they were stored, index garbage collection could easily touch a different page for each key. With only 256 buffers, index garbage collection is running pages through the cache wildly - with luck they're cached by the file system.

I'm not saying that shutting down the machine for seconds or minutes at a time is acceptable, but that the place to look is in index garbage collection with a small page cache and large randomly generated key values.

For what little it's worth, I'd bet that a 64bit primary key generated by a sequence would not show this problem. Nor, for that matter would a GUID that had been re-organized to put the volatile bytes at the end...

Good luck,