Skip site navigation (1) Skip section navigation (2)

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: (view raw, whole thread or download thread mbox)
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

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)
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



pgsql-performance by date

Next:From: Tyrrill, EdDate: 2007-06-25 21:28:32
Previous:From: Tom LaneDate: 2007-06-25 17:12:09
Subject: Re: PITR Backups

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group