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@dunslane.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@dunslane.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@jabber.postgresql.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@dunslane.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@dunslane.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@jabber.postgresql.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@dunslane.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@commandprompt.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@commandprompt.com
wrote:
--num-workers or --num-connections would both work.
--num-parallel?
--num-concurrent?
Michael Glaesemann
michael.glaesemann@myyearbook.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@commandprompt.com
wrote:
--num-workers or --num-connections would both work.
--num-parallel?
--num-concurrent?
--num-bikeshed? ;)
Cheers,
David (purple!)
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.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@dunslane.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@dunslane.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.villemain@dalibo.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@jabber.postgresql.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
