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

Started by PG Bug reporting formabout 7 years ago7 messagesbugs

noreply@postgresql.org

about 7 years ago

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

Jeff Janes

jeff.janes@gmail.com

about 7 years ago

In reply to: PG Bug reporting form (#1)

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

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

David Rowley

dgrowleyml@gmail.com

about 7 years ago

In reply to: Jeff Janes (#2)

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

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

Toshi Harada

harada.toshi@po.ntt-tx.co.jp

about 7 years ago

In reply to: David Rowley (#3)

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

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:

Show quoted text

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

David Rowley

dgrowleyml@gmail.com

about 7 years ago

In reply to: Toshi Harada (#4)

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

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

Toshi Harada

harada.toshi@po.ntt-tx.co.jp

about 7 years ago

In reply to: David Rowley (#5)

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

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:

Show quoted text

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

Tatsuo Ishii

t-ishii@sra.co.jp

about 7 years ago

In reply to: Toshi Harada (#6)

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

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

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

Attachments: