Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 45708F497B7 for ; Mon, 27 Feb 2012 08:46:57 -0400 (AST) Received: from nm18-vm0.bullet.mail.bf1.yahoo.com ([98.139.213.138]) by makus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1S1zyf-00024p-Jc for pgsql-performance@postgresql.org; Mon, 27 Feb 2012 12:46:56 +0000 Received: from [98.139.214.32] by nm18.bullet.mail.bf1.yahoo.com with NNFMP; 27 Feb 2012 12:46:39 -0000 Received: from [98.139.212.251] by tm15.bullet.mail.bf1.yahoo.com with NNFMP; 27 Feb 2012 12:46:39 -0000 Received: from [127.0.0.1] by omp1060.mail.bf1.yahoo.com with NNFMP; 27 Feb 2012 12:46:39 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 391657.49422.bm@omp1060.mail.bf1.yahoo.com Received: (qmail 47602 invoked by uid 60001); 27 Feb 2012 12:46:38 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1330346798; bh=MoLSkUERo3ardo9cD8ZHm3w4ol3B/EDpKgYuXf2PVSM=; 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=2W/PqIbr5phbf1gF/WOKBSywtmy3MRxiNxSE2Y8NLIZdGm7D66ElptJdR2yNT8+hiY9MkyR3msxmbQ7e6MlbRdpy2+yqlJoWj68A0I++i1FOoEcZOZfaq+NWhRcoFfysELYimCpfAFZKfSO8sZa4+dMI/ZSESsj9jNKcdscPOa4= 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=V27EbIeRv3ajUFG9hj1RnU4ZkyrWBPyUqfxbSwPPvffag83gpP9xRa0s4Led7H3CO0JKxJqmdms8+qNiABi+RsfN9/w8Ipu9hDAOw3O6B+qtXOpQiV957ISrpTB5bmbVA51MYlc66T18pgDF6g1vHZzsBKmtsTPtRDH8GHIqqyc=; X-YMail-OSG: 6RM1D50VM1lZ09lzy4_DIOwRFi8LzysuUvbM2A9NE8RuKpe DMJAJuWBMS5HhbmdJMHKFFw6j06.LE5UdbcR8ysAT82Evb59Ycgj3knLKhIy d7Z9Jh7huq_jI3q6ZDr9cOEjfmnlQDTi3VeGS74zgvtFuhQiS1M1kHqmQugP T.OPQmwnFtRlk5VdVV2U2sNnOzfmHDKFK0tuR96st6T2HKlUYSSMwY.fsAyl kpZZzcF1.ulJRaNaKtUvQf.hpjjgSwGdUPoXYGj0O7BQoml1qYYr274XHYBg IK6WAfNtXueogcb5jK7zwTgH4_l7mzCZOuBKM2gKIPXFQ1AeakswuPhOMBkH FLk.LbDvceW0wzqyiBB2zqk7zVMnqHtyhj0JzTQp5js__RIHK55_OIIF3n1Q EO8xCtvF9cED3VAnwJPMHlehFdWemC7IPH2kGTiP9Du6LvbZpz0mMpIkKLbt 2oPQIC1iqzobt1DjBD2vNi5VmusK5.lvsqyTPEHUWiWw24tS0jrTKBdAkYmO lYT0wXW0eBtk4Aw-- Received: from [114.88.119.207] by web30808.mail.mud.yahoo.com via HTTP; Mon, 27 Feb 2012 04:46:38 PST X-Mailer: YahooMailWebService/0.8.116.338427 References: Message-ID: <1330346798.90731.YahooMailNeo@web30808.mail.mud.yahoo.com> Date: Mon, 27 Feb 2012 04:46:38 -0800 (PST) From: Wales Wang Reply-To: Wales Wang Subject: =?utf-8?B?5Zue5aSN77yaIFtQRVJGT1JNXSBQRyBhcyBpbi1tZW1vcnkgZGI/IEhvdyB0?= =?utf-8?B?byB3YXJtIHVwIGFuZCByZS1wb3B1bGF0ZSBidWZmZXJzPyBIb3cgdG8gcmVh?= =?utf-8?B?ZCBpbiBhbGwgdHVwbGVzIGludG8gbWVtb3J5Pw==?= To: Jeff Janes , Stefan Keller Cc: "pgsql-performance@postgresql.org" , Stephen Frost In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="1267847036-1855262320-1330346798=:90731" X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201202/223 X-Sequence-Number: 46395 --1267847036-1855262320-1330346798=:90731 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable There are many=C2=A0approach for PostgreSQL in-memory.=0A=0AThe quick and e= asy way is making slave pgsql run on persistent RAM filesystem, the slave i= s part of master/slave replication cluster.=0A=C2=A0=0AThe fstab and script= =C2=A0make RAM file system persistent is below:=0ASetup:=0AFirst, create a = mountpoint for the disk : =0Amkdir /mnt/ramdisk=0ASecondly, add this line t= o /etc/fstab in to mount the drive at boot-time. =0Atmpfs=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 /mnt/ramdisk tmpfs=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 defaults,size=3D65536M 0 0=0A=0A#! /bin/sh =0A# /etc/ini= t.d/ramdisk.sh=0A#=0A=C2=A0=0Acase "$1" in=0A=C2=A0 start)=0A=C2=A0=C2=A0= =C2=A0 echo "Copying files to ramdisk"=0A=C2=A0=C2=A0=C2=A0 rsync -av /data= /ramdisk-backup/ /mnt/ramdisk/=0A=C2=A0=C2=A0=C2=A0 echo [`date +"%Y-%m-%d = %H:%M"`] Ramdisk Synched from HD >> /var/log/ramdisk_sync.log=0A=C2=A0=C2= =A0=C2=A0 ;;=0A=C2=A0 sync)=0A=C2=A0=C2=A0=C2=A0 echo "Synching files from = ramdisk to Harddisk"=0A=C2=A0=C2=A0=C2=A0 echo [`date +"%Y-%m-%d %H:%M"`] R= amdisk Synched to HD >> /var/log/ramdisk_sync.log=0A=C2=A0=C2=A0=C2=A0 rsyn= c -av --delete --recursive --force /mnt/ramdisk/ /data/ramdisk-backup/=0A= =C2=A0=C2=A0=C2=A0 ;;=0A=C2=A0 stop)=0A=C2=A0=C2=A0=C2=A0 echo "Synching lo= gfiles from ramdisk to Harddisk"=0A=C2=A0=C2=A0=C2=A0 echo [`date +"%Y-%m-%= d %H:%M"`] Ramdisk Synched to HD >> /var/log/ramdisk_sync.log=0A=C2=A0=C2= =A0=C2=A0 rsync -av --delete --recursive --force /mnt/ramdisk/ /data/ramdis= k-backup/=0A=C2=A0=C2=A0=C2=A0 ;;=0A=C2=A0 *)=0A=C2=A0=C2=A0=C2=A0 echo "Us= age: /etc/init.d/ramdisk {start|stop|sync}"=0A=C2=A0=C2=A0=C2=A0 exit 1=0A= =C2=A0=C2=A0=C2=A0 ;;=0Aesac=0Aexit 0=0A=C2=A0=0Ayou can run it when startu= p and shutdown and crontabe hoursly.=0A=C2=A0=0AWales Wang =0A=0A__________= ______________________=0A =E5=8F=91=E4=BB=B6=E4=BA=BA=EF=BC=9A Jeff Janes <= jeff.janes@gmail.com>=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 Wales Wang ; pgsql-performance@postgresql.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= =8827=E6=97=A5, =E6=98=9F=E6=9C=9F=E4=B8=80, =E4=B8=8A=E5=8D=88 6:34=0A=E4= =B8=BB=E9=A2=98: Re: [PERFORM] PG as in-memory db? How to warm up and re-po= pulate buffers? How to read in all tuples into memory?=0A =0AOn Sun, Feb 2= 6, 2012 at 2:56 AM, Stefan Keller wrote:=0A> Hi Jeff a= nd Wales,=0A>=0A> 2012/2/26 Jeff Janes wrote:=0A>>> = The problem is that the initial queries are too 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>>=0A>> Just that table, o= r the entire database?=0A>=0A> The entire database consisting of only about= 5 tables which are=0A> similar but with different geometry types plus a re= lations table (as=0A> OpenStreetMap calls it).=0A=0AAnd all of those combin= ed fit in RAM?=C2=A0 With how much to spare?=0A=0A>=0A>>> 1. How can I warm= up or re-populate shared buffers of Postgres?=0A>>=0A>> Instead, warm the = OS cache. =E7=87=ADhen data will get transferred into the=0A>> postgres sha= red_buffers pool from the OS cache very quickly.=0A>>=0A>> tar -c $PGDATA/b= ase/ |wc -c=0A>=0A> Ok. So with "OS cache" you mean the files which to me a= re THE database itself?=0A=0AMost operating systems will use any otherwise = unused RAM to cache=0A"recently" accessed file-system data.=C2=A0 That is t= he OS cache.=C2=A0 The=0Apurpose of the tar is to populate the OS cache wit= h the "database=0Aitself".=C2=A0 That way, when postgres wants something th= at isn't already=0Ain shared_buffers, it doesn't require a disk read to get= it, just a=0Arequest to the OS.=0A=0ABut this trick is most useful after t= he OS has been restarted so the=0AOS cache is empty.=C2=A0 If the OS has be= en up for a long time, then why=0Aisn't it already populated with the data = you need?=C2=A0 Maybe the data=0Adoesn't fit, maybe some other process has = trashed the cache (in which=0Acase, why would it not continue to trash the = cache on an ongoing=0Abasis?)=0A=0ASince you just recently created the tabl= es and indexes, they must have=0Apassed through the OS cache on the way to = disk.=C2=A0 So why aren't they=0Astill there?=C2=A0 Is shared_buffers so la= rge that little RAM is left over=0Afor the OS?=C2=A0 Did you reboot the OS?= =C2=A0 Are there other processes running=0Athat drive the database-specific= files out of the OS cache?=0A=0A> A cache to me is a second storage with "= controlled redudancy" because=0A> of performance reasons.=0A=0AYeah.=C2=A0 = But there are multiple caches, with different parties in=0Acontrol and diff= erent opinions of what is redundant.=0A=0A>>> 2. Are there any hints on how= to tell Postgres to read in all table=0A>>> contents into memory?=0A>>=0A>= > I don't think so, at least not in core. =E7=87=9F've wondered if it would= =0A>> make sense to suppress ring-buffer strategy when there are buffers on= =0A>> the free-list. =E7=87=ADhat way a sequential scan would populate=0A>>= shared_buffers after a restart. =E7=87=98ut it wouldn't help you get the= =0A>> indexes into cache.=0A>=0A> So, are there any developments going on w= ith PostgreSQL as Stephen=0A> suggested in the former thread?=0A=0AI don't = see any active development for the upcoming release, and most=0Aof what has= been suggested wouldn't help you because they are about=0Are-populating th= e cache with previously hot data, while you are=0Adestroying your previousl= y hot data and wanting to specify the=0Afuture-hot data.=0A=0ABy the way, y= our explain plan would be more useful if it included=0Abuffers.=C2=A0 "Expl= ain (analyze, buffers) select..."=0A=0AI don't know that it is ever better = to run analyze without buffers,=0Aother than for backwards compatibility.= =C2=A0 I'm trying to get in the=0Ahabit of just automatically doing it.=0A= =0ACheers,=0A=0AJeff=0A=0A-- =0ASent via pgsql-performance mailing list (pg= sql-performance@postgresql.org)=0ATo make changes to your subscription:=0Ah= ttp://www.postgresql.org/mailpref/pgsql-performance --1267847036-1855262320-1330346798=:90731 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
There are = many approach for PostgreSQL in-memory.
The quick and= easy way is making slave pgsql run on persistent RAM filesystem, the slave= is part of master/slave replication cluster.
 
Th= e fstab and script make RAM file system persistent is below:
Setup:
First, create a mountpoint for the disk :
mkdir /mnt/= ramdisk
Secondly, add this line to /etc/fstab in to mount the dri= ve at boot-time.
tmpfs        &= nbsp;  /mnt/ramdisk tmpfs      defaults,size= =3D65536M 0 0
#! /bin/sh
# /etc/init.d/ramdisk.sh
# 
case "$1" in
  start)
    echo "Copyin= g files to ramdisk"
    rsync -av /data/ramdisk-backup/ /mnt/ramdisk/
    echo [`date +"%Y-%m-%d %H:%M"`] Ramdis= k Synched from HD >> /var/log/ramdisk_sync.log
    = ;;
  sync)
    echo "Synching files from ramdisk = to Harddisk"
    echo [`date +"%Y-%m-%d %H:%M"`] Ramdisk = Synched to HD >> /var/log/ramdisk_sync.log
    rsyn= c -av --delete --recursive --force /mnt/ramdisk/ /data/ramdisk-backup/
&= nbsp;   ;;
  stop)
    echo "Synching l= ogfiles from ramdisk to Harddisk"
    echo [`date +"%Y-%m= -%d %H:%M"`] Ramdisk Synched to HD >> /var/log/ramdisk_sync.log
&n= bsp;   rsync -av --delete --recursive --force /mnt/ramdisk/ /data= /ramdisk-backup/
    ;;
  *)
   = ; echo "Usage: /etc/init.d/ramdisk {start|stop|sync}"
   = exit 1
    ;;
esac
exit 0
 
you can run it when startup and shutdown and = crontabe hoursly.
 
Wales Wang
=E5= =8F=91=E4=BB=B6=E4=BA=BA=EF=BC=9A Jeff Janes <jeff.janes@gmai= l.com>
=E6=94=B6=E4=BB=B6=E4= =BA=BA=EF=BC=9A Stefan Keller <sfkeller@gmail.com>
= =E6=8A=84=E9=80=81=EF=BC=9A W= ales Wang <wormwang@yahoo.com>; pgsql-performance@postgresql.org; Ste= phen Frost <sfrost@snowman.net>
=E5=8F=91=E9=80=81=E6=97=A5=E6=9C=9F=EF=BC=9A 2012=E5=B9= =B42=E6=9C=8827=E6=97=A5, =E6=98=9F=E6=9C=9F=E4=B8=80, =E4=B8=8A=E5=8D=88 6= :34
=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?

On Sun, Feb = 26, 2012 at 2:56 AM, Stefan Keller <sfkeller@gmail.com> wrote:> Hi Jeff and Wales,
>
> 2012/2/26 Jeff Janes <je= ff.janes@gmail.com> wrote:
>>> The problem is that the i= nitial queries are too slow - and there is no
>>> second chance= . I do have to trash the buffer every night. There is
>>> enoug= h main memory to hold all table contents.
>>
>> Just that= table, or the entire database?
>
> The entire database consist= ing of only about 5 tables which are
> similar but with different geometry types plus a = relations table (as
> OpenStreetMap calls it).

And all of thos= e combined fit in RAM?  With how much to spare?

>
>>= ;> 1. How can I warm up or re-populate shared buffers of Postgres?
&g= t;>
>> Instead, warm the OS cache. =E7=87=ADhen data will get t= ransferred into the
>> postgres shared_buffers pool from the OS ca= che very quickly.
>>
>> tar -c $PGDATA/base/ |wc -c
&g= t;
> Ok. So with "OS cache" you mean the files which to me are THE da= tabase itself?

Most operating systems will use any otherwise unused = RAM to cache
"recently" accessed file-system data.  That is the OS = cache.  The
purpose of the tar is to populate the OS cache with the= "database
itself".  That way, when postgres wants something that i= sn't already
in shared_buffers, it doesn't require a disk read to get it, just a
request to the OS.

But this trick is most useful afte= r the OS has been restarted so the
OS cache is empty.  If the OS ha= s been up for a long time, then why
isn't it already populated with the = data you need?  Maybe the data
doesn't fit, maybe some other proces= s has trashed the cache (in which
case, why would it not continue to tra= sh the cache on an ongoing
basis?)

Since you just recently create= d the tables and indexes, they must have
passed through the OS cache on = the way to disk.  So why aren't they
still there?  Is shared_b= uffers so large that little RAM is left over
for the OS?  Did you r= eboot the OS?  Are there other processes running
that drive the dat= abase-specific files out of the OS cache?

> A cache to me is a se= cond storage with "controlled redudancy" because
> of performance rea= sons.

Yeah.  But there are multiple caches, with different parties in
control and different opinions of what is redundan= t.

>>> 2. Are there any hints on how to tell Postgres to re= ad in all table
>>> contents into memory?
>>
>&g= t; I don't think so, at least not in core. =E7=87=9F've wondered if it woul= d
>> make sense to suppress ring-buffer strategy when there are bu= ffers on
>> the free-list. =E7=87=ADhat way a sequential scan woul= d populate
>> shared_buffers after a restart. =E7=87=98ut it would= n't help you get the
>> indexes into cache.
>
> So, ar= e there any developments going on with PostgreSQL as Stephen
> sugges= ted in the former thread?

I don't see any active development for the= upcoming release, and most
of what has been suggested wouldn't help you= because they are about
re-populating the cache with previously hot data= , while you are
destroying your previously hot data and wanting to speci= fy the
future-hot data.

By the way, your explain plan would be more= useful if it included
buffers.  "Explain (analyze, buffers) select= ..."

I don't know that it is ever better to run analyze without buff= ers,
other than for backwards compatibility.  I'm trying to get in = the
habit of just automatically doing it.

Cheers,

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


--1267847036-1855262320-1330346798=:90731--