Meaning: InnoDB Write Log efficiency

[anatoli26]

I'm getting:

[!!] InnoDB Write Log efficiency: 22.65% (23994 hits/ 105927 total)

But I don't see any suggestions below on how to improve it. So, what does "InnoDB Write Log efficiency" mean and how to improve it?

[jmrenouard]

This means that when you try to write something 22,6% of the time you can write information into Buffer pool with efficiency.

So, it's means you can improve your write log efficiency by performing a warmup of most written data.
But it's a very too large reco' to be true in all cases.

[anatoli26]

So basically with time it should improve on its own as more data is used? Do the pool and log size variables like innodb_buffer_pool_size, innodb_buffer_pool_instances, binlog_cache_size and innodb_log_buffer_size have any impact on this efficiency?

[jmrenouard]

Hi @anatoli26
IHMO, innodb_buffer_pool_size is the main parameter having the greatest impact on this ratio.

BR,

[anatoli26]

@jmrenouard, I already have it at 2Gb for a DB that occupies on disk 1.3Gb. The previous version some time ago recommended to reduce the innodb_log_file_size value (I had it at 512Mb, reduced it to 256Mb), then some months after the change I noticed the new warning (about the Write Log efficiency) with the new version, so I'm not sure what to do, it's a bit confusing :)

[jmrenouard]

Hi @anatoli26

Yes in fact, in tuning advices, there is sometime not ideal situation.
In fact, InnoDb write Log Effiency is not a major tuning items regarding innodb log file size and innodb buffer pool size.

Actually, I will leave it as is to get this metrics.
The main goal is not to get a perfect match all situation configurations tool but to be sure we get all the metrics that can help us to tune nicely MySQL and MariaDB in most of the cases and be sure you can decide by your own wich choice to make.

[jmrenouard]

Hi @anatoli26

If you want to increase your write efficiency, in your case ( more Buffer pool than data + index), just warmup your data.

In order to warmup your data, just select * from table (for all your tables).

Br,

[anatoli26]

Thanks!

[ChristopherSchultz]

How can "warming-up your data" (by reading) improve the efficiency of writes? If I knew what data I was going to write tomorrow, I would certainly write it today to save time :)

[cyford]

Select are fast and writes logs.. u have no logs written so would imagine selecting all quires would help mature this

How can "warming-up your data" (by reading) improve the efficiency of writes? If I knew what data I was going to write tomorrow, I would certainly write it today to save time :)

[seebeen]

For anyone coming here via google or other search engines. Write log efficiency metric in MySQL tuner is completely useless, and you should ignore it unless it's near 100% (which means there is something terribly wroing with your mysql config.

For my mysql server I have the following details:

Some pertinent statuses from mysql -e "show status" are:

Innodb_redo_log_uuid    1927746176
Innodb_redo_log_checkpoint_lsn  19033284074862
Innodb_redo_log_current_lsn     19034019523571
Innodb_redo_log_flushed_to_disk_lsn     19034019249101
Innodb_redo_log_logical_size    735449088
Innodb_redo_log_physical_size   7247757312
Innodb_redo_log_capacity_resized        8589934592
Innodb_redo_log_resize_status   OK
Innodb_log_waits        0
Innodb_log_write_requests       2357836735
Innodb_log_writes       1029426786
Innodb_os_log_fsyncs    6793467
Innodb_os_log_pending_fsyncs    0
Innodb_os_log_pending_writes    0
Innodb_os_log_written   4312958394880
Innodb_redo_log_enabled ON

Let's dive in what is what here.

• Innodb_redo_log_read_only: Shows if the redo log is in read-only mode.
  OFF means it's not in read-only mode, which is normal for a writable database.

• Innodb_redo_log_uuid: A unique identifier for the redo log.
  This is just an identifier and doesn't indicate performance.

• Innodb_redo_log_checkpoint_lsn: The Log Sequence Number (LSN) up to which the data has been written to the data files.
  This is a checkpoint indicator.

• Innodb_redo_log_current_lsn: The current LSN.
  This is the point up to which the log records have been generated.

• Innodb_redo_log_flushed_to_disk_lsn: Indicates the LSN up to which the log has been flushed to disk.
  It's important for data durability.

• Innodb_redo_log_logical_size: The logical size of the redo log.
  This value indicates the total size of log records generated.

• Innodb_redo_log_physical_size: The physical size of the redo log files on disk.
  This is the actual size allocated for logs.

• Innodb_redo_log_capacity_resized: The size to which the redo log capacity was resized.
  This shows the capacity after a resize operation, if any.

• Innodb_redo_log_resize_status: Shows the status of a redo log resize operation.
  OK indicates no issues.

• Innodb_log_waits: Indicates the number of waits due to log buffer being too small.
  0 is ideal, meaning no waits.

• Innodb_log_write_requests: The number of write requests for the log.
  VALUE USED TO CALCULATE WRITE EFFICIENCY

• Innodb_log_writes: The actual number of physical writes to the log.
  VALUE USED TO CALCULATE WRITE EFFICIENCY

• Innodb_os_log_fsyncs: The number of fsync() write operations to the log file.
  This indicates how often the log is synchronized to disk.

• Innodb_os_log_pending_fsyncs: The number of pending fsync operations.
  0 is good, indicating no backlog.

• Innodb_os_log_pending_writes: Number of pending log file writes.
  0 means there is no current write backlog.

• Innodb_os_log_written: The total number of bytes written to the log file.

• Innodb_redo_log_enabled: Shows if the redo log is enabled.
  ON is normal for a functioning database.

The ratio of actual physical writes (Innodb_log_writes) to write requests (Innodb_log_write_requests) is approximately 0.437. This means that for every write request, about 43.7% result in a physical write to the log.

The percentage difference between the number of write requests and the actual physical writes is about 56.34%. This indicates that around 56.34% of the write requests did not result in a physical write to the log. Which is GOOD.

Having a 100% ratio of physical writes (Innodb_log_writes) to write requests (Innodb_log_write_requests) in a database is neither typical nor necessarily desirable.

If the ratio were 100%, it would imply that every write request results in a physical write to disk. This could lead to excessive disk I/O, which is one of the most common bottlenecks in database performance.