We have a search facility in our database that uses full text indexing to search about 300,000 records spread across 2 tables. Nothing fancy there.
The problem is, whenever we restart the database (system crash, lost connectivity to SAN, upgrade, configuration change, etc.) our data is not cached and query performance is really sketchy the first five to ten minutes or so after the restart. This is particularly problematic because the only way the data gets cached in memory is if somebody actively searches for it, and the first few people who visit our site after a restart are pretty much screwed.
I'd like to know what are the recommended strategies for dealing with this problem. We need our search queries to be near instantaneous, and we just can't afford the startup penalty.
I'm also concerned that Postgres may not be pulling data off the SAN as efficiently as theory dictates. What's the best way I can diagnose if the SAN is performing up to spec? I've been using iostat, and some of what I'm seeing concerns me. Here's a typical iostat output (iostat -m -d 1):
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
sda 0.00 0.00 0.00 0 0
sdb 102.97 2.03 0.00 2 0
sdc 0.00 0.00 0.00 0 0
sdd 0.00 0.00 0.00 0 0
sda is the os partitionn (local), sdb is the primary database partion (SAN), sdc is the log file partition (SAN), and sdd is used only for backups (SAN). I very rarely seen sdb MB_read/s much above 2, and most of the time it hovers around 1 or lower. This seems awfully goddamn slow to me, but maybe I just don't fully understand what iostat is telling me. I've seen sdc writes get as high as 10 during a database restore.
A few bits of information about our setup:
Debian Linux 2.6.18-4-amd64 (stable)
4x Intel(R) Xeon(R) CPU 5110 @ 1.60GHz (100% dedicated to database)
RAID 1+0 iSCSI partitions over Gig/E MTU 9000 (99% dedicated to database)
8GB RAM
Postgres v8.1.9
The database is only about 4GB in size and the key tables total about 700MB.
Primary keys are CHAR(32) GUIDs
Thanks,
Bryan
