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

The following bug has been logged on the website:

Bug reference: 15610
Logged by: Toshi Harada
Email address: harada(dot)toshi(at)po(dot)ntt-tx(dot)co(dot)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(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 15610
> Logged by: Toshi Harada
> Email address: harada(dot)toshi(at)po(dot)ntt-tx(dot)co(dot)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(dot)janes(at)gmail(dot)com> wrote:
>
> On Tue, Jan 29, 2019 at 12:46 AM PG Bug reporting form <noreply(at)postgresql(dot)org> wrote:
>>
>> The following bug has been logged on the website:
>>
>> Bug reference: 15610
>> Logged by: Toshi Harada
>> Email address: harada(dot)toshi(at)po(dot)ntt-tx(dot)co(dot)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(dot)rowley(at)2ndquadrant(dot)com> wrote:
> On Wed, 30 Jan 2019 at 07:42, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> >
> > On Tue, Jan 29, 2019 at 12:46 AM PG Bug reporting form <noreply(at)postgresql(dot)org> wrote:
> >>
> >> The following bug has been logged on the website:
> >>
> >> Bug reference: 15610
> >> Logged by: Toshi Harada
> >> Email address: harada(dot)toshi(at)po(dot)ntt-tx(dot)co(dot)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

On Wed, 30 Jan 2019 at 18:08, Toshi Harada <harada(dot)toshi(at)po(dot)ntt-tx(dot)co(dot)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(dot)rowley(at)2ndquadrant(dot)com> wrote:
> On Wed, 30 Jan 2019 at 18:08, Toshi Harada <harada(dot)toshi(at)po(dot)ntt-tx(dot)co(dot)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(dot)rowley(at)2ndquadrant(dot)com> wrote:
>> On Wed, 30 Jan 2019 at 18:08, Toshi Harada <harada(dot)toshi(at)po(dot)ntt-tx(dot)co(dot)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