API tools faq
paste
Advertisement
interscot

MySQLTuner

interscot
Aug 26th, 2018
117
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 9.23 KB | None | 0 0
raw download clone embed print report
  1. >> MySQLTuner 1.7.9 - 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] Currently running supported MySQL version 5.1.73
  7. [OK] Operating on 64-bit architecture
  8.  
  9. -------- Log file Recommendations ------------------------------------------------------------------
  10. [--] Log file: /var/log/mysqld.log(1M)
  11. [OK] Log file /var/log/mysqld.log exists
  12. [OK] Log file /var/log/mysqld.log is readable.
  13. [OK] Log file /var/log/mysqld.log is not empty
  14. [OK] Log file /var/log/mysqld.log is smaller than 32 Mb
  15. [!!] /var/log/mysqld.log contains 35 warning(s).
  16. [!!] /var/log/mysqld.log contains 3988 error(s).
  17. [--] 59 start(s) detected in /var/log/mysqld.log
  18. [--] 1) 180822 1:51:10 [Note] /usr/libexec/mysqld: ready for connections.
  19. [--] 2) 180714 3:41:10 [Note] /usr/libexec/mysqld: ready for connections.
  20. [--] 3) 180601 1:24:35 [Note] /usr/libexec/mysqld: ready for connections.
  21. [--] 4) 180514 1:02:14 [Note] /usr/libexec/mysqld: ready for connections.
  22. [--] 5) 180326 1:41:37 [Note] /usr/libexec/mysqld: ready for connections.
  23. [--] 6) 180320 1:49:41 [Note] /usr/libexec/mysqld: ready for connections.
  24. [--] 7) 180308 1:18:52 [Note] /usr/libexec/mysqld: ready for connections.
  25. [--] 8) 180207 1:11:08 [Note] /usr/libexec/mysqld: ready for connections.
  26. [--] 9) 180122 1:26:23 [Note] /usr/libexec/mysqld: ready for connections.
  27. [--] 10) 180107 1:30:58 [Note] /usr/libexec/mysqld: ready for connections.
  28. [--] 59 shutdown(s) detected in /var/log/mysqld.log
  29. [--] 1) 180822 1:46:34 [Note] /usr/libexec/mysqld: Shutdown complete
  30. [--] 2) 180714 3:38:04 [Note] /usr/libexec/mysqld: Shutdown complete
  31. [--] 3) 180601 1:21:33 [Note] /usr/libexec/mysqld: Shutdown complete
  32. [--] 4) 180514 0:59:09 [Note] /usr/libexec/mysqld: Shutdown complete
  33. [--] 5) 180326 1:39:00 [Note] /usr/libexec/mysqld: Shutdown complete
  34. [--] 6) 180320 1:46:39 [Note] /usr/libexec/mysqld: Shutdown complete
  35. [--] 7) 180308 1:16:29 [Note] /usr/libexec/mysqld: Shutdown complete
  36. [--] 8) 180308 1:07:59 [Note] /usr/libexec/mysqld: Shutdown complete
  37. [--] 9) 180207 1:08:15 [Note] /usr/libexec/mysqld: Shutdown complete
  38. [--] 10) 180122 1:23:40 [Note] /usr/libexec/mysqld: Shutdown complete
  39.  
  40. -------- Storage Engine Statistics -----------------------------------------------------------------
  41. [--] Status: +CSV +InnoDB +MRG_MYISAM
  42. [--] Data in MyISAM tables: 623.4M (Tables: 202)
  43. [--] Data in InnoDB tables: 9.6G (Tables: 2904)
  44. [--] Data in MEMORY tables: 51.2M (Tables: 102)
  45. [OK] Total fragmented tables: 0
  46.  
  47. -------- Security Recommendations ------------------------------------------------------------------
  48. [OK] There are no anonymous accounts for any database users
  49. [OK] All database users have passwords assigned
  50. [!!] User 'anupam@%' does not specify hostname restrictions.
  51. [!!] User 'dev-3@%' does not specify hostname restrictions.
  52. [!!] User 'fcl-dev-2@%' does not specify hostname restrictions.
  53. [!!] User 'fcl-dev-4@%' does not specify hostname restrictions.
  54. [!!] User 'fcl-dev-5@%' does not specify hostname restrictions.
  55. [!!] User 'fcl-dev@%' does not specify hostname restrictions.
  56. [!!] User 'fcl@%' does not specify hostname restrictions.
  57. [!!] User 'magento_e@%' does not specify hostname restrictions.
  58. [!!] There is no basic password file list!
  59.  
  60. -------- CVE Security Recommendations --------------------------------------------------------------
  61. [--] Skipped due to --cvefile option undefined
  62.  
  63. -------- Performance Metrics -----------------------------------------------------------------------
  64. [--] Up for: 4d 18h 37m 12s (19M q [46.994 qps], 277K conn, TX: 30G, RX: 8G)
  65. [--] Reads / Writes: 86% / 14%
  66. [--] Binary logging is disabled
  67. [--] Physical Memory : 62.9G
  68. [--] Max MySQL memory : 26.6G
  69. [--] Other process memory: 1.8G
  70. [--] Total buffers: 16.4G global + 52.2M per thread (200 max threads)
  71. [--] P_S Max memory usage: 0B
  72. [--] Galera GCache Max memory usage: 0B
  73. [OK] Maximum reached memory usage: 16.9G (26.95% of installed RAM)
  74. [OK] Maximum possible memory usage: 26.6G (42.29% of installed RAM)
  75. [OK] Overall possible memory usage with other process is compatible with memory available
  76. [OK] Slow queries: 0% (4/19M)
  77. [OK] Highest usage of available connections: 5% (11/200)
  78. [OK] Aborted connections: 0.00% (1/277537)
  79. [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
  80. [!!] Query cache may be disabled by default due to mutex contention.
  81. [!!] Query cache efficiency: 0.0% (0 cached / 13M selects)
  82. [OK] Query cache prunes per day: 0
  83. [OK] Sorts requiring temporary tables: 0% (165 temp sorts / 1M sorts)
  84. [!!] Joins performed without indexes: 71106
  85. [!!] Temporary tables created on disk: 71% (3M on disk / 4M total)
  86. [OK] Thread cache hit rate: 99% (1K created / 277K connections)
  87. [!!] Table cache hit rate: 0% (781 open / 85K opened)
  88. [OK] Open file limit used: 0% (105/65K)
  89. [OK] Table locks acquired immediately: 99% (22M immediate / 22M locks)
  90.  
  91. -------- Performance schema ------------------------------------------------------------------------
  92. [--] Performance schema is disabled.
  93. [--] Memory used by P_S: 0B
  94. [--] Sys schema isn't installed.
  95.  
  96. -------- ThreadPool Metrics ------------------------------------------------------------------------
  97. [--] ThreadPool stat is disabled.
  98.  
  99. -------- MyISAM Metrics ----------------------------------------------------------------------------
  100. [!!] Key buffer used: 41.5% (27M used / 67M cache)
  101. [OK] Key buffer size / total MyISAM indexes: 64.0M/167.3M
  102. [OK] Read Key buffer hit rate: 99.7% (151M cached / 424K reads)
  103. [!!] Write Key buffer hit rate: 14.0% (8M cached / 1M writes)
  104.  
  105. -------- InnoDB Metrics ----------------------------------------------------------------------------
  106. [--] InnoDB is enabled.
  107. [--] InnoDB Thread Concurrency: 10
  108. [!!] InnoDB File per table is not activated
  109. [OK] InnoDB buffer pool / data size: 16.0G/9.6G
  110. [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (3.125 %): 256.0M * 2/16.0G should be equal 25%
  111. [--] InnoDB Buffer Pool Chunk Size not used or defined in your version
  112. [OK] InnoDB Read buffer efficiency: 100.00% (7294973329 hits/ 7295148290 total)
  113. [OK] InnoDB Write log efficiency: 93.98% (15234542 hits/ 16209739 total)
  114. [!!] InnoDB log waits: 0.01% (78 waits / 975197 writes)
  115.  
  116. -------- AriaDB Metrics ----------------------------------------------------------------------------
  117. [--] AriaDB is disabled.
  118.  
  119. -------- TokuDB Metrics ----------------------------------------------------------------------------
  120. [--] TokuDB is disabled.
  121.  
  122. -------- XtraDB Metrics ----------------------------------------------------------------------------
  123. [--] XtraDB is disabled.
  124.  
  125. -------- RocksDB Metrics ---------------------------------------------------------------------------
  126. [--] RocksDB is disabled.
  127.  
  128. -------- Spider Metrics ----------------------------------------------------------------------------
  129. [--] Spider is disabled.
  130.  
  131. -------- Connect Metrics ---------------------------------------------------------------------------
  132. [--] Connect is disabled.
  133.  
  134. -------- Galera Metrics ----------------------------------------------------------------------------
  135. [--] Galera is disabled.
  136.  
  137. -------- Replication Metrics -----------------------------------------------------------------------
  138. [--] Galera Synchronous replication: NO
  139. [--] No replication slave(s) for this server.
  140. [--] Binlog format: STATEMENT
  141. [--] XA support enabled: ON
  142. [--] Semi synchronous replication Master: Not Activated
  143. [--] Semi synchronous replication Slave: Not Activated
  144. [--] This is a standalone server
  145.  
  146. -------- Recommendations ---------------------------------------------------------------------------
  147. General recommendations:
  148. Control warning line(s) into /var/log/mysqld.log file
  149. Control error line(s) into /var/log/mysqld.log file
  150. Restrict Host for user@% to user@SpecificDNSorIp
  151. Enable the slow query log to troubleshoot bad queries
  152. Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
  153. Adjust your join queries to always utilize indexes
  154. Temporary table size is already large - reduce result set size
  155. Reduce your SELECT DISTINCT queries without LIMIT clauses
  156. Increase table_open_cache gradually to avoid file descriptor limits
  157. Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
  158. This is MyISAM only table_cache scalability problem, InnoDB not affected.
  159. See more details here: https://bugs.mysql.com/bug.php?id=49177
  160. This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
  161. Beware that open_files_limit (65535) variable
  162. should be greater than table_open_cache (2000)
  163. Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: http://bit.ly/2wgkDvS
  164. Variables to adjust:
  165. query_cache_size (=0)
  166. query_cache_type (=0)
  167. query_cache_limit (> 2M, or use smaller result sets)
  168. join_buffer_size (> 16.0M, or always use indexes with JOINs)
  169. table_open_cache (> 2000)
  170. innodb_file_per_table=ON
  171. innodb_log_file_size should be (=2G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
  172. innodb_log_buffer_size (>= 8M)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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!