On Sun, Jan 20, 2013 at 02:11:48PM -0500, Tom Lane wrote:
> Bruce Momjian writes:
> > !* Using pg_restore --single-transaction is faster than
> > other
> > !* methods, like --jobs.
>
> Is this still the case now that Jeff's AtEOXact patch is in? The risk
> of l
On Sunday, January 20, 2013, Stephen Frost wrote:
> * Jeff Janes (jeff.ja...@gmail.com ) wrote:
>
> > By making the list over-flowable, we fix a demonstrated pathological
> > workload (restore of huge schemas); we impose no detectable penalty to
> > normal workloads; and we fail to improve, but
Stephen Frost writes:
> I'm curious if this is going to help with rollback's of transactions
> which created lots of tables..? We've certainly seen that take much
> longer than we'd like, although I've generally attributed it to doing
> all of the unlink'ing and truncating of files.
If a single
Bruce Momjian writes:
> ! * Using pg_restore --single-transaction is faster than
> other
> ! * methods, like --jobs.
Is this still the case now that Jeff's AtEOXact patch is in? The risk
of locktable overflow with --single-transaction makes me think that
pg_u
Jeff Janes writes:
> [ patch for AtEOXact_RelationCache ]
I've reviewed and committed this with some mostly-cosmetic adjustments,
notably:
* Applied it to AtEOSubXact cleanup too. AFAICS that's just as
idempotent, and it seemed weird to not use the same technique both
places.
* Dropped the hac
* Jeff Janes (jeff.ja...@gmail.com) wrote:
> By making the list over-flowable, we fix a demonstrated pathological
> workload (restore of huge schemas); we impose no detectable penalty to
> normal workloads; and we fail to improve, but also fail to make worse, a
> hypothetical pathological workload.
On Wednesday, January 9, 2013, Simon Riggs wrote:
> On 23 November 2012 22:34, Jeff Janes >
> wrote:
>
> > I got rid of need_eoxact_work entirely and replaced it with a short
> > list that fulfills the functions of indicating that work is needed,
> > and suggesting which rels might need that work.
On 9 January 2013 17:50, Jeff Janes wrote:
> On Wed, Jan 9, 2013 at 3:59 AM, Simon Riggs wrote:
>> On 9 November 2012 18:50, Jeff Janes wrote:
>>
>>> quadratic behavior in the resource owner/lock table
>>
>> I didn't want to let that particular phrase go by without saying
>> "exactly what behavi
On Wed, Jan 9, 2013 at 3:59 AM, Simon Riggs wrote:
> On 9 November 2012 18:50, Jeff Janes wrote:
>
>> quadratic behavior in the resource owner/lock table
>
> I didn't want to let that particular phrase go by without saying
> "exactly what behaviour is that?", so we can discuss fixing that also.
On 9 November 2012 18:50, Jeff Janes wrote:
> quadratic behavior in the resource owner/lock table
I didn't want to let that particular phrase go by without saying
"exactly what behaviour is that?", so we can discuss fixing that also.
This maybe something I already know about, but its worth aski
On 23 November 2012 22:34, Jeff Janes wrote:
> I got rid of need_eoxact_work entirely and replaced it with a short
> list that fulfills the functions of indicating that work is needed,
> and suggesting which rels might need that work. There is no attempt
> to prevent duplicates, nor to remove in
On Thu, Nov 29, 2012 at 12:59:19PM -0500, Bruce Momjian wrote:
> I have polished up the patch (attached) and it is ready for application
> to 9.3.
Applied.
---
> Since there is no pg_dump/pg_restore pipe parallelism, I had t
On Wed, Nov 28, 2012 at 03:22:32PM -0500, Bruce Momjian wrote:
> On Tue, Nov 27, 2012 at 09:35:10PM -0800, Jeff Janes wrote:
> > > I tested custom format with pg_restore -j and -1, as well as text
> > > restore. The winner was pg_dump -Fc | pg_restore -1;
> >
> > I don't have the numbers at hand,
On Tue, Nov 27, 2012 at 09:35:10PM -0800, Jeff Janes wrote:
> > I tested custom format with pg_restore -j and -1, as well as text
> > restore. The winner was pg_dump -Fc | pg_restore -1;
>
> I don't have the numbers at hand, but if my relcache patch is
> accepted, then "-1" stops being faster.
>
On Tue, Nov 27, 2012 at 09:35:10PM -0800, Jeff Janes wrote:
> On Tue, Nov 27, 2012 at 8:13 PM, Bruce Momjian wrote:
> >
> > I have some new interesting results (in seconds, test script attached):
> >
> > -Fc --- dump | pg_restore/psql -- -
> > pg_upgrade -
> >
On Tue, Nov 27, 2012 at 8:13 PM, Bruce Momjian wrote:
>
> I have some new interesting results (in seconds, test script attached):
>
> -Fc --- dump | pg_restore/psql -- -
> pg_upgrade -
> dump restore -Fc-Fc|-1 -Fc|-j -Fp-Fp|-1 git
>
On Mon, Nov 26, 2012 at 05:26:42PM -0500, Bruce Momjian wrote:
> I have developed the attached proof-of-concept patch to test this idea.
> Unfortunately, I got poor results:
>
> pg_upgrade
> dump restore dmp|res
Bruce Momjian writes:
> Testing pg_dump for 4k tables (16 seconds) shows the first row is not
> output by pg_dump until 15 seconds, meaning there can't be any
> parallelism with a pipe. (Test script attached.) Does anyone know how
> to get pg_dump to send some output earlier?
You can't. By the
On Wed, Nov 14, 2012 at 10:08:15AM -0500, Bruce Momjian wrote:
> > I agree that parallel restore for schemas is a hard problem. But I
> > didn't mean parallelism within the restore, I meant that we could
> > start both postmasters and pipe the output from dump directly to
> > restore. This way the
On Fri, Nov 23, 2012 at 5:34 PM, Jeff Janes wrote:
> On Thu, Nov 15, 2012 at 7:05 PM, Jeff Janes wrote:
>> On Wed, Nov 14, 2012 at 11:49 AM, Tom Lane wrote:
>>> Jeff Janes writes:
On Thu, Nov 8, 2012 at 9:50 PM, Tom Lane wrote:
> There are at least three ways we could whack that mole:
On Mon, Nov 12, 2012 at 06:14:59PM -0300, Alvaro Herrera wrote:
> Bruce Momjian escribió:
>
> > --- 17,24
> >
> > static void transfer_single_new_db(pageCnvCtx *pageConverter,
> >FileNameMap *maps, int size);
> > ! static int transfer_relfile(pageC
Added to TODO:
Improve cache lookup speed for sessions accessing many relations
http://archives.postgresql.org/pgsql-hackers/2012-11/msg00356.php
---
On Fri, Nov 9, 2012 at 12:50:34AM -0500, T
On Thu, Nov 15, 2012 at 7:05 PM, Jeff Janes wrote:
> On Wed, Nov 14, 2012 at 11:49 AM, Tom Lane wrote:
>> Jeff Janes writes:
>>> On Thu, Nov 8, 2012 at 9:50 PM, Tom Lane wrote:
There are at least three ways we could whack that mole: ...
* Keep a separate list (or data structure o
On Thu, Nov 15, 2012 at 07:05:00PM -0800, Jeff Janes wrote:
> On Wed, Nov 14, 2012 at 11:49 AM, Tom Lane wrote:
> > Jeff Janes writes:
> >> On Thu, Nov 8, 2012 at 9:50 PM, Tom Lane wrote:
> >>> There are at least three ways we could whack that mole: ...
> >>>
> >>> * Keep a separate list (or dat
On Wed, Nov 14, 2012 at 11:49 AM, Tom Lane wrote:
> Jeff Janes writes:
>> On Thu, Nov 8, 2012 at 9:50 PM, Tom Lane wrote:
>>> There are at least three ways we could whack that mole: ...
>>>
>>> * Keep a separate list (or data structure of your choice) so that
>>> relcache entries created in the
Dimitri Fontaine writes:
> Alvaro Herrera writes:
>> Could we use some adaptive mechanism here? Say we use a list for the
>> first ten entries, and if an eleventh one comes in, we create a hash
>> table for that one and all subsequent ones. All future calls would
>> have to examine both the lis
Alvaro Herrera writes:
> Could we use some adaptive mechanism here? Say we use a list for the
> first ten entries, and if an eleventh one comes in, we create a hash
> table for that one and all subsequent ones. All future calls would
> have to examine both the list for the first few and then the
Patch applied to git head. Thanks Ants Aasma for the analysis that lead
to the patch.
---
On Tue, Nov 13, 2012 at 07:03:51PM -0500, Bruce Momjian wrote:
> On Tue, Nov 13, 2012 at 05:44:54AM +0200, Ants Aasma wrote:
> > On M
Tom Lane escribió:
> Jeff Janes writes:
> > The next quadratic behavior is in init_sequence.
>
> Yeah, that's another place that is using a linear list that perhaps
> should be a hashtable. OTOH, probably most sessions don't touch enough
> different sequences for that to be a win.
Could we use
Jeff Janes writes:
> On Thu, Nov 8, 2012 at 9:50 PM, Tom Lane wrote:
>> There are at least three ways we could whack that mole: ...
>>
>> * Keep a separate list (or data structure of your choice) so that
>> relcache entries created in the current xact could be found directly
>> rather than having
On Thu, Nov 8, 2012 at 9:50 PM, Tom Lane wrote:
> Jeff Janes writes:
>> Are sure the server you are dumping out of is head?
>
> I experimented a bit with dumping/restoring 16000 tables matching
> Bruce's test case (ie, one serial column apiece). The pg_dump profile
> seems fairly flat, without a
On Wed, Nov 14, 2012 at 10:25:24AM -0500, Andrew Dunstan wrote:
>
> On 11/14/2012 10:08 AM, Bruce Momjian wrote:
> >On Wed, Nov 14, 2012 at 06:11:27AM +0200, Ants Aasma wrote:
> >>
> >>I agree that parallel restore for schemas is a hard problem. But I
> >>didn't mean parallelism within the restore
On 11/14/2012 10:08 AM, Bruce Momjian wrote:
On Wed, Nov 14, 2012 at 06:11:27AM +0200, Ants Aasma wrote:
I agree that parallel restore for schemas is a hard problem. But I
didn't mean parallelism within the restore, I meant that we could
start both postmasters and pipe the output from dump dir
On Wed, Nov 14, 2012 at 06:11:27AM +0200, Ants Aasma wrote:
> On Wed, Nov 14, 2012 at 2:03 AM, Bruce Momjian wrote:
> > At 64k I see pg_upgrade taking 12% of the duration time, if I subtract
> > out the dump/restore times.
>
> My percentage numbers only included CPU time and I used SSD storage.
>
On Wed, Nov 14, 2012 at 2:03 AM, Bruce Momjian wrote:
> At 64k I see pg_upgrade taking 12% of the duration time, if I subtract
> out the dump/restore times.
My percentage numbers only included CPU time and I used SSD storage.
For the most part there was no IO wait to speak of, but it's
completely
On Tue, Nov 13, 2012 at 07:03:51PM -0500, Bruce Momjian wrote:
> I am attaching an updated pg_upgrade patch, which I believe is ready for
> application for 9.3.
Correction, here is the proper patch. The previous posted version was
had pending merges from the master branch.
--
Bruce Momjian
On Mon, Nov 12, 2012 at 5:17 PM, Simon Riggs wrote:
> On 12 November 2012 16:51, Robert Haas wrote:
>
>> Although there may be some workloads that access very large numbers of
>> tables repeatedly, I bet that's not typical.
>
> Transactions with large numbers of DDL statements are typical at
> up
On 12 November 2012 16:51, Robert Haas wrote:
> Although there may be some workloads that access very large numbers of
> tables repeatedly, I bet that's not typical.
Transactions with large numbers of DDL statements are typical at
upgrade (application or database release level) and the execution
On Fri, Nov 9, 2012 at 10:50 AM, Jeff Janes wrote:
> On Thu, Nov 8, 2012 at 9:50 PM, Tom Lane wrote:
>> Jeff Janes writes:
>>> Are sure the server you are dumping out of is head?
>>
>> I experimented a bit with dumping/restoring 16000 tables matching
>> Bruce's test case (ie, one serial column a
On Mon, Nov 12, 2012 at 12:09:08PM -0500, Bruce Momjian wrote:
> The second approach would be to simply try to copy the fsm, vm, and
> extent files, and ignore any ENOEXIST errors. This allows code
> simplification. The downside is that it doesn't pull all files with
> matching prefixes --- it re
On Mon, Nov 12, 2012 at 06:14:59PM -0300, Alvaro Herrera wrote:
> Bruce Momjian escribió:
>
> > --- 17,24
> >
> > static void transfer_single_new_db(pageCnvCtx *pageConverter,
> >FileNameMap *maps, int size);
> > ! static int transfer_relfile(pageC
Bruce Momjian escribió:
> It is possible that the poor 16k pg_upgrade value is caused by the poor
> 9.2 binary-upgrade number (189.38). Perhaps I need to hack up
> pg_upgrade to allow a 9.3 to 9.3 upgrade to test this.
Hmm? This already works, since "make check" uses it, right?
--
--
Sent
Bruce Momjian escribió:
> --- 17,24
>
> static void transfer_single_new_db(pageCnvCtx *pageConverter,
> FileNameMap *maps, int size);
> ! static int transfer_relfile(pageCnvCtx *pageConverter, FileNameMap *map,
> !
On Mon, Nov 12, 2012 at 03:59:27PM -0500, Bruce Momjian wrote:
> > The second approach would be to simply try to copy the fsm, vm, and
> > extent files, and ignore any ENOEXIST errors. This allows code
> > simplification. The downside is that it doesn't pull all files with
> > matching prefixes -
On Mon, Nov 12, 2012 at 12:09:08PM -0500, Bruce Momjian wrote:
> OK, I have had some time to think about this. What the current code
> does is, for each database, get a directory listing to know about any
> vm, fsm, and >1gig extents that exist in the directory. It caches the
> directory listing
On Sat, Nov 10, 2012 at 12:41:44PM -0500, Bruce Momjian wrote:
> On Sat, Nov 10, 2012 at 07:17:34PM +0200, Ants Aasma wrote:
> > On Sat, Nov 10, 2012 at 7:10 PM, Bruce Momjian wrote:
> > > I am confused why you see a loop. transfer_all_new_dbs() does a
> > > merge-join of old/new database names,
On Fri, Nov 9, 2012 at 12:50 AM, Tom Lane wrote:
> Jeff Janes writes:
>> Are sure the server you are dumping out of is head?
>
> I experimented a bit with dumping/restoring 16000 tables matching
> Bruce's test case (ie, one serial column apiece). The pg_dump profile
> seems fairly flat, without
Bruce Momjian writes:
> On Fri, Nov 9, 2012 at 12:50:34AM -0500, Tom Lane wrote:
>> The hash_seq_search time is probably mostly associated with
>> AtEOXact_RelationCache, which is run during transaction commit and scans
>> the relcache hashtable looking for tables created in the current
>> transa
On Fri, Nov 9, 2012 at 12:50:34AM -0500, Tom Lane wrote:
> Jeff Janes writes:
> > Are sure the server you are dumping out of is head?
>
> I experimented a bit with dumping/restoring 16000 tables matching
> Bruce's test case (ie, one serial column apiece). The pg_dump profile
> seems fairly flat
On Sat, Nov 10, 2012 at 05:59:54PM -0500, Bruce Momjian wrote:
> On Sat, Nov 10, 2012 at 02:45:54PM -0800, Jeff Janes wrote:
> > On Sat, Nov 10, 2012 at 9:15 AM, Bruce Momjian wrote:
> > > On Fri, Nov 9, 2012 at 04:23:40PM -0800, Jeff Janes wrote:
> > >> On Fri, Nov 9, 2012 at 3:06 PM, Bruce Momj
On Sat, Nov 10, 2012 at 02:45:54PM -0800, Jeff Janes wrote:
> On Sat, Nov 10, 2012 at 9:15 AM, Bruce Momjian wrote:
> > On Fri, Nov 9, 2012 at 04:23:40PM -0800, Jeff Janes wrote:
> >> On Fri, Nov 9, 2012 at 3:06 PM, Bruce Momjian wrote:
> >> >
> >> > Again, using SERIAL?
> >>
> >> Yep.
> >
> > O
On Sat, Nov 10, 2012 at 05:20:55PM -0500, Bruce Momjian wrote:
> On Fri, Nov 9, 2012 at 04:06:38PM -0800, Jeff Janes wrote:
> > On Thu, Nov 8, 2012 at 7:25 PM, Bruce Momjian wrote:
> > >
> > > I did some more research and realized that I was not using --schema-only
> > > like pg_upgrade uses. Wi
On Sat, Nov 10, 2012 at 9:15 AM, Bruce Momjian wrote:
> On Fri, Nov 9, 2012 at 04:23:40PM -0800, Jeff Janes wrote:
>> On Fri, Nov 9, 2012 at 3:06 PM, Bruce Momjian wrote:
>> >
>> > Again, using SERIAL?
>>
>> Yep.
>
> Odd why yours is so much after.
You didn't build git head under --enable-casse
On Fri, Nov 9, 2012 at 04:06:38PM -0800, Jeff Janes wrote:
> On Thu, Nov 8, 2012 at 7:25 PM, Bruce Momjian wrote:
> >
> > I did some more research and realized that I was not using --schema-only
> > like pg_upgrade uses. With that setting, things look like this:
> >
> ...
>
> For profiling pg_d
On Sat, Nov 10, 2012 at 07:17:34PM +0200, Ants Aasma wrote:
> On Sat, Nov 10, 2012 at 7:10 PM, Bruce Momjian wrote:
> > I am confused why you see a loop. transfer_all_new_dbs() does a
> > merge-join of old/new database names, then calls gen_db_file_maps(),
> > which loops over the relations and c
On Sat, Nov 10, 2012 at 7:10 PM, Bruce Momjian wrote:
> I am confused why you see a loop. transfer_all_new_dbs() does a
> merge-join of old/new database names, then calls gen_db_file_maps(),
> which loops over the relations and calls create_rel_filename_map(),
> which adds to the map via array in
On Fri, Nov 9, 2012 at 04:23:40PM -0800, Jeff Janes wrote:
> > Actually, pg_upgrade needs pg_dump to restore all those sequence values.
>
> I did an experiment where I had pg_dump just output dummy values
> rather than hitting the database. Once pg_upgrade moves the relation
> files over, the du
On Fri, Nov 9, 2012 at 04:23:40PM -0800, Jeff Janes wrote:
> On Fri, Nov 9, 2012 at 3:06 PM, Bruce Momjian wrote:
> > On Thu, Nov 8, 2012 at 08:59:21PM -0800, Jeff Janes wrote:
> >> On Thu, Nov 8, 2012 at 4:33 PM, Bruce Momjian wrote:
> >> >
> >> > I am actually now dumping git head/9.3, so I a
On Fri, Nov 9, 2012 at 08:20:59AM +0200, Ants Aasma wrote:
> On Fri, Nov 9, 2012 at 7:53 AM, Ants Aasma wrote:
> > I also took two profiles (attached). AtEOXact_RelationCache seems to
> > be the culprit for the quadratic growth.
>
> One more thing that jumps out as quadratic from the profiles is
On Fri, Nov 9, 2012 at 3:06 PM, Bruce Momjian wrote:
> On Thu, Nov 8, 2012 at 08:59:21PM -0800, Jeff Janes wrote:
>> On Thu, Nov 8, 2012 at 4:33 PM, Bruce Momjian wrote:
>> >
>> > I am actually now dumping git head/9.3, so I assume all the problems we
>> > know about should be fixed.
>>
>> Are s
On Thu, Nov 8, 2012 at 7:25 PM, Bruce Momjian wrote:
>
> I did some more research and realized that I was not using --schema-only
> like pg_upgrade uses. With that setting, things look like this:
>
...
For profiling pg_dump in isolation, you should also specify
--binary-upgrade. I was surprised
On 2012-11-08 12:30:11 -0500, Peter Eisentraut wrote:
> On 11/7/12 9:17 PM, Bruce Momjian wrote:
> > As a followup to Magnus's report that pg_upgrade was slow for many
> > tables, I did some more testing with many tables, e.g.:
> >
> > CREATE TABLE test991 (x SERIAL);
> >
> > I ran it for 0, 1k
On Thu, Nov 8, 2012 at 08:59:21PM -0800, Jeff Janes wrote:
> On Thu, Nov 8, 2012 at 4:33 PM, Bruce Momjian wrote:
> > On Thu, Nov 8, 2012 at 03:46:09PM -0800, Jeff Janes wrote:
> >> On Wed, Nov 7, 2012 at 6:17 PM, Bruce Momjian wrote:
> >> > As a followup to Magnus's report that pg_upgrade was
On Thu, Nov 8, 2012 at 9:50 PM, Tom Lane wrote:
> Jeff Janes writes:
>> Are sure the server you are dumping out of is head?
>
> I experimented a bit with dumping/restoring 16000 tables matching
> Bruce's test case (ie, one serial column apiece). The pg_dump profile
> seems fairly flat, without a
On Fri, Nov 9, 2012 at 7:53 AM, Ants Aasma wrote:
> I also took two profiles (attached). AtEOXact_RelationCache seems to
> be the culprit for the quadratic growth.
One more thing that jumps out as quadratic from the profiles is
transfer_all_new_dbs from pg_upgrade (20% of total CPU time at 64k).
Jeff Janes writes:
> Are sure the server you are dumping out of is head?
I experimented a bit with dumping/restoring 16000 tables matching
Bruce's test case (ie, one serial column apiece). The pg_dump profile
seems fairly flat, without any easy optimization targets. But
restoring the dump scrip
On Thu, Nov 8, 2012 at 4:33 PM, Bruce Momjian wrote:
> On Thu, Nov 8, 2012 at 03:46:09PM -0800, Jeff Janes wrote:
>> On Wed, Nov 7, 2012 at 6:17 PM, Bruce Momjian wrote:
>> > As a followup to Magnus's report that pg_upgrade was slow for many
>> > tables, I did some more testing with many tables,
On Thu, Nov 8, 2012 at 12:30:11PM -0500, Peter Eisentraut wrote:
> On 11/7/12 9:17 PM, Bruce Momjian wrote:
> > As a followup to Magnus's report that pg_upgrade was slow for many
> > tables, I did some more testing with many tables, e.g.:
> >
> > CREATE TABLE test991 (x SERIAL);
> >
> > I ra
On Thu, Nov 8, 2012 at 03:46:09PM -0800, Jeff Janes wrote:
> On Wed, Nov 7, 2012 at 6:17 PM, Bruce Momjian wrote:
> > As a followup to Magnus's report that pg_upgrade was slow for many
> > tables, I did some more testing with many tables, e.g.:
> >
> ...
> >
> > Any ideas? I am attaching my test
On Wed, Nov 7, 2012 at 6:17 PM, Bruce Momjian wrote:
> As a followup to Magnus's report that pg_upgrade was slow for many
> tables, I did some more testing with many tables, e.g.:
>
...
>
> Any ideas? I am attaching my test script.
Have you reviewed the thread at:
http://archives.postgresql.org/
On 11/7/12 9:17 PM, Bruce Momjian wrote:
> As a followup to Magnus's report that pg_upgrade was slow for many
> tables, I did some more testing with many tables, e.g.:
>
> CREATE TABLE test991 (x SERIAL);
>
> I ran it for 0, 1k, 2k, ... 16k tables, and got these results:
>
> tables
On Wed, Nov 7, 2012 at 09:17:29PM -0500, Bruce Momjian wrote:
> Things look fine through 2k, but at 4k the duration of pg_dump, restore,
> and pg_upgrade (which is mostly a combination of these two) is 4x,
> rather than the 2x as predicted by the growth in the number of tables.
> To see how bad i
As a followup to Magnus's report that pg_upgrade was slow for many
tables, I did some more testing with many tables, e.g.:
CREATE TABLE test991 (x SERIAL);
I ran it for 0, 1k, 2k, ... 16k tables, and got these results:
tablespg_dump restore pg_upgrade(increase)
73 matches
Mail list logo
