72GB記憶體生産環境中的my.cnf

參考賀老師書裡的内容加上自己的注釋

# MySQL configuration for 72G memory
[client]
port = 3306
socket = /tmp/mysql.sock

#The MySQL server
#########Basic###########
[mysqld]
server-id = 22
port = 3306
user = mysql
basedir = /usr/local/mysql
datadir = /mysqlData/data
tmpdir = /mysqlData/tmp
socket = /tmp/mysql.sock
skip-external-locking     --(This is OFF if mysqld uses external locking (system locking) External locking affects only MyISAM table access)
skip-name-resolve    --(Do not resolve host names when checking client connections. Use only IP addresses. If you use this option, all Host column values in the grant tables must be IP addresses or localhost)
default-storage-engine = INNODB
character-set-server = utf8
wait_timeout = 100    --(The number of seconds the server waits for activity on a noninteractive connection before closing it)
connect_timeout = 20    --(The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake. The default value is 10 seconds)
interactive_timeout = 100 --(The number of seconds the server waits for activity on an interactive connection before closing it.)
back_log = 500      --( The number of outstanding connection requests MySQL can have 指定MySQL可能的連接配接數量。當MySQL主線程在很短的時間内得到非常多的連接配接請求，該參數就起作用，之後主線程花些時間（盡管很短）檢查連接配接并且啟動一個新線程 )
myisam_recover    --(Set the MyISAM storage engine recovery mode)
event_scheduler = ON       --(This variable indicates the status of the Event Scheduler)

########binlog############
log-bin = /mysqlLog/logs/mysql-bin
binlog_format = row
max_binlog_size = 128M
binlog_cache_size = 2M
expire-logs-days = 5

########replication##########
slave-net-timeout = 10    --(The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, and tries to reconnect)
rpl_semi_sync_master_enabled = 1   --(Controls whether semisynchronous replication is enabled on the master.)
rpl_semi_sync_master_wait_no_slave = 1  --(With semisynchronous replication, for each transaction, the master waits until timeout for acknowledgment of receipt from some semisynchronous slave 預設打開，表示當備庫起來後，并跟上主庫時，自動切換到同步模式，如果關閉，即使備庫起來并跟上了，也不會啟用半同步)
rpl_semi_sync_master_timeout = 1000   --(A value in milliseconds that controls how long the master waits on a commit for acknowledgment from a slave before timing out and reverting to asynchronous replication)
rpl_semi_sync_slave_enabled = 1   --(Controls whether semisynchronous replication is enabled on the slave)
skip-slave-start   --(Tells the slave server not to start the slave threads when the server starts)
log_slave_updates = 1  --(Whether updates received by a slave server from a master server should be logged to the slave's own binary log. Binary logging must be enabled on the slave for this variable to have any effect.)
relay_log_recovery = 1   --(Enables automatic relay log recovery immediately following server startup)

########slow log############
slow_query_log = 1
slow_query_log_file = /mysqlLog/logs/mysql.slow
long_query_time = 2   --(If a query takes longer than this many seconds, the server increments the Slow_queries status variable)

########error log############
log-error = /mysqlLog/logs/error.log

########per_thread_buffers######
max_connections = 1024      --(The maximum permitted number of simultaneous client connections 是指整個mysql伺服器的最大連接配接數)
max_user_connections = 1000   --(The maximum number of simultaneous connections permitted to any given MySQL user account. A value of 0 (the default) means “no limit.” 是指每個資料庫使用者的最大連接配接數)
max_connect_errors = 10000   --(If more than this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections.是一個MySQL中與安全有關的計數器值，它負責阻止過多嘗試失敗的用戶端以防止暴力破解密碼的情況)
key_buffer_size = 64M   --(Index blocks for MyISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.)
max_allowed_packet = 128M    --(The maximum size of one packet or any generated/intermediate string, or any parameter sent by the mysql_stmt_send_long_data() C API function Server接受的資料包大小,避免大資料寫入或者更新失敗)
table_cache = 3096   --(5.1.13之前的版本)
table_open_cache = 6144    --(The number of open tables for all threads.)
table_definition_cache= 4096  --(The number of table definitions (from .frm files) that can be stored in the definition cache)
sort_buffer_size = 512K
read_buffer_size - 512K
read_rnd_buffer_size = 512K   --(This variable is used for reads from MyISAM tables, and, for any storage engine, for Multi-Range Read optimization.)
join_buffer_size = 512K  --(The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans)
tmp_table_size = 64M    --(The maximum size of internal in-memory temporary tables. This variable does not apply to user-created MEMORY tables.)
max_heap_table_size = 64M     --(This variable sets the maximum size to which user-created MEMORY tables are permitted to grow 它規定了内部記憶體臨時表的最大值，每個線程都要配置設定。（實際起限制作用的是tmp_table_size和max_heap_table_size的最小值。）如果記憶體臨時表超出了限制，MySQL就會自動地把它轉化為基于磁盤的MyISAM表，存儲在指定的tmpdir目錄下)
query_cache_type = 0  --(Set the query cache type. Setting the GLOBAL value sets the type for all clients that connect thereafter.不緩存查詢結果)
query_cache_size = 0  --(The amount of memory allocated for caching query results)
bulk_insert_buffer_size = 32M   --(MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables.)
thread_cache_size = 64    --(How many threads the server should cache for reuse.)
#thread_concurrency = 32   --(This variable is specific to Solaris 8 and earlier systems, for which mysqld invokes the thr_setconcurrency() function with the variable value, This variable is deprecated as of MySQL 5.6.1 and is removed in MySQL 5.7)
thread_stack = 256K  (The stack size for each thread.)

########InnoDB###############
innodb_data_home_dir = /mysqlData/data
innodb_log_group_home_dir = /mysqlLog/logs
innodb_data_file_path = ibdata1:2G:autoextend
innodb_buffer_pool_size = 50G
innodb_buffer_pool_instances = 8
innodb_additional_mem_pool_size = 16M   --(The size in bytes of a memory pool InnoDB uses to store data dictionary information and other internal data structures.As of MySQL 5.6.3, innodb_additional_mem_pool_size is deprecated and will be removed in a future MySQL release.)
innodb_log_file_size = 1024M
innodb_log_buffer_size = 64M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 2    --(Controls the balance between strict ACID compliance for commit operations, and higher performance that is possible when commit-related I/O operations are rearranged and done in batches.)
innodb_lock_wait_timeout = 10   --(The length of time in seconds an InnoDB transaction waits for a row lock before giving up)
innodb_sync_spin_loops = 40   --(The number of times a thread waits for an InnoDB mutex to be freed before the thread is suspended.)
innodb_max_dirty_pages_pct = 90   --(InnoDB tries to flush data from the buffer pool so that the percentage of dirty pages does not exceed this value.)
innodb_support_xa = 1    --(Enables InnoDB support for two-phase commit in XA transactions, causing an extra disk flush for transaction preparation.)
innodb_thread_concurrency = 0   --(InnoDB tries to keep the number of operating system threads concurrently inside InnoDB less than or equal to the limit given by this variable (InnoDB uses operating system threads to process user transactions).跟cpu有關)
innodb_thread_sleep_delay = 500    --(How long InnoDB threads sleep before joining the InnoDB queue, in microseconds.)
innodb_file_io_threads = 4    --(在innodb plugin中，不在使用innodb_file_io_threads參數，而使用innodb_read_io_threads and innodb_write_io_threads 兩個值代替)
innodb_concurrency_tickets = 1000    --(Determines the number of threads that can enter InnoDB concurrently)
c = 1   --(跟建立函數相關的操作)
innodb_flush_method = O_DIRECT   --(Defines the method used to flush data to the InnoDB data files and log files, which can affect I/O throughput,O_DIRECT: InnoDB uses O_DIRECT (or directio() on Solaris) to open the data files, and uses fsync() to flush both the data and log files. This option is available on some GNU/Linux versions, FreeBSD, and Solaris.)
innodb_file_per_table
innodb_read_io_threads = 16  --(The number of I/O threads for read operations in InnoDB.)
innodb_write_io_threads = 16  --(The number of I/O threads for write operations in InnoDB.)
innodb_io_capacity = 2000   --(The innodb_io_capacity parameter sets an upper limit on the I/O activity performed by the InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the change buffer.在MySQL中一次重新整理的髒頁的數量有一個 innodb_io_capacity的參數進行控制， innodb_io_capacity越大，一次重新整理的髒頁的數量也就越大)
innodb_file_format = Barracuda   --(The file format to use for new InnoDB tables. Currently, Antelope and Barracuda are supported.)
innodb_purge_threads = 1    --(The number of background threads devoted to the InnoDB purge operation)
innodb_purge_batch_size = 32     --(The granularity of changes, expressed in units of redo log records, that trigger a purge operation, flushing the changed buffer pool blocks to disk. 表示一次完成多少個undolog page 但這個值有一個有趣的副作用是會影響到undolog的釋放，因為總是在128輪purge後釋放undolog page，在5.5及之後版本，開始支援128個復原段)
innodb_old_blocks_pct = 75    --(Specifies the approximate percentage of the InnoDB buffer pool used for the old block sublist.)
innodb_change_buffering = all    --(Whether InnoDB performs change buffering, an optimization that delays write operations to secondary indexes so that the I/O operations can be performed sequentially. 當更新/插入的非聚集索引的資料所對應的頁不在記憶體中時（對非聚集索引的更新操作通常會帶來随機IO），會将其放到一個insert buffer中，當随後頁面被讀到記憶體中時，會将這些變化的記錄merge到頁中。當伺服器比較空閑時，背景線程也會做merge操作)
transaction_isolation = READ-COMMITTED

[mysqldump]
quick
max_allowed_packet = 128M
myisam_max_sort_file_size = 10G

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 256K
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 28192      

轉載于:https://www.cnblogs.com/godspeed034/p/7357146.html