[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: Re: [PERFORM] startup caching suggestions
From: "Bryan Murphy" <bryan.murphy () gmail ! com>
Date: 2007-06-25 22:20:17
Message-ID: bd8531800706251520t5bdb04ddse7471afe03759796 () mail ! gmail ! com
[Download RAW message or body]
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
>
[Attachment #3 (text/html)]
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... \
:)<br><br>Bryan<br><br><div><span class="gmail_quote">On 6/25/07, <b \
class="gmail_sendername"> Oleg Bartunov</b> <<a \
href="mailto:oleg@sai.msu.su">oleg@sai.msu.su</a>> wrote:</span><blockquote \
class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt \
0pt 0.8ex; padding-left: 1ex;"> On Mon, 25 Jun 2007, Bryan Murphy wrote:<br><br>> \
We have a search facility in our database that uses full text indexing to<br>> \
search about 300,000 records spread across 2 tables. Nothing fancy \
there.<br>><br> > The problem is, whenever we restart the database (system \
crash, lost<br>> connectivity to SAN, upgrade, configuration change, etc.) our \
data is not<br>> cached and query performance is really sketchy the first five to \
ten minutes <br>> or so after the restart. This is particularly \
problematic because the only<br>> way the data gets cached in memory is if \
somebody actively searches for it,<br>> and the first few people who visit our \
site after a restart are pretty much <br>> screwed.<br>><br>> I'd like \
to know what are the recommended strategies for dealing with this<br>> \
problem. We need our search queries to be near instantaneous, and we \
just<br>> can't afford the startup penalty. <br><br>Bryan, did you try 'dd \
if=/path/to/your/table of=/dev/null' trick ?<br>It will very fast read you data \
into kernel's buffers.<br><br>><br>> I'm also concerned that Postgres \
may not be pulling data off the SAN as <br>> efficiently as theory \
dictates. What's the best way I can diagnose if the<br>> SAN is \
performing up to spec? I've been using iostat, and some of what \
I'm<br>> seeing concerns me. Here's a typical iostat output \
(iostat -m -d 1): <br>><br>> \
Device: tps&nbs \
p; MB_read/s MB_wrtn/s MB_read MB_wrtn<br>> \
sda \
0.00 \
0.00 \
0.00 0 0<br>> \
sdb \
102.97 \
2.03 0.00 \
2 0<br>> \
sdc \
0.00 \
0.00 \
0.00 0 0<br>> \
sdd \
0.00 \
0.00 \
0.00 0 0<br>><br>> \
sda is the os partitionn (local), sdb is the primary database partion (SAN), <br>> \
sdc is the log file partition (SAN), and sdd is used only for backups<br>> \
(SAN). I very rarely seen sdb MB_read/s much above 2, and most of the \
time<br>> it hovers around 1 or lower. This seems awfully goddamn slow \
to me, but <br>> maybe I just don't fully understand what iostat is telling \
me. I've seen<br>> sdc writes get as high as 10 during a database \
restore.<br>><br>> A few bits of information about our setup:<br>> <br>> \
Debian Linux 2.6.18-4-amd64 (stable)<br>> 4x Intel(R) Xeon(R) CPU 5110 @ 1.60GHz \
(100% dedicated to database)<br>> RAID 1+0 iSCSI partitions over Gig/E MTU 9000 \
(99% dedicated to database)<br>> 8GB RAM<br> > Postgres v8.1.9<br>><br>> \
The database is only about 4GB in size and the key tables total about 700MB.<br>> \
Primary keys are CHAR(32) GUIDs<br>><br>> Thanks,<br>> \
Bryan<br>><br><br> Regards, \
<br> &nbs \
p; Oleg<br>_____________________________________________________________<br>Oleg \
Bartunov, Research Scientist, Head of AstroNet (<a \
href="http://www.astronet.ru">www.astronet.ru</a>),<br>Sternberg Astronomical \
Institute, Moscow University, Russia <br>Internet: <a \
href="mailto:oleg@sai.msu.su">oleg@sai.msu.su</a>, <a \
href="http://www.sai.msu.su/~megera/">http://www.sai.msu.su/~megera/</a><br>phone: \
+007(495)939-16-83, +007(495)939-23-83<br></blockquote></div><br>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic