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

Re: startup caching suggestions

From: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
To: Bryan Murphy <bryan(dot)murphy(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: startup caching suggestions
Date: 2007-06-25 22:16:16
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
On Mon, 25 Jun 2007, Bryan Murphy wrote:

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

Bryan, did you try 'dd if=/path/to/your/table of=/dev/null' trick ?
It will very fast read you data into kernel's buffers.

> 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
> Thanks,
> Bryan

Oleg Bartunov, Research Scientist, Head of AstroNet (,
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su,
phone: +007(495)939-16-83, +007(495)939-23-83

In response to


pgsql-performance by date

Next:From: Bryan MurphyDate: 2007-06-25 22:20:17
Subject: Re: startup caching suggestions
Previous:From: Tom LaneDate: 2007-06-25 22:10:15
Subject: Re:

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