0

Environment

  • AMD Ryzen 9 5950X, 128GB 3200MHz Dual Channel
  • Datacenter NVMe SSDs with 3GB/s+ read and write
  • MariaDB 10.6.3 x64
  • Windows Server 2019 (same issue on Debian though)
  • dedicated machine, no other tasks running

my.ini

[mysqld]
default-storage-engine=INNODB
log-output=NONE
general-log=0
general_log_file="mariadb.log"
slow-query-log=0
query_cache_type=OFF
query_cache_size=0
innodb_buffer_pool_size=64G

DDL

CREATE TABLE testinnodb
(
    a INTEGER NOT NULL, b INTEGER NOT NULL, c INTEGER NOT NULL,
    i FLOAT NOT NULL, j FLOAT NOT NULL, k FLOAT NOT NULL,
    x CHAR(20) NOT NULL, y CHAR(20) NOT NULL, z CHAR(20) NOT NULL
) ENGINE=InnoDB;

Same schema for MyISAM and Memory.

Tables are filled with 10M rows of random data, resulting data sizes:
InnoDB: 1.0 GB
MyISAM: 810 MB
Memory: 867 MB

SQL

SELECT * FROM testinnodb WHERE c=1;
SELECT * FROM testmyisam WHERE c=1;
SELECT * FROM testmemory WHERE c=1;

InnoDB: 2.4s !!!
MyISAM: 0.3s
Memory: 0.2s

The queries are run multiple times but performance stays the same. EXPLAIN gives the same output for all three queries (SIMPLE, USING WHERE).

This is clearly not an I/O issue, given the hardware and the performance of MyISAM and Memory in comparison.

64GB for the buffer pool is also more than enough to hold all of that table in memory.
The data must be in the buffer pool because disabling innodb_buffer_pool_load_at_startup, the query will take 4.2s on first run, and then 2.4s in subsequent runs.
innodb_buffer_pool_bytes_data will have grown by over 1GB after the first run, so it looks like the entire data is in fact in the buffer pool.
innodb_buffer_pool_read_requests does increase by about 10M on each execution.

Why the hell is reading the data using InnoDB from the buffer pool (i.e. RAM) 10 times slower than reading the same data using MyISAM (i.e. from SSD)?

I need help understanding what is going on. Surely this can't be right? I've tried playing around with the DB config (e.g. innodb_old_blocks_time=0, innodb_read_io_threads=32 and innodb_write_io_threads=32) but this literally changed nothing.

I know using an INDEX will improve things, but this is not the point.

Let me know if you need some status variables for debugging, I'm new to InnoDB so I'm not sure what was relevant to post here.

Output of SHOW ENGINE InnoDB STATUS; after startup and querying the InnoDB table twice

=====================================
Per second averages calculated from the last 50 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 50 srv_idle
srv_master_thread log flush and writes: 50
----------
SEMAPHORES
----------
------------
TRANSACTIONS
------------
Trx id counter 20001586
Purge done for trx's n:o < 20001583 undo n:o < 0 state: running
History list length 14
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION (000002727BD64108), not started 
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
Pending flushes (fsync) log: 0; buffer pool: 0
71726 OS file reads, 2 OS file writes, 2 OS fsyncs
0.00 reads/s, 16413 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 8881303100
Log flushed up to   8881303100
Pages flushed up to 8881303100
Last checkpoint at  8881303088
0 pending log flushes, 0 pending chkp writes
4 log i/o's done, 0.08 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 68753031168
Dictionary memory allocated 424846000
Buffer pool size   4147712
Free buffers       4075870
Database pages     71842
Old database pages 26539
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 71711, created 131, written 0
1434.19 reads/s, 2.62 creates/s, 0.00 writes/s
Buffer pool hit rate 996 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 71842, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 read views open inside InnoDB
Process ID=0, Main thread ID=0, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 20000000
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 399992.00 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
5
  • Have you tried show engine innodb status?
    – mustaccio
    Jul 13 at 18:53
  • @mustaccio I've added the InnoDB monitor output to the original post.
    – Alex K.
    Jul 14 at 12:49
  • MyISAM is being cached in the ~60 GB of spare room in RAM. That is, its second run requires no I/O.
    – Rick James
    Jul 14 at 21:23
  • @RickJames The 0.3s for MyISAM is achieved on first run already. Also, AFAIK MySQL doesn't cache MyISAM data, only MyISAM keys in memory?
    – Alex K.
    Jul 16 at 14:38
  • The OS, not MySQL, caches MyISAM data. Index blocks (1KB each) are cached in the key_buffer of size key_buffer_size.
    – Rick James
    Jul 16 at 14:52
0

Try this:

START TRANSACTION;
loop     (write a loop inside the transaction)
   SELECT * FROM testinnodb WHERE c=1;
endloop
COMMIT;
2
  • And what does this do exactly= you triggert the lq review
    – nbk
    Jul 14 at 22:23
  • Yeah, I'm not sure I understand this. An infinite loop? Wouldn't this require a stored procedure first?
    – Alex K.
    Jul 16 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.