mariadb數(shù)據(jù)庫(kù)優(yōu)化需要根據(jù)自己業(yè)務(wù)需求以及根據(jù)硬件配置來(lái)進(jìn)行參數(shù)優(yōu)化，下面是一些關(guān)于mariadb數(shù)據(jù)庫(kù)參數(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ù)庫(kù)存放目錄
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                           #跳過(guò)外部鎖定
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ù)庫(kù)服務(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                          #每個(gè)connection（session）第一次需要使用這個(gè)buffer的時(shí)候，一次性分配設(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ā)生變化時(shí)重新排序所需的緩沖
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)閉之前，將對(duì)表的update操作指跟新數(shù)據(jù)到磁盤，而不更新索引到磁盤，把對(duì)索引的更改記錄在內(nèi)存。這樣MyISAM表可以使索引更新更快。在關(guān)閉表的時(shí)候一起更新索引到磁盤
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ù)。每個(gè)連接MySQL的用戶均算作一個(gè)連接
max_connect_errors=30                           #最大失敗連接限制
interactive_timeout=600                         #服務(wù)器關(guān)閉交互式連接前等待活動(dòng)的秒數(shù)
wait_timeout=3600                               #服務(wù)器關(guān)閉非交互連接之前等待活動(dòng)的秒數(shù)
slow_query_log                                  #慢查詢記錄日志
long_query_time = 0.1                           #慢查詢記錄時(shí)間  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語(yǔ)句復(fù)制模式
event_scheduler=1                               #計(jì)劃任務(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)化器開(kāi)關(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

[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ù)庫(kù)存放目錄
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                           #跳過(guò)外部鎖定
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ù)庫(kù)服務(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                          #每個(gè)connection（session）第一次需要使用這個(gè)buffer的時(shí)候，一次性分配設(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ā)生變化時(shí)重新排序所需的緩沖
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)閉之前，將對(duì)表的update操作指跟新數(shù)據(jù)到磁盤，而不更新索引到磁盤，把對(duì)索引的更改記錄在內(nèi)存。這樣MyISAM表可以使索引更新更快。在關(guān)閉表的時(shí)候一起更新索引到磁盤
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ù)。每個(gè)連接MySQL的用戶均算作一個(gè)連接
max_connect_errors=30                           #最大失敗連接限制
interactive_timeout=600                         #服務(wù)器關(guān)閉交互式連接前等待活動(dòng)的秒數(shù)
wait_timeout=3600                               #服務(wù)器關(guān)閉非交互連接之前等待活動(dòng)的秒數(shù)
slow_query_log                                  #慢查詢記錄日志
long_query_time = 0.1                           #慢查詢記錄時(shí)間  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語(yǔ)句復(fù)制模式
event_scheduler=1                               #計(jì)劃任務(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)化器開(kāi)關(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