manopj1

tuningprimer.sh

Aug 16th, 2020
33
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- MYSQL PERFORMANCE TUNING PRIMER 2.0.1-r1 --
  2. - By: Matthew Montgomery -
  3. - By: Markus Kohlmeyer -
  4.  
  5. MySQL Version 10.4.13-MariaDB-1:10.4.13+maria~focal-log x86_64
  6.  
  7. Uptime = 0 days 9 hrs 3 min 0 sec
  8. Avg. qps = 2189
  9. Total Questions = 71321310
  10. Threads Connected = 2
  11.  
  12. Warning: Server has not been running for at least 48hrs.
  13. It may not be safe to use these recommendations
  14.  
  15. To find out more information on how each of these
  16. runtime variables effects performance visit:
  17. http://dev.mysql.com/doc/refman/10.4/en/server-system-variables.html
  18. Visit http://www.mysql.com/products/enterprise/advisors.html
  19. for info about MySQL's Enterprise Monitoring and Advisory Service
  20.  
  21. SLOW QUERIES
  22. The slow query log is NOT enabled.
  23. Current long_query_time = 5.000000 sec.
  24. You have 5057912 out of 71321324 that take longer than 5.000000 sec. to complete
  25. Your long_query_time seems to be fine
  26.  
  27. BINARY UPDATE LOG
  28. The binary update log is enabled
  29.  
  30. WORKER THREADS
  31. Current thread_cache_size = 128
  32. Current threads_cached = 126
  33. Current threads_per_sec = 0
  34. Historic threads_per_sec = 0
  35. Your thread_cache_size is fine
  36.  
  37. MAX CONNECTIONS
  38. Current max_connections = 1024
  39. Current threads_connected = 2
  40. Historic max_used_connections = 303
  41. The number of used connections is 29% of the configured maximum.
  42. Your max_connections variable seems to be fine.
  43.  
  44. INNODB STATUS
  45. Current InnoDB index space = 1.42 G
  46. Current InnoDB data space = 1.99 G
  47. Current InnoDB buffer pool free = 72 %
  48. Current innodb_buffer_pool_size = 16.00 G
  49. Depending on how much space your innodb indexes take up it may be safe
  50. to increase this value to up to 2 / 3 of total system memory
  51.  
  52. MEMORY USAGE
  53. Max Memory Ever Allocated : 19.97 G
  54. Configured Max Per-thread Buffers : 11.31 G
  55. Configured Max Global Buffers : 16.62 G
  56. Configured Max Memory Limit : 27.94 G
  57. Plus 256 M per temporary table created
  58. Physical Memory : 61.70 G
  59. Max memory limit seem to be within acceptable norms
  60.  
  61. KEY BUFFER
  62. No key reads?!
  63. Seriously look into using some indexes
  64. Current MyISAM index space = 0 bytes
  65. Current key_buffer_size = 512 M
  66. Key cache miss rate is 1 : 0
  67. Key buffer free ratio = 98 %
  68. Your key_buffer_size seems to be fine
  69.  
  70. QUERY CACHE
  71. Query cache is enabled
  72. Current query_cache_size = 64 M
  73. Current query_cache_used = 17 K
  74. Current query_cache_limit = 128 K
  75. Current Query cache Memory fill ratio = .02 %
  76. Current query_cache_min_res_unit = 2 K
  77. Your query_cache_size seems to be too high.
  78. Perhaps you can use these resources elsewhere
  79. MySQL won't cache query results that are larger than query_cache_limit in size
  80.  
  81. SORT OPERATIONS
  82. Current sort_buffer_size = 4 M
  83. Current read_rnd_buffer_size = 1 M
  84. Sort buffer seems to be fine
  85.  
  86. JOINS
  87. Current join_buffer_size = 4.00 M
  88. You have had 522132 queries where a join could not use an index properly
  89. join_buffer_size >= 4 M
  90. This is not advised
  91. You should enable "log-queries-not-using-indexes"
  92. Then look for non indexed joins in the slow query log.
  93.  
  94. OPEN FILES LIMIT
  95. Current open_files_limit = 16364 files
  96. The open_files_limit should typically be set to at least 2x-3x
  97. that of table_open_cache if you have heavy MyISAM usage.
  98. Your open_files_limit value seems to be fine
  99.  
  100. TABLE CACHE
  101. Current table_open_cache = 7655 tables
  102. Current table_definition_cache = 2000 tables
  103. You have a total of 574 tables
  104. You have 919 open tables.
  105. The table_open_cache value seems to be fine
  106.  
  107. TEMP TABLES
  108. Current max_heap_table_size = 256 M
  109. Current tmp_table_size = 256 M
  110. Of 5783746 temp tables, 25% were created on disk
  111. Perhaps you should increase your tmp_table_size and/or max_heap_table_size
  112. to reduce the number of disk-based temporary tables
  113. Note! BLOB and TEXT columns are not allow in memory tables.
  114. If you are using these columns raising these values might not impact your
  115. ratio of on disk temp tables.
  116.  
  117. TABLE SCANS
  118. Current read_buffer_size = 2 M
  119. Current table scan ratio = 3 : 1
  120. read_buffer_size seems to be fine
  121.  
  122. TABLE LOCKING
  123. Current Lock Wait ratio = 0 : 71321737
  124. Your table locking seems to be fine
  125.  
RAW Paste Data