API tools faq
paste
manopj1

mysql tuner

manopj1
Jun 16th, 2020 (edited)
146
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 6.59 KB | None | 0 0
raw download clone embed print report
  1. >> MySQLTuner 1.7.19 - 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. [OK] Logged in using credentials from Debian maintenance account.
  7. [OK] Currently running supported MySQL version 10.4.13-MariaDB-1:10.4.13+maria~focal-log
  8. [OK] Operating on 64-bit architecture
  9.  
  10. -------- Log file Recommendations ------------------------------------------------------------------
  11. [!!] Log file doesn't exist
  12.  
  13. -------- Storage Engine Statistics -----------------------------------------------------------------
  14. [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
  15. [--] Data in InnoDB tables: 3.4G (Tables: 492)
  16. [OK] Total fragmented tables: 0
  17.  
  18. -------- Analysis Performance Metrics --------------------------------------------------------------
  19. [--] innodb_stats_on_metadata: OFF
  20. [OK] No stat updates during querying INFORMATION_SCHEMA.
  21.  
  22. -------- Security Recommendations ------------------------------------------------------------------
  23. [OK] There are no anonymous accounts for any database users
  24. [OK] All database users have passwords assigned
  25. [!!] User 'webuser@%' does not specify hostname restrictions.
  26. [!!] There is no basic password file list!
  27.  
  28. -------- CVE Security Recommendations --------------------------------------------------------------
  29. [--] Skipped due to --cvefile option undefined
  30.  
  31. -------- Performance Metrics -----------------------------------------------------------------------
  32. [--] Up for: 8h 14m 27s (69M q [2K qps], 2M conn, TX: 90G, RX: 11G)
  33. [--] Reads / Writes: 91% / 9%
  34. [--] Binary logging is enabled (GTID MODE: OFF)
  35. [--] Physical Memory : 61.7G
  36. [--] Max MySQL memory : 44.9G
  37. [--] Other process memory: 0B
  38. [--] Total buffers: 17.0G global + 27.3M per thread (1024 max threads)
  39. [--] P_S Max memory usage: 629M
  40. [--] Galera GCache Max memory usage: 0B
  41. [OK] Maximum reached memory usage: 25.7G (41.63% of installed RAM)
  42. [OK] Maximum possible memory usage: 44.9G (72.76% of installed RAM)
  43. [OK] Overall possible memory usage with other process is compatible with memory available
  44. [!!] Slow queries: 7% (4M/69M)
  45. [OK] Highest usage of available connections: 29% (303/1024)
  46. [OK] Aborted connections: 0.00% (0/2369339)
  47. [!!] Query cache may be disabled by default due to mutex contention.
  48. [!!] Query cache efficiency: 0.0% (0 cached / 55M selects)
  49. [OK] Query cache prunes per day: 0
  50. [OK] Sorts requiring temporary tables: 0% (3 temp sorts / 4M sorts)
  51. [!!] Joins performed without indexes: 515153
  52. [!!] Temporary tables created on disk: 35% (1M on disk / 5M total)
  53. [OK] Thread cache hit rate: 99% (303 created / 2M connections)
  54. [OK] Table cache hit rate: 95% (918 open / 958 opened)
  55. [OK] table_definition_cache(2000) is upper than number of tables(652)
  56. [OK] Open file limit used: 0% (58/16K)
  57. [OK] Table locks acquired immediately: 100% (695 immediate / 695 locks)
  58. [OK] Binlog cache memory access: 99.61% (2675098 Memory / 2685670 Total)
  59.  
  60. -------- Performance schema ------------------------------------------------------------------------
  61. [--] Memory used by P_S: 629.6M
  62. [--] Sys schema isn't installed.
  63.  
  64. -------- ThreadPool Metrics ------------------------------------------------------------------------
  65. [--] ThreadPool stat is enabled.
  66. [--] Thread Pool Size: 16 thread(s).
  67. [--] Using default value is good enough for your version (10.4.13-MariaDB-1:10.4.13+maria~focal-log)
  68.  
  69. -------- MyISAM Metrics ----------------------------------------------------------------------------
  70. [!!] Key buffer used: 1.4% (7M used / 536M cache)
  71. [!!] Cannot calculate MyISAM index size - re-run script as root user
  72.  
  73. -------- InnoDB Metrics ----------------------------------------------------------------------------
  74. [--] InnoDB is enabled.
  75. [--] InnoDB Thread Concurrency: 6
  76. [OK] InnoDB File per table is activated
  77. [OK] InnoDB buffer pool / data size: 16.0G/3.4G
  78. [OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 2.0G * 2/16.0G should be equal to 25%
  79. [OK] InnoDB buffer pool instances: 16
  80. [--] Number of InnoDB Buffer Pool Chunk : 16 for 16 Buffer Pool Instance(s)
  81. [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
  82. [OK] InnoDB Read buffer efficiency: 99.99% (3738457503 hits/ 3738719747 total)
  83. [!!] InnoDB Write Log efficiency: 65.95% (5029245 hits/ 7626023 total)
  84. [OK] InnoDB log waits: 0.00% (0 waits / 2596778 writes)
  85.  
  86. -------- AriaDB Metrics ----------------------------------------------------------------------------
  87. [--] AriaDB is enabled.
  88. [OK] Aria pagecache size / total Aria indexes: 128.0M/328.0K
  89. [OK] Aria pagecache hit rate: 99.3% (275M cached / 1M reads)
  90.  
  91. -------- TokuDB Metrics ----------------------------------------------------------------------------
  92. [--] TokuDB is disabled.
  93.  
  94. -------- XtraDB Metrics ----------------------------------------------------------------------------
  95. [--] XtraDB is disabled.
  96.  
  97. -------- Galera Metrics ----------------------------------------------------------------------------
  98. [--] Galera is disabled.
  99.  
  100. -------- Replication Metrics -----------------------------------------------------------------------
  101. [--] Galera Synchronous replication: NO
  102. [--] No replication slave(s) for this server.
  103. [--] Binlog format: MIXED
  104. [--] XA support enabled: ON
  105. [--] Semi synchronous replication Master: OFF
  106. [--] Semi synchronous replication Slave: OFF
  107. [--] This is a standalone server
  108.  
  109. -------- Recommendations ---------------------------------------------------------------------------
  110. General recommendations:
  111. Restrict Host for 'webuser'@% to webuser@SpecificDNSorIp
  112. UPDATE mysql.user SET host ='SpecificDNSorIp' WHERE user='webuser' AND host ='%'; FLUSH PRIVILEGES;
  113. MySQL was started within the last 24 hours - recommendations may be inaccurate
  114. We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
  115. See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
  116. (specially the conclusions at the bottom of the page).
  117. Temporary table size is already large - reduce result set size
  118. Reduce your SELECT DISTINCT queries without LIMIT clauses
  119. Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
  120. Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB
  121. Variables to adjust:
  122. query_cache_size (=0)
  123. query_cache_type (=0)
  124. query_cache_limit (> 128K, or use smaller result sets)
  125. join_buffer_size (> 4.0M, or always use indexes with JOINs)
  126.  
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!