Thread

1. Potential performance issues

   Jung, Jinho <jinho.jung@gatech.edu> — 2021-02-28T15:04:33Z

   # 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)
   
   

2. Re: Potential performance issues

   Andrew Dunstan <andrew@dunslane.net> — 2021-03-01T12:59:10Z

   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
   
   
   
   

3. Re: Potential performance issues

   MichaelDBA <michaeldba@sqlexec.com> — 2021-03-01T13:04:19Z

   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)
   >
   
   

4. Re: Potential performance issues

   MichaelDBA <michaeldba@sqlexec.com> — 2021-03-01T13:05:39Z

   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
   >
   >
   
   
   
   
   

5. Re: Potential performance issues

   Bob Jolliffe <bobjolliffe@gmail.com> — 2021-03-01T13:44:38Z

   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
   > >
   > >
   >
   >
   >
   
   
   
   

6. Re: Potential performance issues

   Jung, Jinho <jinho.jung@gatech.edu> — 2021-03-01T14:41:22Z

   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=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)
   
   
   

7. Re: Potential performance issues

   shammat@gmx.net — 2021-03-01T14:44:02Z

   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
   
   
   
   

8. Re: Potential performance issues

   Rick Otten <rottenwindfish@gmail.com> — 2021-03-01T14:53:49Z

   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.
   

9. Re: Potential performance issues

   Pavel Stehule <pavel.stehule@gmail.com> — 2021-03-01T15:06:16Z

   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.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)
   >
   >
   >
   

10. Re: Potential performance issues

    Hannu Krosing <hannuk@google.com> — 2021-03-01T16:58:06Z

    ...
    
    * 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)
    >>
    >>