mdcallag

LSM tree shapes during the "Read-only after write-heavy" tests

LSM tree for 5.6.35
Level    Files   Size     Score Read(GB)  Rn(GB) Rnp1(GB) Write(GB) Wnew(GB) Moved(GB) W-Amp Rd(MB/s) Wr(MB/s) Comp(sec) CompMergeCPU(sec) Comp(cnt) Avg(sec) KeyIn KeyDrop
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  L0      0/0    0.00 KB   0.0      0.0     0.0      0.0      14.5     14.5       0.0   1.0      0.0    255.9     57.88             52.37        81    0.715       0      0
  L5     17/0   1004.18 MB   1.0     29.7    13.4     16.3      22.7      6.4       0.0   1.7    242.1    184.9    125.45            111.21        19    6.603    181M    33M
  L6     28/0    2.00 GB   0.0     16.1     4.7     11.4      11.5      0.1       1.9   2.4    214.0    152.6     77.19             71.02        74    1.043    136M    24M
 Sum     45/0    2.98 GB   0.0     45.8    18.1     27.7      48.6     21.0  

mdcallag

--------------
set long_query_time=0
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
flush status
--------------

mdcallag

--------------
drop table if exists ten
--------------

Query OK, 0 rows affected (0.03 sec)

--------------
drop table if exists one_k
--------------

mdcallag

drop table if exists ten;
drop table if exists one_k;
drop table if exists tq;

create table ten(a int primary key);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table one_k(a int primary key);
insert into one_k select a.a + b.a* 10 + c.a * 100 from ten a, ten b, ten c;
#insert into one_k select a.a + b.a* 10 from ten a, ten b;

mdcallag

--------------
set long_query_time=0
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
SELECT max(pk), j FROM tq GROUP BY j
--------------

mdcallag

--------------
drop table if exists ti
--------------

Query OK, 0 rows affected (0.06 sec)

--------------
drop table if exists tq
--------------

mdcallag

set long_query_time=0;

# Query with loose index scan optimization
SELECT max(pk), j FROM tq GROUP BY j;

# Uncorrelated subquery
SELECT t1.pk, t1.j, t1.k
FROM tq t1, (SELECT max(pk) as maxpk, j FROM tq GROUP BY j) t2
WHERE t2.maxpk = t1.pk;

mdcallag

drop table if exists ti; drop table if exists tq;

create table ti(i int);
create table tq(pk int primary key auto_increment, j int NOT NULL, k int NOT NULL);

insert into ti values (null), (null), (null), (null);
insert into ti select null from ti;
insert into ti select null from ti;
insert into ti select null from ti;
insert into ti select null from ti;

mdcallag

explain
SELECT * FROM tags, LATERAL (SELECT * FROM cpu WHERE cpu.tags_id = tags.id ORDER BY time DESC LIMIT 1) as lp ORDER by tags.hostname, lp.time DESC

Query takes 84+ seconds, examines 300 rows?

# Time: 2020-11-05T20:12:25.702338Z
# User@Host: root[root] @ localhost []  Id:    41
# Query_time: 84.049762  Lock_time: 0.000119 Rows_sent: 100  Rows_examined: 300
SET timestamp=1604607061;
SELECT * FROM tags, LATERAL (SELECT * FROM cpu WHERE cpu.tags_id = tags.id ORDER BY time DESC LIMIT 1) as lp ORDER by tags.hostname, lp.time DESC;

mdcallag

mysql> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
                            QUERY: SELECT * FROM cpu WHERE usage_user > 90.0 and time >= '2016-01-01 00:26:02' AND time < '2016-01-01 12:26:02' AND tags_id IN (SELECT id FROM tags WHERE hostname IN ('host_2'))
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {