startup caching suggestions

From: "Bryan Murphy" <bryan(dot)murphy(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: startup caching suggestions
Date: 2007-06-25 20:18:43
Message-ID: bd8531800706251318t393ef481q240e18ced03a830f@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tyrrill, Ed 2007-06-25 21:28:32
Previous Message Tom Lane 2007-06-25 17:12:09 Re: PITR Backups