Thread: BUG #15610: Performance problem of PostgreSQL 11.1 Windows version (EDB created version)

BUG #15610: Performance problem of PostgreSQL 11.1 Windows version (EDB created version)

From
PG Bug reporting form
Date:
The following bug has been logged on the website:

Bug reference:      15610
Logged by:          Toshi Harada
Email address:      harada.toshi@po.ntt-tx.co.jp
PostgreSQL version: 11.1
Operating system:   Windows Server 2012 R2 Standard Edition (32 CPU)
Description:

[Overview]

Installing PostgreSQL 11.1 Windows verson (EDB created version) and running
pgbench, tps is very low compared to PostgreSQL 10.6.
Also, tps is lower than PostgreSQL 11.1 Windows version (BIgSQL created
version).
Is there something wrong with the build option of PostgreSQL 11.1 in the EDB
created version?

[Verification procedure]

* Install using the following three installers for Windows.
  - https://www.enterprisedb.com/downloads/postgres-postgresql-downloads 
    - postgresql-11.1-1-windows-x64.exe
    - postgresql-10.6-1-windows-x64.exe
  - http://www.openscg.com/bigsql/postgresql/installers/
    - postgresql-11.1-win64.exe 

* Use pgbench to generate data with scale factor 2000.
* Perform the following custom query for 60 seconds with the number of
connections (1, 2, 4, 8, 16, 32, 48, 64, 96).

[custom query]
\set naccounts 100000 * :scale
\set row_count 10000
\set aid_max :naccounts - :row_count
\set aid random(1, :aid_max)

SELECT count(abalance) FROM pgbench_accounts WHERE aid BETWEEN :aid and :aid
+ :row_count;


* Comparison of tps of PostgreSQL 10.6 (EDB), PostgreSQL 11.1 (EDB),
PostgreSQL 11.1 (BigSQL).

connections  10.6(EDB)  11.1(EDB)  11.1(BigSQL)
1            550.0      409.6      605.8
2            1103.6     753.5      1229.8
4            2200.4     1236.8     2539.8
8            4460.9     2241.5     5011.0
16           8862.1     2798.4     9827.0
32           16906.3    1896.0     18865.8
48           16688.8    1599.3     18794.8
64           16614.9    1438.6     19055.3
96           9492.4     1309.5     10396.9


On Tue, Jan 29, 2019 at 12:46 AM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:

Bug reference:      15610
Logged by:          Toshi Harada
Email address:      harada.toshi@po.ntt-tx.co.jp
PostgreSQL version: 11.1
Operating system:   Windows Server 2012 R2 Standard Edition (32 CPU)
Description:       

[Overview]

Installing PostgreSQL 11.1 Windows verson (EDB created version) and running
pgbench, tps is very low compared to PostgreSQL 10.6.
Also, tps is lower than PostgreSQL 11.1 Windows version (BIgSQL created
version).
Is there something wrong with the build option of PostgreSQL 11.1 in the EDB
created version?

Can you compare the configuration settings and report any differences?

select name,setting from pg_settings;

Cheers,

Jeff 
On Wed, 30 Jan 2019 at 07:42, Jeff Janes <jeff.janes@gmail.com> wrote:
>
> On Tue, Jan 29, 2019 at 12:46 AM PG Bug reporting form <noreply@postgresql.org> wrote:
>>
>> The following bug has been logged on the website:
>>
>> Bug reference:      15610
>> Logged by:          Toshi Harada
>> Email address:      harada.toshi@po.ntt-tx.co.jp
>> PostgreSQL version: 11.1
>> Operating system:   Windows Server 2012 R2 Standard Edition (32 CPU)
>> Description:
>>
>> [Overview]
>>
>> Installing PostgreSQL 11.1 Windows verson (EDB created version) and running
>> pgbench, tps is very low compared to PostgreSQL 10.6.
>> Also, tps is lower than PostgreSQL 11.1 Windows version (BIgSQL created
>> version).
>> Is there something wrong with the build option of PostgreSQL 11.1 in the EDB
>> created version?
>
>
> Can you compare the configuration settings and report any differences?
>
> select name,setting from pg_settings;

It might also be useful to see the EXPLAIN ANALYZE from each instance.
Different ANALYZE runs can generate different table stats, so the plan
may differ between instances, even if the code and settings are the
same.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


Hi.

We got configuration and "explain analyze" result.
(buginfo_15610.tgz)

Summary
* The difference in config is the location of the file and the locale.
* I executed the  SELECT statement with "explain analyze". 
  There was no difference in the structure of the execution plan.

----
buginfo_15610.tgz contents.

explain_bigsql_11.1.txt explain analyze log.
explain_edb_10.6.txt    explain analyze log.
explain_edb_11.0.txt    explain analyze log.
select_model.sql        explain analyze statement.
settings_bigsql_11.1    BigSQL 11 configuration.
settings_diff.txt       EDB 11 and BigSQL 11 diff
settings_edb_10.6       EDB 10 configuration.
settings_edb_11.1       EDB 11 configuration.

----

Best regards.

David Rowley <david.rowley@2ndquadrant.com> wrote:
> On Wed, 30 Jan 2019 at 07:42, Jeff Janes <jeff.janes@gmail.com> wrote:
> >
> > On Tue, Jan 29, 2019 at 12:46 AM PG Bug reporting form <noreply@postgresql.org> wrote:
> >>
> >> The following bug has been logged on the website:
> >>
> >> Bug reference:      15610
> >> Logged by:          Toshi Harada
> >> Email address:      harada.toshi@po.ntt-tx.co.jp
> >> PostgreSQL version: 11.1
> >> Operating system:   Windows Server 2012 R2 Standard Edition (32 CPU)
> >> Description:
> >>
> >> [Overview]
> >>
> >> Installing PostgreSQL 11.1 Windows verson (EDB created version) and running
> >> pgbench, tps is very low compared to PostgreSQL 10.6.
> >> Also, tps is lower than PostgreSQL 11.1 Windows version (BIgSQL created
> >> version).
> >> Is there something wrong with the build option of PostgreSQL 11.1 in the EDB
> >> created version?
> >
> >
> > Can you compare the configuration settings and report any differences?
> >
> > select name,setting from pg_settings;
> 
> It might also be useful to see the EXPLAIN ANALYZE from each instance.
> Different ANALYZE runs can generate different table stats, so the plan
> may differ between instances, even if the code and settings are the
> same.
> 
> -- 
>  David Rowley                   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services

Attachment
On Wed, 30 Jan 2019 at 18:08, Toshi Harada <harada.toshi@po.ntt-tx.co.jp> wrote:
> explain_bigsql_11.1.txt explain analyze log.
> explain_edb_10.6.txt    explain analyze log.
> explain_edb_11.0.txt    explain analyze log.
> select_model.sql        explain analyze statement.
> settings_bigsql_11.1    BigSQL 11 configuration.
> settings_diff.txt       EDB 11 and BigSQL 11 diff
> settings_edb_10.6       EDB 10 configuration.
> settings_edb_11.1       EDB 11 configuration.

Most likely down to the collation being C on the EDB versions and
Japanese_Japan.932 on the bigsql one.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


Hi.

thank you for your answer.

However, if it is a locale difference, 
I do not think there will be any significant performance difference between EDB 10.6 and EDB 11.1.
(EDB 10.6 was also the locale C)

----
tps (shown again)
connections  10.6(EDB)  11.1(EDB)  11.1(BigSQL)
1            550.0      409.6      605.8
2            1103.6     753.5      1229.8
4            2200.4     1236.8     2539.8
8            4460.9     2241.5     5011.0
16           8862.1     2798.4     9827.0
32           16906.3    1896.0     18865.8
48           16688.8    1599.3     18794.8
64           16614.9    1438.6     19055.3
96           9492.4     1309.5     10396.9

David Rowley <david.rowley@2ndquadrant.com> wrote:
> On Wed, 30 Jan 2019 at 18:08, Toshi Harada <harada.toshi@po.ntt-tx.co.jp> wrote:
> > explain_bigsql_11.1.txt explain analyze log.
> > explain_edb_10.6.txt    explain analyze log.
> > explain_edb_11.0.txt    explain analyze log.
> > select_model.sql        explain analyze statement.
> > settings_bigsql_11.1    BigSQL 11 configuration.
> > settings_diff.txt       EDB 11 and BigSQL 11 diff
> > settings_edb_10.6       EDB 10 configuration.
> > settings_edb_11.1       EDB 11 configuration.
> 
> Most likely down to the collation being C on the EDB versions and
> Japanese_Japan.932 on the bigsql one.
> 
> -- 
>  David Rowley                   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services




Hi Harada-san,

> Hi.
> 
> thank you for your answer.
> 
> However, if it is a locale difference, 
> I do not think there will be any significant performance difference between EDB 10.6 and EDB 11.1.
> (EDB 10.6 was also the locale C)
> 
> ----
> tps (shown again)
> connections  10.6(EDB)  11.1(EDB)  11.1(BigSQL)
> 1            550.0      409.6      605.8
> 2            1103.6     753.5      1229.8
> 4            2200.4     1236.8     2539.8
> 8            4460.9     2241.5     5011.0
> 16           8862.1     2798.4     9827.0
> 32           16906.3    1896.0     18865.8
> 48           16688.8    1599.3     18794.8
> 64           16614.9    1438.6     19055.3
> 96           9492.4     1309.5     10396.9
> 
> David Rowley <david.rowley@2ndquadrant.com> wrote:
>> On Wed, 30 Jan 2019 at 18:08, Toshi Harada <harada.toshi@po.ntt-tx.co.jp> wrote:
>> > explain_bigsql_11.1.txt explain analyze log.
>> > explain_edb_10.6.txt    explain analyze log.
>> > explain_edb_11.0.txt    explain analyze log.
>> > select_model.sql        explain analyze statement.
>> > settings_bigsql_11.1    BigSQL 11 configuration.
>> > settings_diff.txt       EDB 11 and BigSQL 11 diff
>> > settings_edb_10.6       EDB 10 configuration.
>> > settings_edb_11.1       EDB 11 configuration.
>> 
>> Most likely down to the collation being C on the EDB versions and
>> Japanese_Japan.932 on the bigsql one.

I think David is saying that EDB is slower because of C local, whereas
BigSQl's locale is Japanese_Japan.932. Though I thought C locale
(disabling locale) is always faster than any locale enabled.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp