Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #
- # The MySQL database server configuration file.
- #
- # You can copy this to one of:
- # - "/etc/mysql/my.cnf" to set global options,
- # - "~/.my.cnf" to set user-specific options.
- #
- # One can use all long options that the program supports.
- # Run program with --help to get a list of available options and with
- # --print-defaults to see which it would actually understand and use.
- #
- # For explanations see
- # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
- # This will be passed to all mysql clients
- # It has been reported that passwords should be enclosed with ticks/quotes
- # escpecially if they contain "#" chars...
- # Remember to edit /etc/mysql/debian.cnf when changing the socket location.
- # Here is entries for some specific programs
- # The following values assume you have at least 32M ram
- [mysqld_safe]
- socket = /var/run/mysqld/mysqld.sock
- nice = 0
- [mysqld]
- #
- # * Basic Settings
- #
- #validate_password_policy=LOW
- #federated
- sql-mode=TRADITIONAL
- innodb_lock_wait_timeout=50
- transaction-isolation=READ-COMMITTED
- skip-name-resolve
- user = mysql
- pid-file = /var/run/mysqld/mysqld.pid
- socket = /var/run/mysqld/mysqld.sock
- port = 3306
- basedir = /usr
- datadir = /var/lib/mysql
- tmpdir = /tmp
- lc-messages-dir = /usr/share/mysql
- skip-external-locking
- #
- # coba betulin error free blocks
- #innodb_buffer_pool_size=5G # disable di 12072019_1108 gantinya di b2b tuning dibawah
- #
- #untuk Scheduler
- event_scheduler=ON
- #
- #Untuk Skip Error jika free block Error
- innodb_doublewrite=off
- #
- # Instead of skip-networking the default is now to listen only on
- # localhost which is more compatible and is not less secure.
- #bind-address =127.0.0.1
- bind-address = 0.0.0.0
- #
- # * Fine Tuning
- #
- key_buffer_size = 10M
- # max_allowed_packet = 64M # disable di 12072019_1108 gantinya di b2b tuning dibawah
- thread_stack = 192K
- # thread_cache_size = 8 # disable di 12072019_1108 gantinya di b2b tuning dibawah
- interactive_timeout = 3600
- wait_timeout = 3600 # disable di 12072019_1108 gantinya di b2b tuning dibawah
- innodb_log_file_size = 1G
- innodb_buffer_pool_instances = 4
- # table_open_cache = 3419 # disable di 12072019_1108 gantinya di b2b tuning dibawah
- # join_buffer_size = 1M # disable di 12072019_1108 gantinya di b2b tuning dibawah
- # read_rnd_buffer_size = 1M # disable di 12072019_1108 gantinya di b2b tuning dibawah
- # sort_buffer_size = 1M # disable di 12072019_1108 gantinya di b2b tuning dibawah
- # max_heap_table_size = 128M # disable di 12072019_1108 gantinya di b2b tuning dibawah
- # tmp_table_size = 128M # disable di 12072019_1108 gantinya di b2b tuning dibawah
- thread_stack = 256K
- innodb_stats_on_metadata = 0
- optimizer_search_depth = 0
- # max_connections = 251 # disable di 12072019_1108 gantinya di b2b tuning dibawah
- # table_open_cache = 3369 # disable di 12072019_1108 gantinya di b2b tuning dibawah
- #thread_concurrency = 10
- #
- #Monitor Library database yang dipakai di performance_schema bukan lagi di information_schema
- performance_schema=1
- #
- #
- # This replaces the startup script and checks MyISAM tables if needed
- # the first time they are touched
- myisam-recover-options = BACKUP
- #
- # * Query Cache Configuration
- #
- # query_cache_limit = 1M # disable di 12072019_1108 gantinya di b2b tuning dibawah
- # query_cache_size = 80M # disable di 12072019_1108 gantinya di b2b tuning dibawah
- # query_cache_type = 1 # disable di 12072019_1108 gantinya di b2b tuning dibawah
- #
- # * Logging and Replication
- #
- # Both location gets rotated by the cronjob.
- # Be aware that this log type is a performance killer.
- # As of 5.1 you can enable the log at runtime!
- #general_log_file = /var/log/mysql/mysql.log
- #general_log = 1
- #
- # Error log - should be very few entries.
- #
- log_error = /var/log/mysql/error.log
- #
- # Here you can see queries with especially long duration
- #slow_query_log = 1 # disable di 12072019_1108 gantinya di b2b tuning dibawah
- #slow_query_log_file = /var/log/mysql/mysql-slow.log
- #long_query_time = 2 # disable di 12072019_1108 gantinya di b2b tuning dibawah
- #log-queries-not-using-indexes
- #
- # The following can be used as easy to replay backup logs or for replication.
- # note: if you are setting up a replication slave, see README.Debian about
- # other settings you may need to change.
- #server-id = 1
- #log_bin = /var/log/mysql/mysql-bin.log
- expire_logs_days = 10
- max_binlog_size = 100M
- #binlog_do_db = include_database_name
- #binlog_ignore_db = include_database_name
- #
- # * InnoDB
- #
- # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
- # Read the manual for more InnoDB related options. There are many!
- #
- # * Security Features
- #
- # Read the manual, too, if you want chroot!
- # chroot = /var/lib/mysql/
- #
- # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
- #
- # ssl-ca=/etc/mysql/cacert.pem
- # ssl-cert=/etc/mysql/server-cert.pem
- # ssl-key=/etc/mysql/server-key.pem
- #
- #
- #
- #Tuning suggestion oleh B2B (mysqlservertuning.com) memakai format yymmdd-hhmm
- # 20190711 1048 from mysqlservertuning.com
- # BEGIN STATIC GV changes, will require STOP/START to put in place ONE A DAY
- # 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
- 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
- innodb_autoinc_lock_mode=2 # 190712-1647 from 1 to expedite hot spot during inserts HPM p 177
- innodb_read_io_threads=64 # 190712-1647 from 16 to encourage multi-core processing per Rolando 9/12/11
- innodb_write_io_threads=64 # 190712-1647 from 16 see dba.stackexchange.com question 5666 for details
- innodb_flush_method=O_DIRECT # 190712-1647 from 0 for typical LX configuration
- # BEGIN DYNAMIC GV changes, as root SET GLOBAL (one variable)=newvalue (no K,M,G - *1024 1-2or3 times)
- # consider ONE chg at 10 minute intervals, monitor error log before moving to next suggestion rather than one a day
- net_read_timeout=90 # 190712-1544 from 30 to be more tolerant in reading packets
- net_write_timeout=90 # 190712-1544 from 60 to be more tolerant in writing packets
- read_rnd_buffer_size=192K # 190712-1544 from 1M to reduce handler_read_rnd_next RPS of 994,152
- # 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
- thread_cache_size=100 # 190712-1145 from 8 for 5.7 CAP to avoid OOM and to reduce threads_created count 66
- read_buffer_size=512K # 190712-1145 from 128K to reduce handler_read_next RPS of 699,834
- tmp_table_size=144M # 190712-1145 from 128M to expand capacity
- max_heap_table_size=144M # 190712-1544 from 128M to reduce created_tmp_disk_tables RPS of 1.33
- table_open_cache=60000 # 190712-1145 from 2369 to reduce opened_tables RPS of 4
- table_definition_cache=25000 # 190712-1551 from 1584 to reduce opened_table_definitions RPS of 2
- max_allowed_packet=4M # 190712-1145 from 64M to conserve RAM, if needed in session SET @max_allowed_packet=nnnn bef qry
- max_connections=200 # 190712-1145 from 251 to conserve RAM footprint and support max_used_connections of 150 + 33%
- 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
- query_cache_size=80M # (percentage used for this is already good http://techinfobest.com/optimize-mysql-query_cache_size/) 0 from 80M to conserve CPU cycles and RAM
- query_cache_limit=1M # (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
- query_cache_min_res_unit=512 # 190712-1600 from 4096 (4K) to conserve RAM required per stored entry, when QC is active
- max_connect_errors=10 # 190712-1551 from 100 to frustrate Hacker/Cracker after 10 attempted breakins, not 100
- sort_buffer_size=512K # 190712-1145 from 1M will increase sort_merge_passes RPS of 1 ( a tolerable number)
- query_prealloc_size=32K # 190713-0827 from 8K to reduce malloc delays
- query_alloc_block_size=32K # 190713-0827 from 8K to reduce malloc delays
- transaction_prealloc_size=32K # 190713-0827 from 4K to reduce malloc delays
- transaction_alloc_block_size=32K # 190713-0827 from 8K to reduce malloc delays
- innodb_fast_shutdown=0 # 190713-0827 from 1 to use a few seconds-minutes and avoid RECOVERY processing
- innodb_lru_scan_depth=100 # 190713-0827 from 1024 to reduce CPU effort 90% for this process each SECOND
- innodb_adaptive_max_sleep_delay=20000 # 190713-0827 from 150000 (15 seconds to 2 seconds) when busy
- innodb_flushing_avg_loops=5 # 190713-0827 from 30 trying to keep innodb_buffer_pool_pages_dirty < 1000 (~ 50,000 in 1st GS)
- max_seeks_for_key=64 # 190713-0827 from gt 4 billion max ndx searches
- max_write_lock_count=16 # 190713-0827 from gt 4 billion to allow RD after nn lcks
- innodb_buffer_pool_size=6G # 190712-1145 from 5G likely to grow over time.
- innodb_change_buffer_max_size=15 # 190713-0842 from 25 percent set aside from pool for maint
- wait_timeout=1800 # 190712-1145 from 3600 seconds for 1/2 hour tolerance of user inactivity
- # 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
- slow_query_log=ON # 190713-0842 from OFF for ON to allow monitoring DAILY or whenever you can gettoit.
- # long_query_time=8 # (10s is still acceptable) from 10 seconds to find/correct slow queries. Then reduce until you get to 1 second.
- min_examined_row_limit=1 # 190713-0842 from 0 to be listed on slow query log - eliminates useless CLUTTER
- innodb_buffer_pool_dump_pct=90 # 190713-0856 from 25 percent to minimize warmup time
- innodb_fill_factor=90 # 190713-0856 from default percentage of 93.75 to reduce innodb_pages_created RPS of 5
- innodb_old_blocks_time=20000 # 190713-0856 from 1000 (for 20 seconds before age out) to reduce table SCANS
- innodb_concurrency_tickets=10000 # 190713-0856 from 5000 to allow more CPU cycle usage before REQUE
- innodb_read_ahead_threshold=8 # 190713-0856 from 56 to RD next extent earlier
- # 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
- # innodb_stats_persistent=0 # (not gonna enabled it I think we still gonna need it) from ON to conserve CPU and for exec consistency
- 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
- join_buffer_size=512K # 190712-1145 from 1M to conserve RAM, may have to go back to 1M for you
- 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