Re: Potential performance issues

Hi

po 1. 3. 2021 v 15:59 odesílatel Jung, Jinho <jinho(dot)jung(at)gatech(dot)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(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)
>
>
>