Re: pg_restore --multi-thread

I know we've already had a discussion on the naming of the pg_restore -m
option, but in any case this description in pg_restore --help is confusing:

-m, --multi-thread=NUM use this many parallel connections to restore

Either it is using that many threads in the client, or it is using that many
connections to the server. I assume the implementation does approximately
both, but we should be clear about what we promise to the user. Either:
Reserve this many connections on the server. Or: Reserve this many threads
in the kernel of the client. The documentation in the reference/man page is
equally confused.

Also, the term "multi" is redundant, because whether it is multi or single is
obviously determined by the value of NUM.

Peter Eisentraut wrote:
> I know we've already had a discussion on the naming of the pg_restore -m
> option, but in any case this description in pg_restore --help is confusing:
>
> -m, --multi-thread=NUM use this many parallel connections to restore
>
> Either it is using that many threads in the client, or it is using that many
> connections to the server. I assume the implementation does approximately
> both, but we should be clear about what we promise to the user. Either:
> Reserve this many connections on the server. Or: Reserve this many threads
> in the kernel of the client. The documentation in the reference/man page is
> equally confused.
>
> Also, the term "multi" is redundant, because whether it is multi or single is
> obviously determined by the value of NUM.
>
>

The implementation is actually different across platforms: on Windows
the workers are genuine threads, while elsewhere they are forked
children in the same fashion as the backend (non-EXEC_BACKEND case). In
either case, the program will use up to NUM concurrent connections to
the server.

I'm not sure what you mean about reserving threads in the client kernel.

I also don't really understand what is confusing about the description.

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> The implementation is actually different across platforms: on Windows
> the workers are genuine threads, while elsewhere they are forked
> children in the same fashion as the backend (non-EXEC_BACKEND case). In
> either case, the program will use up to NUM concurrent connections to
> the server.

How about calling it --num-connections or something like that? I agree
with Peter that "thread" is not the best terminology on platforms where
there is no threading involved.

regards, tom lane

On Thu, 2009-02-12 at 11:32 -0500, Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> > The implementation is actually different across platforms: on Windows
> > the workers are genuine threads, while elsewhere they are forked
> > children in the same fashion as the backend (non-EXEC_BACKEND case). In
> > either case, the program will use up to NUM concurrent connections to
> > the server.
>
> How about calling it --num-connections or something like that? I agree
> with Peter that "thread" is not the best terminology on platforms where
> there is no threading involved.

--num-workers or --num-connections would both work.

Joshua D. Drake

>
> regards, tom lane
>
--
PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

Joshua D. Drake wrote:
> On Thu, 2009-02-12 at 11:32 -0500, Tom Lane wrote:
>
>> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>>
>>> The implementation is actually different across platforms: on Windows
>>> the workers are genuine threads, while elsewhere they are forked
>>> children in the same fashion as the backend (non-EXEC_BACKEND case). In
>>> either case, the program will use up to NUM concurrent connections to
>>> the server.
>>>
>> How about calling it --num-connections or something like that? I agree
>> with Peter that "thread" is not the best terminology on platforms where
>> there is no threading involved.
>>
>
> --num-workers or --num-connections would both work.
>
>

*shrug* whatever. What should the short option be (if any?). -n is
taken, so -N ?

cheers

andrew

On Thu, 2009-02-12 at 11:47 -0500, Andrew Dunstan wrote:
>
> Joshua D. Drake wrote:
> > On Thu, 2009-02-12 at 11:32 -0500, Tom Lane wrote:
> >
> >> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> >>
> >>> The implementation is actually different across platforms: on Windows
> >>> the workers are genuine threads, while elsewhere they are forked
> >>> children in the same fashion as the backend (non-EXEC_BACKEND case). In
> >>> either case, the program will use up to NUM concurrent connections to
> >>> the server.
> >>>
> >> How about calling it --num-connections or something like that? I agree
> >> with Peter that "thread" is not the best terminology on platforms where
> >> there is no threading involved.
> >>
> >
> > --num-workers or --num-connections would both work.
> >
> >
>
> *shrug* whatever. What should the short option be (if any?). -n is
> taken, so -N ?

Works for me.

>
> cheers
>
> andrew
>
--
PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Joshua D. Drake a écrit :
> On Thu, 2009-02-12 at 11:47 -0500, Andrew Dunstan wrote:
>> Joshua D. Drake wrote:
>>> On Thu, 2009-02-12 at 11:32 -0500, Tom Lane wrote:
>>>
>>>> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>>>>
>>>>> The implementation is actually different across platforms: on Windows
>>>>> the workers are genuine threads, while elsewhere they are forked
>>>>> children in the same fashion as the backend (non-EXEC_BACKEND case). In
>>>>> either case, the program will use up to NUM concurrent connections to
>>>>> the server.
>>>>>
>>>> How about calling it --num-connections or something like that? I agree
>>>> with Peter that "thread" is not the best terminology on platforms where
>>>> there is no threading involved.
>>>>
>>> --num-workers or --num-connections would both work.
>>>
>>>
>> *shrug* whatever. What should the short option be (if any?). -n is
>> taken, so -N ?
>
> Works for me.

is -j already affected ?

>
>> cheers
>>
>> andrew
>>

- --
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkmUcvUACgkQo/dppWjpEvzT5gCg44yo8CbfT3AAevzbPXphqu3K
oeUAnAy6/epLlwe7DWWneIB8XVeDIu/+
=Q8iq
-----END PGP SIGNATURE-----

On Thu, Feb 12, 2009 at 11:37 AM, Joshua D. Drake <jd(at)commandprompt(dot)com>wrote:

> --num-workers or --num-connections would both work.

--num-parallel?

--
Jonah H. Harris, Senior DBA
myYearbook.com

On 2009-02-12, at 14:15 , Jonah H. Harris wrote:

> On Thu, Feb 12, 2009 at 11:37 AM, Joshua D. Drake <jd(at)commandprompt(dot)com
> >wrote:
>
>> --num-workers or --num-connections would both work.
>
>
> --num-parallel?

--num-concurrent?

Michael Glaesemann
michael(dot)glaesemann(at)myyearbook(dot)com

On Thu, Feb 12, 2009 at 02:16:39PM -0500, Michael Glaesemann wrote:
>
> On 2009-02-12, at 14:15 , Jonah H. Harris wrote:
>
>> On Thu, Feb 12, 2009 at 11:37 AM, Joshua D. Drake <jd(at)commandprompt(dot)com
>> >wrote:
>>
>>> --num-workers or --num-connections would both work.
>>
>> --num-parallel?
>
> --num-concurrent?

--num-bikeshed? ;)

Cheers,
David (purple!)
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Andrew Dunstan wrote:
> I also don't really understand what is confusing about the description.

Where does the benefit of using it come from? When would one want to
use it? Is it because the parallelization happens on the client or on
the server? Does it happen because to CPU parallelization or because of
disk access parallelization? Is it useful to use it on multi-CPU
systems or on multi-disk systems? The current description implies a bit
of each, I think. And it is not clear what a good number to choose is.

On Thursday 12 February 2009 11:50:26 Joshua D. Drake wrote:
> On Thu, 2009-02-12 at 11:47 -0500, Andrew Dunstan wrote:
> > Joshua D. Drake wrote:
> > > On Thu, 2009-02-12 at 11:32 -0500, Tom Lane wrote:
> > >> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> > >>> The implementation is actually different across platforms: on Windows
> > >>> the workers are genuine threads, while elsewhere they are forked
> > >>> children in the same fashion as the backend (non-EXEC_BACKEND case).
> > >>> In either case, the program will use up to NUM concurrent connections
> > >>> to the server.
> > >>
> > >> How about calling it --num-connections or something like that? I
> > >> agree with Peter that "thread" is not the best terminology on
> > >> platforms where there is no threading involved.
> > >
> > > --num-workers or --num-connections would both work.
> >
> > *shrug* whatever. What should the short option be (if any?). -n is
> > taken, so -N ?
>
> Works for me.
>

yikes... -n and -N have specific meaning to pg_dump, I think keeping
consistency with that in pg_restore would be a bonus. (I still see people get
confused because -d work differently between those two apps)

Possibly -w might work, which could expand to --workers, which glosses over
the thread/process difference, is also be available for pg_dump, and has
existing mindshare with autovacuum workers.

not having a short option seems ok to me too, but I really think -N is a bad
idea.

--
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Cédric Villemain a écrit :
> Joshua D. Drake a écrit :
>> On Thu, 2009-02-12 at 11:47 -0500, Andrew Dunstan wrote:
>>> Joshua D. Drake wrote:
>>>> On Thu, 2009-02-12 at 11:32 -0500, Tom Lane wrote:
>>>>
>>>>> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>>>>>
>>>>>> The implementation is actually different across platforms: on Windows
>>>>>> the workers are genuine threads, while elsewhere they are forked
>>>>>> children in the same fashion as the backend (non-EXEC_BACKEND case). In
>>>>>> either case, the program will use up to NUM concurrent connections to
>>>>>> the server.
>>>>>>
>>>>> How about calling it --num-connections or something like that? I agree
>>>>> with Peter that "thread" is not the best terminology on platforms where
>>>>> there is no threading involved.
>>>>>
>>>> --num-workers or --num-connections would both work.
>>>>
>>>>
>>> *shrug* whatever. What should the short option be (if any?). -n is
>>> taken, so -N ?
>> Works for me.
>
> is -j already affected ?

else (like make):

-j [jobs], --jobs[=jobs]
Specifies the number of jobs (pg_restore) to run simultaneously. If the -j
option is given without an argument, pg_restore will not limit the number of
jobs that can run simultaneously.

>
>
>>> cheers
>>>
>>> andrew
>>>
>
>

- --
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkmZSYkACgkQo/dppWjpEvwO8wCfUFztxS7cmRX+hhbVphfqqDzo
ZzUAniFwmwhI9y6f9Mndg9CPGlQiOaae
=fDYZ
-----END PGP SIGNATURE-----

On Mon, Feb 16, 2009 at 12:10 PM, Cédric Villemain
<cedric(dot)villemain(at)dalibo(dot)com> wrote:
>>
>> is -j already affected ?
>
> else (like make):
>
> -j [jobs], --jobs[=jobs]
> Specifies the number of jobs (pg_restore) to run simultaneously. If the -j
> option is given without an argument, pg_restore will not limit the number of
> jobs that can run simultaneously.

I like both -j and -w.
-j because we all know "make -j"
-w because i like --num-workers

--
F4FQM
Kerunix Flan
Laurent Laborde

Cédric Villemain wrote:
>
> -j [jobs], --jobs[=jobs]
> Specifies the number of jobs (pg_restore) to run simultaneously. If the -j
> option is given without an argument, pg_restore will not limit the number of
> jobs that can run simultaneously.
>
>

Quite apart from anything else, this description is almost 100% dead
wrong. The argument is not optional at all, and there is no unlimited
parallelism. If you want to know how it actually works look at the dev docs.

cheers

andrew

Andrew Dunstan wrote:
> Cédric Villemain wrote:
>>
>> -j [jobs], --jobs[=jobs]
>> Specifies the number of jobs (pg_restore) to run simultaneously.
>> If the -j
>> option is given without an argument, pg_restore will not limit the
>> number of
>> jobs that can run simultaneously.

> Quite apart from anything else, this description is almost 100% dead
> wrong. The argument is not optional at all, and there is no unlimited
> parallelism. If you want to know how it actually works look at the dev
> docs.

What I'm still missing here is a piece of documentation or a guideline
that says when a given number of threads/jobs/workers would be
appropriate. For make -j, this is pretty clear: If you have N CPUs to
spare, use -j N. For pg_restore, this is not made clear: Is it the
number of CPUs on the client or the server or the number of disks on the
client or the server or perhaps a combination of this or something else?

Peter Eisentraut wrote:
> Andrew Dunstan wrote:
>> Cédric Villemain wrote:
>>>
>>> -j [jobs], --jobs[=jobs]
>>> Specifies the number of jobs (pg_restore) to run
>>> simultaneously. If the -j
>>> option is given without an argument, pg_restore will not limit the
>>> number of
>>> jobs that can run simultaneously.
>
>> Quite apart from anything else, this description is almost 100% dead
>> wrong. The argument is not optional at all, and there is no
>> unlimited parallelism. If you want to know how it actually works look
>> at the dev docs.
>
> What I'm still missing here is a piece of documentation or a guideline
> that says when a given number of threads/jobs/workers would be
> appropriate. For make -j, this is pretty clear: If you have N CPUs to
> spare, use -j N. For pg_restore, this is not made clear: Is it the
> number of CPUs on the client or the server or the number of disks on
> the client or the server or perhaps a combination of this or something
> else?

The short answer is that we don't know yet. There is anecdotal evidence
that the number of CPUs on the server is a good place to start, but we
should be honest enough to say that this is a new feature and we are
still gathering information about its performance. If you want to give
some advice, then I think the best advice is to try a variety of
settings to see what works best for you, and if you have a good set of
figures report it back to us.

cheers

andrew

On Fri, 2009-02-20 at 09:33 -0500, Andrew Dunstan wrote:

> The short answer is that we don't know yet. There is anecdotal evidence
> that the number of CPUs on the server is a good place to start, but we
> should be honest enough to say that this is a new feature and we are
> still gathering information about its performance. If you want to give
> some advice, then I think the best advice is to try a variety of
> settings to see what works best for you, and if you have a good set of
> figures report it back to us.

There has been some fairly heavy testing and research that caused the
patch in the first place. The thread is here:

http://archives.postgresql.org/pgsql-hackers/2008-02/msg00695.php

It is a long thread. The end was result was the fastest restore time for
220G was performed with 24 threads with an 8 core box. It came in at 3.5
hours.

http://archives.postgresql.org/pgsql-hackers/2008-02/msg01092.php

It is important to point out that this was a machine with 50 spindles.
Which is where your bottleneck is going to be immediately after solving
the CPU bound nature of the problem.

So although the CPU question is easily answered, the IO is not. IO is
extremely variable in its performance.

Sincerely,

Joshua D. Drake

--
PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

Joshua D. Drake wrote:
> On Fri, 2009-02-20 at 09:33 -0500, Andrew Dunstan wrote:
>
>
>> The short answer is that we don't know yet. There is anecdotal evidence
>> that the number of CPUs on the server is a good place to start, but we
>> should be honest enough to say that this is a new feature and we are
>> still gathering information about its performance. If you want to give
>> some advice, then I think the best advice is to try a variety of
>> settings to see what works best for you, and if you have a good set of
>> figures report it back to us.
>>
>
> There has been some fairly heavy testing and research that caused the
> patch in the first place. The thread is here:
>
> http://archives.postgresql.org/pgsql-hackers/2008-02/msg00695.php
>
> It is a long thread. The end was result was the fastest restore time for
> 220G was performed with 24 threads with an 8 core box. It came in at 3.5
> hours.
>
> http://archives.postgresql.org/pgsql-hackers/2008-02/msg01092.php
>
> It is important to point out that this was a machine with 50 spindles.
> Which is where your bottleneck is going to be immediately after solving
> the CPU bound nature of the problem.
>
> So although the CPU question is easily answered, the IO is not. IO is
> extremely variable in its performance.
>
>
>

Yes, quite true. But parallel restore doesn't work quite the same way
your original shell scripts did. It tries harder to keep the job pool
continuously occupied, and so its best number of jobs is likely to be a
bit lower then yours.

But you are right that there isn't a simple formula.

cheers

andrew

On Fri, Feb 20, 2009 at 09:22:58AM -0800, Joshua D. Drake wrote:
> On Fri, 2009-02-20 at 09:33 -0500, Andrew Dunstan wrote:
>
> > The short answer is that we don't know yet. There is anecdotal evidence
> > that the number of CPUs on the server is a good place to start, but we
> > should be honest enough to say that this is a new feature and we are
> > still gathering information about its performance. If you want to give
> > some advice, then I think the best advice is to try a variety of
> > settings to see what works best for you, and if you have a good set of
> > figures report it back to us.
>
> There has been some fairly heavy testing and research that caused the
> patch in the first place. The thread is here:
>
> http://archives.postgresql.org/pgsql-hackers/2008-02/msg00695.php
>
> It is a long thread. The end was result was the fastest restore time for
> 220G was performed with 24 threads with an 8 core box. It came in at 3.5
> hours.
>
> http://archives.postgresql.org/pgsql-hackers/2008-02/msg01092.php
>
> It is important to point out that this was a machine with 50 spindles.
> Which is where your bottleneck is going to be immediately after solving
> the CPU bound nature of the problem.
>
> So although the CPU question is easily answered, the IO is not. IO is
> extremely variable in its performance.
>
> Sincerely,
>
> Joshua D. Drake
>
I also ran some tests against a more modest system that was still
showing a performance improvement at (number-of-cores * 2):

http://archives.postgresql.org/pgsql-hackers/2008-11/msg01399.php

I think that a good starting point for any use should be the number
of cores given these two data points.

Cheers,
Ken

>>> Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> Joshua D. Drake wrote:
>> the fastest restore time for
>> 220G was performed with 24 threads with an 8 core box.

>> It is important to point out that this was a machine with 50
spindles.
>> Which is where your bottleneck is going to be immediately after
solving
>> the CPU bound nature of the problem.

> But you are right that there isn't a simple formula.

Perhaps the greater of the number of CPUs or effective spindles?

(24 sounds suspiciously close to effective spindles on a 50 spindle
box
with RAID 10.)

-Kevin

On Fri, 2009-02-20 at 11:57 -0600, Kevin Grittner wrote:
>
> > But you are right that there isn't a simple formula.
>
> Perhaps the greater of the number of CPUs or effective spindles?
>
> (24 sounds suspiciously close to effective spindles on a 50 spindle
> box
> with RAID 10.)

It does except that you aren't accounting for 7200RPM vs 10k vs 15k vs
iSCSI vs FibreChannel etc...

You would have to literally do the math to figure it all out. Those 50
spindles were DAS. You go iSCSI and all of a sudden you have turned
those 50 spindles into and effective 8 DAS spindles. Not to mention if
you only have a single path for your FibreChannel etc...

Joshua D. Drake

>
> -Kevin
>
--
PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

On Thursday 12 February 2009 17:41:01 Peter Eisentraut wrote:
> I know we've already had a discussion on the naming of the pg_restore -m
> option, but in any case this description in pg_restore --help is confusing:
>
> -m, --multi-thread=NUM use this many parallel connections to restore
>
> Either it is using that many threads in the client, or it is using that
> many connections to the server. I assume the implementation does
> approximately both, but we should be clear about what we promise to the
> user. Either: Reserve this many connections on the server. Or: Reserve
> this many threads in the kernel of the client. The documentation in the
> reference/man page is equally confused.
>
> Also, the term "multi" is redundant, because whether it is multi or single
> is obviously determined by the value of NUM.

After reviewing the discussion and the implementation, I would say "workers"
would be the best description of the feature, but unfortunately the options -w
or -W are not available. I'd also avoid -n or -N for "num..." because pg_dump
already uses -n and -N for something else, and we are now trying to avoid
inconsistent options between these programs. Also, option names usually don't
start with units (imagine --num-shared-buffers or --num-port).

While I think "jobs" isn't a totally accurate description, I would still
propose to use -j/--jobs for the option name, because it is neutral about the
implementation and has a strong precedent as being used to increase the
parallelization to get the work done faster. I also noticed that Andrew D.
used "jobs" in his own emails to comment on the feature. :-)

The attached patch also updated the documentation to give some additional
advice about which numbers to use.

Peter Eisentraut wrote:
>
> While I think "jobs" isn't a totally accurate description, I would still
> propose to use -j/--jobs for the option name, because it is neutral about the
> implementation and has a strong precedent as being used to increase the
> parallelization to get the work done faster. I also noticed that Andrew D.
> used "jobs" in his own emails to comment on the feature. :-)
>
> The attached patch also updated the documentation to give some additional
> advice about which numbers to use.
>
>

Looks reasonable.

cheers

andrew