Thread: Postgres Ubuntu Default Package vs Enterprise DB vs Compiled One
Hi,
For my project, to be able to easily manage postgres instance and version, i want to install it in user's directory and launch by user also.
For my tests, I've got some issues with pg_restore on three differents installations.
First One : Ubuntu 15.10 Default Package
Second One : Enterprise Db installer with installation and data files stored in user's home.
Third One : Compiled Version Postgres with executable and data files stored in user's home.
All versions are 9.4.5.
My Backup is : 414 MB (Directory Archive)
Postgresql.conf modifications for all cases:
shared_buffers = 512MB
work_mem = 128MB # min 64kB
maintenance_work_mem = 512MB # min 1MB
fsync = off # turns forced synchronization on or off
synchronous_commit = off # synchronization level;
full_page_writes = off # recover from partial page writes
wal_buffers = 1024
checkpoint_segments = 64 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 5min # range 30s-1h
checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0
checkpoint_warning = 30s # 0 disables
Same Command for restore : pg_restore -d xxxx -j 4 -F d -U yyy -W backup
First One : Restore Time 1min53
Second One : Restore Time 3min48
Third One : Restore Time 2min51
I check files, encoding but i have not found any misconfiguration parameters.
What do i have to do to have the same performance in the first and the third ?
Is a problem with :
User Space / Kernel Space ?
Compilations options ?
LD Library ?
Missing something ?
Thanks you for your help
Sebastien Boutte
On 12/26/2015 08:16 AM, Sébastien Boutté wrote: > Hi, > > For my project, to be able to easily manage postgres instance and > version, i want to install it in user's directory and launch by user also. > > For my tests, I've got some issues with pg_restore on three differents > installations. > > First One : Ubuntu 15.10 Default Package > Second One : Enterprise Db installer with installation and data files > stored in user's home. > Third One : Compiled Version Postgres with executable and data files > stored in user's home. > > All versions are 9.4.5. > > My Backup is : 414 MB (Directory Archive) > > Postgresql.conf modifications for all cases: > shared_buffers = 512MB > work_mem = 128MB# min 64kB > maintenance_work_mem = 512MB# min 1MB > fsync = off# turns forced synchronization on or off > synchronous_commit = off# synchronization level; > full_page_writes = off# recover from partial page writes > wal_buffers = 1024 > checkpoint_segments = 64# in logfile segments, min 1, 16MB each > checkpoint_timeout = 5min# range 30s-1h > checkpoint_completion_target = 0.9# checkpoint target duration, 0.0 - 1.0 > checkpoint_warning = 30s# 0 disables > > Same Command for restore : pg_restore -d xxxx -j 4 -F d -U yyy -W backup > > First One : Restore Time 1min53 > Second One : Restore Time 3min48 > Third One : Restore Time 2min51 > > I check files, encoding but i have not found any misconfiguration > parameters. > > What do i have to do to have the same performance in the first and the > third ? Honestly, not enough information to make a determination. So: 1) Is the order you show above the actual order you did the restores? 2) Are the above timings from one run for each setup or multiple runs? 3) Just to be clear, this is all on the same machine, correct? > Is a problem with : > User Space / Kernel Space ? > Compilations options ? > LD Library ? > Missing something ? To see how each installation is set up you can: 1) Run the pg_config for each installation, so for instance, on a compiled version on my machine: /usr/local/pgsql/bin/pg_config 2) Run each installations pg_controldata against the installation data directory: /usr/local/pgsql/bin/pg_controldata /usr/local/pgsql/data/ > > Thanks you for your help > > Sebastien Boutte > > > -- Adrian Klaver adrian.klaver@aklaver.com
Honestly, not enough information to make a determination. So:
1) Is the order you show above the actual order you did the restores?
No, i have made multiples tests but it's always the same order of nature.
2) Are the above timings from one run for each setup or multiple runs?
Multiple runs.
3) Just to be clear, this is all on the same machine, correct? Yes
2) Are the above timings from one run for each setup or multiple runs?
Multiple runs.
3) Just to be clear, this is all on the same machine, correct? Yes
Core i5 2500K
16G RAM
SSD 256Go
To see how each installation is set up you can:
1) Run the pg_config for each installation, so for instance, on a compiled version on my machine:
/usr/local/pgsql/bin/pg_config
i've attached config files for :
Is a problem with :
User Space / Kernel Space ?
Compilations options ?
LD Library ?
Missing something ?
To see how each installation is set up you can:
1) Run the pg_config for each installation, so for instance, on a compiled version on my machine:
/usr/local/pgsql/bin/pg_config
i've attached config files for :
Ubuntu Default : pgconfig.ubuntu & pg_controldata.ubuntu
Compiled Version : pg_config.compiled & pg_controldata.compiled
2) Run each installations pg_controldata against the installation data directory:
/usr/local/pgsql/bin/pg_controldata /usr/local/pgsql/data/
/usr/local/pgsql/bin/pg_controldata /usr/local/pgsql/data/
Attachment
On 12/26/2015 01:36 PM, Sébastien Boutté wrote: > Honestly, not enough information to make a determination. So: > > 1) Is the order you show above the actual order you did the restores? > No, i have made multiples tests but it's always the same order of nature. > > 2) Are the above timings from one run for each setup or multiple runs? > Multiple runs. > > 3) Just to be clear, this is all on the same machine, correct? Yes > Core i5 2500K > 16G RAM > SSD 256Go > > > Is a problem with : > User Space / Kernel Space ? > Compilations options ? > LD Library ? > Missing something ? > > > To see how each installation is set up you can: > > 1) Run the pg_config for each installation, so for instance, on a > compiled version on my machine: > > /usr/local/pgsql/bin/pg_config > > i've attached config files for : > > Ubuntu Default : pgconfig.ubuntu & pg_controldata.ubuntu > Compiled Version : pg_config.compiled & pg_controldata.compiled > > > 2) Run each installations pg_controldata against the installation data > directory: > > /usr/local/pgsql/bin/pg_controldata /usr/local/pgsql/data/ Hmm, the Ubuntu configuration sets a lot of *FLAGS. Unfortunately I am not well versed enough to offer any guidance on whether they matter or not. That will depend on someone more capable than I. So, you are interested in only the Ubuntu and compiled versions of the databases, not the EDB version, or am I missing some attachments? Something else came to mind, when you ran the pg_restore command did you run the one specific to each installation or whichever was first on the PATH? Lastly, are there any errors in the respective Postgres logs that might shed light. I ask because the Ubuntu Postgres is compiled with a lot of extras built in, '--with-python' '--with-pam' '--with-openssl', etc, whereas the self compiled version is not. Just wondering if there are objects in your backup that need those extras? > > > > > > > > -- Adrian Klaver adrian.klaver@aklaver.com
I manage to have the same performance between ubuntu package and compiled version ; just a few seconds of difference.
don't know what parameter i've missed i restart again and again ...
Sorry to have pollute the mailing list ;)
Thank you Adrian for the tips on pg_config and pg_controldata.
Good night
Sébastien Boutté
On Sat, Dec 26, 2015 at 10:34 PM, Sébastien Boutté <sebastien.boutte@gmail.com> wrote:
Honestly, not enough information to make a determination. So:
1) Is the order you show above the actual order you did the restores?No, i have made multiples tests but it's always the same order of nature.
2) Are the above timings from one run for each setup or multiple runs?
Multiple runs.
3) Just to be clear, this is all on the same machine, correct? YesCore i5 2500K16G RAMSSD 256GoIs a problem with :
User Space / Kernel Space ?
Compilations options ?
LD Library ?
Missing something ?
To see how each installation is set up you can:
1) Run the pg_config for each installation, so for instance, on a compiled version on my machine:
/usr/local/pgsql/bin/pg_config
i've attached config files for :Ubuntu Default : pgconfig.ubuntu & pg_controldata.ubuntuCompiled Version : pg_config.compiled & pg_controldata.compiled2) Run each installations pg_controldata against the installation data directory:
/usr/local/pgsql/bin/pg_controldata /usr/local/pgsql/data/