Thứ Hai, 13 tháng 3, 2017

Some problems with mysql & mariadb

MySQL max_open_files more than 1024


Edit /etc/security/limits.conf and add the following lines
mysql soft nofile 65535
mysql hard nofile 65535

 Edit file /usr/lib/systemd/system/mysqld.service ( /usr/lib/systemd/system/mariadb.service  ) and /etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf 

Add at end:

LimitNOFILE=65535
LimitNPROC=65535

OR :

LimitNOFILE=infinity
LimitMEMLOCK=infinity



Save file and exexute:
# systemctl daemon-reload

Restart mysql:
# service mysqld restart ( service mariadb restart)

 

vim /etc/my.cnf

 [mysqld]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#log-error=/var/log/mysqld.log

slow-query-log=1
slow-query-log-file=/var/lib/mysql/mysqld-slow.log

user=mysql
binlog_format=ROW
bind-address=0.0.0.0


query_cache_type=0
query_cache_limit=512M
query_cache_size=256M
query_cache_min_res_unit=2k
performance_schema = OFF
innodb_use_sys_malloc=0


#thread_concurrency=24
innodb_thread_concurrency = 24


key_buffer_size=256M
join_buffer_size=64M
myisam_sort_buffer_size=16M
sort_buffer_size=16M
read_buffer_size=64M
read_rnd_buffer_size=32M


bulk_insert_buffer_size=16M
myisam_sort_buffer_size=16M
max_allowed_packet=16M
max_connections = 200
max_heap_table_size=512M

default_storage_engine=innodb
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
innodb_log_file_size=2G
innodb_io_capacity=400

innodb_file_per_table=1
innodb_file_format=Barracuda

innodb_support_xa=0

tmp_table_size=256M

thread_stack = 192K

innodb_buffer_pool_size=2048M
innodb_open_files=400
innodb_log_buffer_size=8M
thread_cache_size=32
innodb_file_per_table=ON
innodb_buffer_pool_instances=2
innodb_flush_log_at_trx_commit = 0


innodb_flush_log_at_trx_commit=2
open_files_limit=50000
skip-name-resolve
table_open_cache=4096
table_definition_cache=4096




[mysqldump]
quote-names
max_allowed_packet = 16M

[isamchk]
key_buffer_size = 16M

[mysql_safe]
#log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid


Không có nhận xét nào:

Đăng nhận xét