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

Re: startup caching suggestions

From: "Bryan Murphy" <bryan(dot)murphy(at)gmail(dot)com>
To: "Oleg Bartunov" <oleg(at)sai(dot)msu(dot)su>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: startup caching suggestions
Date: 2007-06-25 22:20:17
Message-ID: bd8531800706251520t5bdb04ddse7471afe03759796@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
No, but I was just informed of that trick earlier and intend to try it
soon.  Sometimes, the solution is so simple it's TOO obvious... :)

Bryan

On 6/25/07, Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> wrote:
>
> 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)
> > 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
> >
>
>         Regards,
>                 Oleg
> _____________________________________________________________
> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
> Sternberg Astronomical Institute, Moscow University, Russia
> Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
> phone: +007(495)939-16-83, +007(495)939-23-83
>

In response to

pgsql-performance by date

Next:From: Ed TyrrillDate: 2007-06-25 23:39:02
Subject: Re:
Previous:From: Oleg BartunovDate: 2007-06-25 22:16:16
Subject: Re: startup caching suggestions

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