Thread: startup caching suggestions

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

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