Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.5.9
-
* Server: Ubuntu 18.04 (X86 arch, 64bit)
* CockroachDB: v20.2.5 (latest version)
* PostgreSQL: latest commit (21 Feb, 2021)
* SQLite: latest commit (21 Feb, 2021)
* MariaDB: v10.5.9
Description
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, MariaDB, 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.
For example, the query for the test looks like this. This query shows x3000 slower execution than other DBMS in MariaDB.
9132.sql |
select subq_0.c2 as c0
|
from
|
(select
|
ref_1.ol_supply_w_id as c0,
|
32 as c1,
|
ref_1.ol_amount as c2
|
from
|
stock as ref_0
|
left join order_line as ref_1
|
on (ref_0.s_dist_03 = ref_1.ol_dist_info )
|
where ref_1.ol_w_id is not NULL
|
) as subq_0
|
where subq_0.c2 is NULL;
|
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.
Execution result (execution time (second))
Filename | Postgres | Maria | Cockroachdb | Sqlite | Ratio |
9132 | 0.02875 | 3.616 | 0.23545 | 0.001 | 3616.26 |
6219 | 0.68859 | 3.899 | 0.90454 | 0.55 | 7.09 |
29198 | 0.01907 | 2.107 | 0.10694 | 0.0009 | 2341.11 |
2211 | 0.16178 | 2.131 | 0.22258 | 0.176 | 13.17 |
17800 | 0.02689 | 3.509 | 0.14697 | 0.122 | 130.48 |
14386 | 0.00214 | 1.623 | 0.00516 | 0.018 | 756.96 |
28842 | 0.01091 | 1.777 | 0.31683 | 0.638 | 162.86 |
33865 | 0.00588 | 4.494 | 0.0199 | 0.013 | 764.55 |
20991 | 0.00314 | 2.122 | 0.10603 | 0.176 | 675.60 |
20176 | 0.23531 | 1.604 | 0.35238 | 0.202 | 7.94 |
2133 | 0.42076 | 2.129 | 0.21654 | 0.474 | 9.83 |
6001 | 0.00278 | 2.293 | 0.00486 | 0.054 | 823.63 |
13719 | 0.03868 | 2.724 | 0.09397 | 0.0009 | 3026.67 |
32159 | 0.21387 | 2.212 | 0.20432 | 0.674 | 10.83 |
9081 | 0.21369 | 1.854 | 0.18593 | 0.348 | 9.97 |
30682 | 0.41883 | 2.509 | 0.54677 | 1.477 | 5.99 |
5242 | 0.00288 | 2.905 | 0.09527 | 0.058 | 1008.65 |
1566 | 0.18125 | 1.228 | 0.27575 | 0.667 | 6.78 |
30361 | 0.02646 | 3.434 | 0.26835 | 0.125 | 129.78 |
16676 | 0.7622 | 4.755 | 1.88683 | 1.386 | 6.24 |
11743 | 0.02652 | 2.381 | 0.23133 | 0.001 | 2381.17 |
10004 | 0.61912 | 2.676 | 0.62812 | 0.835 | 4.32 |
4379 | 0.02577 | 3.322 | 0.1754 | 0.153 | 128.93 |
39439 | 0.79068 | 3.87 | 3.71243 | 0.332 | 11.66 |
9020 | 0.37833 | 1.858 | 0.30179 | 0.587 | 6.16 |
9089 | 0.44254 | 2.923 | 0.96131 | 1.037 | 6.61 |
16588 | 0.21716 | 1.229 | 0.33667 | 0.178 | 6.90 |
9242 | 0.34564 | 1.24 | 1.16567 | 0.256 | 4.84 |
8056 | 0.20637 | 3.727 | 0.92705 | 0.228 | 18.06 |
26920 | 0.48413 | 2.252 | 0.99654 | 0.278 | 8.10 |
25401 | 0.55265 | 2.557 | 0.9593 | 1.094 | 4.63 |
24070 | 0.00196 | 1.717 | 0.00657 | 0.125 | 874.20 |
23279 | 0.14961 | 2.248 | 1.02763 | 0.314 | 15.03 |
16986 | 0.47433 | 2.521 | 0.86148 | 0.958 | 5.31 |
6450 | 0.42307 | 2.061 | 0.28459 | 0.552 | 7.24 |
13741 | 0.03365 | 2.008 | 0.47748 | 0.042 | 59.67 |
10826 | 0.00552 | 3.875 | 0.0242 | 0.011 | 701.77 |
27869 | 0.00741 | 1.148 | 0.25468 | 0.026 | 154.88 |
15779 | 0.37024 | 1.662 | 0.27441 | 0.518 | 6.06 |
6514 | 0.04097 | 1.7 | 0.11957 | 0.108 | 41.49 |
13327 | 0.319 | 1.9 | 0.8217 | 0.227 | 8.37 |
29573 | 0.55839 | 3.278 | 1.30014 | 1.079 | 5.87 |
34044 | 0.00224 | 2.22 | 0.00581 | 0.016 | 989.73 |
1794 | 0.02562 | 4.196 | 0.14772 | 0.118 | 163.77 |
23804 | 0.00612 | 4.157 | 0.01928 | 0.011 | 678.70 |
7377 | 0.27954 | 1.423 | 0.2698 | 0.431 | 5.27 |
1720 | 0.00428 | 1.29 | 0.1252 | 0.001 | 1290.09 |
2613 | 0.0041 | 2.487 | 0.09094 | 0.001 | 2487.18 |
8466 | 0.34993 | 1.719 | 0.43942 | 0.48 | 4.91 |
28205 | 0.02748 | 3.089 | 0.28023 | 0.122 | 112.41 |
11568 | 0.00761 | 3.777 | 0.02073 | 0.013 | 496.07 |
14875 | 0.02126 | 1.119 | 0.3443 | 0.02 | 55.95 |
10609 | 0.00599 | 3.351 | 0.02311 | 0.013 | 559.05 |
14003 | 0.11701 | 1.941 | 0.25906 | 0.471 | 16.59 |
25426 | 0.00234 | 3.58 | 0.00235 | 0.121 | 1530.49 |
32271 | 0.46209 | 1.852 | 0.57396 | 0.569 | 4.01 |
31758 | 0.49136 | 2.106 | 0.4939 | 0.577 | 4.29 |
25742 | 0.3333 | 2.287 | 0.49831 | 0.586 | 6.86 |
24851 | 0.4089 | 2.167 | 0.47658 | 0.464 | 5.30 |
31701 | 0.00384 | 1.647 | 0.14015 | 0.02 | 428.35 |
21306 | 0.00267 | 1.993 | 0.00852 | 0.124 | 747.30 |
30038 | 0.19652 | 1.5 | 0.59572 | 0.207 | 7.63 |
25928 | 0.44611 | 2.045 | 0.26476 | 1.067 | 7.72 |
25933 | 0.04561 | 4.533 | 0.54544 | 0.241 | 99.39 |
34068 | 0.47106 | 1.669 | 0.29707 | 0.381 | 5.62 |
20134 | 0.02522 | 4.841 | 0.23884 | 0.001 | 4840.20 |
29992 | 0.04083 | 1.22 | 0.09428 | 0.149 | 29.88 |
33186 | 0.5627 | 2.51 | 0.62925 | 0.724 | 4.46 |
38363 | 0.05256 | 2.416 | 0.13534 | 0.1 | 45.96 |
20502 | 0.15727 | 1.197 | 0.64716 | 0.131 | 9.14 |
38487 | 0.00341 | 2.014 | 0.01054 | 0.181 | 590.60 |
9425 | 0.60723 | 2.446 | 1.7144 | 0.456 | 5.36 |
21096 | 0.03516 | 4.379 | 0.19423 | 0.0009 | 4865.56 |
22142 | 0.0269 | 1.406 | 0.33229 | 0.088 | 52.26 |
31087 | 0.37669 | 3.889 | 0.7726 | 0.396 | 10.32 |
30746 | 0.00277 | 1.611 | 0.00692 | 0.093 | 581.80 |
36564 | 0.01616 | 1.175 | 0.09455 | 0.049 | 72.72 |
24199 | 0.16214 | 1.367 | 0.20803 | 0.317 | 8.43 |
35035 | 0.02657 | 1.356 | 0.32301 | 0.089 | 51.04 |
20279 | 0.00434 | 2.136 | 0.10067 | 0.014 | 492.17 |
9986 | 0.02759 | 4.141 | 0.14288 | 0.001 | 4140.31 |
1285 | 0.4027 | 2.595 | 1.19633 | 0.328 | 7.91 |
20027 | 0.00217 | 3.797 | 0.09061 | 0.055 | 1752.97 |
11463 | 0.62118 | 2.599 | 0.81233 | 0.555 | 4.68 |
33621 | 0.00225 | 3.702 | 0.23977 | 0.121 | 1645.37 |
1915 | 0.35241 | 3.48 | 0.6471 | 1.023 | 9.87 |
15133 | 0.02828 | 3.947 | 0.2685 | 0.111 | 139.56 |
27207 | 0.28567 | 1.68 | 0.4414 | 0.252 | 6.67 |
9508 | 0.43986 | 2.063 | 0.28679 | 0.515 | 7.19 |
39109 | 0.00215 | 4.326 | 0.30631 | 1.334 | 2013.82 |
14556 | 0.25188 | 1.402 | 0.35362 | 0.223 | 6.29 |
31505 | 0.4707 | 3.279 | 0.91351 | 0.475 | 6.97 |
8880 | 0.57445 | 2.488 | 1.10095 | 0.834 | 4.33 |
1461 | 0.00184 | 2.258 | 0.12121 | 0.125 | 1225.03 |
16087 | 0.00259 | 2.148 | 0.0048 | 0.132 | 828.07 |
26449 | 0.31107 | 1.841 | 1.03309 | 0.198 | 9.30 |
23461 | 0.37886 | 1.733 | 0.29857 | 0.429 | 5.80 |
18968 | 0.00177 | 4.816 | 0.00467 | 0.131 | 2725.65 |
24603 | 0.2231 | 1.694 | 0.28616 | 0.423 | 7.59 |
22043 | 1.00708 | 3.768 | 0.03203 | 0.01 | 376.80 |
9784 | 0.02768 | 1.857 | 0.12965 | 0.026 | 71.42 |
18850 | 0.00298 | 1.334 | 0.0069 | 0.28 | 447.94 |
16891 | 0.00975 | 1.654 | 0.28168 | 0.705 | 169.68 |
21565 | 0.15788 | 3.243 | 0.31455 | 0.709 | 20.54 |
2144 | 0.35147 | 3.357 | 0.22302 | 0.955 | 15.05 |
Reproduce: install DBMSs, import TPCC benchmark, run query
Cockroach (from binary)
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 demostart 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 --backgroundquit
cockroach20 quit --insecure --host=localhost:26259import 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.sqlstart 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)
remove any previous postgres (if exist)
sudo apt-get --purge remove postgresql postgresql-doc postgresql-commonbuild latest postgres
git clone https://github.com/postgres/postgres.git
mkdir bld
cd bld
../configure
make -j 20install 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/createdbUnknown macro: {username}/usr/local/pgsql/bin/createuser -s
/usr/local/pgsql/bin/createdb postgresdb
/usr/local/pgsql/bin/psql
=# alterUnknown macro: {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.sqltest
/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)
uninstall any existing
sudo apt purge sliqte3build latest sqlite from src
git clone https://github.com/sqlite/sqlite.git
cd sqlite
mkdir bld
cd bld
../configure
make -j 20install DBMS
sudo make installimport DB
wget https://gts3.org/~jjung/tpcc-perf/tpcc_sq.tar.gz
tar xzvf tpcc_sq.tar.gztest
sqlite3 tpcc_sq.db
sqlite> select * from warehouse;run query
sqlite3 tpcc_sq.db < query.sql
MariaDB (from package manager)
install
sudo apt-get install software-properties-common
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://mirror.lstn.net/mariadb/repo/10.5/ubuntu '$(lsb_release -sc)' main'
sudo apt update
sudo apt install mariadb-servercreate user mysql
sudo mysql -u root
DROP USER IF EXISTS 'mysql'@'localhost';
CREATE USER 'mysql'@'localhost' IDENTIFIED BY 'mysql';
GRANT ALL PRIVILEGES ON * . * TO 'mysql'@'localhost';
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.cnfimport 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.sqltest
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