Re: Potential performance issues

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(at)sqlexec(dot)com>
Sent: Monday, March 1, 2021 8:04 AM
To: Jung, Jinho <jinho(dot)jung(at)gatech(dot)edu>
Cc: pgsql-performance(at)postgresql(dot)org <pgsql-performance(at)postgresql(dot)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.gz&data=04%7C01%7Cjinho.jung%40gatech.edu%7C09089041b2a04ee4830008d8dcb28a18%7C482198bbae7b4b258b7a6d7f32faa083%7C0%7C0%7C637502008195574204%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=Kk83y66NUIuc%2BQbB2xXaxxb64kQbiphE60Wqudmfkus%3D&reserved=0>

* 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%2Fcockroach-v20.2.5.linux-amd64.tgz&data=04%7C01%7Cjinho.jung%40gatech.edu%7C09089041b2a04ee4830008d8dcb28a18%7C482198bbae7b4b258b7a6d7f32faa083%7C0%7C0%7C637502008195574204%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=yRiMQP9tuhmMg6QCeYMHCoLvSARheHptOSHUhMZLo2Y%3D&reserved=0>
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_cr.tar.gz&data=04%7C01%7Cjinho.jung%40gatech.edu%7C09089041b2a04ee4830008d8dcb28a18%7C482198bbae7b4b258b7a6d7f32faa083%7C0%7C0%7C637502008195584197%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=9OQRA5Zt8DCBk6t4Sn4NBRFFDDY5W2R9yKhbOJJ9s9o%3D&reserved=0>
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.git&data=04%7C01%7Cjinho.jung%40gatech.edu%7C09089041b2a04ee4830008d8dcb28a18%7C482198bbae7b4b258b7a6d7f32faa083%7C0%7C0%7C637502008195594191%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=PIb%2BUGT9Fu1CvkbxpJscUj5qapTPFNQpUtKWDVfQXPE%3D&reserved=0>
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_pg.tar.gz&data=04%7C01%7Cjinho.jung%40gatech.edu%7C09089041b2a04ee4830008d8dcb28a18%7C482198bbae7b4b258b7a6d7f32faa083%7C0%7C0%7C637502008195594191%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=kDWbBCvTt2lzWTsdsIZrJvWsUCZUQSVS0OErqCTceVA%3D&reserved=0>
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&data=04%7C01%7Cjinho.jung%40gatech.edu%7C09089041b2a04ee4830008d8dcb28a18%7C482198bbae7b4b258b7a6d7f32faa083%7C0%7C0%7C637502008195604185%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=i7uMgx6QTVX0LjQ61m4kJPnJbW6cFDZcmz5x0hJC9Hk%3D&reserved=0>
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_sq.tar.gz&data=04%7C01%7Cjinho.jung%40gatech.edu%7C09089041b2a04ee4830008d8dcb28a18%7C482198bbae7b4b258b7a6d7f32faa083%7C0%7C0%7C637502008195604185%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=vC8vdNyyekSFkbsUKFn9PkIZHZ9nOudUFBSBlWYe5kw%3D&reserved=0>
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-config_0.8.16-1_all.deb&data=04%7C01%7Cjinho.jung%40gatech.edu%7C09089041b2a04ee4830008d8dcb28a18%7C482198bbae7b4b258b7a6d7f32faa083%7C0%7C0%7C637502008195614177%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=O65HyWp3z%2Bjh0g5eXX7SSEnzpM1Q6YRbFofoDsBb%2BQ4%3D&reserved=0>
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%2Ffb72f36bbe333e059b66&data=04%7C01%7Cjinho.jung%40gatech.edu%7C09089041b2a04ee4830008d8dcb28a18%7C482198bbae7b4b258b7a6d7f32faa083%7C0%7C0%7C637502008195624175%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=sM2dgn%2BMZB4J37OWV7rt%2Bxvr1kSUhMCEjk3AEf2%2BOcg%3D&reserved=0>

# 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_my.tar.gz&data=04%7C01%7Cjinho.jung%40gatech.edu%7C09089041b2a04ee4830008d8dcb28a18%7C482198bbae7b4b258b7a6d7f32faa083%7C0%7C0%7C637502008195624175%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=vUUh%2Fxe130fW9zw61uXK%2B9a8aXZi%2F0xx9Mfp47mXsNg%3D&reserved=0>
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)