Обсуждение: pg_upgrade with -j shows no perf improvement

Greetings,
I'm in the early stages of preparing to upgrade a production 9.2
cluster to 9.3, by testing the beta of 9.3.  All of my testing is
happening on RHEL6-x86_64 on a dedicated server with 128GB RAM and 2x
Intel Xeon E5-2670 CPUs, with all of $PGDATA residing on an 8 disk
RAID10 array.

Currently, a full pg_basebackup of my data is approaching 800GB in
size (uncompressed), so this isn't a tiny, trivial database.

I was curious about how much of a performance gain I'd get from
upgrading with the new -j option to pg_upgrade, so first I performed
the upgrade without it to get a baseline.  The command I ran for the
upgrade is as follows:
time pg_upgrade -v -d /var/lib/pgsql/9.2/data -D
/var/lib/pgsql/9.3/data -b /usr/pgsql-9.2/bin -B /usr/pgsql-9.3/bin

time reported the following afterward the upgrade had completed successfully:
real    24m59.255s
user    0m17.069s
sys    15m25.153s


I then repeated the upgrade (after blowing away $PGDATA, and running
initdb again for 9.3), and re-ran pg_upgrade with the same command as
above, only with '-j4' appended to the end.  Surprisingly, the
completion time was less than 30 seconds faster.  I repeated a third
time with '-j8', and that was about the same completion time as with
'-j4'.  I guess I could repeat with 'j2', but I'd be surprised if it
was dramatically faster when -j4 was only marginally so.  It seems
like the parallelism of the -j option doesn't seem to be helping much
at all, in my case.

Is this expected, or is it possible that there's a bug somewhere?  Let
me know if I can provide any logs from the upgrade.

thanks

On Fri, Jun 14, 2013 at 02:29:24PM -0700, Lonni J Friedman wrote:
> Greetings,
> I'm in the early stages of preparing to upgrade a production 9.2
> cluster to 9.3, by testing the beta of 9.3.  All of my testing is
> happening on RHEL6-x86_64 on a dedicated server with 128GB RAM and 2x
> Intel Xeon E5-2670 CPUs, with all of $PGDATA residing on an 8 disk
> RAID10 array.
>
> Currently, a full pg_basebackup of my data is approaching 800GB in
> size (uncompressed), so this isn't a tiny, trivial database.
>
> I was curious about how much of a performance gain I'd get from
> upgrading with the new -j option to pg_upgrade, so first I performed
> the upgrade without it to get a baseline.  The command I ran for the
> upgrade is as follows:
> time pg_upgrade -v -d /var/lib/pgsql/9.2/data -D
> /var/lib/pgsql/9.3/data -b /usr/pgsql-9.2/bin -B /usr/pgsql-9.3/bin
>
> time reported the following afterward the upgrade had completed successfully:
> real    24m59.255s
> user    0m17.069s
> sys    15m25.153s
>
>
> I then repeated the upgrade (after blowing away $PGDATA, and running
> initdb again for 9.3), and re-ran pg_upgrade with the same command as
> above, only with '-j4' appended to the end.  Surprisingly, the
> completion time was less than 30 seconds faster.  I repeated a third
> time with '-j8', and that was about the same completion time as with
> '-j4'.  I guess I could repeat with 'j2', but I'd be surprised if it
> was dramatically faster when -j4 was only marginally so.  It seems
> like the parallelism of the -j option doesn't seem to be helping much
> at all, in my case.
>
> Is this expected, or is it possible that there's a bug somewhere?  Let
> me know if I can provide any logs from the upgrade.

The documentation states:

     The <option>--jobs</> option allows multiple CPU cores to be used
     for copying/linking of files and to dump and reload database schemas
     in parallel;  a good place to start is the maximum of the number of
     CPU cores and tablespaces.  This option can dramatically reduce the
     time to upgrade a multi-database server running on a multiprocessor
     machine.

My guess is that you didn't have many tablespaces or databases, or the
copy time overwhelmed the performance improvement of the parallelism.
I am not surprised you didn't see a big win.  Can you test --link
mode?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

On Sat, Jun 15, 2013 at 8:03 PM, Bruce Momjian <bruce@momjian.us> wrote:
> On Fri, Jun 14, 2013 at 02:29:24PM -0700, Lonni J Friedman wrote:
>> Greetings,
>> I'm in the early stages of preparing to upgrade a production 9.2
>> cluster to 9.3, by testing the beta of 9.3.  All of my testing is
>> happening on RHEL6-x86_64 on a dedicated server with 128GB RAM and 2x
>> Intel Xeon E5-2670 CPUs, with all of $PGDATA residing on an 8 disk
>> RAID10 array.
>>
>> Currently, a full pg_basebackup of my data is approaching 800GB in
>> size (uncompressed), so this isn't a tiny, trivial database.
>>
>> I was curious about how much of a performance gain I'd get from
>> upgrading with the new -j option to pg_upgrade, so first I performed
>> the upgrade without it to get a baseline.  The command I ran for the
>> upgrade is as follows:
>> time pg_upgrade -v -d /var/lib/pgsql/9.2/data -D
>> /var/lib/pgsql/9.3/data -b /usr/pgsql-9.2/bin -B /usr/pgsql-9.3/bin
>>
>> time reported the following afterward the upgrade had completed successfully:
>> real    24m59.255s
>> user    0m17.069s
>> sys    15m25.153s
>>
>>
>> I then repeated the upgrade (after blowing away $PGDATA, and running
>> initdb again for 9.3), and re-ran pg_upgrade with the same command as
>> above, only with '-j4' appended to the end.  Surprisingly, the
>> completion time was less than 30 seconds faster.  I repeated a third
>> time with '-j8', and that was about the same completion time as with
>> '-j4'.  I guess I could repeat with 'j2', but I'd be surprised if it
>> was dramatically faster when -j4 was only marginally so.  It seems
>> like the parallelism of the -j option doesn't seem to be helping much
>> at all, in my case.
>>
>> Is this expected, or is it possible that there's a bug somewhere?  Let
>> me know if I can provide any logs from the upgrade.
>
> The documentation states:
>
>      The <option>--jobs</> option allows multiple CPU cores to be used
>      for copying/linking of files and to dump and reload database schemas
>      in parallel;  a good place to start is the maximum of the number of
>      CPU cores and tablespaces.  This option can dramatically reduce the
>      time to upgrade a multi-database server running on a multiprocessor
>      machine.
>
> My guess is that you didn't have many tablespaces or databases, or the
> copy time overwhelmed the performance improvement of the parallelism.
> I am not surprised you didn't see a big win.  Can you test --link
> mode?

I only have 1 tablespace, although I have 9 databases.  However, one
of the databases is about 95% of the total on-disk space, so that's
probably the explanation of why -j isn't helping me?

I don't have sufficient disk space to efficiently test --link mode,
unless there's some way to quickly roll back to the pre-upgrade
version of the database after a --link mode upgrade has completed
successfully that I'm not seeing?

On Mon, Jun 17, 2013 at 10:23 AM, Matheus de Oliveira
<matioli.matheus@gmail.com> wrote:
>
> On Mon, Jun 17, 2013 at 12:18 PM, Lonni J Friedman <netllama@gmail.com>
> wrote:
>>
>> On Sat, Jun 15, 2013 at 8:03 PM, Bruce Momjian <bruce@momjian.us> wrote:
>> > On Fri, Jun 14, 2013 at 02:29:24PM -0700, Lonni J Friedman wrote:
>> >> Greetings,
>> >> I'm in the early stages of preparing to upgrade a production 9.2
>> >> cluster to 9.3, by testing the beta of 9.3.  All of my testing is
>> >> happening on RHEL6-x86_64 on a dedicated server with 128GB RAM and 2x
>> >> Intel Xeon E5-2670 CPUs, with all of $PGDATA residing on an 8 disk
>> >> RAID10 array.
>> >>
>> >> Currently, a full pg_basebackup of my data is approaching 800GB in
>> >> size (uncompressed), so this isn't a tiny, trivial database.
>> >>
>> >> I was curious about how much of a performance gain I'd get from
>> >> upgrading with the new -j option to pg_upgrade, so first I performed
>> >> the upgrade without it to get a baseline.  The command I ran for the
>> >> upgrade is as follows:
>> >> time pg_upgrade -v -d /var/lib/pgsql/9.2/data -D
>> >> /var/lib/pgsql/9.3/data -b /usr/pgsql-9.2/bin -B /usr/pgsql-9.3/bin
>> >>
>> >> time reported the following afterward the upgrade had completed
>> >> successfully:
>> >> real    24m59.255s
>> >> user    0m17.069s
>> >> sys    15m25.153s
>> >>
>> >>
>> >> I then repeated the upgrade (after blowing away $PGDATA, and running
>> >> initdb again for 9.3), and re-ran pg_upgrade with the same command as
>> >> above, only with '-j4' appended to the end.  Surprisingly, the
>> >> completion time was less than 30 seconds faster.  I repeated a third
>> >> time with '-j8', and that was about the same completion time as with
>> >> '-j4'.  I guess I could repeat with 'j2', but I'd be surprised if it
>> >> was dramatically faster when -j4 was only marginally so.  It seems
>> >> like the parallelism of the -j option doesn't seem to be helping much
>> >> at all, in my case.
>> >>
>> >> Is this expected, or is it possible that there's a bug somewhere?  Let
>> >> me know if I can provide any logs from the upgrade.
>> >
>> > The documentation states:
>> >
>> >      The <option>--jobs</> option allows multiple CPU cores to be used
>> >      for copying/linking of files and to dump and reload database
>> > schemas
>> >      in parallel;  a good place to start is the maximum of the number of
>> >      CPU cores and tablespaces.  This option can dramatically reduce the
>> >      time to upgrade a multi-database server running on a multiprocessor
>> >      machine.
>> >
>> > My guess is that you didn't have many tablespaces or databases, or the
>> > copy time overwhelmed the performance improvement of the parallelism.
>> > I am not surprised you didn't see a big win.  Can you test --link
>> > mode?
>>
>> I only have 1 tablespace, although I have 9 databases.  However, one
>> of the databases is about 95% of the total on-disk space, so that's
>> probably the explanation of why -j isn't helping me?
>>
>
> That is probably why you DID had some improvement, although so little.
>
>>
>> I don't have sufficient disk space to efficiently test --link mode,
>> unless there's some way to quickly roll back to the pre-upgrade
>> version of the database after a --link mode upgrade has completed
>> successfully that I'm not seeing?
>>
>
> I didn't got it. AFAIK, in link mode it would take **less** space than
> normal mode, not the opposite. Am I wrong?

No, you're correct, but I want to keep the old version unchanged after
the upgrade.  I didn't think that was possible with link mode.

>
> Unless you want to keep a backup of the old cluster on the same machine, but
> even on that case you could take this backup compressed, although the
> overall time would be worst.

On Mon, Jun 17, 2013 at 10:26:48AM -0700, Lonni J Friedman wrote:
> >> I only have 1 tablespace, although I have 9 databases.  However, one
> >> of the databases is about 95% of the total on-disk space, so that's
> >> probably the explanation of why -j isn't helping me?
> >>
> >
> > That is probably why you DID had some improvement, although so little.

Agreed.

> >> I don't have sufficient disk space to efficiently test --link mode,
> >> unless there's some way to quickly roll back to the pre-upgrade
> >> version of the database after a --link mode upgrade has completed
> >> successfully that I'm not seeing?
> >>
> >
> > I didn't got it. AFAIK, in link mode it would take **less** space than
> > normal mode, not the opposite. Am I wrong?
>
> No, you're correct, but I want to keep the old version unchanged after
> the upgrade.  I didn't think that was possible with link mode.

It is not possible in link mode if you start the new cluster.  If you
don't start the new cluster, you can just delete the new cluster and
restart the unchanged old cluster, thought you have to remove the .old
suffix on the control file.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +