Optimize pg_dump schema-only

2019-04-28 Thread senor
Hi All, I'm looking for advice for optimizing the pg_dump portion of "pg_upgrade --link". Since this schema only dump can't take advantage of parallel processing with jobs I'm looking for any preparation or configuration settings that can improve speed. 9.2 to 9.6 CentOS 6/64bit 512GB I see o

Re: Optimize pg_dump schema-only

2019-04-28 Thread Adrian Klaver
On 4/28/19 1:21 PM, senor wrote: Hi All, I'm looking for advice for optimizing the pg_dump portion of "pg_upgrade --link". Since this schema only dump can't take advantage of parallel The above is going to need more explanation or a command line example. processing with jobs I'm looking for

Re: Optimize pg_dump schema-only

2019-04-28 Thread Ron
On 4/28/19 3:21 PM, senor wrote: Hi All, I'm looking for advice for optimizing the pg_dump portion of "pg_upgrade --link". Since this schema only dump can't take advantage of parallel processing with jobs I'm looking for any preparation or configuration settings that can improve speed. 9.2 to 9

Re: Optimize pg_dump schema-only

2019-04-28 Thread Tom Lane
Ron writes: > On 4/28/19 3:21 PM, senor wrote: >> I see only one CPU of 32 doing anything and it's often at 100%. Disk IO >> is minimal. Memory use varies but always plenty to spare. > "pg_dump --schema-only" is single-threaded. Yup. But ... pg_upgrade does have a --jobs option, since 9.5 or s

Re: Optimize pg_dump schema-only

2019-04-28 Thread senor
I know from a previous post to the community that pg_dump --schema-only is single threaded and the --jobs option cannot benefit pg_dump in my case (single DB, Single schema, 10+ tables). Using pg_upgrade with the --link option is very fast except for the pg_dump portion of the upgrade which

Re: Optimize pg_dump schema-only

2019-04-28 Thread Tom Lane
senor writes: > I know from a previous post to the community that pg_dump --schema-only is > single threaded and the --jobs option cannot benefit pg_dump in my case > (single DB, Single schema, 10+ tables). Using pg_upgrade with the --link > option is very fast except for the pg_dump portio

Re: Optimize pg_dump schema-only

2019-04-28 Thread senor
I'm afraid it is 9.2.4. I'm using pg_upgrade from 9.6 and that is using pg_dump from 9.6. I noticed on 2 installations with similar table numbers (~200,000), schema and hardware that one was done in hours and the other didn't finish over the weekend. Keeping tabs on pg_stat_activity indicated

Re: Optimize pg_dump schema-only

2019-04-28 Thread Tom Lane
senor writes: > I'm afraid it is 9.2.4. That's pretty old :-( > I noticed on 2 installations with similar table numbers (~200,000), > schema and hardware that one was done in hours and the other didn't > finish over the weekend. Keeping tabs on pg_stat_activity indicated > pg_dump was still p

Thousands of partitions performance questions

2019-04-28 Thread Shai Cantor
- *General* - Our system gathers data from CI builds regarding a customer's code base - *Data includes* - type - methods/lines/branches A.K.A - code elements - files - *Queries* - The data is queried for a specific build only - Queries are

Re: Thousands of partitions performance questions

2019-04-28 Thread David Rowley
On Mon, 29 Apr 2019 at 17:13, Shai Cantor wrote: > Will the db hold 135000 (45000 * 3 months) partitions under the assumption > that I query only 1 partition? > Should I model it differently in terms of schema, partitions etc.? Which PG version? Before PG11 the query planner pruned unneeded par