cpu distributed linux sql

Comparing SQL engines by CPU instructions for DML

By coursity June 7, 2024No Comments

Comparing SQL engines by CPU instructions for DML

When comparing databases, people often focus on the response time, but it’s also essential to consider the global CPU usage. Running a database in a Docker container automatically assigns it to a Linux control group (cgroup), making it easy to obtain execution statistics using perf stat -G. This method offers the benefit of measuring not just one process, but all the database activity when running specific SQL queries. It also enables comparisons with databases that use multiple threads to handle requests, such as YugabyteDB.

Following this idea, I tested a similar workload on multiple databases, inserting two million rows, updating them, counting them, and deleting them. I measured the number of CPU instructions used during that execution and compared PostgreSQL, Oracle, YugabyteDB, and CockroachDB.

This is NOT a benchmark

I am running the database engines using their latest official Docker images and all default configurations, which is not what is typically used in production. In fact, this setup even demonstrates the limitations of benchmarks: databases have different implementations and trade-offs. It’s easy to find workloads that are fast in one database and slow in another. I am measuring on a single instance. It is important to note that even when running on a single node cluster, a distributed database architecture that provides elasticity and resilience with built-in distribution utilizes more CPU instructions than traditional monolithic databases. In a cloud-native environment, the cost remains lower by scaling up and down as needed, rather than constantly provisioning capacity for peak demands.

Summary

Here is a summary of the results. Gi is the number of billion instructions in user space, and s is the number of seconds, all reported by perf stat -e instructions:u -a -G docker/$containerid. The detailed test and output follow.

Database	sleep	insert	update	select	delete
PostgreSQL	0.02Gi/11s	53Gi/20s	74Gi/39s	1Gi/1s	10Gi/10s
MySQL	0.03Gi/11s	68Gi/18s	67Gi/21s	3Gi/2s	47Gi/20s
Oracle	6Gi/10s	30Gi/22s	50Gi/30s	4Gi/7s	101Gi/48s
YugabyteDB	1Gi/11s	377Gi/38s	919Gi/114s	11Gi/2s	422Gi/69s
CockroachDB	7Gi/11s	1344Gi/458s	747Gi/395s	13Gi/3s	799Gi/486s

All runs follow the same process: start the database in a docker container and keep the container ID in a variable. Then, connect with the right client for the database and run SQL, with perf stat measuring the CPU instructions.

PostgreSQL

Start the database in a Docker container

postgres=$( docker run -d \ -e POSTGRES_PASSWORD=postgres \ postgres:latest \
)

Enter fullscreen mode
Exit fullscreen mode

Start a client, connect, and create a table

docker run -i --link $postgres:db -e PGPASSWORD=postgres \ postgres \ psql -h db -p 5432 -U postgres -ec '
select version();
drop table if exists demo;
create extension if not exists pgcrypto;
create table demo ( primary key (id) , id uuid default gen_random_uuid() , value float
); '

Enter fullscreen mode
Exit fullscreen mode


NOTICE: table "demo" does not exist, skipping version
--------------------------------------------------------------------------------------------------------------------- PostgreSQL 16.3 (Debian 16.3-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row) DROP TABLE
CREATE EXTENSION
CREATE TABLE

Enter fullscreen mode
Exit fullscreen mode

Measure background activity when sleeping 10 seconds

perf stat -e instructions:u -G docker/$postgres -a \
docker run -i --link $postgres:db -e PGPASSWORD=postgres \ postgres \ psql -h db -p 5432 -U postgres -ec '
select pg_sleep(10); '

Enter fullscreen mode
Exit fullscreen mode


select pg_sleep(10); pg_sleep
---------- (1 row) Performance counter stats for 'system wide': 17,728,103 instructions:u docker/6ee868ec9bcb10ec206224aa84db9489d70db5c71d46f6ec489da4c8f074d0ab 10.666744875 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

In PostgreSQL, I run VACUUM after each statement because it is necessary to leave the database ready for further queries. Not doing it here would not account for the real resource usage.

Insert two million rows in two transactions of one million rows

perf stat -e instructions:u -G docker/$postgres -a \
docker run -i --link $postgres:db -e PGPASSWORD=postgres \ postgres \ psql -h db -p 5432 -U postgres -ec '
insert into demo(value) select generate_series(1,1000000);
insert into demo(value) select generate_series(1,1000000); ' -c '
vacuum '

Enter fullscreen mode
Exit fullscreen mode


insert into demo(value) select generate_series(1,1000000);
INSERT 0 1000000 insert into demo(value) select generate_series(1,1000000);
INSERT 0 1000000 vacuum
VACUUM Performance counter stats for 'system wide': 52,812,253,895 instructions:u docker/6ee868ec9bcb10ec206224aa84db9489d70db5c71d46f6ec489da4c8f074d0ab 20.423514447 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

Update those two million rows

perf stat -e instructions:u -G docker/$postgres -a \
docker run -i --link $postgres:db -e PGPASSWORD=postgres \ postgres \ psql -h db -p 5432 -U postgres -ec '
update demo set value=value+1; ' -c '
vacuum '

Enter fullscreen mode
Exit fullscreen mode

update demo set value=value+1; UPDATE 2000000 vacuum VACUUM Performance counter stats for 'system wide': 74,393,453,562 instructions:u docker/6ee868ec9bcb10ec206224aa84db9489d70db5c71d46f6ec489da4c8f074d0ab 38.580002880 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

Count those two million values

perf stat -e instructions:u -G docker/$postgres -a \
docker run -i --link $postgres:db -e PGPASSWORD=postgres \ postgres \ psql -h db -p 5432 -U postgres -ec '
select count(value) from demo; '

Enter fullscreen mode
Exit fullscreen mode

select count(value) from demo; count
--------- 2000000
(1 row) Performance counter stats for 'system wide': 1,391,096,618 instructions:u docker/6ee868ec9bcb10ec206224aa84db9489d70db5c71d46f6ec489da4c8f074d0ab 0.732223707 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

Delete those two million rows

perf stat -e instructions:u -G docker/$postgres -a \
docker run -i --link $postgres:db -e PGPASSWORD=postgres \ postgres \ psql -h db -p 5432 -U postgres -ec '
delete from demo; ' -c '
vacuum '

Enter fullscreen mode
Exit fullscreen mode

delete from demo; DELETE 2000000 vacuum VACUUM Performance counter stats for 'system wide': 10,148,828,821 instructions:u docker/6ee868ec9bcb10ec206224aa84db9489d70db5c71d46f6ec489da4c8f074d0ab 9.696420600 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

YugabyteDB

Start the database in a Docker container

yugabytedb=$( docker run -d \ yugabytedb/yugabyte:latest \ yugabyted start --background=false
)

Enter fullscreen mode
Exit fullscreen mode

Start a client, connect, and create a table

docker run -i --link $yugabytedb:db postgres \ psql -h db -p 5433 -U yugabyte -ec '
select version();
drop table if exists demo;
create extension if not exists pgcrypto;
create table demo ( primary key (id) , id uuid default gen_random_uuid() , value float
); '

Enter fullscreen mode
Exit fullscreen mode

NOTICE: table "demo" does not exist, skipping version ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 11.2-YB-2.21.0.1-b0 on x86_64-pc-linux-gnu, compiled by clang version 16.0.6 (https://github.com/yugabyte/llvm-project.git 1e6329f40e5c531c09ade7015278078682293ebd), 64-bit
(1 row) DROP TABLE
CREATE EXTENSION
CREATE TABLE

Enter fullscreen mode
Exit fullscreen mode

Measure background activity when sleeping 10 seconds

perf stat -e instructions:u -G docker/$yugabytedb -a \
docker run -i --link $yugabytedb:db \ postgres:latest \ psql -h db -p 5433 -U yugabyte -e << 'SQL'
select pg_sleep(10);
SQL

Enter fullscreen mode
Exit fullscreen mode

select pg_sleep(10); pg_sleep
---------- (1 row) Performance counter stats for 'system wide': 668,261,558 instructions:u docker/9c32896e86dc88e026e5f80fa3f143f70fa0a7815ee81acca13c750c2a4a8d4c 10.859333028 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

Insert two million rows in two transactions of one million rows

perf stat -e instructions:u -G docker/$yugabytedb -a \
docker run -i --link $yugabytedb:db \ postgres:latest \ psql -h db -p 5433 -U yugabyte -e << 'SQL'
insert into demo(value) select generate_series(1,1000000);
insert into demo(value) select generate_series(1,1000000);
SQL

Enter fullscreen mode
Exit fullscreen mode

insert into demo(value) select generate_series(1,1000000);
INSERT 0 1000000
insert into demo(value) select generate_series(1,1000000);
INSERT 0 1000000 Performance counter stats for 'system wide': 377,180,116,073 instructions:u docker/9c32896e86dc88e026e5f80fa3f143f70fa0a7815ee81acca13c750c2a4a8d4c 38.254201842 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

Update those two million rows

perf stat -e instructions:u -G docker/$yugabytedb -a \
docker run -i --link $yugabytedb:db \ postgres:latest \ psql -h db -p 5433 -U yugabyte -e << 'SQL'
update demo set value=value+1;
SQL

Enter fullscreen mode
Exit fullscreen mode

update demo set value=value+1;
UPDATE 2000000 Performance counter stats for 'system wide': 918,965,397,040 instructions:u docker/9c32896e86dc88e026e5f80fa3f143f70fa0a7815ee81acca13c750c2a4a8d4c 113.679966282 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

Count those two million values

perf stat -e instructions:u -G docker/$yugabytedb -a \
docker run -i --link $yugabytedb:db \ postgres:latest \ psql -h db -p 5433 -U yugabyte -e << 'SQL'
select count(value) from demo;
SQL

Enter fullscreen mode
Exit fullscreen mode

select count(value) from demo; count
--------- 2000000
(1 row) Performance counter stats for 'system wide': 11,168,496,896 instructions:u docker/9c32896e86dc88e026e5f80fa3f143f70fa0a7815ee81acca13c750c2a4a8d4c 2.475239412 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

Delete those two million rows

perf stat -e instructions:u -G docker/$yugabytedb -a \
docker run -i --link $yugabytedb:db \ postgres:latest \ psql -h db -p 5433 -U yugabyte -e << 'SQL'
delete from demo;
SQL

Enter fullscreen mode
Exit fullscreen mode

delete from demo;
DELETE 2000000 Performance counter stats for 'system wide': 421,678,154,604 instructions:u docker/9c32896e86dc88e026e5f80fa3f143f70fa0a7815ee81acca13c750c2a4a8d4c 68.652693864 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

Oracle

Start the database in a Docker container

oracle=$( docker run -d \ -e ORACLE_PASSWORD=franck -e APP_USER=franck -e APP_USER_PASSWORD=franck \ gvenzl/oracle-free:slim
)

Enter fullscreen mode
Exit fullscreen mode

Start a client, connect, and create a table

docker run -i --link $oracle:db \ container-registry.oracle.com/database/sqlcl:latest \ -s franck/franck@//db/FREEPDB1 <<'SQL'
select banner_full from v$version;
drop table if exists demo;
create table demo ( primary key (id) , id raw(16) default sys_guid() , value float
);
SQL

Enter fullscreen mode
Exit fullscreen mode

BANNER_FULL
_______________________________________________________________________________________________________
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05 Table DEMO dropped. Table DEMO created.

Enter fullscreen mode
Exit fullscreen mode

Measure background activity when sleeping 10 seconds with sqlcl

perf stat -e instructions:u -G docker/$oracle -a \
docker run -i --link $oracle:db \ container-registry.oracle.com/database/sqlcl:latest \ -s franck/franck@//db/FREEPDB1 @ /dev/stdin <<'SQL'
exec dbms_session.sleep(10);
SQL

Enter fullscreen mode
Exit fullscreen mode


PL/SQL procedure successfully completed. Performance counter stats for 'system wide': 833,762,410 instructions:u docker/8db3df9fbc74f04e20a28e016cc0b91e04a99dc88ae3ef1923172eb6ac724aa0 16.695102734 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

It takes an additional 7 seconds to connect because sqlcl is a Java application that is very slow to start and connect. I cannot use it for this test. Then, instead if running a container with the database client, I’ll connect from sqlplus within the database container. I didn’t find an official image to run only sqlplus without starting a database.

Measure background activity when sleeping 10 seconds with sqlplus

perf stat -e instructions:u -G docker/$oracle -a \
docker exec -i $oracle \ sqlplus -s franck/franck@//localhost/FREEPDB1 @ /dev/stdin <<'SQL'
exec dbms_session.sleep(10);
SQL

Enter fullscreen mode
Exit fullscreen mode


PL/SQL procedure successfully completed. Performance counter stats for 'system wide': 6,350,743,728 instructions:u docker/8db3df9fbc74f04e20a28e016cc0b91e04a99dc88ae3ef1923172eb6ac724aa0 10.188907434 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

Oracle is not auto-commit by default, so I add a COMMIT statement after each DML statement.

Insert two million rows in two transactions of one million rows

perf stat -e instructions:u -G docker/$oracle -a \
docker run -i --link $oracle:db \ container-registry.oracle.com/database/sqlcl:latest \ -s franck/franck@//db/FREEPDB1 @ /dev/stdin <<'SQL'
insert into demo(value) select rownum from xmltable('1 to 1000000');
commit;
insert into demo(value) select rownum from xmltable('1 to 1000000');
commit;
SQL

Enter fullscreen mode
Exit fullscreen mode


1,000,000 rows inserted. Commit complete. 1,000,000 rows inserted. Commit complete. Performance counter stats for 'system wide': 29,948,655,538 instructions:u docker/8db3df9fbc74f04e20a28e016cc0b91e04a99dc88ae3ef1923172eb6ac724aa0 22.445749204 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

Update those two million rows

perf stat -e instructions:u -G docker/$oracle -a \
docker run -i --link $oracle:db \ container-registry.oracle.com/database/sqlcl:latest \ -s franck/franck@//db/FREEPDB1 @ /dev/stdin <<'SQL'
update demo set value=value+1;
commit;
SQL

Enter fullscreen mode
Exit fullscreen mode


2,000,000 rows updated. Performance counter stats for 'system wide': 50,447,105,827 instructions:u docker/8db3df9fbc74f04e20a28e016cc0b91e04a99dc88ae3ef1923172eb6ac724aa0 30.286655516 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

Count those two million values

perf stat -e instructions:u -G docker/$oracle -a \
docker run -i --link $oracle:db \ container-registry.oracle.com/database/sqlcl:latest \ -s franck/franck@//db/FREEPDB1 @ /dev/stdin <<'SQL'
select count(value) from demo;
SQL

Enter fullscreen mode
Exit fullscreen mode

 COUNT(VALUE)
_______________ 2000000 Performance counter stats for 'system wide': 4,446,769,589 instructions:u docker/8db3df9fbc74f04e20a28e016cc0b91e04a99dc88ae3ef1923172eb6ac724aa0 6.698948743 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

Delete those two million rows

perf stat -e instructions:u -G docker/$oracle -a \
docker run -i --link $oracle:db \ container-registry.oracle.com/database/sqlcl:latest \ -s franck/franck@//db/FREEPDB1 @ /dev/stdin <<'SQL'
delete from demo;
commit;
SQL

Enter fullscreen mode
Exit fullscreen mode


2,000,000 rows deleted. Performance counter stats for 'system wide': 101,012,043,703 instructions:u docker/8db3df9fbc74f04e20a28e016cc0b91e04a99dc88ae3ef1923172eb6ac724aa0 47.742493451 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

CockroachDB

Start the database in a Docker container

cockroachdb=$(
docker run -d \ cockroachdb/cockroach \ bash -c 'cockroach start-single-node --insecure'
)

Enter fullscreen mode
Exit fullscreen mode

Start a client, connect, and create a table

docker run -i --link $cockroachdb:db postgres \ psql -h db -p 26257 -U root -d defaultdb -e <<'SQL'
select version();
drop table if exists demo;
create table demo ( primary key (id) , id uuid default gen_random_uuid() , value float
);
SQL

Enter fullscreen mode
Exit fullscreen mode

select version(); version
--------------------------------------------------------------------------------------------------------- CockroachDB CCL v24.1.0 (x86_64-pc-linux-gnu, built 2024/05/15 21:28:29, go1.22.2 X:nocoverageredesign)
(1 row) drop table if exists demo;
DROP TABLE
create table demo ( primary key (id) , id uuid default gen_random_uuid() , value float
);
CREATE TABLE

Enter fullscreen mode
Exit fullscreen mode

Measure background activity when sleeping 10 seconds

perf stat -e instructions:u -G docker/$cockroachdb -a \
docker run -i --link $cockroachdb:db postgres \ psql -h db -p 26257 -U root -d defaultdb -e <<'SQL'
select pg_sleep(10);
SQL

Enter fullscreen mode
Exit fullscreen mode

select pg_sleep(10); pg_sleep
---------- t
(1 row) Performance counter stats for 'system wide': 7,449,329,979 instructions:u docker/17aa81758d4af146a23dd785c046e2b27f5926ccc561f7a67c654d1e5d7f587d 10.676270411 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

Insert two million rows in two transactions of one million rows

perf stat -e instructions:u -G docker/$cockroachdb -a \
docker run -i --link $cockroachdb:db postgres \ psql -h db -p 26257 -U root -d defaultdb -e <<'SQL'
insert into demo(value) select generate_series(1,1000000);
insert into demo(value) select generate_series(1,1000000);
SQL

Enter fullscreen mode
Exit fullscreen mode

insert into demo(value) select generate_series(1,1000000);
INSERT 0 1000000
insert into demo(value) select generate_series(1,1000000);
INSERT 0 1000000 Performance counter stats for 'system wide': 1,344,459,032,711 instructions:u docker/17aa81758d4af146a23dd785c046e2b27f5926ccc561f7a67c654d1e5d7f587d 457.629554299 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

Update those two million rows

perf stat -e instructions:u -G docker/$cockroachdb -a \
docker run -i --link $cockroachdb:db postgres \ psql -h db -p 26257 -U root -d defaultdb -e <<'SQL'
update demo set value=value+1;
SQL

Enter fullscreen mode
Exit fullscreen mode

update demo set value=value+1;
UPDATE 2000000 Performance counter stats for 'system wide': 747,076,086,139 instructions:u docker/17aa81758d4af146a23dd785c046e2b27f5926ccc561f7a67c654d1e5d7f587d 395.113462545 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

Count those two million values

perf stat -e instructions:u -G docker/$cockroachdb -a \
docker run -i --link $cockroachdb:db postgres \ psql -h db -p 26257 -U root -d defaultdb -e <<'SQL'
select count(value) from demo;
SQL

Enter fullscreen mode
Exit fullscreen mode

select count(value) from demo; count
--------- 2000000
(1 row) Performance counter stats for 'system wide': 12,906,358,871 instructions:u docker/17aa81758d4af146a23dd785c046e2b27f5926ccc561f7a67c654d1e5d7f587d 2.983402815 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

Delete those two million rows

perf stat -e instructions:u -G docker/$cockroachdb -a \
docker run -i --link $cockroachdb:db postgres \ psql -h db -p 26257 -U root -d defaultdb -e <<'SQL'
delete from demo;
SQL

Enter fullscreen mode
Exit fullscreen mode

delete from demo;
DELETE 2000000 Performance counter stats for 'system wide': 799,367,485,634 instructions:u docker/17aa81758d4af146a23dd785c046e2b27f5926ccc561f7a67c654d1e5d7f587d 486.132498193 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

MySQL

Start the database in a Docker container

mysql=$(
docker run -d \ -e MYSQL_ROOT_PASSWORD=secret -e MSQL_DATABASE=db -e MYSQL_USER=franck -e MYSQL_PASSWORD=franck -e MYSQL_ROOT_HOST=% \ mysql:latest \
)

Enter fullscreen mode
Exit fullscreen mode

Start a client, connect, and create a table

docker run -i --link $mysql:db mysql:latest \ mysql -h db -P 3306 -u root -psecret -v <<'SQL'
select version();
create database if not exists db;
use db;
drop table if exists demo;
create table demo ( primary key (id) , id binary(32) default (UUID_TO_BIN(UUID())) , value float
);
SQL

Enter fullscreen mode
Exit fullscreen mode

mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
select version()
-------------- version()
8.4.0
--------------
create database if not exists db
-------------- --------------
drop table if exists demo
-------------- --------------
create table demo ( primary key (id) , id binary(32) default (UUID_TO_BIN(UUID())) , value float
)
--------------

Enter fullscreen mode
Exit fullscreen mode

Measure background activity when sleeping 10 seconds

perf stat -e instructions:u -G docker/$mysql -a \
docker run -i --link $mysql:db mysql:latest \ mysql -h db -P 3306 -u root -psecret -v -e '
do sleep(10); '

Enter fullscreen mode
Exit fullscreen mode

mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
do sleep(10)
-------------- Performance counter stats for 'system wide': 26,942,875 instructions:u docker/379507f1eac6f65bd3a88b7eeaf6c3cc2e084912fa76b22118521e8e35f37f31 10.634240192 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

Generating rows in MySQL is a bit more difficult. I’m using a WITH clause and CROSS JOIN.

Insert two million rows in two transactions of one million rows

perf stat -e instructions:u -G docker/$mysql -a \
docker run -i --link $mysql:db mysql:latest \ mysql -h db -P 3306 -u root -psecret -v -e '
INSERT INTO db.demo(value)
with x as ( SELECT 0 as x UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 )
select x1.x+10*x2.x+100*x3.x+1000*x4.x+10000*x5.x+100000*x6.x from x x1 , x x2 , x x3, x x4, x x5, x x6;
commit;
INSERT INTO db.demo(value)
with x as ( SELECT 0 as x UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 )
select x1.x+10*x2.x+100*x3.x+1000*x4.x+10000*x5.x+100000*x6.x from x x1 , x x2 , x x3, x x4, x x5, x x6;
commit; '

Enter fullscreen mode
Exit fullscreen mode

mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
INSERT INTO db.demo(value)
with x as ( SELECT 0 as x UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 )
select x1.x+10*x2.x+100*x3.x+1000*x4.x+10000*x5.x+100000*x6.x from x x1 , x x2 , x x3, x x4, x x5, x x6
-------------- --------------
commit
-------------- --------------
INSERT INTO db.demo(value)
with x as ( SELECT 0 as x UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 )
select x1.x+10*x2.x+100*x3.x+1000*x4.x+10000*x5.x+100000*x6.x from x x1 , x x2 , x x3, x x4, x x5, x x6
-------------- --------------
commit
-------------- Performance counter stats for 'system wide': 68,011,238,184 instructions:u docker/f02f5a42cadc39cdbbc7c1c2213bd5697032cdaeee1e3881a17fa091dd87a9e0 18.342903060 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

Update those two million rows

perf stat -e instructions:u -G docker/$mysql -a \
docker run -i --link $mysql:db mysql:latest \ mysql -h db -P 3306 -u root -psecret -v -e '
update db.demo set value=value+1; '

Enter fullscreen mode
Exit fullscreen mode

mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
update db.demo set value=value+1
-------------- Performance counter stats for 'system wide': 67,272,334,190 instructions:u docker/f02f5a42cadc39cdbbc7c1c2213bd5697032cdaeee1e3881a17fa091dd87a9e0 21.026414415 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

Count those two million values

perf stat -e instructions:u -G docker/$mysql -a \
docker run -i --link $mysql:db mysql:latest \ mysql -h db -P 3306 -u root -psecret -v -e '
select count(value) from db.demo; '

Enter fullscreen mode
Exit fullscreen mode

mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
select count(value) from db.demo
-------------- count(value)
2000000 Performance counter stats for 'system wide': 3,324,827,627 instructions:u docker/f02f5a42cadc39cdbbc7c1c2213bd5697032cdaeee1e3881a17fa091dd87a9e0 2.400244863 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

Delete those two million rows

perf stat -e instructions:u -G docker/$mysql -a \
docker run -i --link $mysql:db mysql:latest \ mysql -h db -P 3306 -u root -psecret -v -e '
delete from db.demo; '

Enter fullscreen mode
Exit fullscreen mode

mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
delete from db.demo
-------------- Performance counter stats for 'system wide': 47,284,490,837 instructions:u docker/f02f5a42cadc39cdbbc7c1c2213bd5697032cdaeee1e3881a17fa091dd87a9e0 19.846219766 seconds time elapsed

Enter fullscreen mode
Exit fullscreen mode

In conclusion

With those simple tests, the most popular open-source monolithic databases, PostgreSQL and MySQL, perform well and similarly. This is interesting because they have a completely different implementation: PostgreSQL uses heap tables with in-place multi-version concurrency control, whereas MySQL stores the table in its primary key B-Tree, and past versions are moved to the transactional undo log. The reason is that those databases have been there for a long time, and those simple workloads were optimized for each different architecture.

For the same reason, Oracle Database also performs well in terms of CPU usage, which is crucial given that it runs under a commercial license with an initial price and annual fees based on the physical CPU cores in most platforms, without the possibility of scaling down and reducing the support fees.

Two Distributed SQL databases were tested, with built-in resilience based on Raft to distribute and replicate and LSM Trees to store the distributed tables and indexes.

I have no idea why CockroachDB uses so many CPU instructions for inserts and deletes. I generated a perf report and have seen many samples in the call stack (https://share.firefox.dev/3KwItMA) in Peeble’s seek functions, their rewrite of RocksDB in Golang. Please comment if you think something is wrong with the setup. CockroachDB has only a subset of features available for free in the community edition, so maybe some optimizations are only available in the Enterprise edition.

YugabyteDB shows numbers closer to monolithic databases, with higher CPU utilization due to its use of multi-threading for batch processing of reads and writes. The advantages of this approach may not be immediately apparent when dealing with small, single-session queries. However, in distributed systems, while individual response times may be slightly higher, the overall throughput can increase significantly due to its scalable architecture.

Related

Discover more from Coursity

Subscribe to get the latest posts sent to your email.

cpu distributed linux sql

Comments

No comments yet. Why don’t you start the discussion?

Leave a Reply Cancel reply