manopj1

mysql tuner

Jun 16th, 2020 (edited)
82
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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.  
RAW Paste Data