> I'm really curious to know how PostgreSQL and MySQL avoid this > thrashing problem. Does anybody have any insight on this?
MySQL (in the InnoDB engine) reduces thrashing through clustering of data in (roughly) primary key order. This helps if you obtain data with similar primary keys. It doesn't help if you prefer to access data in inserted order (where the primary key is all over the shop). Also, typically, you throw boatloads of RAM at MySQL servers if you have any decent sized databases which is dedicated to caching query results and database pages. We have around 250 million rows across about 250 tables comprising around 80GB of databases running on MySQL with InnoDB with an 8 disk 15K RPM RAID 10 array - MySQL gets 3GB of dedicated RAM for its own use, and also run with O_DIRECT so the operating system doesn't cache the disk sectors in its own buffers - thus avoiding "dual caching" of MySQL data files. Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK & USA - www.premiumdata.net
