Guest User

Untitled

a guest
Feb 19th, 2018
94
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 5.86 KB | None | 0 0
  1. [mysqld]
  2.  
  3. innodb_file_per_table=1
  4.  
  5. local-infile=0
  6.  
  7. tmpdir =/dev/shm
  8.  
  9. max_connections = 1024
  10.  
  11. max_user_connections=650
  12.  
  13. key_buffer_size = 1024M
  14.  
  15. myisam_sort_buffer_size = 64M
  16.  
  17. #default-storage-engine=MyISAM
  18.  
  19. join_buffer_size = 1M
  20.  
  21. read_buffer_size = 1M
  22.  
  23. sort_buffer_size = 256K
  24.  
  25. thread_cache_size = 384
  26.  
  27. wait_timeout = 20
  28.  
  29. connect_timeout = 10
  30.  
  31. tmp_table_size = 64M
  32.  
  33. max_heap_table_size = 64M
  34.  
  35. max_allowed_packet = 64M
  36.  
  37. net_buffer_length = 16384
  38.  
  39. max_connect_errors = 10
  40.  
  41. thread_concurrency = 16
  42.  
  43. concurrent_insert = 2
  44.  
  45. read_rnd_buffer_size = 786432
  46.  
  47. bulk_insert_buffer_size = 8M
  48.  
  49. query_cache_limit = 7M
  50.  
  51. query_cache_size = 64M
  52.  
  53. query_cache_type = 1
  54.  
  55. query_prealloc_size = 262144
  56.  
  57. query_alloc_block_size = 65536
  58.  
  59. transaction_alloc_block_size = 8192
  60.  
  61. transaction_prealloc_size = 4096
  62.  
  63. max_write_lock_count = 16
  64.  
  65. innodb_buffer_pool_size=8G #75 % of RAM
  66.  
  67. innodb_flush_log_at_trx_commit=2
  68.  
  69. innodb_thread_concurrency=8 # of cpus
  70.  
  71. innodb_flush_method=O_DIRECT
  72.  
  73.  
  74. [mysqld_safe]
  75.  
  76. open_files_limit = 8192
  77.  
  78.  
  79. [mysqldump]
  80.  
  81. quick
  82.  
  83. max_allowed_packet = 16M
  84.  
  85.  
  86. [myisamchk]
  87.  
  88. key_buffer = 384M
  89.  
  90. sort_buffer = 384M
  91.  
  92. read_buffer = 256M
  93.  
  94. /////////////////////////////
  95.  
  96. [--] Up for: 2d 3h 25m 54s (23M q [127.290 qps], 468K conn, TX: 138G, RX: 7G)
  97.  
  98. [--] Reads / Writes: 89% / 11%
  99.  
  100. [--] Binary logging is disabled
  101.  
  102. [--] Physical Memory : 31.3G
  103.  
  104. [--] Max MySQL memory : 12.9G
  105.  
  106. [--] Other process memory: 3.9G
  107.  
  108. [--] Total buffers: 9.1G global + 3.2M per thread (1024 max threads)
  109.  
  110. [--] P_S Max memory usage: 555M
  111.  
  112. [--] Galera GCache Max memory usage: 0B
  113.  
  114. [OK] Maximum reached memory usage: 10.0G (31.77% of installed RAM)
  115.  
  116. [OK] Maximum possible memory usage: 12.9G (41.28% of installed RAM)
  117.  
  118. [OK] Overall possible memory usage with other process is compatible with memory available
  119.  
  120. [OK] Slow queries: 0% (5K/23M)
  121.  
  122. [OK] Highest usage of available connections: 8% (85/1024)
  123.  
  124. [OK] Aborted connections: 0.55% (2598/468725)
  125.  
  126. [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
  127.  
  128. [!!] Query cache may be disabled by default due to mutex contention.
  129.  
  130. [OK] Query cache efficiency: 61.7% (12M cached / 19M selects)
  131.  
  132. [!!] Query cache prunes per day: 1008253
  133.  
  134. [OK] Sorts requiring temporary tables: 0% (2K temp sorts / 34M sorts)
  135.  
  136. [!!] Joins performed without indexes: 150075
  137.  
  138. [!!] Temporary tables created on disk: 52% (577K on disk / 1M total)
  139.  
  140. [OK] Thread cache hit rate: 99% (85 created / 468K connections)
  141.  
  142. [!!] Table cache hit rate: 1% (2K open / 102K opened)
  143.  
  144. [OK] Open file limit used: 24% (1K/8K)
  145.  
  146. [OK] Table locks acquired immediately: 99% (38M immediate / 38M locks)
  147.  
  148.  
  149. -------- Performance schema ------------------------------------------------------------------------
  150.  
  151. [--] Memory used by P_S: 555.4M
  152.  
  153. [--] Sys schema isn't installed.
  154.  
  155.  
  156. -------- ThreadPool Metrics ------------------------------------------------------------------------
  157.  
  158. [--] ThreadPool stat is disabled.
  159.  
  160.  
  161. -------- MyISAM Metrics ----------------------------------------------------------------------------
  162.  
  163. [!!] Key buffer used: 26.8% (287M used / 1B cache)
  164.  
  165. [OK] Key buffer size / total MyISAM indexes: 1.0G/1.5G
  166.  
  167. [OK] Read Key buffer hit rate: 100.0% (2B cached / 572K reads)
  168.  
  169. [!!] Write Key buffer hit rate: 30.1% (2M cached / 899K writes)
  170.  
  171.  
  172. -------- InnoDB Metrics ----------------------------------------------------------------------------
  173.  
  174. [--] InnoDB is enabled.
  175.  
  176. [--] InnoDB Thread Concurrency: 8
  177.  
  178. [OK] InnoDB File per table is activated
  179.  
  180. [OK] InnoDB buffer pool / data size: 8.0G/1.2G
  181.  
  182. [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (1.171875 %): 48.0M * 2/8.0G should be equal 25%
  183.  
  184. [OK] InnoDB buffer pool instances: 8
  185.  
  186. [--] InnoDB Buffer Pool Chunk Size not used or defined in your version
  187.  
  188. [OK] InnoDB Read buffer efficiency: 100.00% (786635092 hits/ 786673138 total)
  189.  
  190. [!!] InnoDB Write Log efficiency: 51.42% (236640 hits/ 460226 total)
  191.  
  192. [OK] InnoDB log waits: 0.00% (0 waits / 223586 writes)
  193.  
  194.  
  195. -------- AriaDB Metrics ----------------------------------------------------------------------------
  196.  
  197. [--] AriaDB is disabled.
  198.  
  199.  
  200. -------- TokuDB Metrics ----------------------------------------------------------------------------
  201.  
  202. [--] TokuDB is disabled.
  203.  
  204.  
  205. -------- XtraDB Metrics ----------------------------------------------------------------------------
  206.  
  207. [--] XtraDB is disabled.
  208.  
  209.  
  210. -------- RocksDB Metrics ---------------------------------------------------------------------------
  211.  
  212. [--] RocksDB is disabled.
  213.  
  214.  
  215. -------- Spider Metrics ----------------------------------------------------------------------------
  216.  
  217. [--] Spider is disabled.
  218.  
  219.  
  220. -------- Connect Metrics ---------------------------------------------------------------------------
  221.  
  222. [--] Connect is disabled.
  223.  
  224.  
  225. -------- Galera Metrics ----------------------------------------------------------------------------
  226.  
  227. [--] Galera is disabled.
  228.  
  229.  
  230. -------- Replication Metrics -----------------------------------------------------------------------
  231.  
  232. [--] Galera Synchronous replication: NO
  233.  
  234. [--] No replication slave(s) for this server.
  235.  
  236. [--] This is a standalone server.
  237.  
  238.  
  239. -------- Recommendations ---------------------------------------------------------------------------
  240.  
  241. Control warning line(s) into /var/lib/mysql/bh1.behost.es.err file
  242.  
  243. Control error line(s) into /var/lib/mysql/bh1.behost.es.err file
  244.  
  245. Run OPTIMIZE TABLE to defragment tables for better performance
  246.  
  247. OPTIMIZE TABLE `modsec`.`hits`; -- can free 382.777297973633 MB
  248.  
  249. Total freed space after theses OPTIMIZE TABLE : 382.777297973633 Mb
  250.  
  251. Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
  252. Restrict Host for user@% to user@SpecificDNSorIp
  253.  
  254. query_cache_size (=0)
  255.  
  256. query_cache_type (=0)
  257.  
  258. query_cache_size (> 64M)
  259.  
  260. join_buffer_size (> 1.0M, or always use indexes with joins)
  261.  
  262. tmp_table_size (> 64M)
  263.  
  264. max_heap_table_size (> 64M)
  265.  
  266. table_open_cache (> 2000)
  267.  
  268. innodb_log_file_size should be (=1G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
Add Comment
Please, Sign In to add comment