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

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@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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@sai.msu.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@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(495)939-16-83, +007(495)939-23-83
>