4

I have MariaDB on my server with 16/32 CPU cores, Everything seems to be ok when running mysqltuner except InnoDB Write Log efficiency, taking 1953.15%, wondering if that's normal? or is there any solution how to fix that? Appreciate the advices!

-------- InnoDB Metrics --------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 16.0G/7.0G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 4.0G * 1/16.0G should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk : 128 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.89% (235648939 hits/ 235906897 total)
[!!] InnoDB Write Log efficiency: 1953.15% (24219 hits/ 1240 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1447 writes)

Btw, The cpu usage for mariadb/mysql is more than 100% as well...

Bentix
  • 77
  • 1
  • 8
  • 1
    High CPU implies missing index and/or poorly formulated queries. Locate the queries via [_SlowLog_](http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog) – Rick James Apr 28 '22 at 05:17

2 Answers2

5

Ignore this result in MySQLTuner when using MariaDB. The calculation is misleading due to a bug in MariaDB, according to the discussion in this issue: https://github.com/major/MySQLTuner-perl/issues/385

See also a similar issue to yours: https://dba.stackexchange.com/questions/194832/innodb-write-log-efficiency-above-100

It's not clear why "write log efficiency" is even a thing to pay attention to, even if it were calculated correctly. It lacks important context. What is the expected result? What is a bad result? What should one change if it is too high or too low?

There is even an issue asking these questions: https://github.com/major/MySQLTuner-perl/issues/291

The reply from the developer indicates to me that he doesn't understand what the InnoDB redo log is. He seems to think log writes are buffer pool writes, which is not the case.

I don't use MySQLTuner, because so frequently its advice is useless.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • I understand, Thanks for the infos, yeah everything works perfect it was just this issue was on my mind that I didn't get it. :) – Bentix Apr 27 '22 at 17:40
1

Have to say it's funny I never thought an apparently optimal log write efficiency would be regarded as an "issue" -> [!!]

Mine is:

[!!] InnoDB Write Log efficiency: 2778.86% (667427 hits/ 24018 total)
Ricardo Mendes
  • 325
  • 3
  • 13