mariadb5.5优化配置参数

mariadb5.5提供了aria存储引擎和线程池,功能比mysql强大

个人打开查询缓存, 线程池, 设置默认引擎Aria ,关闭innodb,关闭bin-log(重要数据多备份),添加jemalloc

https://mariadb.com/kb/en/library/thread-pool-in-mariadb/

[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock

[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
user    = mariadb
basedir = /usr/local/mariadb
datadir = /usr/local/mariadb/var
log_error = /usr/local/mariadb/var/mariadb.err
pid-file = /usr/local/mariadb/var/mariadb.pid

default_storage_engine = Aria
loose-skip-innodb
skip-external-locking
skip-name-resolve
thread_handling = pool-of-threads
thread_pool_stall_limit = 100
init_connect='SET NAMES utf8mb4' 
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
skip-character-set-client-handshake = 1

key_buffer_size = 512M
max_allowed_packet = 64M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
net_buffer_length = 16K
read_buffer_size = 2M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 32M
thread_cache_size = 32
query_cache_size = 64M
query_cache_type = 1
tmp_table_size = 512M

aria_pagecache_buffer_size = 512M
aria_sort_buffer_size = 256M

#skip-networking
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

#log-bin=mysql-bin
#binlog_format=mixed
server-id       = 1
expire_logs_days = 3

[mysqldump]
quick
max_allowed_packet = 64M

[mysql]
no-auto-rehash

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

[mysqlhotcopy]
interactive-timeout


查询aria版本
SELECT * FROM information_schema.plugins where PLUGIN_NAME='Aria' ;
SELECT * FROM information_schema.plugins where PLUGIN_NAME like '%aria%' ;
alter table table_name engine=Aria; 

# 数据库设置成utf8mb4:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# 数据表设置成utf8mb4:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# 列设置成utf8mb4:
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

此处评论已关闭