dimanche 30 novembre 2014

How to prevent thread_created and Innodb_master_thread_active_loops grow up very quickly?


I am using maria galera cluster include three nodes: two cluster node and one garbd. I notice Threads_created and Innodb_master_thread_active_loops increase very quickly.


My information system:



Centos 6.6
linux kernel 2.6.32-431.el6.x86_64
Mariadb Server version 10.0.14
Using two SSD disk 480GB/disk Raid 1
32G Ram
16G Swap


This is my config of mysql server:



[galera]
bind_address=0.0.0.0
innodb_flush_log_at_trx_commit=1
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=<galera_user>:<galera_passwd>
wsrep_cluster_name=xxx
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_node_address='<ip of node 1>'
wsrep_node_name='node1'
wsrep_cluster_address=gcomm://<ip of node 2>

wsrep_slave_threads=24
wsrep_auto_increment_control=0
wsrep_provider_options = "gcache.size = 32768M;gcache.name = /data/galera/galera.cache;gcache.dir=/data/galera/;gcs.recv_q_hard_limit=32768M;gcs.fc_limit=16;gcs.fc_master_slave=YES;gcs.fc_factor=0.5"

wsrep_sst_receive_address=<ip of node 1>
wsrep_sst_donor=node1,node2
wsrep_notify_cmd=

wsrep_log_conflicts=ON
wsrep_certify_nonPK=ON
wsrep_recover=OFF

wsrep_max_ws_row=128000
wsrep_max_ws_size=1G

[mariadb]
### basic ###
user=mysql
datadir=/var/lib/mysql
log_error=/var/log/mysqld/mysql_error.log
socket=/var/lib/mysql/mysql.sock
port=3306
default_storage_engine=InnoDB
binlog_format=ROW
innodb_autoinc_lock_mode=2
innodb_doublewrite=1
sync_binlog=1
skip_name_resolve=1
log_output=FILE
sort_buffer_size=32M
max_allowed_packet=64M
low_priority_updates = 1

### innodb ###
innodb_buffer_pool_size = 26000M
innodb_buffer_pool_instances = 64
innodb_file_per_table = 1
innodb_write_io_threads = 20
innodb_read_io_threads = 8
innodb_io_capacity = 1000
innodb_flush_method=O_DIRECT
innodb_log_buffer_size= 8M
innodb_open_files = 81920
innodb_thread_concurrency=50

### buffer ###
bulk_insert_buffer_size = 16M
read_buffer_size = 8M
read_rnd_buffer_size = 524288

### query cache ###
query_cache_limit = 8M
query_cache_size = 256M
query_cache_type = 1
query_prealloc_size = 163840
query_alloc_block_size = 32768

### table open cache ###
table_open_cache=8192

### network ###
back_log=75

### thread and connection ###
thread_stack = 256K
thread_cache_size = 64M
max_connections = 4096

### timeout ###
wait_timeout=30
interactive_timeout=30
low_priority_updates = 1
connect_timeout=60
net_read_timeout=60
net_write_timeout=60


### temporary tables ###
tmp_table_size=1024M
max_heap_table_size=1024M
tmpdir=/dev/shm

### slow query ###
slow_query_log=1
long_query_time = 2
slow_query_log_file = /var/log/mysqld/mysql_slow.log

### general log ###
# for debugging
general_log=0
general_log_file=/var/log/mysqld/mysql_general.log

### redo log ###
innodb_log_files_in_group=2
innodb_log_file_size=67108864

### replication ###
expire_logs_days=4
log_bin = /var/log/mysql/mysql-bin
log_bin_index = /var/log/mysql/mysql-bin.log.index
max_binlog_size = 100M
server_id=<server-id>
log_slave_updates=1

##security ###
max_connect_errors=10

###thread pool###
thread_handling=pool-of-threads
thread_pool_max_threads=200
thread_pool_idle_timeout=30
thread_pool_stall_limit=300

[xtrabackup]
datadir = /var/lib/mysql
streamfmt=xbstream


I hope someone help me in situation. I am getting stuck.





Aucun commentaire:

Enregistrer un commentaire