Re: startup caching suggestions

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