Hi, i ran a cluster with 3 nodes. It’s created and run normally in about 1 day before 1 node crash randomly.
My information
-
Server version: 5.6.21-70.1-56-log Percona XtraDB Cluster (GPL), Release rel70.1, Revision 938, WSREP version 25.8, wsrep_25.8.r4150
-
Ram 32G, CPU 24 core, 4 HDD - raid 10
-
datafile: 4G
-
file: /etc/my.conf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
REPLICATE
Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_provider_options=“gcache.size = 1G; gcache.page_size = 512M; gcs.fc_limit = 512”
wsrep_slave_threads=24
wsrep_restart_slave=1
wsrep_forced_binlog_format=ROW
Cluster connection URL contains IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.1.83,192.168.1.84,192.168.1.85
In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
Node #2 address
wsrep_node_address=192.168.1.xx
Cluster name
wsrep_cluster_name=my_centos_cluster
SST method
wsrep_sst_method=xtrabackup-v2
#Authentication for SST method
wsrep_sst_auth=“xxx:xxx”
Maximum number of rows in write set
wsrep_max_ws_rows=262144
Maximum size of write set
wsrep_max_ws_size=2147483648
#################### TUNNING ########################
Slow query log
slow_query_log=1
slow_query_log_file =/var/log/mysql/slow_queries.log
long_query_time=4
connect_timeout=300
skip_name_resolve
innodb_flush_log_at_trx_commit=2
innodb_file_per_table=1
max_allowed_packet=1G
max_connect_errors=1000000
innodb_buffer_pool_size=4G
read_buffer_size=4M
read_rnd_buffer_size=4M
join_buffer_size=8M
sort_buffer_size=4M
innodb_log_buffer_size=16M
thread_cache_size=256
innodb_additional_mem_pool_size=32M
innodb_flush_method=O_DIRECT
log_queries_not_using_indexes=1
innodb_thread_concurrency=0
wait_timeout=300
interactive_timeout=300
max_connections=800
innodb_fast_shutdown=0
open_files_limit=10000
table_open_cache=3000
tmp_table_size=32M
max_heap_table_size=32M
Set Ramdisk
tmpdir = /usr/mysqltmp
#######################
- Error message
05:05:00 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
Please help us make Percona XtraDB Cluster better by reporting any
bugs at [url]https://bugs.launchpad.net/percona-xtradb-cluster[/url]
key_buffer_size=8388608
read_buffer_size=4194304
max_used_connections=45
max_threads=802
thread_count=27
connection_count=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 6590372 K bytes of memory
Hope that’s ok; if not, decrease some variables in the equation.
Thread pointer: 0xa09eba0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong…
stack_bottom = 7fa617fedd38 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x8f97d5]
/usr/sbin/mysqld(handle_fatal_signal+0x4b4)[0x6655c4]
/lib64/libpthread.so.0(+0xf710)[0x7fa78c98e710]
/usr/sbin/mysqld(_Z11ull_get_keyPKhPmc+0x14)[0x5fb2b4]
/usr/sbin/mysqld(my_hash_first_from_hash_value+0x6b)[0x8e2c4b]
/usr/sbin/mysqld(my_hash_search+0x11)[0x8e2e31]
/usr/sbin/mysqld(_ZN22Item_func_release_lock7val_intEv+0x10f)[0x60068f]
/usr/sbin/mysqld(_ZN4Item4sendEP8ProtocolP6String+0x1c4)[0x5b06d4]
/usr/sbin/mysqld(_ZN8Protocol19send_result_set_rowEP4ListI4ItemE+0xc7)[0x65ef47]
/usr/sbin/mysqld(_ZN11select_send9send_dataER4ListI4ItemE+0x67)[0x6ae287]
/usr/sbin/mysqld(_ZN4JOIN4execEv+0x521)[0x6c9e81]
/usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_P10SQL_I_ListI8st_orderESB_S7_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x250)[0x7120e0]
/usr/sbin/mysqld(_Z13handle_selectP3THDP13select_resultm+0x187)[0x712967]
/usr/sbin/mysqld[0x6e836d]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x3cdb)[0x6ed50b]
/usr/sbin/mysqld(_ZN18Prepared_statement7executeEP6Stringb+0x40e)[0x7002ae]
/usr/sbin/mysqld(ZN18Prepared_statement12execute_loopEP6StringbPhS2+0xde)[0x7044ae]
/usr/sbin/mysqld(_Z22mysql_sql_stmt_executeP3THD+0xbe)[0x70500e]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x1324)[0x6eab54]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x658)[0x6f0338]
/usr/sbin/mysqld[0x6f0491]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x19d5)[0x6f2675]
/usr/sbin/mysqld(_Z10do_commandP3THD+0x22b)[0x6f3b5b]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x17f)[0x6bc30f]
/usr/sbin/mysqld(handle_one_connection+0x47)[0x6bc4f7]
/usr/sbin/mysqld(pfs_spawn_thread+0x12a)[0xaf38ba]
/lib64/libpthread.so.0(+0x79d1)[0x7fa78c9869d1]
/lib64/libc.so.6(clone+0x6d)[0x7fa78ae8a8fd]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fa4dc004178): is an invalid pointer
Connection ID (thread ID): 6222895
Status: NOT_KILLED
You may download the Percona XtraDB Cluster operations manual by visiting
[url]http://www.percona.com/software/percona-xtradb-cluster/[/url]. You may find information
in the manual which will help you identify the cause of the crash.
150608 12:05:00 mysqld_safe Number of processes running now: 0
150608 12:05:00 mysqld_safe WSREP: not restarting wsrep node automatically
150608 12:05:00 mysqld_safe mysqld from pid file /var/lib/mysql/RR-Cluster-DB2.pid ended
I dig the internet for a month but nothing can fix my error. Hope some here, in percona forum can help me.
Thanks million times.