I just got to the bottom of a database speed problem. You know the sort: when you do a simple INSERT, it takes 30 seconds to add a row to the bottom of a table.
The problem first manifested itself with a big UPDATE query. I thought I'd done the query wrong (if you've ever done an UPDATE with a load of JOINs in, you'll know how easy it is to typo something and quadruple the number of rows hit) but then the INSERTS started to run really slowly too.
So I started to think laterally this morning, and found myself thinking: the disks are fast, the RAID adaptor is fast, the processor is fast, and nothing's memory-bound. What else is there?
One answer was that there was something sitting between the application and the disk subsystem: the anti-virus package. So I figured I'd take it off, temporarily, to see if that helped. I wasn't actually all that optimistic - after all, the AV processes hardly registered in the performance stats - but lo and behold, the users have stopped scowling and the log that records queries that take too long is looking very nice indeed.
The moral of the story: AV software is a very worthwhile thing to own, but there comes a time when you have to realise that it puts a layer of slowness between your applications and your disks.