Hi,
I have a database with close to 11 million entries and lately deletes have started to get painfully slow. I've set up a new server with a lot of improvements, but if anyone have an idea about what the deciding factor for the performance difference is, then I would be grateful.
On the old server: -16 cores. 42Gb RAM, entire database in memory -XFS file system on (hw)RAID-1 -Database and BerkeleDB log on same filesystem -some, but not much load (~35 read waiters) -time to delete 157 entries: 9 minutes.
New server: -16 cores, 48Gb RAM, entire database in memory -ext3 filesystem on (hw)RAID-10 -Database and log on difference disk -no load. -time to delete the same 157 entries: 6.2 seconds
I'm aware that the new server has all the benefits, but even under low load conditions, the old server is only able to delete an entry every 3 seconds and there's orders of magnitude difference between 6 and 540 seconds.
My suspicion is that there's one of the above factors (XFS?, db/log on same fs?) which get very pronounced when the database gets above a certain size, since this slowdown for deletes seem to have accelerated with the growth of the database the last few months.
/Peter