Guest User

tuning-primer fixed

a guest
Sep 15th, 2015
73
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- MYSQL PERFORMANCE TUNING PRIMER --
  2. - By: Matthew Montgomery -
  3.  
  4. MySQL Version 10.0.21-MariaDB-log amd64
  5.  
  6. Uptime = 4 days 23 hrs 18 min 0 sec
  7. Avg. qps = 12
  8. Total Questions = 5275601
  9. Threads Connected = 3
  10.  
  11. Server has been running for over 48hrs.
  12. It should be safe to follow these recommendations
  13.  
  14. To find out more information on how each of these
  15. runtime variables effects performance visit:
  16. http://dev.mysql.com/doc/refman/10.0/en/server-system-variables.html
  17. Visit http://www.mysql.com/products/enterprise/advisors.html
  18. for info about MySQL's Enterprise Monitoring and Advisory Service
  19.  
  20. SLOW QUERIES
  21. The slow query log is NOT enabled.
  22. Current long_query_time = 10.000000 sec.
  23. You have 0 out of 5275622 that take longer than 10.000000 sec. to complete
  24. [: 0.00000000000000000000: bad number
  25. Your long_query_time seems to be fine
  26.  
  27. BINARY UPDATE LOG
  28. The binary update log is enabled
  29. Binlog sync is not enabled, you could loose binlog records during a server crash
  30.  
  31. WORKER THREADS
  32. Current thread_cache_size = 8
  33. Current threads_cached = 6
  34. Current threads_per_sec = 0
  35. Historic threads_per_sec = 0
  36. Your thread_cache_size is fine
  37.  
  38. MAX CONNECTIONS
  39. Current max_connections = 250
  40. Current threads_connected = 3
  41. Historic max_used_connections = 10
  42. The number of used connections is 4% of the configured maximum.
  43. You are using less than 10% of your configured max_connections.
  44. Lowering max_connections could help to avoid an over-allocation of memory
  45. See "MEMORY USAGE" section to make sure you are not over-allocating
  46.  
  47. INNODB STATUS
  48. Current InnoDB index space = 384 K
  49. Current InnoDB data space = 608 K
  50. Current InnoDB buffer pool free = 99 %
  51. Current innodb_buffer_pool_size = 3.00 G
  52. Depending on how much space your innodb indexes take up it may be safe
  53. to increase this value to up to 2 / 3 of total system memory
  54.  
  55. MEMORY USAGE
  56. Max Memory Ever Allocated : 3.57 G
  57. Configured Max Per-thread Buffers : 3.73 G
  58. Configured Max Global Buffers : 3.42 G
  59. Configured Max Memory Limit : 7.16 G
  60. Physical Memory : 8.00 G
  61. Max memory limit seem to be within acceptable norms
  62.  
  63. KEY BUFFER
  64. Current MyISAM index space = 0 bytes
  65. Current key_buffer_size = 384 M
  66. Key cache miss rate is 1 : 7261
  67. Key buffer free ratio = 79 %
  68. Your key_buffer_size seems to be fine
  69.  
  70. QUERY CACHE
  71. Query cache is enabled
  72. Current query_cache_size = 32 M
  73. Current query_cache_used = 17 M
  74. Current query_cache_limit = 512 K
  75. Current Query cache Memory fill ratio = 54.57 %
  76. Current query_cache_min_res_unit = 4 K
  77. Query Cache is 27 % fragmented
  78. Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory
  79. If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.
  80. MySQL won't cache query results that are larger than query_cache_limit in size
  81.  
  82. SORT OPERATIONS
  83. Current sort_buffer_size = 4 M
  84. Current read_rnd_buffer_size = 8 M
  85. Sort buffer seems to be fine
  86.  
  87. JOINS
  88. Current join_buffer_size = 1.00 M
  89. You have had 67 queries where a join could not use an index properly
  90. You should enable "log-queries-not-using-indexes"
  91. Then look for non indexed joins in the slow query log.
  92. If you are unable to optimize your queries you may want to increase your
  93. join_buffer_size to accommodate larger joins in one pass.
  94.  
  95. Note! This script will still suggest raising the join_buffer_size when
  96. ANY joins not using indexes are found.
  97.  
  98. OPEN FILES LIMIT
  99. Current open_files_limit = 235233 files
  100. The open_files_limit should typically be set to at least 2x-3x
  101. that of table_cache if you have heavy MyISAM usage.
  102. Your open_files_limit value seems to be fine
  103.  
  104. TABLE CACHE
  105. Current table_open_cache = 1536 tables
  106. Current table_definition_cache = 768 tables
  107. You have a total of 11 tables
  108. You have 427 open tables.
  109. The table_cache value seems to be fine
  110.  
  111. TEMP TABLES
  112. Current max_heap_table_size = 1.00 G
  113. Current tmp_table_size = 1.00 G
  114. Of 54408 temp tables, 50% were created on disk
  115. Perhaps you should increase your tmp_table_size and/or max_heap_table_size
  116. to reduce the number of disk-based temporary tables
  117. Note! BLOB and TEXT columns are not allow in memory tables.
  118. If you are using these columns raising these values might not impact your
  119. ratio of on disk temp tables.
  120.  
  121. TABLE SCANS
  122. Current read_buffer_size = 2 M
  123. Current table scan ratio = 10 : 1
  124. read_buffer_size seems to be fine
  125.  
  126. TABLE LOCKING
  127. Current Lock Wait ratio = 1 : 45460
  128. Your table locking seems to be fine
RAW Paste Data