Not the answer you need?
Register and ask your own question!

tuning of galara replication

DBA100DBA100 Current User Role Patron
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

Answers

  • DBA100DBA100 Current User Role Patron
    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.
    

    Select allOpen in new window


       which one is true ?

    Once this is set, this can be set:

    gcs.fc_limit= wsrep_slave_threads * 5
    

    Select allOpen in new window


      then I can also set this in my.cnf:
    gcs.fc_factor=0.8
    

    Select allOpen in new window

     
      then it is solved.


    and this one:
    "set up parameter wsrep_notify_cmd to be notify "
    is about email notisification about change in cluster status,  which I found information like:

    https://galeracluster.com/library/documentation/notification-cmd.html 

    "You can enable your notification script or command through the wsrep_notify_cmd parameter in the configuration file. Below is an excerpt from that file showing how it might look:
    wsrep_notify_cmd=/path/wsrep_notify.sh
     
    "

    so should I say I only have this left:

    innodb_flush_log_at_trx_commit
    

    Select allOpen in new window

    what is this ?

    and I just tried this:

    # Galera replication
    wsrep_slave_threads=16
    gcs.fc_limit=80
    gcs.fc_factor=0.8
    # innodb_flush_log_at_trx_commit = 0
    
    
    

    Select allOpen in new window


    it make mysql failed to start and this is the error message:

    .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
    
    
    
    

    Select allOpen in new window


    is it because of this?

    [Server] unknown variable 'gcs.fc_limit=80'.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.