Re: startup caching suggestions - Mailing list 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)
> 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