pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Potential performance issues
10+ messages / 8 participants
[nested] [flat]

* Potential performance issues
@ 2021-02-28 15:04  Jung, Jinho <jinho.jung@gatech.edu>
  0 siblings, 3 replies; 10+ messages in thread

From: Jung, Jinho @ 2021-02-28 15:04 UTC (permalink / raw)
  To: pgsql-performance

# Performance issues discovered from differential test

Hello. We are studying DBMS from GeorgiaTech and reporting interesting queries that potentially show performance problems.

To discover such cases, we used the following procedures:

* Install four DBMSs with the latest version (PostgreSQL, SQLite, MySQL, CockroachDB)
* Import TPCC-C benchmark for each DBMS
* Generate random query (and translate the query to handle different dialects)
* Run the query and measure the query execution time
   * Remove `LIMIT` to prevent any non-deterministic behaviors
   * Discard the test case if any DBMS returned an error
   * Some DBMS does not show the actual query execution time. In this case, query the `current time` before and after the actual query, and then we calculate the elapsed time.

In this report, we attached a few queries. We believe that there are many duplicated or false-positive cases. It would be great if we can get feedback about the reported queries. Once we know the root cause of the problem or false positive, we will make a follow-up report after we remove them all.

For example, the below query runs x1000 slower than other DBMSs from PostgreSQL.

    select ref_0.ol_amount as c0
    from order_line as ref_0
        left join stock as ref_1
          on (ref_0.ol_o_id = ref_1.s_w_id )
        inner join warehouse as ref_2
        on (ref_1.s_dist_09 is NULL)
    where ref_2.w_tax is NULL;


* Query files link:

wget https://gts3.org/~jjung/report1/pg.tar.gz

* Execution result (execution time (second))

| Filename | Postgres |   Mysql  | Cockroachdb |  Sqlite  |   Ratio  |
|---------:|---------:|---------:|------------:|---------:|---------:|
|    34065 |  1.31911 |    0.013 |     0.02493 |    1.025 |   101.47 |
|    36399 |  3.60298 |    0.015 |     1.05593 |    3.487 |   240.20 |
|    35767 |  4.01327 |    0.032 |     0.00727 |    2.311 |   552.19 |
|    11132 |   4.3518 |    0.022 |     0.00635 |    3.617 |   684.88 |
|    29658 |   4.6783 |    0.034 |     0.00778 |     2.63 |   601.10 |
|    19522 |  1.06943 |    0.014 |     0.00569 |   0.0009 |  1188.26 |
|    38388 |  3.21383 |    0.013 |     0.00913 |    2.462 |   352.09 |
|     7187 |  1.20267 |    0.015 |     0.00316 |   0.0009 |  1336.30 |
|    24121 |  2.80611 |    0.014 |     0.03083 |    0.005 |   561.21 |
|    25800 |  3.95163 |    0.024 |     0.73027 |    3.876 |   164.65 |
|     2030 |  1.91181 |    0.013 |     0.04123 |    1.634 |   147.06 |
|    17383 |  3.28785 |    0.014 |     0.00611 |      2.4 |   538.45 |
|    19551 |  4.70967 |    0.014 |     0.00329 |   0.0009 |  5232.97 |
|    26595 |  3.70423 |    0.014 |     0.00601 |    2.747 |   615.92 |
|      469 |  4.18906 |    0.013 |     0.12343 |    0.016 |   322.23 |


# Reproduce: install DBMSs, import TPCC benchmark, run query

### Cockroach (from binary)

```sh
# install DBMS
wget https://binaries.cockroachdb.com/cockroach-v20.2.5.linux-amd64.tgz
tar xzvf cockroach-v20.2.5.linux-amd64.tgz
sudo cp -i cockroach-v20.2.5.linux-amd64/cockroach /usr/local/bin/cockroach20

sudo mkdir -p /usr/local/lib/cockroach
sudo cp -i cockroach-v20.2.5.linux-amd64/lib/libgeos.so /usr/local/lib/cockroach/
sudo cp -i cockroach-v20.2.5.linux-amd64/lib/libgeos_c.so /usr/local/lib/cockroach/

# test
which cockroach20
cockroach20 demo

# start the DBMS (to make initial node files)
cd ~
cockroach20 start-single-node --insecure --store=node20 --listen-addr=localhost:26259 --http-port=28080 --max-sql-memory=1GB --background
# quit
cockroach20 quit --insecure --host=localhost:26259

# import DB
mkdir -p node20/extern
wget https://gts3.org/~jjung/tpcc-perf/tpcc_cr.tar.gz
tar xzvf tpcc_cr.tar.gz
cp tpcc_cr.sql node20/tpcc.sql

# start the DBMS again and createdb
cockroach20 sql --insecure --host=localhost:26259 --execute="CREATE DATABASE IF NOT EXISTS cockroachdb;"
--cockroach20 sql --insecure --host=localhost:26259 --execute="DROP DATABASE cockroachdb;"

cockroach20 sql --insecure --host=localhost:26259 --database=cockroachdb --execute="IMPORT PGDUMP 'nodelocal://self/tpcc.sql';"

# test
cockroach20 sql --insecure --host=localhost:26259 --database=cockroachdb --execute="explain analyze select count(*) from order_line;"

# run query
cockroach20 sql --insecure --host=localhost --port=26259 --database=cockroachdb < query.sql
```


### Postgre (from SRC)

```sh
# remove any previous postgres (if exist)
sudo apt-get --purge remove postgresql postgresql-doc postgresql-common

# build latest postgres
git clone https://github.com/postgres/postgres.git
mkdir bld
cd bld
../configure
make -j 20

# install DBMS
sudo su
make install
adduser postgres
rm -rf /usr/local/pgsql/data
mkdir /usr/local/pgsql/data
chown -R postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
/usr/local/pgsql/bin/createdb jjung
#/usr/local/pgsql/bin/psql postgresdb

/usr/local/pgsql/bin/createuser -s {username}
/usr/local/pgsql/bin/createdb postgresdb
/usr/local/pgsql/bin/psql

=# alter {username} with superuser

# import DB
wget https://gts3.org/~jjung/tpcc-perf/tpcc_pg.tar.gz
tar xzvf tpcc_pg.tar.gz
/usr/local/pgsql/bin/psql -p 5432 -d postgresdb -f tpcc_pg.sql

# test
/usr/local/pgsql/bin/psql -p 5432 -d postgresdb -c "select * from warehouse"
/usr/local/pgsql/bin/psql -p 5432 -d postgresdb -c "\\dt"

# run query
/usr/local/pgsql/bin/psql -p 5432 -d postgresdb -f query.sql
```


### Sqlite (from SRC)

```sh
# uninstall any existing
sudo apt purge sliqte3

# build latest sqlite from src
git clone https://github.com/sqlite/sqlite.git
cd sqlite
mkdir bld
cd bld
../configure
make -j 20

# install DBMS
sudo make install

# import DB
wget https://gts3.org/~jjung/tpcc-perf/tpcc_sq.tar.gz
tar xzvf tpcc_sq.tar.gz

# test
sqlite3 tpcc_sq.db
sqlite> select * from warehouse;

# run query
sqlite3 tpcc_sq.db < query.sql
```


### Mysql (install V8.0.X)

```sh
# remove mysql v5.X (if exist)
sudo apt purge mysql-server mysql-common mysql-client

# install
wget https://dev.mysql.com/get/mysql-apt-config_0.8.16-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.16-1_all.deb
 # then select mysql 8.0 server
sudo apt update
sudo apt install mysql-client mysql-community-server mysql-server

# check
mysql -u root -p

# create user mysql
 CREATE USER 'mysql'@'localhost' IDENTIFIED BY 'mysql';
 alter user 'root'@'localhost' identified by 'mysql';

# modify the conf (should add "skip-grant-tables" under [mysqld])
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

# optimize
# e.g., https://gist.github.com/fevangelou/fb72f36bbe333e059b66

# import DB
wget https://gts3.org/~jjung/tpcc-perf/tpcc_my.tar.gz
tar xzvf tpcc_my.tar.gz
mysql -u mysql -pmysql -e "create database mysqldb"
mysql -u mysql -pmysql mysqldb < tpcc_my.sql

# test
mysql -u mysql -pmysql mysqldb -e "show tables"
mysql -u mysql -pmysql mysqldb -e "select * from customer"

# run query
mysql -u mysql -pmysql mysqldb < query.sql
```


# Evaluation environment

* Server: Ubuntu 18.04 (64bit)
* CockroachDB: v20.2.5
* PostgreSQL: latest commit (21 Feb, 2021)
* MySQL: v8.0.23
* SQLite: latest commit (21 Feb, 2021)

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Potential performance issues
@ 2021-03-01 12:59  Andrew Dunstan <andrew@dunslane.net>
  parent: Jung, Jinho <jinho.jung@gatech.edu>
  2 siblings, 1 reply; 10+ messages in thread

From: Andrew Dunstan @ 2021-03-01 12:59 UTC (permalink / raw)
  To: Jung, Jinho <jinho.jung@gatech.edu>; pgsql-performance


On 2/28/21 10:04 AM, Jung, Jinho wrote:
> # install DBMS
> sudo su
> make install
> adduser postgres
> rm -rf /usr/local/pgsql/data
> mkdir /usr/local/pgsql/data
> chown -R postgres /usr/local/pgsql/data
> su - postgres
> /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
> /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
> /usr/local/pgsql/bin/createdb jjung


Using an untuned Postgres is fairly useless for a performance test. Out
of the box, shared_buffers and work_mem are too low for almost all
situations, and many other settings can also usually be improved. The
default settings are deliberately very conservative.


cheers


andrew



-- Andrew Dunstan EDB: https://www.enterprisedb.com





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Potential performance issues
@ 2021-03-01 13:04  MichaelDBA <MichaelDBA@sqlexec.com>
  parent: Jung, Jinho <jinho.jung@gatech.edu>
  2 siblings, 1 reply; 10+ messages in thread

From: MichaelDBA @ 2021-03-01 13:04 UTC (permalink / raw)
  To: Jung, Jinho <jinho.jung@gatech.edu>; +Cc: pgsql-performance

Hi,

It is worthy work trying to compare performance across multiple database 
vendors, but unfortunately, it does not really come across as comparing 
apples to apples.

For instance, configuration parameters:  I do not see where you are 
doing any modification of configuration at all.  Since DBVendors are 
different in how they apply "out of the box" configuration,  this alone 
can severely affect your comparison tests even though you are using a 
standard in benchmark testing, TPCC-C.  Postgres is especially 
conservative in "out of the box" configuration.  For instance, 
"work_mem" is set to an incredibly low value of 4MB.  This has a big 
impact on many types of queries. Oracle has something called SGA_TARGET, 
which if enabled, self-regulates where the memory is utilized, thus not 
limiting query memory specifically in the way Postgres does.  This is 
just one example of a bazillion others where differences in "out of the 
box" configuration makes these tests more like comparing apples to 
oranges.  There are many other areas of configuration related to memory, 
disk, parallel execution, io concurrency, etc.

In sum, when comparing performance across different database vendors, 
there are many other factors that must be taken into account when trying 
to do an impartial comparison.  I just showed one: how configuration 
differences can skew the results.

Regards,
Michael Vitale




Jung, Jinho wrote on 2/28/2021 10:04 AM:
> # Performance issues discovered from differential test
>
> Hello. We are studying DBMS from GeorgiaTech and reporting interesting 
> queries that potentially show performance problems.
>
> To discover such cases, we used the following procedures:
>
> * Install four DBMSs with the latest version (PostgreSQL, SQLite, 
> MySQL, CockroachDB)
> * Import TPCC-C benchmark for each DBMS
> * Generate random query (and translate the query to handle different 
> dialects)
> * Run the query and measure the query execution time
>    * Remove `LIMIT` to prevent any non-deterministic behaviors
>    * Discard the test case if any DBMS returned an error
>    * Some DBMS does not show the actual query execution time. In this 
> case, query the `current time` before and after the actual query, and 
> then we calculate the elapsed time.
>
> In this report, we attached a few queries. We believe that there are 
> many duplicated or false-positive cases. It would be great if we can 
> get feedback about the reported queries. Once we know the root cause 
> of the problem or false positive, we will make a follow-up report 
> after we remove them all.
>
> For example, the below query runs x1000 slower than other DBMSs from 
> PostgreSQL.
>
>     select ref_0.ol_amount as c0
>     from order_line as ref_0
>         left join stock as ref_1
>           on (ref_0.ol_o_id = ref_1.s_w_id )
>         inner join warehouse as ref_2
>         on (ref_1.s_dist_09 is NULL)
>     where ref_2.w_tax is NULL;
>
>
> * Query files link:
>
> wget https://gts3.org/~jjung/report1/pg.tar.gz
>
> * Execution result (execution time (second))
>
> | Filename | Postgres |   Mysql  | Cockroachdb |  Sqlite  |   Ratio  |
> |---------:|---------:|---------:|------------:|---------:|---------:|
> |    34065 |  1.31911 |    0.013 |     0.02493 |    1.025 | 101.47 |
> |    36399 |  3.60298 |    0.015 |     1.05593 |    3.487 | 240.20 |
> |    35767 |  4.01327 |    0.032 |     0.00727 |    2.311 | 552.19 |
> |    11132 |   4.3518 |    0.022 |     0.00635 |    3.617 | 684.88 |
> |    29658 |   4.6783 |    0.034 |     0.00778 |     2.63 | 601.10 |
> |    19522 |  1.06943 |    0.014 |     0.00569 |   0.0009 |  1188.26 |
> |    38388 |  3.21383 |    0.013 |     0.00913 |    2.462 | 352.09 |
> |     7187 |  1.20267 |    0.015 |     0.00316 |   0.0009 |  1336.30 |
> |    24121 |  2.80611 |    0.014 |     0.03083 |    0.005 | 561.21 |
> |    25800 |  3.95163 |    0.024 |     0.73027 |    3.876 | 164.65 |
> |     2030 |  1.91181 |    0.013 |     0.04123 |    1.634 | 147.06 |
> |    17383 |  3.28785 |    0.014 |     0.00611 |      2.4 | 538.45 |
> |    19551 |  4.70967 |    0.014 |     0.00329 |   0.0009 |  5232.97 |
> |    26595 |  3.70423 |    0.014 |     0.00601 |    2.747 | 615.92 |
> |      469 |  4.18906 |    0.013 |     0.12343 |    0.016 | 322.23 |
>
>
> # Reproduce: install DBMSs, import TPCC benchmark, run query
>
> ### Cockroach (from binary)
>
> ```sh
> # install DBMS
> wget https://binaries.cockroachdb.com/cockroach-v20.2.5.linux-amd64.tgz
> tar xzvf cockroach-v20.2.5.linux-amd64.tgz
> sudo cp -i cockroach-v20.2.5.linux-amd64/cockroach 
> /usr/local/bin/cockroach20
>
> sudo mkdir -p /usr/local/lib/cockroach
> sudo cp -i cockroach-v20.2.5.linux-amd64/lib/libgeos.so 
> /usr/local/lib/cockroach/
> sudo cp -i cockroach-v20.2.5.linux-amd64/lib/libgeos_c.so 
> /usr/local/lib/cockroach/
>
> # test
> which cockroach20
> cockroach20 demo
>
> # start the DBMS (to make initial node files)
> cd ~
> cockroach20 start-single-node --insecure --store=node20 
> --listen-addr=localhost:26259 --http-port=28080 --max-sql-memory=1GB 
> --background
> # quit
> cockroach20 quit --insecure --host=localhost:26259
>
> # import DB
> mkdir -p node20/extern
> wget https://gts3.org/~jjung/tpcc-perf/tpcc_cr.tar.gz
> tar xzvf tpcc_cr.tar.gz
> cp tpcc_cr.sql node20/tpcc.sql
>
> # start the DBMS again and createdb
> cockroach20 sql --insecure --host=localhost:26259 --execute="CREATE 
> DATABASE IF NOT EXISTS cockroachdb;"
> --cockroach20 sql --insecure --host=localhost:26259 --execute="DROP 
> DATABASE cockroachdb;"
>
> cockroach20 sql --insecure --host=localhost:26259 
> --database=cockroachdb --execute="IMPORT PGDUMP 
> 'nodelocal://self/tpcc.sql';"
>
> # test
> cockroach20 sql --insecure --host=localhost:26259 
> --database=cockroachdb --execute="explain analyze select count(*) from 
> order_line;"
>
> # run query
> cockroach20 sql --insecure --host=localhost --port=26259 
> --database=cockroachdb < query.sql
> ```
>
>
> ### Postgre (from SRC)
>
> ```sh
> # remove any previous postgres (if exist)
> sudo apt-get --purge remove postgresql postgresql-doc postgresql-common
>
> # build latest postgres
> git clone https://github.com/postgres/postgres.git
> mkdir bld
> cd bld
> ../configure
> make -j 20
>
> # install DBMS
> sudo su
> make install
> adduser postgres
> rm -rf /usr/local/pgsql/data
> mkdir /usr/local/pgsql/data
> chown -R postgres /usr/local/pgsql/data
> su - postgres
> /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
> /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
> /usr/local/pgsql/bin/createdb jjung
> #/usr/local/pgsql/bin/psql postgresdb
>
> /usr/local/pgsql/bin/createuser -s {username}
> /usr/local/pgsql/bin/createdb postgresdb
> /usr/local/pgsql/bin/psql
>
> =# alter {username} with superuser
>
> # import DB
> wget https://gts3.org/~jjung/tpcc-perf/tpcc_pg.tar.gz
> tar xzvf tpcc_pg.tar.gz
> /usr/local/pgsql/bin/psql -p 5432 -d postgresdb -f tpcc_pg.sql
>
> # test
> /usr/local/pgsql/bin/psql -p 5432 -d postgresdb -c "select * from 
> warehouse"
> /usr/local/pgsql/bin/psql -p 5432 -d postgresdb -c "\\dt"
>
> # run query
> /usr/local/pgsql/bin/psql -p 5432 -d postgresdb -f query.sql
> ```
>
>
> ### Sqlite (from SRC)
>
> ```sh
> # uninstall any existing
> sudo apt purge sliqte3
>
> # build latest sqlite from src
> git clone https://github.com/sqlite/sqlite.git
> cd sqlite
> mkdir bld
> cd bld
> ../configure
> make -j 20
>
> # install DBMS
> sudo make install
>
> # import DB
> wget https://gts3.org/~jjung/tpcc-perf/tpcc_sq.tar.gz
> tar xzvf tpcc_sq.tar.gz
>
> # test
> sqlite3 tpcc_sq.db
> sqlite> select * from warehouse;
>
> # run query
> sqlite3 tpcc_sq.db < query.sql
> ```
>
>
> ### Mysql (install V8.0.X)
>
> ```sh
> # remove mysql v5.X (if exist)
> sudo apt purge mysql-server mysql-common mysql-client
>
> # install
> wget https://dev.mysql.com/get/mysql-apt-config_0.8.16-1_all.deb
> sudo dpkg -i mysql-apt-config_0.8.16-1_all.deb
>  # then select mysql 8.0 server
> sudo apt update
> sudo apt install mysql-client mysql-community-server mysql-server
>
> # check
> mysql -u root -p
>
> # create user mysql
>  CREATE USER 'mysql'@'localhost' IDENTIFIED BY 'mysql';
>  alter user 'root'@'localhost' identified by 'mysql';
>
> # modify the conf (should add "skip-grant-tables" under [mysqld])
> sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
>
> # optimize
> # e.g., https://gist.github.com/fevangelou/fb72f36bbe333e059b66
>
> # import DB
> wget https://gts3.org/~jjung/tpcc-perf/tpcc_my.tar.gz
> tar xzvf tpcc_my.tar.gz
> mysql -u mysql -pmysql -e "create database mysqldb"
> mysql -u mysql -pmysql mysqldb < tpcc_my.sql
>
> # test
> mysql -u mysql -pmysql mysqldb -e "show tables"
> mysql -u mysql -pmysql mysqldb -e "select * from customer"
>
> # run query
> mysql -u mysql -pmysql mysqldb < query.sql
> ```
>
>
> # Evaluation environment
>
> * Server: Ubuntu 18.04 (64bit)
> * CockroachDB: v20.2.5
> * PostgreSQL: latest commit (21 Feb, 2021)
> * MySQL: v8.0.23
> * SQLite: latest commit (21 Feb, 2021)
>

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Potential performance issues
@ 2021-03-01 13:05  MichaelDBA <MichaelDBA@sqlexec.com>
  parent: Andrew Dunstan <andrew@dunslane.net>
  0 siblings, 1 reply; 10+ messages in thread

From: MichaelDBA @ 2021-03-01 13:05 UTC (permalink / raw)
  To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Jung, Jinho <jinho.jung@gatech.edu>; pgsql-performance

Ha, Andrew beat me to the punch!

Andrew Dunstan wrote on 3/1/2021 7:59 AM:
> On 2/28/21 10:04 AM, Jung, Jinho wrote:
>> # install DBMS
>> sudo su
>> make install
>> adduser postgres
>> rm -rf /usr/local/pgsql/data
>> mkdir /usr/local/pgsql/data
>> chown -R postgres /usr/local/pgsql/data
>> su - postgres
>> /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
>> /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
>> /usr/local/pgsql/bin/createdb jjung
>
> Using an untuned Postgres is fairly useless for a performance test. Out
> of the box, shared_buffers and work_mem are too low for almost all
> situations, and many other settings can also usually be improved. The
> default settings are deliberately very conservative.
>
>
> cheers
>
>
> andrew
>
>
>
> -- Andrew Dunstan EDB: https://www.enterprisedb.com
>
>






^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Potential performance issues
@ 2021-03-01 13:44  Bob Jolliffe <bobjolliffe@gmail.com>
  parent: MichaelDBA <MichaelDBA@sqlexec.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Bob Jolliffe @ 2021-03-01 13:44 UTC (permalink / raw)
  To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: Andrew Dunstan <andrew@dunslane.net>; Jung, Jinho <jinho.jung@gatech.edu>; pgsql-performance

Was just about to reply similarly.  Mind you it perhaps does raise the
question : are the default postgresql settings perhaps too
conservative or too static.  For example, in the absence of other
explicit configuration, might it make more sense for many use cases
for postgres to assess the physical memory available and make some
half-sensible allocations based on that?  I know there are downsides
to assuming that postgresql has free reign to all that it sees, but
there are clearly also some downsides in assuming it has next to
nothing.  This could also be more correctly part of a package
installation procedure, but just floating the idea ... some kind of
auto-tuning vs ultra-conservative defaults.

On Mon, 1 Mar 2021 at 13:05, MichaelDBA <MichaelDBA@sqlexec.com> wrote:
>
> Ha, Andrew beat me to the punch!
>
> Andrew Dunstan wrote on 3/1/2021 7:59 AM:
> > On 2/28/21 10:04 AM, Jung, Jinho wrote:
> >> # install DBMS
> >> sudo su
> >> make install
> >> adduser postgres
> >> rm -rf /usr/local/pgsql/data
> >> mkdir /usr/local/pgsql/data
> >> chown -R postgres /usr/local/pgsql/data
> >> su - postgres
> >> /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
> >> /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
> >> /usr/local/pgsql/bin/createdb jjung
> >
> > Using an untuned Postgres is fairly useless for a performance test. Out
> > of the box, shared_buffers and work_mem are too low for almost all
> > situations, and many other settings can also usually be improved. The
> > default settings are deliberately very conservative.
> >
> >
> > cheers
> >
> >
> > andrew
> >
> >
> >
> > -- Andrew Dunstan EDB: https://www.enterprisedb.com
> >
> >
>
>
>





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Potential performance issues
@ 2021-03-01 14:41  Jung, Jinho <jinho.jung@gatech.edu>
  parent: MichaelDBA <MichaelDBA@sqlexec.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Jung, Jinho @ 2021-03-01 14:41 UTC (permalink / raw)
  To: pgsql-performance; +Cc: MichaelDBA <MichaelDBA@sqlexec.com>; Andrew Dunstan <andrew@dunslane.net>; Bob Jolliffe <bobjolliffe@gmail.com>

Andrew, Bob, Michael

Thanks for the valuable feedback! Even with the default setting, PostgreSQL mostly showed good performance than other DBMSs. The reported queries are a very tiny portion among all executed queries (e.g., <0.001%).

As you guided, we will make the follow-up report after we test again with the performance-tuned PostgreSQL.

Hope we can contribute to improving PostgreSQL.

Thanks,
Jinho Jung

________________________________
From: MichaelDBA <MichaelDBA@sqlexec.com>
Sent: Monday, March 1, 2021 8:04 AM
To: Jung, Jinho <jinho.jung@gatech.edu>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Potential performance issues

Hi,

It is worthy work trying to compare performance across multiple database vendors, but unfortunately, it does not really come across as comparing apples to apples.

For instance, configuration parameters:  I do not see where you are doing any modification of configuration at all.  Since DBVendors are different in how they apply "out of the box" configuration,  this alone can severely affect your comparison tests even though you are using a standard in benchmark testing, TPCC-C.  Postgres is especially conservative in "out of the box" configuration.  For instance, "work_mem" is set to an incredibly low value of 4MB.  This has a big impact on many types of queries. Oracle has something called SGA_TARGET, which if enabled, self-regulates where the memory is utilized, thus not limiting query memory specifically in the way Postgres does.  This is just one example of a bazillion others where differences in "out of the box" configuration makes these tests more like comparing apples to oranges.  There are many other areas of configuration related to memory, disk, parallel execution, io concurrency, etc.

In sum, when comparing performance across different database vendors, there are many other factors that must be taken into account when trying to do an impartial comparison.  I just showed one: how configuration differences can skew the results.

Regards,
Michael Vitale




Jung, Jinho wrote on 2/28/2021 10:04 AM:
# Performance issues discovered from differential test

Hello. We are studying DBMS from GeorgiaTech and reporting interesting queries that potentially show performance problems.

To discover such cases, we used the following procedures:

* Install four DBMSs with the latest version (PostgreSQL, SQLite, MySQL, CockroachDB)
* Import TPCC-C benchmark for each DBMS
* Generate random query (and translate the query to handle different dialects)
* Run the query and measure the query execution time
   * Remove `LIMIT` to prevent any non-deterministic behaviors
   * Discard the test case if any DBMS returned an error
   * Some DBMS does not show the actual query execution time. In this case, query the `current time` before and after the actual query, and then we calculate the elapsed time.

In this report, we attached a few queries. We believe that there are many duplicated or false-positive cases. It would be great if we can get feedback about the reported queries. Once we know the root cause of the problem or false positive, we will make a follow-up report after we remove them all.

For example, the below query runs x1000 slower than other DBMSs from PostgreSQL.

    select ref_0.ol_amount as c0
    from order_line as ref_0
        left join stock as ref_1
          on (ref_0.ol_o_id = ref_1.s_w_id )
        inner join warehouse as ref_2
        on (ref_1.s_dist_09 is NULL)
    where ref_2.w_tax is NULL;


* Query files link:

wget https://gts3.org/~jjung/report1/pg.tar.gz<https://nam12.safelinks.protection.outlook.com/?url=htt...;

* Execution result (execution time (second))

| Filename | Postgres |   Mysql  | Cockroachdb |  Sqlite  |   Ratio  |
|---------:|---------:|---------:|------------:|---------:|---------:|
|    34065 |  1.31911 |    0.013 |     0.02493 |    1.025 |   101.47 |
|    36399 |  3.60298 |    0.015 |     1.05593 |    3.487 |   240.20 |
|    35767 |  4.01327 |    0.032 |     0.00727 |    2.311 |   552.19 |
|    11132 |   4.3518 |    0.022 |     0.00635 |    3.617 |   684.88 |
|    29658 |   4.6783 |    0.034 |     0.00778 |     2.63 |   601.10 |
|    19522 |  1.06943 |    0.014 |     0.00569 |   0.0009 |  1188.26 |
|    38388 |  3.21383 |    0.013 |     0.00913 |    2.462 |   352.09 |
|     7187 |  1.20267 |    0.015 |     0.00316 |   0.0009 |  1336.30 |
|    24121 |  2.80611 |    0.014 |     0.03083 |    0.005 |   561.21 |
|    25800 |  3.95163 |    0.024 |     0.73027 |    3.876 |   164.65 |
|     2030 |  1.91181 |    0.013 |     0.04123 |    1.634 |   147.06 |
|    17383 |  3.28785 |    0.014 |     0.00611 |      2.4 |   538.45 |
|    19551 |  4.70967 |    0.014 |     0.00329 |   0.0009 |  5232.97 |
|    26595 |  3.70423 |    0.014 |     0.00601 |    2.747 |   615.92 |
|      469 |  4.18906 |    0.013 |     0.12343 |    0.016 |   322.23 |


# Reproduce: install DBMSs, import TPCC benchmark, run query

### Cockroach (from binary)

```sh
# install DBMS
wget https://binaries.cockroachdb.com/cockroach-v20.2.5.linux-amd64.tgz<https://nam12.safelinks.protec...;
tar xzvf cockroach-v20.2.5.linux-amd64.tgz
sudo cp -i cockroach-v20.2.5.linux-amd64/cockroach /usr/local/bin/cockroach20

sudo mkdir -p /usr/local/lib/cockroach
sudo cp -i cockroach-v20.2.5.linux-amd64/lib/libgeos.so /usr/local/lib/cockroach/
sudo cp -i cockroach-v20.2.5.linux-amd64/lib/libgeos_c.so /usr/local/lib/cockroach/

# test
which cockroach20
cockroach20 demo

# start the DBMS (to make initial node files)
cd ~
cockroach20 start-single-node --insecure --store=node20 --listen-addr=localhost:26259 --http-port=28080 --max-sql-memory=1GB --background
# quit
cockroach20 quit --insecure --host=localhost:26259

# import DB
mkdir -p node20/extern
wget https://gts3.org/~jjung/tpcc-perf/tpcc_cr.tar.gz<https://nam12.safelinks.protection.outlook.com/?...;
tar xzvf tpcc_cr.tar.gz
cp tpcc_cr.sql node20/tpcc.sql

# start the DBMS again and createdb
cockroach20 sql --insecure --host=localhost:26259 --execute="CREATE DATABASE IF NOT EXISTS cockroachdb;"
--cockroach20 sql --insecure --host=localhost:26259 --execute="DROP DATABASE cockroachdb;"

cockroach20 sql --insecure --host=localhost:26259 --database=cockroachdb --execute="IMPORT PGDUMP 'nodelocal://self/tpcc.sql';"

# test
cockroach20 sql --insecure --host=localhost:26259 --database=cockroachdb --execute="explain analyze select count(*) from order_line;"

# run query
cockroach20 sql --insecure --host=localhost --port=26259 --database=cockroachdb < query.sql
```


### Postgre (from SRC)

```sh
# remove any previous postgres (if exist)
sudo apt-get --purge remove postgresql postgresql-doc postgresql-common

# build latest postgres
git clone https://github.com/postgres/postgres.git<https://nam12.safelinks.protection.outlook.com/?url=http...;
mkdir bld
cd bld
../configure
make -j 20

# install DBMS
sudo su
make install
adduser postgres
rm -rf /usr/local/pgsql/data
mkdir /usr/local/pgsql/data
chown -R postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
/usr/local/pgsql/bin/createdb jjung
#/usr/local/pgsql/bin/psql postgresdb

/usr/local/pgsql/bin/createuser -s {username}
/usr/local/pgsql/bin/createdb postgresdb
/usr/local/pgsql/bin/psql

=# alter {username} with superuser

# import DB
wget https://gts3.org/~jjung/tpcc-perf/tpcc_pg.tar.gz<https://nam12.safelinks.protection.outlook.com/?...;
tar xzvf tpcc_pg.tar.gz
/usr/local/pgsql/bin/psql -p 5432 -d postgresdb -f tpcc_pg.sql

# test
/usr/local/pgsql/bin/psql -p 5432 -d postgresdb -c "select * from warehouse"
/usr/local/pgsql/bin/psql -p 5432 -d postgresdb -c "\\dt"

# run query
/usr/local/pgsql/bin/psql -p 5432 -d postgresdb -f query.sql
```


### Sqlite (from SRC)

```sh
# uninstall any existing
sudo apt purge sliqte3

# build latest sqlite from src
git clone https://github.com/sqlite/sqlite.git<https://nam12.safelinks.protection.outlook.com/?url=https%3A...;
cd sqlite
mkdir bld
cd bld
../configure
make -j 20

# install DBMS
sudo make install

# import DB
wget https://gts3.org/~jjung/tpcc-perf/tpcc_sq.tar.gz<https://nam12.safelinks.protection.outlook.com/?...;
tar xzvf tpcc_sq.tar.gz

# test
sqlite3 tpcc_sq.db
sqlite> select * from warehouse;

# run query
sqlite3 tpcc_sq.db < query.sql
```


### Mysql (install V8.0.X)

```sh
# remove mysql v5.X (if exist)
sudo apt purge mysql-server mysql-common mysql-client

# install
wget https://dev.mysql.com/get/mysql-apt-config_0.8.16-1_all.deb<https://nam12.safelinks.protection.ou...;
sudo dpkg -i mysql-apt-config_0.8.16-1_all.deb
 # then select mysql 8.0 server
sudo apt update
sudo apt install mysql-client mysql-community-server mysql-server

# check
mysql -u root -p

# create user mysql
 CREATE USER 'mysql'@'localhost' IDENTIFIED BY 'mysql';
 alter user 'root'@'localhost' identified by 'mysql';

# modify the conf (should add "skip-grant-tables" under [mysqld])
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

# optimize
# e.g., https://gist.github.com/fevangelou/fb72f36bbe333e059b66<https://nam12.safelinks.protection.outloo...;

# import DB
wget https://gts3.org/~jjung/tpcc-perf/tpcc_my.tar.gz<https://nam12.safelinks.protection.outlook.com/?...;
tar xzvf tpcc_my.tar.gz
mysql -u mysql -pmysql -e "create database mysqldb"
mysql -u mysql -pmysql mysqldb < tpcc_my.sql

# test
mysql -u mysql -pmysql mysqldb -e "show tables"
mysql -u mysql -pmysql mysqldb -e "select * from customer"

# run query
mysql -u mysql -pmysql mysqldb < query.sql
```


# Evaluation environment

* Server: Ubuntu 18.04 (64bit)
* CockroachDB: v20.2.5
* PostgreSQL: latest commit (21 Feb, 2021)
* MySQL: v8.0.23
* SQLite: latest commit (21 Feb, 2021)

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Potential performance issues
@ 2021-03-01 14:44  Thomas Kellerer <shammat@gmx.net>
  parent: Jung, Jinho <jinho.jung@gatech.edu>
  2 siblings, 0 replies; 10+ messages in thread

From: Thomas Kellerer @ 2021-03-01 14:44 UTC (permalink / raw)
  To: pgsql-performance@lists.postgresql.org

Jung, Jinho schrieb am 28.02.2021 um 16:04:
> # Performance issues discovered from differential test
>
> For example, the below query runs x1000 slower than other DBMSs from PostgreSQL.
>
>     select ref_0.ol_amount as c0
>     from order_line as ref_0
>         left join stock as ref_1
>           on (ref_0.ol_o_id = ref_1.s_w_id )
>         inner join warehouse as ref_2
>         on (ref_1.s_dist_09 is NULL)
>     where ref_2.w_tax is NULL;

I find this query extremely weird to be honest.

There is no join condition between warehouse and the other two tables which results in a cross join.
Which is "reduced" somehow by applying the IS NULL conditions - but still, to me this makes no sense.

Maybe the Postgres optimizer doesn't handle this ugly "join condition" the same way the others do.

I would rather expect a NOT EXISTS against the warehouse table.

Thomas





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Potential performance issues
@ 2021-03-01 14:53  Rick Otten <rottenwindfish@gmail.com>
  parent: Bob Jolliffe <bobjolliffe@gmail.com>
  0 siblings, 0 replies; 10+ messages in thread

From: Rick Otten @ 2021-03-01 14:53 UTC (permalink / raw)
  To: pgsql-performance

On Mon, Mar 1, 2021 at 8:44 AM Bob Jolliffe <bobjolliffe@gmail.com> wrote:

> Was just about to reply similarly.  Mind you it perhaps does raise the
> question : are the default postgresql settings perhaps too
> conservative or too static.  For example, in the absence of other
> explicit configuration, might it make more sense for many use cases
> for postgres to assess the physical memory available and make some
> half-sensible allocations based on that?  I know there are downsides
> to assuming that postgresql has free reign to all that it sees, but
> there are clearly also some downsides in assuming it has next to
> nothing.  This could also be more correctly part of a package
> installation procedure, but just floating the idea ... some kind of
> auto-tuning vs ultra-conservative defaults.
>
>
When you spin up an Aurora or RDS instance in AWS, their default parameter
group values are mostly set by formulas which derive values based on the
instance size.  Of course they can assume free reign of the entire system,
but the values they choose are still somewhat interesting.

For example, they set `maintenance_work_mem` like this:
"GREATEST({DBInstanceClassMemory/63963136*1024},65536)"

It doesn't completely remove the need for a human to optimize the parameter
group based on your use case, but it does seem to give you a better novice
starting point to work from.  And there are definitely some formulas that I
disagree with in the general case.  However it is something that is
adaptable for those times when you bump up the server size, but don't want
to have to revisit and update every parameter to support the change.

I've been thinking a lot about running PG in containers for dev
environments lately, and trying to tune to get reasonable dev performance
out of a container without crushing the other services and containers on
the laptop.  Most developers that I've worked with over the past few years
only have exposure to running PG in a container.  They've simply never run
it on a server or even barebones on their laptop.  I think any modern
approach to a default set of tuning parameters would probably also need to
be "container aware", which is for all practical purposes the new default
"minimal configuration" on multi-purpose systems.

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Potential performance issues
@ 2021-03-01 15:06  Pavel Stehule <pavel.stehule@gmail.com>
  parent: Jung, Jinho <jinho.jung@gatech.edu>
  0 siblings, 1 reply; 10+ messages in thread

From: Pavel Stehule @ 2021-03-01 15:06 UTC (permalink / raw)
  To: Jung, Jinho <jinho.jung@gatech.edu>; +Cc: pgsql-performance; MichaelDBA <MichaelDBA@sqlexec.com>; Andrew Dunstan <andrew@dunslane.net>; Bob Jolliffe <bobjolliffe@gmail.com>

Hi

po 1. 3. 2021 v 15:59 odesílatel Jung, Jinho <jinho.jung@gatech.edu> napsal:

> Andrew, Bob, Michael
>
> Thanks for the valuable feedback! Even with the default setting,
> PostgreSQL mostly showed good performance than other DBMSs. The reported
> queries are a very tiny portion among all executed queries (e.g., <0.001%).
>
>
> As you guided, we will make the follow-up report after we test again with
> the performance-tuned PostgreSQL.
>
> Hope we can contribute to improving PostgreSQL.
>

Important thing - assign execution plan of slow query

https://explain.depesz.com/

https://wiki.postgresql.org/wiki/Slow_Query_Questions

Regards

Pavel


> Thanks,
> Jinho Jung
>
> ------------------------------
> *From:* MichaelDBA <MichaelDBA@sqlexec.com>
> *Sent:* Monday, March 1, 2021 8:04 AM
> *To:* Jung, Jinho <jinho.jung@gatech.edu>
> *Cc:* pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
> *Subject:* Re: Potential performance issues
>
> Hi,
>
> It is worthy work trying to compare performance across multiple database
> vendors, but unfortunately, it does not really come across as comparing
> apples to apples.
>
> For instance, configuration parameters:  I do not see where you are doing
> any modification of configuration at all.  Since DBVendors are different in
> how they apply "out of the box" configuration,  this alone can severely
> affect your comparison tests even though you are using a standard in
> benchmark testing, TPCC-C.  Postgres is especially conservative in "out of
> the box" configuration.  For instance, "work_mem" is set to an incredibly
> low value of 4MB.  This has a big impact on many types of queries. Oracle
> has something called SGA_TARGET, which if enabled, self-regulates where the
> memory is utilized, thus not limiting query memory specifically in the way
> Postgres does.  This is just one example of a bazillion others where
> differences in "out of the box" configuration makes these tests more like
> comparing apples to oranges.  There are many other areas of configuration
> related to memory, disk, parallel execution, io concurrency, etc.
>
> In sum, when comparing performance across different database vendors,
> there are many other factors that must be taken into account when trying to
> do an impartial comparison.  I just showed one: how configuration
> differences can skew the results.
>
> Regards,
> Michael Vitale
>
>
>
>
> Jung, Jinho wrote on 2/28/2021 10:04 AM:
>
> # Performance issues discovered from differential test
>
> Hello. We are studying DBMS from GeorgiaTech and reporting interesting
> queries that potentially show performance problems.
>
> To discover such cases, we used the following procedures:
>
> * Install four DBMSs with the latest version (PostgreSQL, SQLite, MySQL,
> CockroachDB)
> * Import TPCC-C benchmark for each DBMS
> * Generate random query (and translate the query to handle different
> dialects)
> * Run the query and measure the query execution time
>    * Remove `LIMIT` to prevent any non-deterministic behaviors
>    * Discard the test case if any DBMS returned an error
>    * Some DBMS does not show the actual query execution time. In this
> case, query the `current time` before and after the actual query, and then
> we calculate the elapsed time.
>
> In this report, we attached a few queries. We believe that there are many
> duplicated or false-positive cases. It would be great if we can get
> feedback about the reported queries. Once we know the root cause of the
> problem or false positive, we will make a follow-up report after we remove
> them all.
>
> For example, the below query runs x1000 slower than other DBMSs from
> PostgreSQL.
>
>     select ref_0.ol_amount as c0
>     from order_line as ref_0
>         left join stock as ref_1
>           on (ref_0.ol_o_id = ref_1.s_w_id )
>         inner join warehouse as ref_2
>         on (ref_1.s_dist_09 is NULL)
>     where ref_2.w_tax is NULL;
>
>
> * Query files link:
>
> wget https://gts3.org/~jjung/report1/pg.tar.gz
> <https://nam12.safelinks.protection.outlook.com/?url=https:%2F%2Fgts3.org%2F~jjung%2Freport1%2Fpg.tar...;
>
> * Execution result (execution time (second))
>
> | Filename | Postgres |   Mysql  | Cockroachdb |  Sqlite  |   Ratio  |
> |---------:|---------:|---------:|------------:|---------:|---------:|
> |    34065 |  1.31911 |    0.013 |     0.02493 |    1.025 |   101.47 |
> |    36399 |  3.60298 |    0.015 |     1.05593 |    3.487 |   240.20 |
> |    35767 |  4.01327 |    0.032 |     0.00727 |    2.311 |   552.19 |
> |    11132 |   4.3518 |    0.022 |     0.00635 |    3.617 |   684.88 |
> |    29658 |   4.6783 |    0.034 |     0.00778 |     2.63 |   601.10 |
> |    19522 |  1.06943 |    0.014 |     0.00569 |   0.0009 |  1188.26 |
> |    38388 |  3.21383 |    0.013 |     0.00913 |    2.462 |   352.09 |
> |     7187 |  1.20267 |    0.015 |     0.00316 |   0.0009 |  1336.30 |
> |    24121 |  2.80611 |    0.014 |     0.03083 |    0.005 |   561.21 |
> |    25800 |  3.95163 |    0.024 |     0.73027 |    3.876 |   164.65 |
> |     2030 |  1.91181 |    0.013 |     0.04123 |    1.634 |   147.06 |
> |    17383 |  3.28785 |    0.014 |     0.00611 |      2.4 |   538.45 |
> |    19551 |  4.70967 |    0.014 |     0.00329 |   0.0009 |  5232.97 |
> |    26595 |  3.70423 |    0.014 |     0.00601 |    2.747 |   615.92 |
> |      469 |  4.18906 |    0.013 |     0.12343 |    0.016 |   322.23 |
>
>
> # Reproduce: install DBMSs, import TPCC benchmark, run query
>
> ### Cockroach (from binary)
>
> ```sh
> # install DBMS
> wget https://binaries.cockroachdb.com/cockroach-v20.2.5.linux-amd64.tgz
> <https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fbinaries.cockroachdb.com%2Fcockroa...;
> tar xzvf cockroach-v20.2.5.linux-amd64.tgz
> sudo cp -i cockroach-v20.2.5.linux-amd64/cockroach
> /usr/local/bin/cockroach20
>
> sudo mkdir -p /usr/local/lib/cockroach
> sudo cp -i cockroach-v20.2.5.linux-amd64/lib/libgeos.so
> /usr/local/lib/cockroach/
> sudo cp -i cockroach-v20.2.5.linux-amd64/lib/libgeos_c.so
> /usr/local/lib/cockroach/
>
> # test
> which cockroach20
> cockroach20 demo
>
> # start the DBMS (to make initial node files)
> cd ~
> cockroach20 start-single-node --insecure --store=node20
> --listen-addr=localhost:26259 --http-port=28080 --max-sql-memory=1GB
> --background
> # quit
> cockroach20 quit --insecure --host=localhost:26259
>
> # import DB
> mkdir -p node20/extern
> wget https://gts3.org/~jjung/tpcc-perf/tpcc_cr.tar.gz
> <https://nam12.safelinks.protection.outlook.com/?url=https:%2F%2Fgts3.org%2F~jjung%2Ftpcc-perf%2Ftpcc...;
> tar xzvf tpcc_cr.tar.gz
> cp tpcc_cr.sql node20/tpcc.sql
>
> # start the DBMS again and createdb
> cockroach20 sql --insecure --host=localhost:26259 --execute="CREATE
> DATABASE IF NOT EXISTS cockroachdb;"
> --cockroach20 sql --insecure --host=localhost:26259 --execute="DROP
> DATABASE cockroachdb;"
>
> cockroach20 sql --insecure --host=localhost:26259 --database=cockroachdb
> --execute="IMPORT PGDUMP 'nodelocal://self/tpcc.sql';"
>
> # test
> cockroach20 sql --insecure --host=localhost:26259 --database=cockroachdb
> --execute="explain analyze select count(*) from order_line;"
>
> # run query
> cockroach20 sql --insecure --host=localhost --port=26259
> --database=cockroachdb < query.sql
> ```
>
>
> ### Postgre (from SRC)
>
> ```sh
> # remove any previous postgres (if exist)
> sudo apt-get --purge remove postgresql postgresql-doc postgresql-common
>
> # build latest postgres
> git clone https://github.com/postgres/postgres.git
> <https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fpostgres%2Fpostgres.g...;
> mkdir bld
> cd bld
> ../configure
> make -j 20
>
> # install DBMS
> sudo su
> make install
> adduser postgres
> rm -rf /usr/local/pgsql/data
> mkdir /usr/local/pgsql/data
> chown -R postgres /usr/local/pgsql/data
> su - postgres
> /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
> /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
> /usr/local/pgsql/bin/createdb jjung
> #/usr/local/pgsql/bin/psql postgresdb
>
> /usr/local/pgsql/bin/createuser -s {username}
> /usr/local/pgsql/bin/createdb postgresdb
> /usr/local/pgsql/bin/psql
>
> =# alter {username} with superuser
>
> # import DB
> wget https://gts3.org/~jjung/tpcc-perf/tpcc_pg.tar.gz
> <https://nam12.safelinks.protection.outlook.com/?url=https:%2F%2Fgts3.org%2F~jjung%2Ftpcc-perf%2Ftpcc...;
> tar xzvf tpcc_pg.tar.gz
> /usr/local/pgsql/bin/psql -p 5432 -d postgresdb -f tpcc_pg.sql
>
> # test
> /usr/local/pgsql/bin/psql -p 5432 -d postgresdb -c "select * from
> warehouse"
> /usr/local/pgsql/bin/psql -p 5432 -d postgresdb -c "\\dt"
>
> # run query
> /usr/local/pgsql/bin/psql -p 5432 -d postgresdb -f query.sql
> ```
>
>
> ### Sqlite (from SRC)
>
> ```sh
> # uninstall any existing
> sudo apt purge sliqte3
>
> # build latest sqlite from src
> git clone https://github.com/sqlite/sqlite.git
> <https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fsqlite%2Fsqlite.git&a...;
> cd sqlite
> mkdir bld
> cd bld
> ../configure
> make -j 20
>
> # install DBMS
> sudo make install
>
> # import DB
> wget https://gts3.org/~jjung/tpcc-perf/tpcc_sq.tar.gz
> <https://nam12.safelinks.protection.outlook.com/?url=https:%2F%2Fgts3.org%2F~jjung%2Ftpcc-perf%2Ftpcc...;
> tar xzvf tpcc_sq.tar.gz
>
> # test
> sqlite3 tpcc_sq.db
> sqlite> select * from warehouse;
>
> # run query
> sqlite3 tpcc_sq.db < query.sql
> ```
>
>
> ### Mysql (install V8.0.X)
>
> ```sh
> # remove mysql v5.X (if exist)
> sudo apt purge mysql-server mysql-common mysql-client
>
> # install
> wget https://dev.mysql.com/get/mysql-apt-config_0.8.16-1_all.deb
> <https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdev.mysql.com%2Fget%2Fmysql-apt-co...;
> sudo dpkg -i mysql-apt-config_0.8.16-1_all.deb
>  # then select mysql 8.0 server
> sudo apt update
> sudo apt install mysql-client mysql-community-server mysql-server
>
> # check
> mysql -u root -p
>
> # create user mysql
>  CREATE USER 'mysql'@'localhost' IDENTIFIED BY 'mysql';
>  alter user 'root'@'localhost' identified by 'mysql';
>
> # modify the conf (should add "skip-grant-tables" under [mysqld])
> sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
>
> # optimize
> # e.g., https://gist.github.com/fevangelou/fb72f36bbe333e059b66
> <https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgist.github.com%2Ffevangelou%2Ffb7...;
>
> # import DB
> wget https://gts3.org/~jjung/tpcc-perf/tpcc_my.tar.gz
> <https://nam12.safelinks.protection.outlook.com/?url=https:%2F%2Fgts3.org%2F~jjung%2Ftpcc-perf%2Ftpcc...;
> tar xzvf tpcc_my.tar.gz
> mysql -u mysql -pmysql -e "create database mysqldb"
> mysql -u mysql -pmysql mysqldb < tpcc_my.sql
>
> # test
> mysql -u mysql -pmysql mysqldb -e "show tables"
> mysql -u mysql -pmysql mysqldb -e "select * from customer"
>
> # run query
> mysql -u mysql -pmysql mysqldb < query.sql
> ```
>
>
> # Evaluation environment
>
> * Server: Ubuntu 18.04 (64bit)
> * CockroachDB: v20.2.5
> * PostgreSQL: latest commit (21 Feb, 2021)
> * MySQL: v8.0.23
> * SQLite: latest commit (21 Feb, 2021)
>
>
>

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Potential performance issues
@ 2021-03-01 16:58  Hannu Krosing <hannuk@google.com>
  parent: Pavel Stehule <pavel.stehule@gmail.com>
  0 siblings, 0 replies; 10+ messages in thread

From: Hannu Krosing @ 2021-03-01 16:58 UTC (permalink / raw)
  To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Jung, Jinho <jinho.jung@gatech.edu>; pgsql-performance; MichaelDBA <MichaelDBA@sqlexec.com>; Andrew Dunstan <andrew@dunslane.net>; Bob Jolliffe <bobjolliffe@gmail.com>

...

* Remove `LIMIT` to prevent any non-deterministic behaviors

This seems counterproductive, as for example PostgreSQL has special
handling of "fast start" queries which is triggered by presence of
LIMIT or OFFSET, so this will miss some optimisations.

Also,it is not like removing LIMIT is some magic bullet which
guarantees there are not non-deterministic behaviors  - cost-based
optimisers can see lots of plan changes due to many things, like when
analyse and/or  vacuum was run last time, what is and is not in shared
buffers, how much of table fits in disk cache, and which parts etc.

Cheers
Hannu




On Mon, Mar 1, 2021 at 4:07 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
> Hi
>
> po 1. 3. 2021 v 15:59 odesílatel Jung, Jinho <jinho.jung@gatech.edu> napsal:
>>
>> Andrew, Bob, Michael
>>
>> Thanks for the valuable feedback! Even with the default setting, PostgreSQL mostly showed good performance than other DBMSs. The reported queries are a very tiny portion among all executed queries (e.g., <0.001%).
>>
>> As you guided, we will make the follow-up report after we test again with the performance-tuned PostgreSQL.
>>
>> Hope we can contribute to improving PostgreSQL.
>
>
> Important thing - assign execution plan of slow query
>
> https://explain.depesz.com/
>
> https://wiki.postgresql.org/wiki/Slow_Query_Questions
>
> Regards
>
> Pavel
>
>>
>> Thanks,
>> Jinho Jung
>>
>> ________________________________
>> From: MichaelDBA <MichaelDBA@sqlexec.com>
>> Sent: Monday, March 1, 2021 8:04 AM
>> To: Jung, Jinho <jinho.jung@gatech.edu>
>> Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
>> Subject: Re: Potential performance issues
>>
>> Hi,
>>
>> It is worthy work trying to compare performance across multiple database vendors, but unfortunately, it does not really come across as comparing apples to apples.
>>
>> For instance, configuration parameters:  I do not see where you are doing any modification of configuration at all.  Since DBVendors are different in how they apply "out of the box" configuration,  this alone can severely affect your comparison tests even though you are using a standard in benchmark testing, TPCC-C.  Postgres is especially conservative in "out of the box" configuration.  For instance, "work_mem" is set to an incredibly low value of 4MB.  This has a big impact on many types of queries. Oracle has something called SGA_TARGET, which if enabled, self-regulates where the memory is utilized, thus not limiting query memory specifically in the way Postgres does.  This is just one example of a bazillion others where differences in "out of the box" configuration makes these tests more like comparing apples to oranges.  There are many other areas of configuration related to memory, disk, parallel execution, io concurrency, etc.
>>
>> In sum, when comparing performance across different database vendors, there are many other factors that must be taken into account when trying to do an impartial comparison.  I just showed one: how configuration differences can skew the results.
>>
>> Regards,
>> Michael Vitale
>>
>>
>>
>>
>> Jung, Jinho wrote on 2/28/2021 10:04 AM:
>>
>> # Performance issues discovered from differential test
>>
>> Hello. We are studying DBMS from GeorgiaTech and reporting interesting queries that potentially show performance problems.
>>
>> To discover such cases, we used the following procedures:
>>
>> * Install four DBMSs with the latest version (PostgreSQL, SQLite, MySQL, CockroachDB)
>> * Import TPCC-C benchmark for each DBMS
>> * Generate random query (and translate the query to handle different dialects)
>> * Run the query and measure the query execution time
>>    * Remove `LIMIT` to prevent any non-deterministic behaviors
>>    * Discard the test case if any DBMS returned an error
>>    * Some DBMS does not show the actual query execution time. In this case, query the `current time` before and after the actual query, and then we calculate the elapsed time.
>>
>> In this report, we attached a few queries. We believe that there are many duplicated or false-positive cases. It would be great if we can get feedback about the reported queries. Once we know the root cause of the problem or false positive, we will make a follow-up report after we remove them all.
>>
>> For example, the below query runs x1000 slower than other DBMSs from PostgreSQL.
>>
>>     select ref_0.ol_amount as c0
>>     from order_line as ref_0
>>         left join stock as ref_1
>>           on (ref_0.ol_o_id = ref_1.s_w_id )
>>         inner join warehouse as ref_2
>>         on (ref_1.s_dist_09 is NULL)
>>     where ref_2.w_tax is NULL;
>>
>>
>> * Query files link:
>>
>> wget https://gts3.org/~jjung/report1/pg.tar.gz
>>
>> * Execution result (execution time (second))
>>
>> | Filename | Postgres |   Mysql  | Cockroachdb |  Sqlite  |   Ratio  |
>> |---------:|---------:|---------:|------------:|---------:|---------:|
>> |    34065 |  1.31911 |    0.013 |     0.02493 |    1.025 |   101.47 |
>> |    36399 |  3.60298 |    0.015 |     1.05593 |    3.487 |   240.20 |
>> |    35767 |  4.01327 |    0.032 |     0.00727 |    2.311 |   552.19 |
>> |    11132 |   4.3518 |    0.022 |     0.00635 |    3.617 |   684.88 |
>> |    29658 |   4.6783 |    0.034 |     0.00778 |     2.63 |   601.10 |
>> |    19522 |  1.06943 |    0.014 |     0.00569 |   0.0009 |  1188.26 |
>> |    38388 |  3.21383 |    0.013 |     0.00913 |    2.462 |   352.09 |
>> |     7187 |  1.20267 |    0.015 |     0.00316 |   0.0009 |  1336.30 |
>> |    24121 |  2.80611 |    0.014 |     0.03083 |    0.005 |   561.21 |
>> |    25800 |  3.95163 |    0.024 |     0.73027 |    3.876 |   164.65 |
>> |     2030 |  1.91181 |    0.013 |     0.04123 |    1.634 |   147.06 |
>> |    17383 |  3.28785 |    0.014 |     0.00611 |      2.4 |   538.45 |
>> |    19551 |  4.70967 |    0.014 |     0.00329 |   0.0009 |  5232.97 |
>> |    26595 |  3.70423 |    0.014 |     0.00601 |    2.747 |   615.92 |
>> |      469 |  4.18906 |    0.013 |     0.12343 |    0.016 |   322.23 |
>>
>>
>> # Reproduce: install DBMSs, import TPCC benchmark, run query
>>
>> ### Cockroach (from binary)
>>
>> ```sh
>> # install DBMS
>> wget https://binaries.cockroachdb.com/cockroach-v20.2.5.linux-amd64.tgz
>> tar xzvf cockroach-v20.2.5.linux-amd64.tgz
>> sudo cp -i cockroach-v20.2.5.linux-amd64/cockroach /usr/local/bin/cockroach20
>>
>> sudo mkdir -p /usr/local/lib/cockroach
>> sudo cp -i cockroach-v20.2.5.linux-amd64/lib/libgeos.so /usr/local/lib/cockroach/
>> sudo cp -i cockroach-v20.2.5.linux-amd64/lib/libgeos_c.so /usr/local/lib/cockroach/
>>
>> # test
>> which cockroach20
>> cockroach20 demo
>>
>> # start the DBMS (to make initial node files)
>> cd ~
>> cockroach20 start-single-node --insecure --store=node20 --listen-addr=localhost:26259 --http-port=28080 --max-sql-memory=1GB --background
>> # quit
>> cockroach20 quit --insecure --host=localhost:26259
>>
>> # import DB
>> mkdir -p node20/extern
>> wget https://gts3.org/~jjung/tpcc-perf/tpcc_cr.tar.gz
>> tar xzvf tpcc_cr.tar.gz
>> cp tpcc_cr.sql node20/tpcc.sql
>>
>> # start the DBMS again and createdb
>> cockroach20 sql --insecure --host=localhost:26259 --execute="CREATE DATABASE IF NOT EXISTS cockroachdb;"
>> --cockroach20 sql --insecure --host=localhost:26259 --execute="DROP DATABASE cockroachdb;"
>>
>> cockroach20 sql --insecure --host=localhost:26259 --database=cockroachdb --execute="IMPORT PGDUMP 'nodelocal://self/tpcc.sql';"
>>
>> # test
>> cockroach20 sql --insecure --host=localhost:26259 --database=cockroachdb --execute="explain analyze select count(*) from order_line;"
>>
>> # run query
>> cockroach20 sql --insecure --host=localhost --port=26259 --database=cockroachdb < query.sql
>> ```
>>
>>
>> ### Postgre (from SRC)
>>
>> ```sh
>> # remove any previous postgres (if exist)
>> sudo apt-get --purge remove postgresql postgresql-doc postgresql-common
>>
>> # build latest postgres
>> git clone https://github.com/postgres/postgres.git
>> mkdir bld
>> cd bld
>> ../configure
>> make -j 20
>>
>> # install DBMS
>> sudo su
>> make install
>> adduser postgres
>> rm -rf /usr/local/pgsql/data
>> mkdir /usr/local/pgsql/data
>> chown -R postgres /usr/local/pgsql/data
>> su - postgres
>> /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
>> /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
>> /usr/local/pgsql/bin/createdb jjung
>> #/usr/local/pgsql/bin/psql postgresdb
>>
>> /usr/local/pgsql/bin/createuser -s {username}
>> /usr/local/pgsql/bin/createdb postgresdb
>> /usr/local/pgsql/bin/psql
>>
>> =# alter {username} with superuser
>>
>> # import DB
>> wget https://gts3.org/~jjung/tpcc-perf/tpcc_pg.tar.gz
>> tar xzvf tpcc_pg.tar.gz
>> /usr/local/pgsql/bin/psql -p 5432 -d postgresdb -f tpcc_pg.sql
>>
>> # test
>> /usr/local/pgsql/bin/psql -p 5432 -d postgresdb -c "select * from warehouse"
>> /usr/local/pgsql/bin/psql -p 5432 -d postgresdb -c "\\dt"
>>
>> # run query
>> /usr/local/pgsql/bin/psql -p 5432 -d postgresdb -f query.sql
>> ```
>>
>>
>> ### Sqlite (from SRC)
>>
>> ```sh
>> # uninstall any existing
>> sudo apt purge sliqte3
>>
>> # build latest sqlite from src
>> git clone https://github.com/sqlite/sqlite.git
>> cd sqlite
>> mkdir bld
>> cd bld
>> ../configure
>> make -j 20
>>
>> # install DBMS
>> sudo make install
>>
>> # import DB
>> wget https://gts3.org/~jjung/tpcc-perf/tpcc_sq.tar.gz
>> tar xzvf tpcc_sq.tar.gz
>>
>> # test
>> sqlite3 tpcc_sq.db
>> sqlite> select * from warehouse;
>>
>> # run query
>> sqlite3 tpcc_sq.db < query.sql
>> ```
>>
>>
>> ### Mysql (install V8.0.X)
>>
>> ```sh
>> # remove mysql v5.X (if exist)
>> sudo apt purge mysql-server mysql-common mysql-client
>>
>> # install
>> wget https://dev.mysql.com/get/mysql-apt-config_0.8.16-1_all.deb
>> sudo dpkg -i mysql-apt-config_0.8.16-1_all.deb
>>  # then select mysql 8.0 server
>> sudo apt update
>> sudo apt install mysql-client mysql-community-server mysql-server
>>
>> # check
>> mysql -u root -p
>>
>> # create user mysql
>>  CREATE USER 'mysql'@'localhost' IDENTIFIED BY 'mysql';
>>  alter user 'root'@'localhost' identified by 'mysql';
>>
>> # modify the conf (should add "skip-grant-tables" under [mysqld])
>> sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
>>
>> # optimize
>> # e.g., https://gist.github.com/fevangelou/fb72f36bbe333e059b66
>>
>> # import DB
>> wget https://gts3.org/~jjung/tpcc-perf/tpcc_my.tar.gz
>> tar xzvf tpcc_my.tar.gz
>> mysql -u mysql -pmysql -e "create database mysqldb"
>> mysql -u mysql -pmysql mysqldb < tpcc_my.sql
>>
>> # test
>> mysql -u mysql -pmysql mysqldb -e "show tables"
>> mysql -u mysql -pmysql mysqldb -e "select * from customer"
>>
>> # run query
>> mysql -u mysql -pmysql mysqldb < query.sql
>> ```
>>
>>
>> # Evaluation environment
>>
>> * Server: Ubuntu 18.04 (64bit)
>> * CockroachDB: v20.2.5
>> * PostgreSQL: latest commit (21 Feb, 2021)
>> * MySQL: v8.0.23
>> * SQLite: latest commit (21 Feb, 2021)
>>
>>





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

end of thread, other threads:[~2021-03-01 16:58 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-02-28 15:04 Potential performance issues Jung, Jinho <jinho.jung@gatech.edu>
2021-03-01 12:59 ` Andrew Dunstan <andrew@dunslane.net>
2021-03-01 13:05   ` MichaelDBA <MichaelDBA@sqlexec.com>
2021-03-01 13:44     ` Bob Jolliffe <bobjolliffe@gmail.com>
2021-03-01 14:53       ` Rick Otten <rottenwindfish@gmail.com>
2021-03-01 13:04 ` MichaelDBA <MichaelDBA@sqlexec.com>
2021-03-01 14:41   ` Jung, Jinho <jinho.jung@gatech.edu>
2021-03-01 15:06     ` Pavel Stehule <pavel.stehule@gmail.com>
2021-03-01 16:58       ` Hannu Krosing <hannuk@google.com>
2021-03-01 14:44 ` Thomas Kellerer <shammat@gmx.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox