MariaDB/MySQL配置文件參數(shù)優(yōu)化
技術(shù)支持服務(wù)電話：15308000360 【7x24提供運(yùn)維服務(wù)，解決各類系統(tǒng)/軟硬件疑難技術(shù)問題】
mariadb數(shù)據(jù)庫優(yōu)化需要根據(jù)自己業(yè)務(wù)需求以及根據(jù)硬件配置來進(jìn)行參數(shù)優(yōu)化，下面是一些關(guān)于mariadb數(shù)據(jù)庫參數(shù)優(yōu)化的配置文件。 如下為128G內(nèi)存32線程處理器的MySQL/MariaDB配置參數(shù)優(yōu)化：
[client] #password= your_password port= 3306 socket= /tmp/mysql.sock !includedir /opt/local/mysql/wsrep # The MySQL server [mysqld] port= 3306 socket= /tmp/mysql.sock basedir = /opt/local/mysql datadir=/opt/local/mysql/data #數(shù)據(jù)庫存放目錄 relay-log=/opt/local/mysql/relaylog/s74-relay-bin pid-file = /opt/local/mysql/mysql.pid log-error = /opt/local/mysql/logs/mysqld.log open_files_limit = 65535 # #skip-locking skip-external-locking #跳過外部鎖定 back_log=3000 #暫存的連接數(shù)量 skip-name-resolve #關(guān)閉mysql的dns反查功能 memlock #將mysqld 進(jìn)程鎖定在內(nèi)存中 lower_case_table_names = 1 #query_response_time_stats=1 #core-file #core-file-size = unlimited query_cache_type=1 #查詢緩存 (0 = off、1 = on、2 = demand) performance_schema=0 #收集數(shù)據(jù)庫服務(wù)器性能參數(shù) net_read_timeout=3600 #連接繁忙階段（query）起作用 net_write_timeout=3600 #連接繁忙階段（query）起作用 key_buffer_size = 32M #設(shè)置索引塊緩存大小 max_allowed_packet = 128M #通信緩沖大小 table_open_cache = 1024 #table高速緩存的數(shù)量 sort_buffer_size = 12M #每個connection（session）第一次需要使用這個buffer的時候，一次性分配設(shè)置的內(nèi)存 read_buffer_size = 8M #順序讀取數(shù)據(jù)緩沖區(qū)使用內(nèi)存 #sort_buffer_size = 32M #read_buffer_size = 32M read_rnd_buffer_size = 32M #隨機(jī)讀取數(shù)據(jù)緩沖區(qū)使用內(nèi)存 myisam_sort_buffer_size = 32M #MyISAM表發(fā)生變化時重新排序所需的緩沖 thread_cache_size = 120 #重新利用保存在緩存中線程的數(shù)量 query_cache_size = 64M join_buffer_size = 8M #Join操作使用內(nèi)存 bulk_insert_buffer_size = 32M #批量插入數(shù)據(jù)緩存大小 delay_key_write=ON #在表關(guān)閉之前，將對表的update操作指跟新數(shù)據(jù)到磁盤，而不更新索引到磁盤，把對索引的更改記錄在內(nèi)存。這樣MyISAM表可以使索引更新更快。在關(guān)閉表的時候一起更新索引到磁盤 delayed_insert_limit=4000 delayed_insert_timeout=600 delayed_queue_size=4000 # Try number of CPU's*2 for thread_concurrency # The variable only affects Solaris! thread_concurrency = 64 #CPU核數(shù) * 2 max_connections=1500 #最大連接（用戶）數(shù)。每個連接MySQL的用戶均算作一個連接 max_connect_errors=30 #最大失敗連接限制 interactive_timeout=600 #服務(wù)器關(guān)閉交互式連接前等待活動的秒數(shù) wait_timeout=3600 #服務(wù)器關(guān)閉非交互連接之前等待活動的秒數(shù) slow_query_log #慢查詢記錄日志 long_query_time = 0.1 #慢查詢記錄時間 0.1秒 slow_query_log_file=/opt/local/mysql/logs/slow_query.log #慢查詢?nèi)罩韭窂? #log_slow_verbosity=full log_slow_verbosity=query_plan # # Replication Master Server (default) # binary logging is required for replication log-bin=/opt/local/mysql/binlog/mysql-bin #binlog 名稱 log-slave-updates #從master取得并執(zhí)行的二進(jìn)制日志寫入自己的二進(jìn)制日志文件中 replicate-ignore-db=mysql #不同步的表 # binary logging format - mixed recommended binlog_format=row #binlog 格式 分別為 row=行格式 丶 mixed=混合格式 丶 STATEMENT=SQL語句復(fù)制模式 event_scheduler=1 #計劃任務(wù) 事件調(diào)度器 # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id= 1 # # Point the following paths to different dedicated disks #tmpdir= /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /data/mysql-5.1.48/mysql-data/ #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend #innodb_log_group_home_dir = /data/mysql-5.1.48/mysql-data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_file_format=barracuda innodb_file_format_max=barracuda innodb_file_per_table=1 innodb_fast_shutdown=0 innodb_buffer_pool_size = 90000M innodb_buffer_pool_instances = 4 #innodb_additional_mem_pool_size = 20M #innodb_use_sys_malloc = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 512M #innodb_log_buffer_size = 8M innodb_log_files_in_group = 3 innodb_flush_log_at_trx_commit = 2 innodb_lock_wait_timeout = 3 innodb_rollback_on_timeout = on innodb_flush_method=O_DIRECT transaction-isolation=READ-COMMITTED innodb_thread_concurrency=0 innodb_io_capacity=800 innodb_purge_threads=1 innodb_open_files=65535 #innodb_stats_update_need_lock=0 #innodb_flush_neighbor_pages=0 #innodb_aio_pending_ios_per_thread=256 #for binlog_format=row innodb_autoinc_lock_mode=2 #innodb_fast_checksum = 1 innodb_read_io_threads = 8 innodb_write_io_threads = 12 innodb_stats_on_metadata = 0 #使用線程池處理連接 thread_handling=pool-of-threads thread_pool_oversubscribe=30 thread_pool_size=64 thread_pool_idle_timeout=7200 thread_pool_max_threads=2000 #查詢優(yōu)化器開關(guān) #optimizer_switch='index_condition_pushdown=on' #optimizer_switch='mrr=on' #optimizer_switch='mrr_sort_keys=on' #optimizer_switch='mrr_cost_based=off' #mrr_buffer_size=32M #optimizer_switch='join_cache_incremental=on' #optimizer_switch='join_cache_hashed=on' #optimizer_switch='join_cache_bka=on' #join_cache_level=4 #join_buffer_size=32M #join_buffer_space_limit=32M [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [myisamchk] key_buffer_size = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [isamchk] key_buffer_size = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
如下為256G內(nèi)存64線程處理器的MySQL/MariaDB配置參數(shù)優(yōu)化：
[client] #password= your_password port= 3306 socket= /tmp/mysql.sock !includedir /opt/local/mysql/wsrep # The MySQL server [mysqld] port= 3306 socket= /tmp/mysql.sock basedir = /opt/local/mysql datadir=/opt/local/mysql/data #數(shù)據(jù)庫存放目錄 relay-log=/opt/local/mysql/relaylog/s74-relay-bin pid-file = /opt/local/mysql/mysql.pid log-error = /opt/local/mysql/logs/mysqld.log open_files_limit = 65535 # #skip-locking skip-external-locking #跳過外部鎖定 back_log=3000 #暫存的連接數(shù)量 skip-name-resolve #關(guān)閉mysql的dns反查功能 memlock #將mysqld 進(jìn)程鎖定在內(nèi)存中 lower_case_table_names = 1 #query_response_time_stats=1 #core-file #core-file-size = unlimited query_cache_type=1 #查詢緩存 (0 = off、1 = on、2 = demand) performance_schema=0 #收集數(shù)據(jù)庫服務(wù)器性能參數(shù) net_read_timeout=3600 #連接繁忙階段（query）起作用 net_write_timeout=3600 #連接繁忙階段（query）起作用 key_buffer_size = 32M #設(shè)置索引塊緩存大小 max_allowed_packet = 128M #通信緩沖大小 table_open_cache = 1024 #table高速緩存的數(shù)量 sort_buffer_size = 12M #每個connection（session）第一次需要使用這個buffer的時候，一次性分配設(shè)置的內(nèi)存 read_buffer_size = 8M #順序讀取數(shù)據(jù)緩沖區(qū)使用內(nèi)存 #sort_buffer_size = 32M #read_buffer_size = 32M read_rnd_buffer_size = 32M #隨機(jī)讀取數(shù)據(jù)緩沖區(qū)使用內(nèi)存 myisam_sort_buffer_size = 32M #MyISAM表發(fā)生變化時重新排序所需的緩沖 thread_cache_size = 120 #重新利用保存在緩存中線程的數(shù)量 query_cache_size = 64M join_buffer_size = 8M #Join操作使用內(nèi)存 bulk_insert_buffer_size = 32M #批量插入數(shù)據(jù)緩存大小 delay_key_write=ON #在表關(guān)閉之前，將對表的update操作指跟新數(shù)據(jù)到磁盤，而不更新索引到磁盤，把對索引的更改記錄在內(nèi)存。這樣MyISAM表可以使索引更新更快。在關(guān)閉表的時候一起更新索引到磁盤 delayed_insert_limit=4000 delayed_insert_timeout=600 delayed_queue_size=4000 # Try number of CPU's*2 for thread_concurrency # The variable only affects Solaris! thread_concurrency = 96 #CPU核數(shù) * 2 max_connections=1500 #最大連接（用戶）數(shù)。每個連接MySQL的用戶均算作一個連接 max_connect_errors=30 #最大失敗連接限制 interactive_timeout=600 #服務(wù)器關(guān)閉交互式連接前等待活動的秒數(shù) wait_timeout=3600 #服務(wù)器關(guān)閉非交互連接之前等待活動的秒數(shù) slow_query_log #慢查詢記錄日志 long_query_time = 0.1 #慢查詢記錄時間 0.1秒 slow_query_log_file=/opt/local/mysql/logs/slow_query.log #慢查詢?nèi)罩韭窂? #log_slow_verbosity=full log_slow_verbosity=query_plan # # Replication Master Server (default) # binary logging is required for replication log-bin=/opt/local/mysql/binlog/mysql-bin #binlog 名稱 log-slave-updates #從master取得并執(zhí)行的二進(jìn)制日志寫入自己的二進(jìn)制日志文件中 replicate-ignore-db=mysql #不同步的表 # binary logging format - mixed recommended binlog_format=row #binlog 格式 分別為 row=行格式 丶 mixed=混合格式 丶 STATEMENT=SQL語句復(fù)制模式 event_scheduler=1 #計劃任務(wù) 事件調(diào)度器 # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id= 1 # # Point the following paths to different dedicated disks #tmpdir= /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /data/mysql-5.1.48/mysql-data/ #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend #innodb_log_group_home_dir = /data/mysql-5.1.48/mysql-data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_file_format=barracuda innodb_file_format_max=barracuda innodb_file_per_table=1 innodb_fast_shutdown=0 innodb_buffer_pool_size = 180000M innodb_buffer_pool_instances = 4 #innodb_additional_mem_pool_size = 20M #innodb_use_sys_malloc = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 512M #innodb_log_buffer_size = 8M innodb_log_files_in_group = 3 innodb_flush_log_at_trx_commit = 2 innodb_lock_wait_timeout = 3 innodb_rollback_on_timeout = on innodb_flush_method=O_DIRECT transaction-isolation=READ-COMMITTED innodb_thread_concurrency=0 innodb_io_capacity=800 innodb_purge_threads=1 innodb_open_files=65535 #innodb_stats_update_need_lock=0 #innodb_flush_neighbor_pages=0 #innodb_aio_pending_ios_per_thread=256 #for binlog_format=row innodb_autoinc_lock_mode=2 #innodb_fast_checksum = 1 innodb_read_io_threads = 8 innodb_write_io_threads = 12 innodb_stats_on_metadata = 0 #使用線程池處理連接 thread_handling=pool-of-threads thread_pool_oversubscribe=30 thread_pool_size=64 thread_pool_idle_timeout=7200 thread_pool_max_threads=2000 #查詢優(yōu)化器開關(guān) #optimizer_switch='index_condition_pushdown=on' #optimizer_switch='mrr=on' #optimizer_switch='mrr_sort_keys=on' #optimizer_switch='mrr_cost_based=off' #mrr_buffer_size=32M #optimizer_switch='join_cache_incremental=on' #optimizer_switch='join_cache_hashed=on' #optimizer_switch='join_cache_bka=on' #join_cache_level=4 #join_buffer_size=32M #join_buffer_space_limit=32M [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [myisamchk] key_buffer_size = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [isamchk] key_buffer_size = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
