API tools faq
paste
Advertisement
rei4u2005

mycnf 2019-07-20

rei4u2005
Jul 19th, 2019
417
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 11.06 KB | None | 0 0
raw download clone embed print report
  1. #
  2. # The MySQL database server configuration file.
  3. #
  4. # You can copy this to one of:
  5. # - "/etc/mysql/my.cnf" to set global options,
  6. # - "~/.my.cnf" to set user-specific options.
  7. #
  8. # One can use all long options that the program supports.
  9. # Run program with --help to get a list of available options and with
  10. # --print-defaults to see which it would actually understand and use.
  11. #
  12. # For explanations see
  13. # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
  14.  
  15. # This will be passed to all mysql clients
  16. # It has been reported that passwords should be enclosed with ticks/quotes
  17. # escpecially if they contain "#" chars...
  18. # Remember to edit /etc/mysql/debian.cnf when changing the socket location.
  19.  
  20. # Here is entries for some specific programs
  21. # The following values assume you have at least 32M ram
  22.  
  23. [mysqld_safe]
  24. socket = /var/run/mysqld/mysqld.sock
  25. nice = 0
  26.  
  27. [mysqld]
  28. #
  29. # * Basic Settings
  30. #
  31. #validate_password_policy=LOW
  32. federated
  33. sql-mode=TRADITIONAL
  34. innodb_lock_wait_timeout=50
  35. transaction-isolation=READ-COMMITTED
  36. skip-name-resolve
  37. user = mysql
  38. pid-file = /var/run/mysqld/mysqld.pid
  39. socket = /var/run/mysqld/mysqld.sock
  40. port = 3306
  41. basedir = /usr
  42. datadir = /var/lib/mysql
  43. tmpdir = /tmp
  44. lc-messages-dir = /usr/share/mysql
  45. skip-external-locking
  46. #
  47. # coba betulin error free blocks
  48. #innodb_buffer_pool_size=5G # disable di 12072019_1108 gantinya di b2b tuning dibawah
  49. #
  50. #untuk Scheduler
  51. event_scheduler=ON
  52. #
  53. #Untuk Skip Error jika free block Error
  54. innodb_doublewrite=off
  55. #
  56. # Instead of skip-networking the default is now to listen only on
  57. # localhost which is more compatible and is not less secure.
  58. #bind-address =127.0.0.1
  59. bind-address = 0.0.0.0
  60. #
  61. # * Fine Tuning
  62. #
  63. key_buffer_size = 10M
  64. # max_allowed_packet = 64M # disable di 12072019_1108 gantinya di b2b tuning dibawah
  65. thread_stack = 192K
  66. # thread_cache_size = 8 # disable di 12072019_1108 gantinya di b2b tuning dibawah
  67. interactive_timeout = 3600
  68. wait_timeout = 3600 # disable di 12072019_1108 gantinya di b2b tuning dibawah
  69. innodb_log_file_size = 1G
  70. innodb_buffer_pool_instances = 4
  71. # table_open_cache = 3419 # disable di 12072019_1108 gantinya di b2b tuning dibawah
  72. # join_buffer_size = 1M # disable di 12072019_1108 gantinya di b2b tuning dibawah
  73. # read_rnd_buffer_size = 1M # disable di 12072019_1108 gantinya di b2b tuning dibawah
  74. # sort_buffer_size = 1M # disable di 12072019_1108 gantinya di b2b tuning dibawah
  75. # max_heap_table_size = 128M # disable di 12072019_1108 gantinya di b2b tuning dibawah
  76. # tmp_table_size = 128M # disable di 12072019_1108 gantinya di b2b tuning dibawah
  77. thread_stack = 256K
  78. innodb_stats_on_metadata = 0
  79. optimizer_search_depth = 0
  80. # max_connections = 251 # disable di 12072019_1108 gantinya di b2b tuning dibawah
  81. # table_open_cache = 3369 # disable di 12072019_1108 gantinya di b2b tuning dibawah
  82. #thread_concurrency = 10
  83. #
  84. #Monitor Library database yang dipakai di performance_schema bukan lagi di information_schema
  85. performance_schema=1
  86. #
  87. #
  88. # This replaces the startup script and checks MyISAM tables if needed
  89. # the first time they are touched
  90. myisam-recover-options = BACKUP
  91. #
  92. # * Query Cache Configuration
  93. #
  94. # query_cache_limit = 1M # disable di 12072019_1108 gantinya di b2b tuning dibawah
  95. # query_cache_size = 80M # disable di 12072019_1108 gantinya di b2b tuning dibawah
  96. # query_cache_type = 1 # disable di 12072019_1108 gantinya di b2b tuning dibawah
  97. #
  98. # * Logging and Replication
  99. #
  100. # Both location gets rotated by the cronjob.
  101. # Be aware that this log type is a performance killer.
  102. # As of 5.1 you can enable the log at runtime!
  103. #general_log_file = /var/log/mysql/mysql.log
  104. #general_log = 1
  105. #
  106. # Error log - should be very few entries.
  107. #
  108. log_error = /var/log/mysql/error.log
  109. #
  110. # Here you can see queries with especially long duration
  111. #slow_query_log = 1 # disable di 12072019_1108 gantinya di b2b tuning dibawah
  112. #slow_query_log_file = /var/log/mysql/mysql-slow.log
  113. #long_query_time = 2 # disable di 12072019_1108 gantinya di b2b tuning dibawah
  114. #log-queries-not-using-indexes
  115. #
  116. # The following can be used as easy to replay backup logs or for replication.
  117. # note: if you are setting up a replication slave, see README.Debian about
  118. # other settings you may need to change.
  119. #server-id = 1
  120. #log_bin = /var/log/mysql/mysql-bin.log
  121. expire_logs_days = 10
  122. max_binlog_size = 100M
  123. #binlog_do_db = include_database_name
  124. #binlog_ignore_db = include_database_name
  125. #
  126. # * InnoDB
  127. #
  128. # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
  129. # Read the manual for more InnoDB related options. There are many!
  130. #
  131. # * Security Features
  132. #
  133. # Read the manual, too, if you want chroot!
  134. # chroot = /var/lib/mysql/
  135. #
  136. # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
  137. #
  138. # ssl-ca=/etc/mysql/cacert.pem
  139. # ssl-cert=/etc/mysql/server-cert.pem
  140. # ssl-key=/etc/mysql/server-key.pem
  141. #
  142. #
  143. #
  144.  
  145. # BEGIN STATIC GV changes, will require STOP/START to put in place ONE A DAY
  146.  
  147. # open_files_limit=96000 # (it now depend the daemon setting in /lib/systemd/system/mysql.service so we set LimitNOFILE=200000 so innodb_open_files can be 60000) from 5000 to accomodate high volume of file activity
  148.  
  149. innodb_open_files=60000 # 190712-1145 (this also depend to the setting LimitNOFILE in mysql.service) from 2369 to support opened_tables RPHr 13,092
  150.  
  151. innodb_autoinc_lock_mode=2 # 190712-1647 from 1 to expedite hot spot during inserts HPM p 177
  152.  
  153. innodb_read_io_threads=64 # 190712-1647 from 16 to encourage multi-core processing per Rolando 9/12/11
  154.  
  155. innodb_write_io_threads=64 # 190712-1647 from 16 see dba.stackexchange.com question 5666 for details
  156.  
  157. innodb_flush_method=O_DIRECT # 190712-1647 from 0 for typical LX configuration
  158.  
  159.  
  160.  
  161. # BEGIN DYNAMIC GV changes, as root SET GLOBAL (one variable)=newvalue (no K,M,G - *1024 1-2or3 times)
  162.  
  163. # consider ONE chg at 10 minute intervals, monitor error log before moving to next suggestion rather than one a day
  164.  
  165. net_read_timeout=90 # 190712-1544 from 30 to be more tolerant in reading packets
  166.  
  167. net_write_timeout=90 # 190712-1544 from 60 to be more tolerant in writing packets
  168.  
  169. read_rnd_buffer_size=192K # 190712-1544 from 1M to reduce handler_read_rnd_next RPS of 994,152
  170.  
  171. # innodb_io_capacity=1900 # (not gonna implement this because our harddisk is RAID 1 using 2 TB 7200rpm) from 200 to allow more IOPS on your SSD - expect more CPU usage
  172.  
  173. thread_cache_size=100 # 190712-1145 from 8 for 5.7 CAP to avoid OOM and to reduce threads_created count 66
  174.  
  175. read_buffer_size=512K # 190712-1145 from 128K to reduce handler_read_next RPS of 699,834
  176.  
  177. tmp_table_size=144M # 190712-1145 from 128M to expand capacity
  178.  
  179. max_heap_table_size=144M # 190712-1544 from 128M to reduce created_tmp_disk_tables RPS of 1.33
  180.  
  181. table_open_cache=60000 # 190712-1145 from 2369 to reduce opened_tables RPS of 4
  182.  
  183. table_definition_cache=25000 # 190712-1551 from 1584 to reduce opened_table_definitions RPS of 2
  184.  
  185. max_allowed_packet=256M # 190718-1050 (increase from 128M to 256M because error occur Server has gone away) 190712-1145 from 64M to conserve RAM, if needed in session SET @max_allowed_packet=nnnn bef qry
  186.  
  187. max_connections=200 # 190712-1145 from 251 to conserve RAM footprint and support max_used_connections of 150 + 33%
  188.  
  189. query_cache_type=1 # (need to turn on because the end user experience) 0 from ON for OFF since only 25% of queries are eligible for QC
  190.  
  191. query_cache_size=10M # 190719-0940 reduce from 80M to 10M (percentage used for this is already good http://techinfobest.com/optimize-mysql-query_cache_size/) 0 from 80M to conserve CPU cycles and RAM
  192.  
  193. query_cache_limit=256K # 190718-0907 reduce from 1M to 256K (the query cache hit rate is already good http://techinfobest.com/optimize-mysql-query_cache_size/) 256K from 1M to conserve RAM and be prepared to enable QC if OFF is really BAD
  194.  
  195. query_cache_min_res_unit=512 # 190712-1600 from 4096 (4K) to conserve RAM required per stored entry, when QC is active
  196.  
  197. max_connect_errors=10 # 190712-1551 from 100 to frustrate Hacker/Cracker after 10 attempted breakins, not 100
  198.  
  199. sort_buffer_size=512K # 190712-1145 from 1M will increase sort_merge_passes RPS of 1 ( a tolerable number)
  200.  
  201. query_prealloc_size=32K # 190713-0827 from 8K to reduce malloc delays
  202.  
  203. query_alloc_block_size=32K # 190713-0827 from 8K to reduce malloc delays
  204.  
  205. transaction_prealloc_size=32K # 190713-0827 from 4K to reduce malloc delays
  206.  
  207. transaction_alloc_block_size=32K # 190713-0827 from 8K to reduce malloc delays
  208.  
  209. innodb_fast_shutdown=0 # 190713-0827 from 1 to use a few seconds-minutes and avoid RECOVERY processing
  210.  
  211. innodb_lru_scan_depth=100 # 190713-0827 from 1024 to reduce CPU effort 90% for this process each SECOND
  212.  
  213. innodb_adaptive_max_sleep_delay=20000 # 190713-0827 from 150000 (15 seconds to 2 seconds) when busy
  214.  
  215. innodb_flushing_avg_loops=5 # 190713-0827 from 30 trying to keep innodb_buffer_pool_pages_dirty < 1000 (~ 50,000 in 1st GS)
  216.  
  217. max_seeks_for_key=64 # 190713-0827 from gt 4 billion max ndx searches
  218.  
  219. max_write_lock_count=16 # 190713-0827 from gt 4 billion to allow RD after nn lcks
  220.  
  221. innodb_buffer_pool_size=5G # (190716-1514 from 6G back to 5G) 190712-1145 from 5G likely to grow over time.
  222.  
  223. innodb_change_buffer_max_size=15 # 190713-0842 from 25 percent set aside from pool for maint
  224.  
  225. wait_timeout=1800 # 190712-1145 from 3600 seconds for 1/2 hour tolerance of user inactivity
  226.  
  227. # innodb_flush_neighbors=0 # (not gonna enabled it because we still using rotation disk not SSD) from 1 to conserve CPU cycles - with SSD there is no need to look for neighbors
  228.  
  229. slow_query_log=ON # 190713-0842 from OFF for ON to allow monitoring DAILY or whenever you can gettoit.
  230.  
  231. # long_query_time=8 # (10s is still acceptable) from 10 seconds to find/correct slow queries. Then reduce until you get to 1 second.
  232.  
  233. min_examined_row_limit=1 # 190713-0842 from 0 to be listed on slow query log - eliminates useless CLUTTER
  234.  
  235. innodb_buffer_pool_dump_pct=90 # 190713-0856 from 25 percent to minimize warmup time
  236.  
  237. innodb_fill_factor=90 # 190713-0856 from default percentage of 93.75 to reduce innodb_pages_created RPS of 5
  238.  
  239. innodb_old_blocks_time=20000 # 190713-0856 from 1000 (for 20 seconds before age out) to reduce table SCANS
  240.  
  241. innodb_concurrency_tickets=10000 # 190713-0856 from 5000 to allow more CPU cycle usage before REQUE
  242.  
  243. innodb_read_ahead_threshold=8 # 190713-0856 from 56 to RD next extent earlier
  244.  
  245. # innodb_stats_auto_recalc=0 # (not gonna enabled it I think we still gonna need it) from ON to conserve CPU - use ANALYZE daily to cover ndx updates
  246.  
  247. # innodb_stats_persistent=0 # (not gonna enabled it I think we still gonna need it) from ON to conserve CPU and for exec consistency
  248.  
  249. innodb_stats_transient_sample_pages=256 # 190713-0856 (in the last version mysql it name innodb_stats_sample_pages) from 8 for more accurate cardinality in data used by optimizer
  250.  
  251. join_buffer_size=512K # 190712-1145 from 1M to conserve RAM, may have to go back to 1M for you
  252.  
  253. connect_timeout=20 # 190713-0845 from 10 to make CONNECT more tolerant on your system before timeout
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!