Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ################################################################################
- #DATE: 2011-02-02
- #SITE: http://datastrangler.com
- #DESCRIPTION: MySQL config 5.0.x, 5.1.x, 5.5.x
- #RAM: 32GB RAM dedicated server
- #Connections: 2000 connections
- ### This my-itd.cnf file is (also) based on the following speedemy.com blog post:
- ### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof
- ################################################################################
- [mysqld_safe]
- nice = -15
- [client]
- socket = /var/run/mysqld/mysqld.sock
- default-character-set = utf8
- port = 3306
- [mysqld]
- bind-address = 0.0.0.0
- ## Charset and Collation
- character-set-server = utf8
- collation-server = utf8_general_ci
- sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
- ## Files
- back_log = 300
- open-files-limit = 10000
- open-files = 1024
- port = 3306
- socket = /var/run/mysqld/mysqld.sock
- pid-file = /var/run/mysqld/mysqld.pid
- skip-external-locking
- skip-name-resolve
- ## Logging
- datadir = /var/lib/mysql
- relay_log = mysql-relay-bin
- relay_log_index = mysql-relay-index
- #log = mysql-gen.log
- log_error = /var/log/mysql/mysql-error.log
- log_warnings
- log_bin = mysql-bin
- log_slow_queries = /var/log/mysql/mysql-slow.log
- log-queries-not-using-indexes = 0
- long_query_time = 10 #default: 10
- max_binlog_size = 256M #max size for binlog before rolling
- expire_logs_days = 4 #binlog files older than this will be purged
- ## Per-Thread Buffers * (max_connections) = total per-thread mem usage
- thread_stack = 256K #default: 32bit: 192K, 64bit: 256K
- sort_buffer_size = 1M #default: 2M, larger may cause perf issues
- read_buffer_size = 1M #default: 128K, change in increments of 4K
- read_rnd_buffer_size = 1M #default: 256K
- join_buffer_size = 1M #default: 128K
- binlog_cache_size = 64K #default: 32K, size of buffer to hold TX queries
- ## total per-thread buffer memory usage: 8832000K = 8.625GB
- ## Query Cache
- query_cache_size = 32M #global buffer
- query_cache_limit = 512K #max query result size to put in cache
- ## Connections
- max_connections = 2000 #multiplier for memory usage via per-thread buffers
- max_connect_errors = 100 #default: 10
- concurrent_insert = 2 #default: 1, 2: enable insert for all instances
- connect_timeout = 30 #default -5.1.22: 5, +5.1.22: 10
- max_allowed_packet = 32M #max size of incoming data to allow
- ## Default Table Settings
- sql_mode = NO_AUTO_CREATE_USER
- ## Table and TMP settings
- max_heap_table_size = 1G #recommend same size as tmp_table_size
- bulk_insert_buffer_size = 1G #recommend same size as tmp_table_size
- tmp_table_size = 1G #recommend 1G min
- #tmpdir = /data/mysql-tmp0:/data/mysql-tmp1 #Recommend using RAMDISK for tmpdir
- ## Table cache settings
- #table_cache = 512 #5.0.x <default: 64>
- #table_open_cache = 512 #5.1.x, 5.5.x <default: 64>
- ## Thread settings
- thread_concurrency = 16 #recommend 2x CPU cores
- thread_cache_size = 100 #recommend 5% of max_connections
- ## Replication
- #read_only
- #skip-slave-start
- #slave-skip-errors = <default: none, recommend:1062>
- #slave-net-timeout = <default: 3600>
- #slave-load-tmpdir = <location of slave tmpdir>
- #slave_transaction_retries = <default: 10>
- #server-id = <unique value>
- #replicate-same-server-id = <default: 0, recommend: 0, !if log_slave_updates=1>
- #auto-increment-increment = <default: none>
- #auto-increment-offset = <default: none>
- #master-connect-retry = <default: 60>
- #log-slave-updates = <default: 0 disable>
- #report-host = <master_server_ip>
- #report-user = <replication_user>
- #report-password = <replication_user_pass>
- #report-port = <default: 3306>
- #replicate-do-db =
- #replicate-ignore-db =
- #replicate-do-table =
- #relicate-ignore-table =
- #replicate-rewrite-db =
- #replicate-wild-do-table =
- #replicate-wild-ignore-table =
- ## Replication Semi-Synchronous 5.5.x only, requires dynamic plugin loading ability
- #rpl_semi_sync_master_enabled = 1 #enable = 1, disable = 0
- #rpl_semi_sync_master_timeout = 1000 #in milliseconds <default: 10000>, master only setting
- ## 5.1.x and 5.5.x replication related setting.
- #binlog_format = MIXED
- ## MyISAM Engine
- key_buffer = 1M #global buffer
- myisam_sort_buffer_size = 128M #index buffer size for creating/altering indexes
- myisam_max_sort_file_size = 256M #max file size for tmp table when creating/alering indexes
- myisam_repair_threads = 4 #thread quantity when running repairs
- myisam_recover = BACKUP #repair mode, recommend BACKUP
- ## InnoDB Plugin Dependent Settings
- #ignore-builtin-innodb
- #plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so
- ## InnoDB IO Capacity - 5.1.x plugin, 5.5.x
- #innodb_io_capacity = 200
- ## InnoDB IO settings - 5.1.x only
- #innodb_file_io_threads = 16
- ## InnoDB IO settings - 5.5.x and greater
- #innodb_write_io_threads = 16
- #innodb_read_io_threads = 16
- ## InnoDB Plugin Independent Settings
- innodb_data_home_dir = /var/lib/mysql
- innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend
- innodb_log_file_size = 512M #64G_RAM+ = 768, 24G_RAM+ = 512, 8G_RAM+ = 256, 2G_RAM+ = 128
- innodb_log_files_in_group = 4 #combined size of all logs <4GB. <2G_RAM = 2, >2G_RAM = 4
- innodb_buffer_pool_size = 18G #global buffer
- innodb_additional_mem_pool_size = 4M #global buffer
- innodb_status_file #extra reporting
- innodb_file_per_table #enable always
- innodb_flush_log_at_trx_commit = 2 #2/0 = perf, 1 = ACID
- innodb_table_locks = 0 #preserve table locks
- innodb_log_buffer_size = 128M #global buffer
- innodb_lock_wait_timeout = 60
- innodb_thread_concurrency = 16 #recommend 2x core quantity
- innodb_commit_concurrency = 16 #recommend 4x num disks
- #innodb_flush_method = O_DIRECT #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI
- innodb_support_xa = 0 #recommend 0, disable xa to negate extra disk flush
- skip-innodb-doublewrite
- ## Binlog sync settings
- ## XA transactions = 1, otherwise set to 0 for best performance
- sync_binlog = 0
- ## TX Isolation
- transaction-isolation = REPEATABLE-READ #REPEATABLE-READ req for ACID, SERIALIZABLE req XA
- ## Per-Thread Buffer memory utilization equation:
- #(read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size) * max_connections
- ## Global Buffer memory utilization equation:
- # innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size
- [mysqldump]
- events
- quick
- quote-names
- max_allowed_packet = 128M
- ignore-table=mysql.events
- [client]
- host = localhost
- user = debian-sys-maint
- password = ########
- socket = /var/run/mysqld/mysqld.sock
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement