Hi po 1. 3. 2021 v 15:59 odesÃlatel Jung, Jinho <jinho.j...@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 <michael...@sqlexec.com> > *Sent:* Monday, March 1, 2021 8:04 AM > *To:* Jung, Jinho <jinho.j...@gatech.edu> > *Cc:* pgsql-performa...@postgresql.org <pgsql-performa...@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) > > >
