herhor67

MYSQL tuner for dba.stack

Jul 29th, 2023 (edited)
143
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 7.80 KB | None | 0 0
  1. >> MySQLTuner 1.7.17 - Major Hayden <major@mhtx.net>
  2. >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
  3. >> Run with '--help' for additional options and output filtering
  4.  
  5. [--] Skipped version check for MySQLTuner script
  6. Please enter your MySQL administrative login: root
  7. Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 8.0.33-0ubuntu0.22.04.2
  8. [OK] Operating on 64-bit architecture
  9.  
  10. -------- Log file Recommendations ------------------------------------------------------------------
  11. [OK] Log file /var/log/mysql/error.log exists
  12. [--] Log file: /var/log/mysql/error.log(0B)
  13. [OK] Log file /var/log/mysql/error.log is readable.
  14. [!!] Log file /var/log/mysql/error.log is empty
  15. [OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
  16. [OK] /var/log/mysql/error.log doesn't contain any warning.
  17. [OK] /var/log/mysql/error.log doesn't contain any error.
  18. [--] 0 start(s) detected in /var/log/mysql/error.log
  19. [--] 0 shutdown(s) detected in /var/log/mysql/error.log
  20.  
  21. -------- Storage Engine Statistics -----------------------------------------------------------------
  22. [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
  23. [--] Data in InnoDB tables: 55.0G (Tables: 4467)
  24. [!!] Total fragmented tables: 16
  25.  
  26. -------- Analysis Performance Metrics --------------------------------------------------------------
  27. [--] innodb_stats_on_metadata: OFF
  28. [OK] No stat updates during querying INFORMATION_SCHEMA.
  29.  
  30. -------- Security Recommendations ------------------------------------------------------------------
  31. [--] Skipped due to unsupported feature for MySQL 8
  32.  
  33. -------- CVE Security Recommendations --------------------------------------------------------------
  34. [OK] NO SECURITY CVE FOUND FOR YOUR VERSION
  35.  
  36. -------- Performance Metrics -----------------------------------------------------------------------
  37. [--] Up for: 1d 17h 6m 52s (21K q [0.148 qps], 43 conn, TX: 63M, RX: 35M)
  38. [--] Reads / Writes: 0% / 100%
  39. [--] Binary logging is disabled
  40. [--] Physical Memory : 15.4G
  41. [--] Max MySQL memory : 4.3G
  42. [--] Other process memory: 0B
  43. [--] Total buffers: 4.0G global + 1.9M per thread (151 max threads)
  44. [--] P_S Max memory usage: 72B
  45. [--] Galera GCache Max memory usage: 0B
  46. [OK] Maximum reached memory usage: 4.1G (26.38% of installed RAM)
  47. [OK] Maximum possible memory usage: 4.3G (28.09% of installed RAM)
  48. [OK] Overall possible memory usage with other process is compatible with memory available
  49. [OK] Slow queries: 0% (23/21K)
  50. [OK] Highest usage of available connections: 4% (7/151)
  51. [!!] Aborted connections: 4.65% (2/43)
  52. [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
  53. [--] Query cache have been removed in MySQL 8
  54. [!!] Sorts requiring temporary tables: 19% (1K temp sorts / 9K sorts)
  55. [OK] No joins without indexes
  56. [OK] Temporary tables created on disk: 0% (0 on disk / 9K total)
  57. [OK] Thread cache hit rate: 83% (7 created / 43 connections)
  58. [!!] Table cache hit rate: 6% (940 open / 13K opened)
  59. [OK] Open file limit used: 0% (0/10K)
  60. [OK] Table locks acquired immediately: 100% (55 immediate / 55 locks)
  61.  
  62. -------- Performance schema ------------------------------------------------------------------------
  63. [--] Memory used by P_S: 72B
  64. [--] Sys schema is installed.
  65.  
  66. -------- ThreadPool Metrics ------------------------------------------------------------------------
  67. [--] ThreadPool stat is disabled.
  68.  
  69. -------- MyISAM Metrics ----------------------------------------------------------------------------
  70. [--] MyISAM Metrics are disabled on last MySQL versions.
  71.  
  72. -------- InnoDB Metrics ----------------------------------------------------------------------------
  73. [--] InnoDB is enabled.
  74. [--] InnoDB Thread Concurrency: 0
  75. [OK] InnoDB File per table is activated
  76. [!!] InnoDB buffer pool / data size: 4.0G/55.0G
  77. [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (2.34375 %): 48.0M * 2/4.0G should be equal to 25%
  78. [!!] InnoDB buffer pool instances: 8
  79. [--] Number of InnoDB Buffer Pool Chunk : 32 for 8 Buffer Pool Instance(s)
  80. [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
  81. [OK] InnoDB Read buffer efficiency: 99.58% (474767698 hits/ 476760981 total)
  82. [OK] InnoDB Write log efficiency: 97.46% (189292863 hits/ 194217238 total)
  83. [OK] InnoDB log waits: 0.00% (0 waits / 4924375 writes)
  84.  
  85. -------- AriaDB Metrics ----------------------------------------------------------------------------
  86. [--] AriaDB is disabled.
  87.  
  88. -------- TokuDB Metrics ----------------------------------------------------------------------------
  89. [--] TokuDB is disabled.
  90.  
  91. -------- XtraDB Metrics ----------------------------------------------------------------------------
  92. [--] XtraDB is disabled.
  93.  
  94. -------- Galera Metrics ----------------------------------------------------------------------------
  95. [--] Galera is disabled.
  96.  
  97. -------- Replication Metrics -----------------------------------------------------------------------
  98. [--] Galera Synchronous replication: NO
  99. [--] No replication slave(s) for this server.
  100. [--] Binlog format: ROW
  101. [--] XA support enabled: ON
  102. [--] Semi synchronous replication Master: Not Activated
  103. [--] Semi synchronous replication Slave: Not Activated
  104. [--] This is a standalone server
  105.  
  106. -------- Recommendations ---------------------------------------------------------------------------
  107. General recommendations:
  108. Run OPTIMIZE TABLE to defragment tables for better performance
  109. OPTIMIZE TABLE `wot_players`.`accounts_a`; -- can free 104 MB
  110. OPTIMIZE TABLE `wot_players`.`accounts_c`; -- can free 118 MB
  111. OPTIMIZE TABLE `wot_players`.`accounts_e`; -- can free 696 MB
  112. OPTIMIZE TABLE `wot_players`.`accounts_n`; -- can free 184 MB
  113. OPTIMIZE TABLE `wot_players`.`accounts_r`; -- can free 822 MB
  114. OPTIMIZE TABLE `wot_players`.`tank_1041_r`; -- can free 12 MB
  115. OPTIMIZE TABLE `wot_players`.`tank_10529_r`; -- can free 24 MB
  116. OPTIMIZE TABLE `wot_players`.`tank_11265_r`; -- can free 17 MB
  117. OPTIMIZE TABLE `wot_players`.`tank_11553_r`; -- can free 17 MB
  118. OPTIMIZE TABLE `wot_players`.`tank_14145_r`; -- can free 17 MB
  119. OPTIMIZE TABLE `wot_players`.`tank_18193_r`; -- can free 13 MB
  120. OPTIMIZE TABLE `wot_players`.`tank_3585_r`; -- can free 26 MB
  121. OPTIMIZE TABLE `wot_players`.`tank_513_r`; -- can free 26 MB
  122. OPTIMIZE TABLE `wot_players`.`tank_5377_r`; -- can free 29 MB
  123. OPTIMIZE TABLE `wot_players`.`tank_6465_r`; -- can free 21 MB
  124. OPTIMIZE TABLE `wot_players`.`tank_6657_r`; -- can free 21 MB
  125. Total freed space after theses OPTIMIZE TABLE : 2147 Mb
  126. Reduce or eliminate unclosed connections and network issues
  127. Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
  128. Increase table_open_cache gradually to avoid file descriptor limits
  129. Read this before increasing table_open_cache over 64: https://bit.ly/1mi7c4C
  130. Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
  131. This is MyISAM only table_cache scalability problem, InnoDB not affected.
  132. See more details here: https://bugs.mysql.com/bug.php?id=49177
  133. This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
  134. Beware that open_files_limit (10000) variable
  135. should be greater than table_open_cache (4919)
  136. Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
  137. Variables to adjust:
  138. sort_buffer_size (> 256K)
  139. read_rnd_buffer_size (> 256K)
  140. table_open_cache (> 4919)
  141. innodb_buffer_pool_size (>= 55.0G) if possible.
  142. innodb_log_file_size should be (=512M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
  143. innodb_buffer_pool_instances(=4)
Add Comment
Please, Sign In to add comment