Re: [PERFORM] Best way to load test a postgresql server

[Erik Aronesty]

Technically you can then use pgbench on that set of statements, but I
usually just use perl's "Benchmark" module (i'm sure ruby or java
or whatever has a similar tool)

(First, I log statements by loading the application or web server with
statement logging turned on so I'm not "guessing" what sql will be
called.   Usually doing this exposes a flotilla of inefficencies in
the code )


On Tue, Jun 9, 2009 at 9:53 AM, Tom Lane wrote:
> Shaul Dar  writes:
>> Have you actually run pgbench against your own schema? Can you point me to
>> an example? I also had the same impression reading the documentation. But
>> when I tried it with the proper flags to use my own DB and query file I got
>> an error that it couldn't find one of the tables mentioned in the built-in
>> test! I concluded that I cannot use any schema,
>
> No, you just need to read the documentation.  There's a switch that
> prevents the default action of trying to vacuum the "standard" tables.
> I think -N, but too lazy to look ...
>
>                        regards, tom lane
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

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

Re: [PERFORM] GiST index performance

[Tom Lane]

Matthew Wakeling  writes:
> The gistnext total doesn't seem to correspond to the amount I get by 
> adding up all the individual lines in gistnest.

Hmm, hadn't you determined that some other stuff was being inlined into
gistnext?  I'm not really sure how opannotate displays such cases, but
this might be an artifact of that.

> However, yes it does seem like fmgr.c accounts for a large proportion of 
> samples. Also, I still seem to be getting mcount, even after recompiling 
> without --enable-profiling.

You must still have some -pg code in there someplace.  Maybe you didn't
recompile bioseg.so, or even psql?  Remember the libc counts you are
looking at are for system-wide usage of libc.

regards, tom lane

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

Re: [PERFORM] GiST index performance

[Matthew Wakeling]

Okay, I don't know quite what's happening here. Tom, perhaps you could 
advise. Running opannotate --source, I get this sort of stuff:


/*
 * Total samples for file : 
".../postgresql-8.4beta2/src/backend/access/gist/gistget.c"
 *
 *   6880  0.2680
 */

and then:

   :static int64
   :gistnext(IndexScanDesc scan, TIDBitmap *tbm)
81  0.0032 :{ /* gistnext total: 420087 16.3649 */
   :Pagep;



The gistnext total doesn't seem to correspond to the amount I get by 
adding up all the individual lines in gistnest. Moreover, it is greater 
than the total samples attributed to the whole file, and greater than the 
samples assigned to all the lines where gistnext is called.


However, yes it does seem like fmgr.c accounts for a large proportion of 
samples. Also, I still seem to be getting mcount, even after recompiling 
without --enable-profiling.


CPU: Core 2, speed 1998 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask 
of 0x00 (Unhalted core cycles) count 10
samples  %image name   app name symbol name
460213   17.9280  postgres postgres 
fmgr_oldstyle
420087   16.3649  postgres postgres gistnext
2549759.9328  postgres postgres 
FunctionCall5
2395729.3327  libc-2.9.so  libc-2.9.so  mcount
2199638.5689  libc-2.9.so  libc-2.9.so  
__mcount_internal
1256744.8957  no-vmlinux   no-vmlinux   (no symbols)
1171844.5650  postgres postgres 
gistdentryinit
1069674.1670  btree_gist.sobtree_gist.so
gbt_int4_consistent
95677 3.7272  postgres postgres 
FunctionCall1
75397 2.9372  bioseg.sobioseg.so
bioseg_gist_consistent
58832 2.2919  btree_gist.sobtree_gist.so
gbt_num_consistent
39128 1.5243  bioseg.sobioseg.so
bioseg_overlap
33874 1.3196  libxul.solibxul.so(no symbols)
32008 1.2469  bioseg.sobioseg.so
bioseg_gist_leaf_consistent
20890 0.8138  nvidia_drv.sonvidia_drv.so(no symbols)
19321 0.7527  bioseg.sobioseg.so
bioseg_gist_decompress
17365 0.6765  libmozjs.so.1d   libmozjs.so.1d   (no symbols)

Matthew

--
A good programmer is one who looks both ways before crossing a one-way street.
Considering the quality and quantity of one-way streets in Cambridge, it
should be no surprise that there are so many good programmers there.

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

Re: [PERFORM] GiST index performance

[Matthew Wakeling]

On Thu, 11 Jun 2009, Matthew Wakeling wrote:
A quick grep in the postgres source for mcount reveals no hits. No idea what 
it does - there is no man page for it.


Ah - that's part of gprof. I'll recompile without --enable-profiling and 
try again. Duh.


Matthew

--
What goes up must come down. Ask any system administrator.

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

Re: [PERFORM] GiST index performance

[Matthew Wakeling]

On Thu, 11 Jun 2009, Tom Lane wrote:

So it'd be worth converting your functions to V1 style.


Does that produce a significant reduction in overhead? (You'll probably 
say "yes, that's the whole point").



hmm ... memcpy or qsort maybe?


Surprise:

CPU: Core 2, speed 1998 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask 
of 0x00 (Unhalted core cycles) count 10
samples  %image name   app name symbol name
3005354  40.2868  libc-2.9.so  libc-2.9.so  
__mcount_internal
1195592  16.0269  libc-2.9.so  libc-2.9.so  mcount
5499987.3727  postgres postgres gistnext
4204655.6363  postgres postgres 
fmgr_oldstyle
3763335.0447  no-vmlinux   no-vmlinux   (no symbols)
2109842.8282  postgres postgres 
FunctionCall5
1825092.4465  postgres postgres 
gistdentryinit
1743562.3372  btree_gist.sobtree_gist.so
gbt_int4_consistent
1428291.9146  postgres postgres 
FunctionCall1
1298001.7400  postgres postgres .plt
1191801.5976  nvidia_drv.sonvidia_drv.so(no symbols)
96351 1.2916  libxul.solibxul.so(no symbols)
91726 1.2296  btree_gist.sobtree_gist.so
gbt_num_consistent

A quick grep in the postgres source for mcount reveals no hits. No idea 
what it does - there is no man page for it.


Matthew

--
I pause for breath to allow you to get over your shock that I really did cover
all that in only five minutes...-- Computer Science Lecturer

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

Re: [PERFORM] Postgres replication: dump/restore, PITR, Slony,...?

[Joshua D. Drake]

On Thu, 2009-06-11 at 16:30 +, Greg Sabino Mullane wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: RIPEMD160
> 
> >> Then there are Slony-I, Buchardo, Mamoth Replicator from CMO, simple
> >> replication in Postgres 8.4 and other projects...
> 
> > CMO? :)
> 
> Buchardo? :)

A new desert, Buchardo CMO:

Two shots of brandy
One shot of rum
Vanilla Ice cream
Cherries

Blend to perfection.

Joshua D. Drake

-- 
PostgreSQL - XMPP: jdr...@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997


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

Re: [PERFORM] Postgres replication: dump/restore, PITR, Slony,...?

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160

>> Then there are Slony-I, Buchardo, Mamoth Replicator from CMO, simple
>> replication in Postgres 8.4 and other projects...

> CMO? :)

Buchardo? :)


- --
Greg Sabino Mullane g...@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200906111229
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAkoxMPkACgkQvJuQZxSWSsizywCbBtuo7cbCwmlHzvbi1kak9leF
XwYAnA5dXlZqyyUOQrymXZf4yGJSMSq6
=UPhb
-END PGP SIGNATURE-



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

Re: [PERFORM] Postgres replication: dump/restore, PITR, Slony,...?

[Joshua D. Drake]

> 
> Then there are Slony-I, Buchardo, Mamoth Replicator from CMO, simple
> replication in Postgres 8.4 and other projects...

CMO? :)

Joshua D. Drake
> 
> Suggestions?
> Thanks,
> 
> -- Shaul
> 
-- 
PostgreSQL - XMPP: jdr...@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997


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

Re: [PERFORM] GiST index performance

[Tom Lane]

Matthew Wakeling  writes:
> So it seems that btree_gist and bioseg are not using that much CPU at all, 
> compared to core postgres code. In fact, the majority of time seems to be 
> spent in libc. Unfortunately my libc doesn't have any debugging symbols.

hmm ... memcpy or qsort maybe?

> Anyway, running opannotate seems to make it clear that time *is* spent in 
> the gistnext function, but almost all of that is in children of the 
> function. Lots of time is actually spent in fmgr_oldstyle though.

So it'd be worth converting your functions to V1 style.

> I'm guessing my next step is to install a version of libc with debugging 
> symbols?

Yeah, if you want to find out what's happening in libc, that's what you
need.

regards, tom lane

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

Re: [PERFORM] GiST index performance

[Matthew Wakeling]

On Thu, 11 Jun 2009, Tom Lane wrote:

Oprofile scares me with the sheer number of options.


You can ignore practically all of them; the defaults are pretty sane.


Thanks, that was helpful. Here is the top of opreport --long-filenames:

CPU: Core 2, speed 1998 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask 
of 0x00 (Unhalted core cycles) count 10
CPU_CLK_UNHALT...|
  samples|  %|
--
  5114464 61.5404 /lib/libc-2.9.so
  1576829 18.9734 /changeable/pgsql_8.4_profiling/bin/postgres
CPU_CLK_UNHALT...|
  samples|  %|
--
  1572346 99.7157 /changeable/pgsql_8.4_profiling/bin/postgres
 4482  0.2842 [vdso] (tgid:13593 
range:0x7fff8dbff000-0x7fff8dc0)
1 6.3e-05 [vdso] (tgid:13193 
range:0x7fff8dbff000-0x7fff8dc0)
   409534  4.9278 /no-vmlinux
   309990  3.7300 /changeable/pgsql_8.4_profiling/lib/btree_gist.so
   203684  2.4509 /changeable/pgsql_8.4_profiling/lib/bioseg.so

So it seems that btree_gist and bioseg are not using that much CPU at all, 
compared to core postgres code. In fact, the majority of time seems to be 
spent in libc. Unfortunately my libc doesn't have any debugging symbols.


Anyway, running opannotate seems to make it clear that time *is* spent in 
the gistnext function, but almost all of that is in children of the 
function. Lots of time is actually spent in fmgr_oldstyle though.


Here is the top of opreport -l:

CPU: Core 2, speed 1998 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask 
of 0x00 (Unhalted core cycles) count 10
samples  %image name   app name symbol name
5114464  61.5404  libc-2.9.so  libc-2.9.so  (no symbols)
4962155.9708  postgres postgres gistnext
4095344.9278  no-vmlinux   no-vmlinux   (no symbols)
4040374.8616  postgres postgres 
fmgr_oldstyle
1700792.0465  btree_gist.sobtree_gist.so
gbt_int4_consistent
1600161.9254  postgres postgres 
gistdentryinit
1532661.8442  nvidia_drv.sonvidia_drv.so(no symbols)
1524631.8345  postgres postgres 
FunctionCall5
1493741.7974  postgres postgres 
FunctionCall1
1311121.5776  libxul.solibxul.so(no symbols)
1208711.4544  postgres postgres .plt
94506 1.1372  bioseg.sobioseg.so
bioseg_gist_consistent

I'm guessing my next step is to install a version of libc with debugging 
symbols?


Matthew

--
Some people, when confronted with a problem, think "I know, I'll use regular
expressions." Now they have two problems.  -- Jamie Zawinski

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

Re: [PERFORM] Postgres replication: dump/restore, PITR, Slony,...?

[Dimitri Fontaine]

Hi,

Shaul Dar  writes:
> 1. A staging server, which receives new data and updates the DB
> 2. Two web servers that have copies of the DB (essentially read-only)
> and answer user queries (with load balancer)

[...]

> Suggestions?

I'd consider WAL Shipping for the staging server and some trigger based
asynchronous replication for feeding the web servers.

More specifically, I'd have a try at Skytools, using walmgr.py for WAL
Shipping and Londiste for replication.
  http://wiki.postgresql.org/wiki/Skytools
  http://wiki.postgresql.org/wiki/Londiste_Tutorial

Regards,
-- 
dim

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

Re: [PERFORM] GiST index performance

[Tom Lane]

Matthew Wakeling  writes:
> That sucks. However, as another observation, no calls to "gistfindnext" 
> are recorded in the profile either, and that's in the same source file as 
> "gistnext" which is recorded. Could it have been inlined?

Probably.

> Shouldn't inlining be switched off on a profiling build?

Why?  You generally want to profile the code's actual behavior, or as
near as you can get to observing that.  Defeating compiler optimizations
doesn't sound like something that -pg should do on its own.  If you
really want it, there's a switch for it.

> Oprofile scares me with the sheer number of options.

You can ignore practically all of them; the defaults are pretty sane.
The recipe I usually follow is:


Initial setup (only needed once per system boot):

sudo opcontrol --init
sudo opcontrol --setup --no-vmlinux

(If you need detail about what the kernel is doing, you need kernel
debug symbols and then specify them on the previous line)

Start/stop profiling

sudo opcontrol --start
sudo opcontrol --reset
... exercise your debug-enabled program here ...
sudo opcontrol --dump ; sudo opcontrol --shutdown

The test case should run at least a minute or two to get numbers with
reasonable accuracy.

Analysis:

opreport --long-filenames | more

opreport -l image:/path/to/postgres | more

if you really want detail:

opannotate --source /path/to/postgres >someplace

regards, tom lane

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

[PERFORM] Postgres replication: dump/restore, PITR, Slony,...?

[Shaul Dar]

Hi,

Our configuration is as follows:

1. A staging server, which receives new data and updates the DB
2. Two web servers that have copies of the DB (essentially read-only) and
answer user queries (with load balancer)

Currently we use dump (to SQL file, i.e. pg_dump with no args) + file copy
to replicate the DB daily between the staging and web servers, and then
restore (via psql) the servers one at the time. In our application we expect
that average daily change is only to 3% of the records. My question is what
would be the best way to do this replication?

I read about continuous archiving and
PITR.
My understanding however (e.g. from
this)
is that I cannot do a base backup once and then e.g. apply WAL files on a
daily basis, starting from yesterday's DB, but must instead redo the full
base backup before starting recovery?

Then there are Slony-I, Buchardo, Mamoth Replicator from CMO, simple
replication in Postgres 8.4 and other projects...

Suggestions?
Thanks,

-- Shaul

Re: [PERFORM] GiST index performance

[Matthew Wakeling]

On Wed, 10 Jun 2009, Tom Lane wrote:

Also, no calls to anything including "bioseg"
in the name are recorded, although they are definitely called as the GiST
support functions for that data type.


I have never had any success in getting gprof to profile functions that
are in loadable libraries, which of course is exactly what you need to do
here.


That sucks. However, as another observation, no calls to "gistfindnext" 
are recorded in the profile either, and that's in the same source file as 
"gistnext" which is recorded. Could it have been inlined? Shouldn't 
inlining be switched off on a profiling build?


...the best bet is probably to make a test build of Postgres in which 
your functions are linked directly into the main postgres executable.


I'll give that a try. Oprofile scares me with the sheer number of options.

Matthew

--
Prolog doesn't have enough parentheses. -- Computer Science Lecturer

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

Re: [PERFORM] Hosted servers with good DB disk performance?

[Dimitri Fontaine]

"Markus Wanner"  writes:
> If anybody has ever tried their systems, I'd like to hear back. I wish  such
> an offering would exist for Europe (guess that's just a matter of  time).

  http://www.niftyname.org/
  http://lost-oasis.fr/

It seems to be coming very soon, in France :)
-- 
dim

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