InnoDB Write Log efficiency above 100%
0
votes
1
answer
4184
views
After running
mysqltuner.pl
, I get an Innodb write log efficiency way above 100 %. Why? What does it mean?
There is no specific reccomendation from mysqltuner
.
DB is MariaDB 10.1.26 on Debian 9.x with 1 GB RAM.
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 64.0M/9.0M
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 8.0M * 2/64.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (116110977 hits/ 116111816 total)
[!!] InnoDB Write Log efficiency: 4857.14% (10880 hits/ 224 total)
[OK] InnoDB log waits: 0.00% (0 waits / 11104 writes)
This is the configuration of InnoDb engine:
innodb ON
innodb-adaptive-flushing TRUE
innodb-adaptive-flushing-lwm 10
innodb-adaptive-hash-index TRUE
innodb-adaptive-hash-index-partitions 1
innodb-adaptive-max-sleep-delay 150000
innodb-additional-mem-pool-size 8388608
innodb-api-bk-commit-interval 5
innodb-api-disable-rowlock FALSE
innodb-api-enable-binlog FALSE
innodb-api-enable-mdl FALSE
innodb-api-trx-level 0
innodb-autoextend-increment 64
innodb-autoinc-lock-mode 1
innodb-background-scrub-data-check-interval 3600
innodb-background-scrub-data-compressed FALSE
innodb-background-scrub-data-interval 604800
innodb-background-scrub-data-uncompressed FALSE
innodb-buf-dump-status-frequency 0
innodb-buffer-page ON
innodb-buffer-page-lru ON
innodb-buffer-pool-dump-at-shutdown FALSE
innodb-buffer-pool-dump-now FALSE
innodb-buffer-pool-dump-pct 100
innodb-buffer-pool-filename ib_buffer_pool
innodb-buffer-pool-instances 1
innodb-buffer-pool-load-abort FALSE
innodb-buffer-pool-load-at-startup FALSE
innodb-buffer-pool-load-now FALSE
innodb-buffer-pool-populate FALSE
innodb-buffer-pool-size 67108864
innodb-buffer-pool-stats ON
innodb-change-buffer-max-size 25
innodb-change-buffering all
innodb-changed-pages ON
innodb-checksum-algorithm INNODB
innodb-checksums TRUE
innodb-cleaner-lsn-age-factor HIGH_CHECKPOINT
innodb-cmp ON
innodb-cmp-per-index ON
innodb-cmp-per-index-enabled FALSE
innodb-cmp-per-index-reset ON
innodb-cmp-reset ON
innodb-cmpmem ON
innodb-cmpmem-reset ON
innodb-commit-concurrency 0
innodb-compression-algorithm zlib
innodb-compression-failure-threshold-pct 5
innodb-compression-level 6
innodb-compression-pad-pct-max 50
innodb-concurrency-tickets 5000
innodb-corrupt-table-action assert
innodb-data-file-path (No default value)
innodb-data-home-dir (No default value)
innodb-default-encryption-key-id 1
innodb-defragment FALSE
innodb-defragment-fill-factor 0.9
innodb-defragment-fill-factor-n-recs 20
innodb-defragment-frequency 40
innodb-defragment-n-pages 7
innodb-defragment-stats-accuracy 0
innodb-disable-sort-file-cache FALSE
innodb-doublewrite TRUE
innodb-empty-free-list-algorithm BACKOFF
innodb-encrypt-log FALSE
innodb-encrypt-tables OFF
innodb-encryption-rotate-key-age 1
innodb-encryption-rotation-iops 100
innodb-encryption-threads 0
innodb-fake-changes FALSE
innodb-fast-shutdown 1
innodb-fatal-semaphore-wait-threshold 600
innodb-file-format Antelope
innodb-file-format-check TRUE
innodb-file-format-max Antelope
innodb-file-io-threads 4
innodb-file-per-table TRUE
innodb-flush-log-at-timeout 1
innodb-flush-log-at-trx-commit 1
innodb-flush-method (No default value)
innodb-flush-neighbors 1
innodb-flushing-avg-loops 30
innodb-force-load-corrupted FALSE
innodb-force-primary-key FALSE
innodb-force-recovery 0
innodb-foreground-preflush EXPONENTIAL_BACKOFF
innodb-ft-aux-table (No default value)
innodb-ft-being-deleted ON
innodb-ft-cache-size 8000000
innodb-ft-config ON
innodb-ft-default-stopword ON
innodb-ft-deleted ON
innodb-ft-enable-diag-print FALSE
innodb-ft-enable-stopword TRUE
innodb-ft-index-cache ON
innodb-ft-index-table ON
innodb-ft-max-token-size 84
innodb-ft-min-token-size 3
innodb-ft-num-word-optimize 2000
innodb-ft-result-cache-limit 2000000000
innodb-ft-server-stopword-table (No default value)
innodb-ft-sort-pll-degree 2
innodb-ft-total-cache-size 640000000
innodb-ft-user-stopword-table (No default value)
innodb-idle-flush-pct 100
innodb-immediate-scrub-data-uncompressed FALSE
innodb-instrument-semaphores FALSE
innodb-io-capacity 200
innodb-io-capacity-max 18446744073709551615
innodb-kill-idle-transaction 0
innodb-large-prefix FALSE
innodb-lock-schedule-algorithm fcfs
innodb-lock-wait-timeout 50
innodb-lock-waits ON
innodb-locking-fake-changes TRUE
innodb-locks ON
innodb-locks-unsafe-for-binlog FALSE
innodb-log-arch-dir (No default value)
innodb-log-arch-expire-sec 0
innodb-log-archive FALSE
innodb-log-block-size 512
innodb-log-buffer-size 16777216
innodb-log-checksum-algorithm INNODB
innodb-log-compressed-pages TRUE
innodb-log-file-size 8388608
innodb-log-files-in-group 2
innodb-log-group-home-dir (No default value)
innodb-lru-scan-depth 1024
innodb-max-bitmap-file-size 104857600
innodb-max-changed-pages 1000000
innodb-max-dirty-pages-pct 75
innodb-max-dirty-pages-pct-lwm 0.001
innodb-max-purge-lag 0
innodb-max-purge-lag-delay 0
innodb-metrics ON
innodb-mirrored-log-groups 0
innodb-monitor-disable (No default value)
innodb-monitor-enable all
innodb-monitor-reset (No default value)
innodb-monitor-reset-all (No default value)
innodb-mtflush-threads 8
innodb-mutexes ON
innodb-old-blocks-pct 37
innodb-old-blocks-time 1000
innodb-online-alter-log-max-size 134217728
innodb-open-files 0
innodb-optimize-fulltext-only FALSE
innodb-page-size 16384
innodb-prefix-index-cluster-optimization FALSE
innodb-print-all-deadlocks FALSE
innodb-purge-batch-size 300
innodb-purge-threads 1
innodb-random-read-ahead FALSE
innodb-read-ahead-threshold 56
innodb-read-io-threads 4
innodb-read-only FALSE
innodb-replication-delay 0
innodb-rollback-on-timeout FALSE
innodb-rollback-segments 128
innodb-sched-priority-cleaner 19
innodb-scrub-log FALSE
innodb-scrub-log-speed 256
innodb-show-locks-held 10
innodb-show-verbose-locks 0
innodb-simulate-comp-failures 0
innodb-sort-buffer-size 1048576
innodb-spin-wait-delay 6
innodb-stats-auto-recalc TRUE
innodb-stats-include-delete-marked FALSE
innodb-stats-method nulls_equal
innodb-stats-modified-counter 0
innodb-stats-on-metadata FALSE
innodb-stats-persistent TRUE
innodb-stats-persistent-sample-pages 20
innodb-stats-sample-pages 8
innodb-stats-traditional TRUE
innodb-stats-transient-sample-pages 8
innodb-status-file FALSE
innodb-status-output FALSE
innodb-status-output-locks FALSE
innodb-strict-mode TRUE
innodb-support-xa TRUE
innodb-sync-array-size 1
innodb-sync-spin-loops 30
innodb-sys-columns ON
innodb-sys-datafiles ON
innodb-sys-fields ON
innodb-sys-foreign ON
innodb-sys-foreign-cols ON
innodb-sys-indexes ON
innodb-sys-semaphore-waits ON
innodb-sys-tables ON
innodb-sys-tablespaces ON
innodb-sys-tablestats ON
innodb-table-locks TRUE
innodb-tablespaces-encryption ON
innodb-tablespaces-scrubbing ON
innodb-thread-concurrency 0
innodb-thread-sleep-delay 10000
innodb-tmpdir (No default value)
innodb-track-changed-pages FALSE
innodb-trx ON
innodb-undo-directory .
innodb-undo-logs 128
innodb-undo-tablespaces 0
innodb-use-atomic-writes FALSE
innodb-use-fallocate FALSE
innodb-use-global-flush-log-at-trx-commit TRUE
innodb-use-mtflush FALSE
innodb-use-native-aio TRUE
innodb-use-stacktrace FALSE
innodb-use-sys-malloc TRUE
innodb-use-trim FALSE
innodb-write-io-threads 4
Asked by Timido
(173 rep)
Jan 7, 2018, 12:12 PM
Last activity: Jul 11, 2020, 05:31 PM
Last activity: Jul 11, 2020, 05:31 PM