mdcallag

--- query plan with MySQL 8.0.21

mysql> show create table cpu\G
*************************** 1. row ***************************
       Table: cpu
Create Table: CREATE TABLE `cpu` (
  `time` timestamp NOT NULL,
  `tags_id` bigint NOT NULL,
  `additional_tags` text,
  `usage_user` double NOT NULL,

mdcallag

$ bin/pg_test_fsync -f /data/m/my/data/mysql.ibd
5 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.

Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync is Linux's default)
        open_datasync                       435.834 ops/sec    2294 usecs/op
        fdatasync                           739.356 ops/sec    1353 usecs/op
        fsync                               275.887 ops/sec    3625 usecs/op
        fsync_writethrough                              n/a

mdcallag

--- Postgres 12.3, l.i0 loads 500m rows

l.i0
ips     qps     rps     rkbps   wkbps   rpq     rkbpq   wkbpi   csps    cpups   cspq    cpupq   dbgb1   dbgb2   rss     maxop   p50     p99     tag
101854  0       43      352     39935   0.000   0.003   0.392   12144   46.9    0.119   18      47.8    55.5    0.0     0.232   101988  98292   pg123.cx4a
104998  0       46      373     41078   0.000   0.004   0.391   12481   42.7    0.119   16      47.8    55.7    0.0     0.143   105188  99635   pg123.cx4a

l.x
ips     qps     rps     rkbps   wkbps   rpq     rkbpq   wkbpi   csps    cpups   cspq    cpupq   dbgb1   dbgb2   rss     maxop   p50     p99     tag
111879  0       288     35824   41466   0.003   0.320   0.371   351     24.1    0.003   9       91.9    97.7    0.0     0.003   NA      NA      pg123.cx4a

mdcallag

--- Postgres 12.3, l.i0 loads 100m rows

l.i0
ips     qps     rps     rkbps   wkbps   rpq     rkbpq   wkbpi   csps    cpups   cspq    cpupq   dbgb1   dbgb2   rss     maxop   p50     p99     tag
100908  0       0       0       43960   0.000   0.000   0.436   12282   46.6    0.122   18      9.6     17.2    0.0     0.009   101089  96965   pg123.cx4a
104384  0       0       1       45243   0.000   0.000   0.433   12388   42.8    0.119   16      9.6     17.4    1.1     0.031   104686  99063   pg123.cx4a

l.x
ips     qps     rps     rkbps   wkbps   rpq     rkbpq   wkbpi   csps    cpups   cspq    cpupq   dbgb1   dbgb2   rss     maxop   p50     p99     tag
127842  0       40      4954    40339   0.000   0.039   0.316   311     24.5    0.002   8       18.4    26.0    0.0     0.002   NA      NA      pg123.cx4a

mdcallag

--- Postgres 12.3, 20m rows loaded in l.i0

l.i0
ips     qps     rps     rkbps   wkbps   rpq     rkbpq   wkbpi   csps    cpups   cspq    cpupq   dbgb1   dbgb2   rss     maxop   p50     p99     tag
101523  0       0       0       43776   0.000   0.000   0.431   12578   46.7    0.124   18      1.9     5.2     0.0     0.009   101888  81114   pg123.cx4a
104167  0       0       0       44958   0.000   0.000   0.432   12559   42.8    0.121   16      1.9     5.2     0.0     0.002   104587  83469   pg123.cx4a

l.x
ips     qps     rps     rkbps   wkbps   rpq     rkbpq   wkbpi   csps    cpups   cspq    cpupq   dbgb1   dbgb2   rss     maxop   p50     p99     tag
157031  0       0       0       40427   0.000   0.000   0.257   1091    24.5    0.007   6       3.7     7.9     0.0     0.002   NA      NA      pg123.cx4a

mdcallag

ips="100 100 200 200 400 400 600 600 800 800 1000 1000"

ns=1800
bash ra1.sh  20000000 20000000  20m 1 $ns $mbd $rdir $dev $only1t $npart $ips
bash ra2.sh 100000000 10000000 100m 1 $ns $mbd $rdir $dev $only1t $npart $ips
bash ra2.sh 500000000 10000000 500m 1 $ns $mbd $rdir $dev $only1t $npart $ips

mdcallag

[mysqld]
sql_mode=no_engine_substitution,no_unsigned_subtraction
default_tmp_storage_engine=MyISAM
tmpdir=/data/m/fbmy/data
query_cache_size=0
query_cache_type=0
# slow_query_log_file=/data/m/fbmy/slow.log
long_query_time=5
# skip_log_bin
log_bin=/data/m/fbmy/binlogs/bl

mdcallag

[mysqld]
sql_mode=no_engine_substitution,no_unsigned_subtraction
default_tmp_storage_engine=MyISAM
#query_cache_size=0
#query_cache_type=0
long_query_time=5
# skip_log_bin
max_binlog_size=128M
server_id=77
binlog_format=ROW

mdcallag

Ratio of ips or qps for rx80.cx6a / rx56.cx6a. A value less than zero means that rx80 is slower.
* rx56 is MyRocks from MySQL 5.6.35
* rx80 is MyRocks from MySQL 8.0.17
* cx6a names the my.cnf I used

l.i0    l.x     l.i1    q100.2  q200.2  q400.2  q600.2  q800.2  q1000.2
.80     1.03    .87     .92     .90     .89     .90     .91     .92

l.i0
ips     qps     rps     rkbps   wkbps   rpq     rkbpq   wkbpi   csps    cpups   cspq    cpupq   dbgb1   dbgb2   rss     maxop   p50     p99     tag

mdcallag

Ratio of ips or qps for rx80.cx6a / rx56.cx6a. A value less than zero means that rx80 is slower.
* rx56 is MyRocks from MySQL 5.6.35
* rx80 is MyRocks from MySQL 8.0.17
* cx6a names the my.cnf I used

l.i0    l.x     l.i1    q100.2  q200.2  q400.2  q600.2  q800.2  q1000.2
.82     .99     .87     .99     .89     .90     .90     .90     .91

l.i0
ips     qps     rps     rkbps   wkbps   rpq     rkbpq   wkbpi   csps    cpups   cspq    cpupq   dbgb1   dbgb2   rss     maxop   p50     p99     tag