Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 13B73C76A58 for ; Sun, 26 Feb 2012 04:52:22 -0400 (AST) Received: from nm8-vm0.bullet.mail.ne1.yahoo.com ([98.138.91.23]) by makus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1S1Zq9-000226-4c for pgsql-performance@postgresql.org; Sun, 26 Feb 2012 08:52:22 +0000 Received: from [98.138.90.57] by nm8.bullet.mail.ne1.yahoo.com with NNFMP; 26 Feb 2012 08:52:08 -0000 Received: from [98.138.89.244] by tm10.bullet.mail.ne1.yahoo.com with NNFMP; 26 Feb 2012 08:52:08 -0000 Received: from [127.0.0.1] by omp1058.mail.ne1.yahoo.com with NNFMP; 26 Feb 2012 08:52:07 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 986163.16967.bm@omp1058.mail.ne1.yahoo.com Received: (qmail 93235 invoked by uid 60001); 26 Feb 2012 08:52:07 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1330246327; bh=iIwUiO2ahMYcjFX2ksD7bGSf8i+OyrfY/GXgY9a+inQ=; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=tdGFd4cKRe3w2psH0ezowyXAEcqzhpL5//dm1rw9GRktlGYJ2zLQkmpl4OOHTiecRiUmvPoVnaMUHSsJPAj+HDpeJNswle64nzyuBNpO1pVxUgfU9bGIrYuKHYQmK2XgGxjfGalqxpjqagDNRib1vwQVg7WgE6Ll/C33tKCUzHQ= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=WsvLgikrs1icWvuDTH3/GafRKyUUlxsywKZBKiJpV02mSJjq4EfRX3+IOwML3bQGsCJXJv1P5pO1eHJ+JjBuEz73FPDc3CjT2LTnUcHZJEHhE+KuAD9S0wO3kQg4+1SpSIGDCXRXpOhiEV+O/qS2kFJ31D0vteKy9D3//qBg+so=; X-YMail-OSG: kU_pt_QVM1ko22uOFuNMseddp6QFj1lJN9jz_Al1oORvjyO F6X3M7ybZJaESWz.v_6kTbcHur5u5R1QTADbWMe2Ozf03iq2taxfHvjvpVAV BtNahJ.RTd_AGZufAGks6oAf_Xemdf0P8Z5S6wYL0CJGU.V9Mc.EG7jCeUO3 YmKWZ8wLorrB4zo9.D7Ri27_iUuzj4kEJl8raz8LLqrHgB03yZbttJok2.MI 9IymStyN.CCifXD1rEkooILkqdQEbtn6wYHRGIvFzxD4zwwl2VtYDrAXE8pj xfmQ4nvV7GOgVCYHCbsNqSG0Mj3gGFNTi9F9VywRbCaGrumGbXs9sSqXikQa 3jGyplZqGU8Ul36nsml1UANIjJTKg39rhe3j_0g9.EFM6Mt.snVlCaHQidIe m6NPYLuG975H5wS_gDzUhXQNKvUJmxqlWbG72mb5CcsrYfnlMC1Pk4N9Fi6I PumbZ9TGkTubroGWkhO8UgVxC5LK6tZsEGiBx Received: from [117.136.8.99] by web30802.mail.mud.yahoo.com via HTTP; Sun, 26 Feb 2012 00:52:07 PST X-Mailer: YahooMailWebService/0.8.117.340979 References: Message-ID: <1330246327.76787.YahooMailNeo@web30802.mail.mud.yahoo.com> Date: Sun, 26 Feb 2012 00:52:07 -0800 (PST) From: Wales Wang Reply-To: Wales Wang Subject: =?utf-8?B?UmXvvJogW1BFUkZPUk1dIFBHIGFzIGluLW1lbW9yeSBkYj8gSG93IHRvIHdh?= =?utf-8?B?cm0gdXAgYW5kIHJlLXBvcHVsYXRlIGJ1ZmZlcnM/IEhvdyB0byByZWFkIGlu?= =?utf-8?B?IGFsbCB0dXBsZXMgaW50byBtZW1vcnk/?= To: Jeff Janes , Stefan Keller Cc: "pgsql-performance@postgresql.org" , Stephen Frost In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="1165585747-1193595810-1330246327=:76787" X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201202/206 X-Sequence-Number: 46378 --1165585747-1193595810-1330246327=:76787 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable You can try=C2=A0PostgreSQL 9.x master/slave replication, then try run=C2= =A0slave on=C2=A0persistent RAM Fileystem(tmpfs)=0ASo,=C2=A0access your all= data from slave PostgreSQL=C2=A0that run on tmpfs..=0A =0A=0A_____________= ___________________=0A =E5=8F=91=E4=BB=B6=E4=BA=BA=EF=BC=9A Jeff Janes =0A=E6=94=B6=E4=BB=B6=E4=BA=BA=EF=BC=9A Stefan Keller =0A=E6=8A=84=E9=80=81=EF=BC=9A pgsql-performance@postgres= ql.org; Stephen Frost =0A=E5=8F=91=E9=80=81=E6=97=A5= =E6=9C=9F=EF=BC=9A 2012=E5=B9=B42=E6=9C=8826=E6=97=A5, =E6=98=9F=E6=9C=9F= =E6=97=A5, =E4=B8=8A=E5=8D=88 10:13=0A=E4=B8=BB=E9=A2=98: Re: [PERFORM] PG = as in-memory db? How to warm up and re-populate buffers? How to read in all= tuples into memory?=0A =0AOn Sat, Feb 25, 2012 at 4:16 PM, Stefan Keller = wrote:=0A>=0A> I'd like to come back on the issue of a= ka of in-memory key-value database.=0A>=0A> To remember, it contains table = definition and queries as indicated in=0A> the appendix [0]. There exist 4 = other tables of similar structure.=0A> There are indexes on each column. Th= e tables contain around 10 million=0A> tuples. The database is "read-only";= it's completely updated every=0A> day. I don't expect more than 5 concurre= nt users at any time. A=0A> typical query looks like [1] and varies in an u= nforeseable way (that's=0A> why hstore is used). EXPLAIN tells me that the = indexes are used [2].=0A>=0A> The problem is that the initial queries are t= oo slow - and there is no=0A> second chance. I do have to trash the buffer = every night. There is=0A> enough main memory to hold all table contents.=0A= =0AJust that table, or the entire database?=0A=0A>=0A> 1. How can I warm up= or re-populate shared buffers of Postgres?=0A=0AInstead, warm the OS cache= .=C2=A0 Then data will get transferred into the=0Apostgres shared_buffers p= ool from the OS cache very quickly.=0A=0Atar -c $PGDATA/base/ |wc -c=0A=0AI= f you need to warm just one table, because the entire base directory=0Awon'= t fit in OS cache, then you need to do a bit more work to find out=0Awhich = files to use.=0A=0AYou might feel clever and try this instead:=0A=0Atar -c = /dev/null $PGDATA/base/ > /dev/null=0A=0ABut my tar program is too clever b= y half.=C2=A0 It detects that it is=0Awriting to /dev/null, and just does n= ot actually read the data.=0A=0A> 2. Are there any hints on how to tell Pos= tgres to read in all table=0A> contents into memory?=0A=0AI don't think so,= at least not in core.=C2=A0 I've wondered if it would=0Amake sense to supp= ress ring-buffer strategy when there are buffers on=0Athe free-list.=C2=A0 = That way a sequential scan would populate=0Ashared_buffers after a restart.= =C2=A0 But it wouldn't help you get the=0Aindexes into cache.=0A=0ACheers,= =0A=0AJeff=0A=0A-- =0ASent via pgsql-performance mailing list (pgsql-perfor= mance@postgresql.org)=0ATo make changes to your subscription:=0Ahttp://www.= postgresql.org/mailpref/pgsql-performance --1165585747-1193595810-1330246327=:76787 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
You can tr= y PostgreSQL 9.x master/slave replication, then try run slave on persistent RAM Fileystem(tmpfs)=
So, access your all data from slave PostgreSQL t= hat run on tmpfs..

=E5=8F=91=E4=BB=B6= =E4=BA=BA=EF=BC=9A Jeff Janes <jeff.janes@gmail.com>
<= b>=E6=94=B6=E4=BB=B6=E4=BA=BA=EF=BC=9A Stefan Keller <s= fkeller@gmail.com>
=E6=8A=84= =E9=80=81=EF=BC=9A pgsql-performance@postgresql.org; Stephen Fro= st <sfrost@snowman.net>
= =E5=8F=91=E9=80=81=E6=97=A5=E6=9C=9F=EF=BC=9A 2012=E5=B9=B42=E6= =9C=8826=E6=97=A5, =E6=98=9F=E6=9C=9F=E6=97=A5, =E4=B8=8A=E5=8D=88 10:13 =E4=B8=BB=E9=A2=98: Re: = [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How t= o read in all tuples into memory?

On Sat, Feb 25, 20= 12 at 4:16 PM, Stefan Keller <sfkeller@gmail.com> wrote:
>=
> I'd like to come back on the issue of aka of in-memory key-value d= atabase.
>
> To remember, it contains table definition and quer= ies as indicated in
> the appendix [0]. There exist 4 other tables of= similar structure.
> There are indexes on each column. The tables contain around 10 million
> tuples. The database is "read-onl= y"; it's completely updated every
> day. I don't expect more than 5 c= oncurrent users at any time. A
> typical query looks like [1] and var= ies in an unforeseable way (that's
> why hstore is used). EXPLAIN tel= ls me that the indexes are used [2].
>
> The problem is that th= e initial queries are too slow - and there is no
> second chance. I d= o have to trash the buffer every night. There is
> enough main memory= to hold all table contents.

Just that table, or the entire database= ?

>
> 1. How can I warm up or re-populate shared buffers of= Postgres?

Instead, warm the OS cache.  Then data will get tran= sferred into the
postgres shared_buffers pool from the OS cache very qui= ckly.

tar -c $PGDATA/base/ |wc -c

If you need to warm just on= e table, because the entire base directory
won't fit in OS cache, then you need to do a bit more work to find out
which files to use.
=
You might feel clever and try this instead:

tar -c /dev/null $PG= DATA/base/ > /dev/null

But my tar program is too clever by half.&= nbsp; It detects that it is
writing to /dev/null, and just does not actu= ally read the data.

> 2. Are there any hints on how to tell Postg= res to read in all table
> contents into memory?

I don't think= so, at least not in core.  I've wondered if it would
make sense to= suppress ring-buffer strategy when there are buffers on
the free-list.&= nbsp; That way a sequential scan would populate
shared_buffers after a r= estart.  But it wouldn't help you get the
indexes into cache.
Cheers,

Jeff

--
Sent via pgsql-performance mailing list= (pgsql-performance@post= gresql.org)
To make changes to your subscription:
http://= www.postgresql.org/mailpref/pgsql-performance


= --1165585747-1193595810-1330246327=:76787--