Innodb
InnoDB Write Log 效率超過 100%
執行後
mysqltuner.pl，我得到了 100% 以上的 Innodb 寫入日誌效率方式。為什麼？這是什麼意思？沒有具體的推薦mysqltuner。DB 是 Debian 9.x 上的 MariaDB 10.1.26，具有 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)這是 InnoDb 引擎的配置：
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
這似乎是從
abs(Innodb_log_write_requests - Innodb_log_writes) hits / Innodb_log_write_requests total值來自哪裡
SHOW GLOBAL STATUS。“…_write_requests”是 InnoDB 需要在磁碟上向 iblog 寫入內容的時候。但是，寫入被緩沖在 buffer_pool 中，希望通過一次寫入（“…_writes”）將多個內容收集在一起。
通常，多個“請求”最終會導致單個“寫入”。
“abs()”是隱藏負數（寫比請求多）。這些沒有意義，但似乎確實發生了。
在我檢查的一百台伺服器中，沒有一個超過 100%。
我會忽略這個指標——mysqltuner 有問題，而且 InnoDB 似乎在用這個數字做一些額外的事情，以使它們在以這種方式進行比較時不太有用。
當您只有 9MB 的數據 + 索引時，很難解決任何性能問題（調整可以解決）。