Re: startup caching suggestions

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
>