API tools faq
paste
Advertisement
plattnat

Requested

plattnat
Jan 18th, 2021
93
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 10.75 KB | None | 0 0
raw download clone embed print report
  1. ./mysqltuner.pl
  2. >> MySQLTuner 1.7.20 - Major Hayden <[email protected]>
  3. >> Bug reports, feature requests, and downloads at http://mysqltuner.pl/
  4. >> Run with '--help' for additional options and output filtering
  5.  
  6. [--] Skipped version check for MySQLTuner script
  7. [OK] Currently running supported MySQL version 5.7.33
  8. [OK] Operating on 64-bit architecture
  9.  
  10. -------- Log file Recommendations ------------------------------------------------------------------
  11. [OK] Log file /var/log/mysqld.log exists
  12. [--] Log file: /var/log/mysqld.log(211K)
  13. [OK] Log file /var/log/mysqld.log is readable.
  14. [OK] Log file /var/log/mysqld.log is not empty
  15. [OK] Log file /var/log/mysqld.log is smaller than 32 Mb
  16. [!!] /var/log/mysqld.log contains 354 warning(s).
  17. [!!] /var/log/mysqld.log contains 46 error(s).
  18. [--] 24 start(s) detected in /var/log/mysqld.log
  19. [--] 1) 2021-01-18T07:52:15.495804Z 0 [Note] /usr/sbin/mysqld: ready for connections.
  20. [--] 2) 2021-01-18T00:18:51.190631Z 0 [Note] /usr/sbin/mysqld: ready for connections.
  21. [--] 3) 2021-01-18T00:18:34.063526Z 0 [Note] /usr/sbin/mysqld: ready for connections.
  22. [--] 4) 2021-01-17T20:47:50.403309Z 0 [Note] /usr/sbin/mysqld: ready for connections.
  23. [--] 5) 2021-01-17T16:36:54.163673Z 0 [Note] /usr/sbin/mysqld: ready for connections.
  24. [--] 6) 2021-01-17T14:12:33.208790Z 0 [Note] /usr/sbin/mysqld: ready for connections.
  25. [--] 7) 2021-01-15T10:25:09.524791Z 0 [Note] /usr/sbin/mysqld: ready for connections.
  26. [--] 8) 2021-01-15T09:37:07.264257Z 0 [Note] /usr/sbin/mysqld: ready for connections.
  27. [--] 9) 2021-01-14T11:30:59.930120Z 0 [Note] /usr/sbin/mysqld: ready for connections.
  28. [--] 10) 2021-01-14T11:08:52.530228Z 0 [Note] /usr/sbin/mysqld: ready for connections.
  29. [--] 12 shutdown(s) detected in /var/log/mysqld.log
  30. [--] 1) 2021-01-18T07:50:30.130362Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
  31. [--] 2) 2021-01-18T00:18:48.895307Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
  32. [--] 3) 2021-01-18T00:18:29.135485Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
  33. [--] 4) 2021-01-15T10:25:04.851885Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
  34. [--] 5) 2021-01-15T09:37:02.771313Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
  35. [--] 6) 2021-01-14T11:27:51.895559Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
  36. [--] 7) 2021-01-14T11:08:49.145150Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
  37. [--] 8) 2021-01-14T11:06:24.969477Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
  38. [--] 9) 2021-01-14T10:58:53.419431Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
  39. [--] 10) 2021-01-14T09:35:54.472640Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
  40.  
  41. -------- Storage Engine Statistics -----------------------------------------------------------------
  42. [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
  43. [--] Data in InnoDB tables: 535.2M (Tables: 445)
  44. [OK] Total fragmented tables: 0
  45.  
  46. -------- Analysis Performance Metrics --------------------------------------------------------------
  47. [--] innodb_stats_on_metadata: OFF
  48. [OK] No stat updates during querying INFORMATION_SCHEMA.
  49.  
  50. -------- Security Recommendations ------------------------------------------------------------------
  51. [OK] There are no anonymous accounts for any database users
  52. [OK] All database users have passwords assigned
  53. [!!] There is no basic password file list!
  54.  
  55. -------- CVE Security Recommendations --------------------------------------------------------------
  56. [--] Skipped due to --cvefile option undefined
  57.  
  58. -------- Performance Metrics -----------------------------------------------------------------------
  59. [--] Up for: 2h 20m 20s (751K q [89.279 qps], 4K conn, TX: 5G, RX: 295M)
  60. [--] Reads / Writes: 89% / 11%
  61. [--] Binary logging is disabled
  62. [--] Physical Memory : 2.8G
  63. [--] Max MySQL memory : 38.1G
  64. [--] Other process memory: 0B
  65. [--] Total buffers: 169.0M global + 257.1M per thread (151 max threads)
  66. [--] P_S Max memory usage: 72B
  67. [--] Galera GCache Max memory usage: 0B
  68. [!!] Maximum reached memory usage: 3.7G (133.00% of installed RAM)
  69. [!!] Maximum possible memory usage: 38.1G (1376.09% of installed RAM)
  70. [!!] Overall possible memory usage with other process exceeded memory
  71. [OK] Slow queries: 0% (0/751K)
  72. [OK] Highest usage of available connections: 9% (14/151)
  73. [OK] Aborted connections: 0.04% (2/4681)
  74. [!!] Query cache may be disabled by default due to mutex contention.
  75. [!!] Query cache efficiency: 0.0% (0 cached / 646K selects)
  76. [OK] Query cache prunes per day: 0
  77. [OK] Sorts requiring temporary tables: 0% (32 temp sorts / 64K sorts)
  78. [OK] No joins without indexes
  79. [!!] Temporary tables created on disk: 74% (37K on disk / 49K total)
  80. [OK] Thread cache hit rate: 99% (33 created / 4K connections)
  81. [!!] Table cache hit rate: 8% (1K open / 21K opened)
  82. [OK] table_definition_cache(1400) is upper than number of tables(724)
  83. [OK] Open file limit used: 0% (32/40K)
  84. [OK] Table locks acquired immediately: 100% (462 immediate / 462 locks)
  85.  
  86. -------- Performance schema ------------------------------------------------------------------------
  87. [--] Memory used by P_S: 72B
  88. [--] Sys schema is installed.
  89.  
  90. -------- ThreadPool Metrics ------------------------------------------------------------------------
  91. [--] ThreadPool stat is disabled.
  92.  
  93. -------- MyISAM Metrics ----------------------------------------------------------------------------
  94. [!!] Key buffer used: 18.3% (1M used / 8M cache)
  95. [OK] Key buffer size / total MyISAM indexes: 8.0M/43.0K
  96. [!!] Read Key buffer hit rate: 89.5% (656 cached / 69 reads)
  97.  
  98. -------- InnoDB Metrics ----------------------------------------------------------------------------
  99. [--] InnoDB is enabled.
  100. [--] InnoDB Thread Concurrency: 0
  101. [OK] InnoDB File per table is activated
  102. [!!] InnoDB buffer pool / data size: 128.0M/535.2M
  103. [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal to 25%
  104. [OK] InnoDB buffer pool instances: 1
  105. [--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
  106. [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
  107. [OK] InnoDB Read buffer efficiency: 99.99% (76785770 hits/ 76790596 total)
  108. [!!] InnoDB Write Log efficiency: 47.76% (68173 hits/ 142749 total)
  109. [OK] InnoDB log waits: 0.00% (0 waits / 74576 writes)
  110.  
  111. -------- AriaDB Metrics ----------------------------------------------------------------------------
  112. [--] AriaDB is disabled.
  113.  
  114. -------- TokuDB Metrics ----------------------------------------------------------------------------
  115. [--] TokuDB is disabled.
  116.  
  117. -------- XtraDB Metrics ----------------------------------------------------------------------------
  118. [--] XtraDB is disabled.
  119.  
  120. -------- Galera Metrics ----------------------------------------------------------------------------
  121. [--] Galera is disabled.
  122.  
  123. -------- Replication Metrics -----------------------------------------------------------------------
  124. [--] Galera Synchronous replication: NO
  125. [--] No replication slave(s) for this server.
  126. [--] Binlog format: ROW
  127. [--] XA support enabled: ON
  128. [--] Semi synchronous replication Master: Not Activated
  129. [--] Semi synchronous replication Slave: Not Activated
  130. [--] This is a standalone server
  131.  
  132. -------- Recommendations ---------------------------------------------------------------------------
  133. General recommendations:
  134. Control warning line(s) into /var/log/mysqld.log file
  135. Control error line(s) into /var/log/mysqld.log file
  136. MySQL was started within the last 24 hours - recommendations may be inaccurate
  137. Reduce your overall MySQL memory footprint for system stability
  138. Dedicate this server to your database for highest performance.
  139. When making adjustments, make tmp_table_size/max_heap_table_size equal
  140. Reduce your SELECT DISTINCT queries which have no LIMIT clause
  141. Increase table_open_cache gradually to avoid file descriptor limits
  142. Read this before increasing table_open_cache over 64: https://bit.ly/2Fulv7r
  143. Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
  144. This is MyISAM only table_cache scalability problem, InnoDB not affected.
  145. See more details here: https://bugs.mysql.com/bug.php?id=49177
  146. This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
  147. Beware that open_files_limit (40000) variable
  148. should be greater than table_open_cache (2000)
  149. Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
  150. Variables to adjust:
  151. *** MySQL's maximum memory usage is dangerously high ***
  152. *** Add RAM before increasing MySQL buffer variables ***
  153. query_cache_size (=0)
  154. query_cache_type (=0)
  155. query_cache_limit (> 1M, or use smaller result sets)
  156. tmp_table_size (> 16M)
  157. max_heap_table_size (> 16M)
  158. table_open_cache (> 2000)
  159. innodb_buffer_pool_size (>= 535.2M) if possible.
  160. innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
  161.  
  162. ulimit -a
  163. core file size (blocks, -c) 1000000
  164. data seg size (kbytes, -d) unlimited
  165. scheduling priority (-e) 0
  166. file size (blocks, -f) unlimited
  167. pending signals (-i) 11115
  168. max locked memory (kbytes, -l) 64
  169. max memory size (kbytes, -m) unlimited
  170. open files (-n) 4096
  171. pipe size (512 bytes, -p) 8
  172. POSIX message queues (bytes, -q) 819200
  173. real-time priority (-r) 0
  174. stack size (kbytes, -s) 8192
  175. cpu time (seconds, -t) unlimited
  176. max user processes (-u) 14335
  177. virtual memory (kbytes, -v) unlimited
  178. file locks (-x) unlimited
  179.  
  180. iostat -xm 5 3
  181. Linux 3.10.0-1160.11.1.el7.x86_64 (31-22-2-109.cprapid.com) 01/18/2021 _x86_64_ (4 CPU)
  182.  
  183. avg-cpu: %user %nice %system %iowait %steal %idle
  184. 15.59 0.22 6.61 1.18 0.16 76.24
  185.  
  186. Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
  187. xvda 0.45 19.17 24.34 53.02 0.64 0.62 33.47 0.66 8.58 19.63 3.51 0.66 5.10
  188.  
  189. avg-cpu: %user %nice %system %iowait %steal %idle
  190. 10.37 0.00 3.46 0.55 0.10 85.52
  191.  
  192. Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
  193. xvda 0.00 7.20 0.20 59.60 0.00 1.61 55.04 0.41 6.86 1.00 6.88 0.48 2.88
  194.  
  195. avg-cpu: %user %nice %system %iowait %steal %idle
  196. 5.21 0.00 1.95 0.10 0.05 92.69
  197.  
  198. Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
  199. xvda 0.00 5.40 0.00 11.40 0.00 0.20 36.63 0.02 2.07 0.00 2.07 0.60 0.68
  200.  
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!