hi,
how to optimized the galera replication setting based on :
1) gcs.fc_limit (usually = wsrep_slave_threads * 5)
2) gcs.fc_factor ( is 0.8 is good for it ?)
3) innodb_flush_log_at_trx_commit
also :
1) how to Set wsrep_slave_threads in case of
HA_ERR_FOUND_DUPP_KEY crash on slave?
2) set up parameter wsrep_notify_cmd to be notify
hi,
tks for your reply and actually I want to focus on this today as according to my other ticket about the mysqltuner.sh
what it suggest is:
gcs.fc_limit= wsrep_slave_threads * 5
gcs.fc_factor=0.8
innodb_flush_log_at_trx_commit = 0
Select allOpen in new window
and I read this:
https://github.com/major/MySQLTuner-perl/issues/374
And here, https://github.com/major/MySQLTuner-perl/issues/305
this is the conclusion :
``` wsrep_slave_threads= Nb of Core CPU * 4 Or just wsrep_slave_threads= Nb of Core CPU * 2 to avoid over kill.
<p>Select all<a rel="nofollow" href="https://www.experts-exchange.com/viewCodeSnippet.jsp?codeSnippetId=20-43148958-2">Open in new window</a></p><br> which one is true ?<br><br>Once this is set, this can be set:<br><br>
gcs.fc_limit= wsrep_slave_threads * 5
<p>Select all<a rel="nofollow" href="https://www.experts-exchange.com/viewCodeSnippet.jsp?codeSnippetId=20-43148958-3">Open in new window</a></p><br> then I can also set this in my.cnf:
gcs.fc_factor=0.8
<p>Select all<a rel="nofollow" href="https://www.experts-exchange.com/viewCodeSnippet.jsp?codeSnippetId=20-43148958-4">Open in new window</a></p> <br> then it is solved.<br><br><br>and this one:<br>"set up parameter wsrep_notify_cmd to be notify "<br>is about email notisification about change in cluster status, which I found information like:<br><br><a rel="nofollow" href="https://galeracluster.com/library/documentation/notification-cmd.html">https://galeracluster.com/library/documentation/notification-cmd.html</a> <br><br>"You can enable your notification script or command through the <a rel="nofollow" href="https://galeracluster.com/library/documentation/mysql-wsrep-options.html#wsrep-notify-cmd">wsrep_notify_cmd</a> parameter in the configuration file. Below is an excerpt from that file showing how it might look:<br>wsrep_notify_cmd=/path/wsrep_notify.sh<br> <br>"<br><br>so should I say I only have this left:<br><br>
innodb_flush_log_at_trx_commit
<p>Select all<a rel="nofollow" href="https://www.experts-exchange.com/viewCodeSnippet.jsp?codeSnippetId=20-43148958-5">Open in new window</a></p>what is this ?<br><br>and I just tried this:<br><br>
Galera replication
wsrep_slave_threads=16
gcs.fc_limit=80
gcs.fc_factor=0.8
innodb_flush_log_at_trx_commit = 0
<p>Select all<a rel="nofollow" href="https://www.experts-exchange.com/viewCodeSnippet.jsp?codeSnippetId=20-43148958-6">Open in new window</a></p><br>it make mysql failed to start and this is the error message:<br><br>
.183:4567
2020-08-31T18:27:02.320765+08:00 0 [Note] [MY-000000] [Galera] (824e6d7c, ‘tcp://0.0.0.0:4567’) connection established to e3b5f89f tcp://172.18.116.184:4567
2020-08-31T18:27:02.321442+08:00 0 [Note] [MY-000000] [Galera] (824e6d7c, ‘tcp://0.0.0.0:4567’) turning message relay requesting on, nonlive peers:
2020-08-31T18:27:02.321743+08:00 0 [Note] [MY-000000] [Galera] (824e6d7c, ‘tcp://0.0.0.0:4567’) connection established to 119c9d5c tcp://172.18.116.187:4567
2020-08-31T18:27:02.809231+08:00 0 [Note] [MY-000000] [Galera] EVS version upgrade 0 -> 1
2020-08-31T18:27:02.809914+08:00 0 [Note] [MY-000000] [Galera] declaring 119c9d5c at tcp://172.18.116.187:4567 stable
2020-08-31T18:27:02.810387+08:00 0 [Note] [MY-000000] [Galera] declaring e3b5f89f at tcp://172.18.116.184:4567 stable
2020-08-31T18:27:02.810826+08:00 0 [Note] [MY-000000] [Galera] PC protocol upgrade 0 -> 1
2020-08-31T18:27:02.812198+08:00 0 [Note] [MY-000000] [Galera] Node 119c9d5c state primary
2020-08-31T18:27:02.813518+08:00 0 [Note] [MY-000000] [Galera] Current view of cluster as seen by this node
view (view_id(PRIM,119c9d5c,137)
memb {
119c9d5c,0
824e6d7c,0
e3b5f89f,0
}
joined {
}
left {
}
partitioned {
}
)
2020-08-31T18:27:02.814008+08:00 0 [Note] [MY-000000] [Galera] Save the discovered primary-component to disk
2020-08-31T18:27:02.823176+08:00 0 [Note] [MY-000000] [Galera] gcomm: connected
2020-08-31T18:27:02.823721+08:00 0 [Note] [MY-000000] [Galera] Changing maximum packet size to 64500, resulting msg size: 32636
2020-08-31T18:27:02.824477+08:00 0 [Note] [MY-000000] [Galera] Shifting CLOSED -> OPEN (TO: 0)
2020-08-31T18:27:02.825153+08:00 0 [Note] [MY-000000] [Galera] Opened channel ‘fmkdb’
2020-08-31T18:27:02.826047+08:00 0 [Note] [MY-000000] [Galera] New COMPONENT: primary = yes, bootstrap = no, my_idx = 1, memb_num = 3
2020-08-31T18:27:02.826940+08:00 1 [Note] [MY-000000] [WSREP] Starting rollbacker thread 1
2020-08-31T18:27:02.827796+08:00 0 [Note] [MY-000000] [Galera] STATE EXCHANGE: Waiting for state UUID.
2020-08-31T18:27:02.828714+08:00 2 [Note] [MY-000000] [WSREP] Starting applier thread 2
2020-08-31T18:27:02.829624+08:00 0 [Note] [MY-000000] [Galera] STATE EXCHANGE: sent state msg: 829c1a4b-eb74-11ea-b97a-3fc3305b4f17
2020-08-31T18:27:02.830524+08:00 0 [Note] [MY-000000] [Galera] STATE EXCHANGE: got state msg: 829c1a4b-eb74-11ea-b97a-3fc3305b4f17 from 0 (host24)
2020-08-31T18:27:02.831341+08:00 0 [Note] [MY-000000] [Galera] STATE EXCHANGE: got state msg: 829c1a4b-eb74-11ea-b97a-3fc3305b4f17 from 2 (host21)
2020-08-31T18:27:02.832203+08:00 0 [Note] [MY-000000] [Galera] STATE EXCHANGE: got state msg: 829c1a4b-eb74-11ea-b97a-3fc3305b4f17 from 1 (host20)
2020-08-31T18:27:02.833092+08:00 0 [Note] [MY-000000] [Galera] Quorum results:
version = 6,
component = PRIMARY,
conf_id = 122,
members = 2/3 (primary/total),
act_id = 51203,
last_appl. = 51111,
protocols = 2/10/4 (gcs/repl/appl),
vote policy= 0,
group UUID = a95d48bc-dacb-11ea-ab8c-835eea66e65c
2020-08-31T18:27:02.834109+08:00 0 [Note] [MY-000000] [Galera] Flow-control interval: [173, 173]
2020-08-31T18:27:02.834838+08:00 0 [Note] [MY-000000] [Galera] Shifting OPEN -> PRIMARY (TO: 51204)
2020-08-31T18:27:02.835918+08:00 2 [Note] [MY-000000] [Galera] ####### processing CC 51204, local, ordered
2020-08-31T18:27:02.837237+08:00 2 [Note] [MY-000000] [Galera] Drain monitors from -1 upto -1
2020-08-31T18:27:02.837903+08:00 2 [Note] [MY-000000] [Galera] REPL Protocols: 10 (5, 3)
2020-08-31T18:27:02.838642+08:00 2 [Note] [MY-000000] [Galera] ####### My UUID: 824e6d7c-eb74-11ea-97d5-cf0f58a6432b
2020-08-31T18:27:02.839386+08:00 2 [Note] [MY-000000] [Galera] Server iuatifmkdb20 connected to cluster at position a95d48bc-dacb-11ea-ab8c-835eea66e65c:51204 with ID 824e6d7c-eb74-11ea-97d5-cf0f58a6432b
2020-08-31T18:27:02.840030+08:00 2 [Note] [MY-000000] [WSREP] Server status change disconnected -> connected
2020-08-31T18:27:02.840801+08:00 2 [Note] [MY-000000] [WSREP] wsrep_notify_cmd is not defined, skipping notification.
2020-08-31T18:27:02.841602+08:00 2 [Note] [MY-000000] [Galera] State transfer required:
Group state: a95d48bc-dacb-11ea-ab8c-835eea66e65c:51204
Local state: 00000000-0000-0000-0000-000000000000:-1
2020-08-31T18:27:02.842299+08:00 2 [Note] [MY-000000] [WSREP] Server status change connected -> joiner
2020-08-31T18:27:02.842970+08:00 2 [Note] [MY-000000] [WSREP] wsrep_notify_cmd is not defined, skipping notification.
2020-08-31T18:27:02.844218+08:00 0 [Note] [MY-000000] [WSREP] Initiating SST/IST transfer on JOINER side (wsrep_sst_xtrabackup-v2 --role ‘joiner’ --address ‘<ip address>’ --datadir ‘/var/lib/mysql/’ --basedir ‘/usr/’ --plugindir ‘/usr/lib64/mysql/plugin/’ --defaults-file ‘/etc/my.cnf’ --defaults-group-suffix ‘’ --parent ‘415852’ --mysqld-version ‘8.0.19-10’ ‘’ )
2020-08-31T18:27:05.323931+08:00 0 [Note] [MY-000000] [Galera] (824e6d7c, ‘tcp://0.0.0.0:4567’) turning message relay requesting off
2020-08-31T18:27:07.028623+08:00 2 [Note] [MY-000000] [WSREP] Prepared SST request: xtrabackup-v2|172.18.116.183:4444/xtrabackup_sst//1
2020-08-31T18:27:07.029529+08:00 2 [Note] [MY-000000] [Galera] Cert index reset to 00000000-0000-0000-0000-000000000000:-1 (proto: 10), state transfer needed: yes
2020-08-31T18:27:07.030463+08:00 0 [Note] [MY-000000] [Galera] Service thread queue flushed.
2020-08-31T18:27:07.031294+08:00 2 [Note] [MY-000000] [Galera] ####### Assign initial position for certification: 00000000-0000-0000-0000-000000000000:-1, protocol version: 5
2020-08-31T18:27:07.031994+08:00 2 [Note] [MY-000000] [Galera] Check if state gap can be serviced using IST
2020-08-31T18:27:07.032779+08:00 2 [Note] [MY-000000] [Galera] Local UUID: 00000000-0000-0000-0000-000000000000 != Group UUID: a95d48bc-dacb-11ea-ab8c-835eea66e65c
2020-08-31T18:27:07.033660+08:00 2 [Note] [MY-000000] [Galera] ####### IST uuid:00000000-0000-0000-0000-000000000000 f: 0, l: 51204, STRv: 3
2020-08-31T18:27:07.034552+08:00 2 [Note] [MY-000000] [Galera] IST receiver addr using tcp://<IP address>:4568
2020-08-31T18:27:07.035756+08:00 2 [Note] [MY-000000] [Galera] Prepared IST receiver for 0-51204, listening at: tcp://172.18.116.183:4568
2020-08-31T18:27:07.038000+08:00 0 [Note] [MY-000000] [Galera] Member 1.0 (host20) requested state transfer from ‘any’. Selected 0.0 (iuatifmkdb24)(SYNCED) as donor.
2020-08-31T18:27:07.038950+08:00 0 [Note] [MY-000000] [Galera] Shifting PRIMARY -> JOINER (TO: 51204)
2020-08-31T18:27:07.039922+08:00 2 [Note] [MY-000000] [Galera] Requesting state transfer: success, donor: 0
2020-08-31T18:27:07.040832+08:00 2 [Note] [MY-000000] [Galera] Resetting GCache seqno map due to different histories.
2020-08-31T18:27:07.041689+08:00 2 [Note] [MY-000000] [Galera] GCache history reset: old(a95d48bc-dacb-11ea-ab8c-835eea66e65c:51200 -> a95d48bc-dacb-11ea-ab8c-835eea66e65c:51204
2020-08-31T18:27:07.043446+08:00 2 [Note] [MY-000000] [Galera] GCache DEBUG: RingBuffer::seqno_reset(): full reset
2020-08-31T18:27:07.045418+08:00 2 [Note] [MY-000000] [Galera] Complete reset of the galera cache
2020-08-31T18:27:07.282131+08:00 2 [Note] [MY-000000] [Galera] Flushing memory map to disk…
2020-08-31T18:27:11.262596+08:00 0 [Note] [MY-000000] [WSREP-SST] Proceeding with SST…
2020-08-31T18:27:11.425811+08:00 0 [Note] [MY-000000] [WSREP-SST] …Waiting for SST streaming to complete!
2020-08-31T18:30:24.911316+08:00 0 [Note] [MY-000000] [Galera] 0.0 (host24): State transfer to 1.0 (iuatifmkdb20) complete.
2020-08-31T18:30:24.913202+08:00 0 [Note] [MY-000000] [Galera] Member 0.0 (host24) synced with group.
2020-08-31T18:30:24.925167+08:00 0 [Note] [MY-000000] [WSREP-SST] Preparing the backup at /var/lib/mysql//sst-xb-tmpdir
2020-08-31T18:30:31.769273+08:00 0 [Note] [MY-000000] [WSREP-SST] Moving the backup to /var/lib/mysql/
2020-08-31T18:30:32.107962+08:00 0 [Note] [MY-000000] [WSREP-SST] Running post-processing…
2020-08-31T18:30:32.126398+08:00 0 [Note] [MY-000000] [WSREP-SST] Skipping mysql_upgrade (sst): local version (8.0.19) == donor version (8.0.19)
2020-08-31T18:30:32.999653+08:00 0 [Note] [MY-000000] [WSREP-SST] Waiting for server instance to start… This may take some time
2020-08-31T18:30:39.085821+08:00 0 [ERROR] [MY-000000] [WSREP-SST] ******************* FATAL ERROR **********************
2020-08-31T18:30:39.088764+08:00 0 [ERROR] [MY-000000] [WSREP-SST] Failed to start the mysql server that checks for async replication.
2020-08-31T18:30:39.088843+08:00 0 [ERROR] [MY-000000] [WSREP-SST] Check the parameters and retry
2020-08-31T18:30:39.088929+08:00 0 [ERROR] [MY-000000] [WSREP-SST] Line 483 pid:416707
2020-08-31T18:30:39.090455+08:00 0 [ERROR] [MY-000000] [WSREP-SST] ------------ mysql error log (START) ------------
---- Starting the MySQL server used for post-processing ----
2020-08-31T18:30:32.944402+08:00 0 [Warning] [MY-010140] [Server] Could not increase number of max_open_files to more than 16364 (request: 50000)
2020-08-31T18:30:32.944437+08:00 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 3177 (requested 4000)
2020-08-31T18:30:33.378858+08:00 0 [Warning] [MY-000076] [Server] option ‘log_bin_trust_function_creators’: boolean value ‘1;’ was not recognized. Set to OFF.
2020-08-31T18:30:33.379002+08:00 0 [Warning] [MY-010084] [Server] options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --slow-query-log is not set
2020-08-31T18:30:33.383470+08:00 0 [Warning] [MY-000000] [WSREP] Node is not a cluster node. Disabling pxc_strict_mode
2020-08-31T18:30:33.383625+08:00 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.19-10) starting as process 416707
2020-08-31T18:30:33.417036+08:00 0 [Warning] [MY-013245] [Server] The SSL library function CRYPTO_set_mem_functions failed. This is typically caused by the SSL library already being used. As a result the SSL memory allocation will not be instrumented.
2020-08-31T18:30:35.380527+08:00 1 [Note] [MY-000000] [WSREP] wsrep_init_schema_and_SR (nil)
2020-08-31T18:30:35.416244+08:00 1 [System] [MY-000000] [WSREP] PXC upgrade completed successfully
2020-08-31T18:30:35.473016+08:00 0 [System] [MY-010229] [Server] Starting XA crash recovery…
2020-08-31T18:30:35.491397+08:00 0 [System] [MY-010232] [Server] XA crash recovery finished.
2020-08-31T18:30:35.607310+08:00 0 [Warning] [MY-010075] [Server] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 0170760e-eb75-11ea-ae69-005056b0a696.
2020-08-31T18:30:35.743749+08:00 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-08-31T18:30:35.768860+08:00 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location ‘/tmp’ in the path is accessible to all OS users. Consider choosing a different directory.
2020-08-31T18:30:35.829203+08:00 0 [ERROR] [MY-000067] [Server] unknown variable ‘gcs.fc_limit=80’.
2020-08-31T18:30:35.830841+08:00 0 [ERROR] [MY-010119] [Server] Aborting
2020-08-31T18:30:38.021192+08:00 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.19-10) Percona XtraDB Cluster (GPL), Release rel10, Revision 727f180, WSREP version 26.4.3.
2020-08-31T18:30:39.090592+08:00 0 [ERROR] [MY-000000] [WSREP-SST] ------------ mysql error log (END) ------------
2020-08-31T18:30:39.090661+08:00 0 [ERROR] [MY-000000] [WSREP-SST] ******************************************************
2020-08-31T18:30:39.091538+08:00 0 [Note] [MY-000000] [WSREP-SST] …post-processing failed. Exiting
2020-08-31T18:30:39.091793+08:00 0 [ERROR] [MY-000000] [WSREP-SST] Cleanup after exit with status:3
2020-08-31T18:30:39.160113+08:00 0 [ERROR] [MY-000000] [WSREP] Process completed with error: wsrep_sst_xtrabackup-v2 --role ‘joiner’ --address ‘<IP address>’ --datadir ‘/var/lib/mysql/’ --basedir ‘/usr/’ --plugindir ‘/usr/lib64/mysql/plugin/’ --defaults-file ‘/etc/my.cnf’ --defaults-group-suffix ‘’ --parent ‘415852’ --mysqld-version ‘8.0.19-10’ ‘’ : 3 (No such process)
2020-08-31T18:30:39.160293+08:00 0 [ERROR] [MY-000000] [WSREP] Failed to read uuid:seqno from joiner script.
2020-08-31T18:30:39.160369+08:00 0 [ERROR] [MY-000000] [WSREP] SST script aborted with error 3 (No such process)
2020-08-31T18:30:39.160763+08:00 3 [Note] [MY-000000] [Galera] Processing SST received
2020-08-31T18:30:39.160889+08:00 3 [Note] [MY-000000] [Galera] SST received: 00000000-0000-0000-0000-000000000000:-1
2020-08-31T18:30:39.161000+08:00 3 [System] [MY-000000] [WSREP] SST completed
2020-08-31T18:30:39.161158+08:00 2 [Note] [MY-000000] [Galera] str_proto_ver_: 3 sst_seqno_: -1 cc_seqno: 51204 req->ist_len(): 72
2020-08-31T18:30:39.161301+08:00 2 [ERROR] [MY-000000] [Galera] Application received wrong state:
Received: 00000000-0000-0000-0000-000000000000
Required: a95d48bc-dacb-11ea-ab8c-835eea66e65c
2020-08-31T18:30:39.161397+08:00 2 [ERROR] [MY-000000] [Galera] Application state transfer failed. This is unrecoverable condition, restart required.
2020-08-31T18:30:39.161570+08:00 2 [Note] [MY-000000] [Galera] ReplicatorSMM::abort()
2020-08-31T18:30:39.161737+08:00 2 [Note] [MY-000000] [Galera] Closing send monitor…
2020-08-31T18:30:39.161823+08:00 2 [Note] [MY-000000] [Galera] Closed send monitor.
2020-08-31T18:30:39.161893+08:00 2 [Note] [MY-000000] [Galera] gcomm: terminating thread
2020-08-31T18:30:39.162027+08:00 2 [Note] [MY-000000] [Galera] gcomm: joining thread
2020-08-31T18:30:39.162402+08:00 2 [Note] [MY-000000] [Galera] gcomm: closing backend
2020-08-31T18:30:39.164043+08:00 2 [Note] [MY-000000] [Galera] Current view of cluster as seen by this node
view (view_id(NON_PRIM,119c9d5c,137)
memb {
824e6d7c,0
}
joined {
}
left {
}
partitioned {
119c9d5c,0
e3b5f89f,0
}
)
2020-08-31T18:30:39.164275+08:00 2 [Note] [MY-000000] [Galera] PC protocol downgrade 1 -> 0
2020-08-31T18:30:39.164365+08:00 2 [Note] [MY-000000] [Galera] Current view of cluster as seen by this node
view ((empty))
2020-08-31T18:30:39.165282+08:00 2 [Note] [MY-000000] [Galera] gcomm: closed
2020-08-31T18:30:39.165596+08:00 0 [Note] [MY-000000] [Galera] New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1
2020-08-31T18:30:39.165875+08:00 0 [Note] [MY-000000] [Galera] Flow-control interval: [100, 100]
2020-08-31T18:30:39.165954+08:00 0 [Note] [MY-000000] [Galera] Received NON-PRIMARY.
2020-08-31T18:30:39.166180+08:00 0 [Note] [MY-000000] [Galera] Shifting JOINER -> OPEN (TO: 51204)
2020-08-31T18:30:39.166279+08:00 0 [Note] [MY-000000] [Galera] New SELF-LEAVE.
2020-08-31T18:30:39.166380+08:00 0 [Note] [MY-000000] [Galera] Flow-control interval: [0, 0]
2020-08-31T18:30:39.166446+08:00 0 [Note] [MY-000000] [Galera] Received SELF-LEAVE. Closing connection.
2020-08-31T18:30:39.166504+08:00 0 [Note] [MY-000000] [Galera] Shifting OPEN -> CLOSED (TO: -1)
2020-08-31T18:30:39.166565+08:00 0 [Note] [MY-000000] [Galera] RECV thread exiting 0: Success
2020-08-31T18:30:39.166831+08:00 2 [Note] [MY-000000] [Galera] recv_thread() joined.
2020-08-31T18:30:39.166979+08:00 2 [Note] [MY-000000] [Galera] Closing replication queue.
2020-08-31T18:30:39.167141+08:00 2 [Note] [MY-000000] [Galera] Closing slave action queue.
2020-08-31T18:30:39.167262+08:00 2 [Note] [MY-000000] [Galera] /usr/sbin/mysqld: Terminated.
2020-08-31T18:30:39.167338+08:00 2 [Note] [MY-000000] [WSREP] Initiating SST cancellation
<p>Select all<a rel="nofollow" href="https://www.experts-exchange.com/viewCodeSnippet.jsp?codeSnippetId=20-43148958-7">Open in new window</a></p><br>is it because of this?<br><br>
[Server] unknown variable ‘gcs.fc_limit=80’.