I have a PXC cluster (5.7) with three nodes running on CentOS 7 VMs, each with the following system info:

mysql_db# free 

              total        used        free      shared  buff/cache   available

Mem:        8009248     2591576      358504      146040     5059168     4875716

Swap:       5242876        3592     5239284

mysql_db# free -g

              total        used        free      shared  buff/cache   available

Mem:              7           2           0           0           4           4

Swap:             4           0           4

mysql_db# nproc

8



mysql_db# df -h

Filesystem           Size  Used Avail Use% Mounted on

/dev/mapper/cl-root   44G   20G   25G  46% /

devtmpfs             3.9G     0  3.9G   0% /dev

tmpfs                3.9G     0  3.9G   0% /dev/shm

tmpfs                3.9G  146M  3.7G   4% /run

tmpfs                3.9G     0  3.9G   0% /sys/fs/cgroup

/dev/sda1           1014M  272M  743M  27% /boot

tmpfs                783M     0  783M   0% /run/user/1318214225

tmpfs                783M     0  783M   0% /run/user/0



mysql_db# top

top - 07:39:38 up 50 days,  4:54,  2 users,  load average: 0.46, 0.61, 0.61

Tasks: 227 total,   1 running, 226 sleeping,   0 stopped,   0 zombie

%Cpu(s): 20.3 us,  4.0 sy,  0.0 ni, 75.7 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st

KiB Mem :  8009248 total,   357176 free,  2592348 used,  5059724 buff/cache

KiB Swap:  5242876 total,  5239284 free,     3592 used.  4874700 avail Mem 



  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                                                                                                                                            

 1574 mysql     20   0 9241472   2.3g 238932 S 104.8 30.1   6343:02 mysqld                                                                                                                                                                             

 3988 root      20   0  172268   2348   1580 R  19.0  0.0   0:00.12 top                                                                                                                                                                                

13164 root      20   0  177264  94680   4676 S  19.0  1.2   2693:42 mysqld_exporter                                                                                                                                                                    

    1 root      20   0  191404   4420   2612 S   0.0  0.1 187:50.00 systemd 



mysql_db# iostat

Linux 3.10.0-862.14.4.el7.x86_64 (dev_mysql_02.pd.local)        11/20/2018      _x86_64_        (8 CPU)



avg-cpu:  %user   %nice %system %iowait  %steal   %idle

           2.35    0.00    0.36    0.05    0.00   97.24



Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn

sda               3.83       153.19       153.17  664744979  664666279

dm-0              3.74       139.26       153.17  604320443  664647691

dm-1              0.00         0.00         0.00       2236       2460



mysql_db#lscpu

Architecture:          x86_64

CPU op-mode(s):        32-bit, 64-bit

Byte Order:            Little Endian

CPU(s):                8

On-line CPU(s) list:   0-7

Thread(s) per core:    1

Core(s) per socket:    1

Socket(s):             8

NUMA node(s):          1

Vendor ID:             AuthenticAMD

CPU family:            21

Model:                 1

Model name:            AMD Opteron(TM) Processor 6276

Stepping:              2

CPU MHz:               2300.028

BogoMIPS:              4600.05

Hypervisor vendor:     VMware

Virtualization type:   full

L1d cache:             16K

L1i cache:             64K

L2 cache:              2048K

L3 cache:              12288K

NUMA node0 CPU(s):     0-7

Below is some important InnoDB variables in the my.cnf config file:

...

back_log = 65535

binlog_format = ROW

character_set_server = utf8

collation_server = utf8_general_ci

default_storage_engine = InnoDB

enforce-gtid-consistency = 1

expand_fast_index_creation = 1

gtid_mode = ON

innodb_autoinc_lock_mode = 2

innodb_buffer_pool_instances = 3

innodb_buffer_pool_size = 3G

innodb_data_file_path = ibdata1:64M;ibdata2:64M:autoextend

innodb_file_format = Barracuda

innodb_file_per_table

innodb_flush_log_at_trx_commit = 2

innodb_flush_method = O_DIRECT

innodb_io_capacity = 1600

innodb_large_prefix

innodb_log_file_size = 256M

innodb_print_all_deadlocks = 1

innodb_read_io_threads = 64

innodb_stats_on_metadata = FALSE

innodb_write_io_threads = 64

long_query_time = 1

log_bin_trust_function_creators = 1

master_info_repository = TABLE

max_allowed_packet = 64M

max_connect_errors = 4294967295

max_connections = 2600

max_user_connections = 2500

min_examined_row_limit = 1000

relay_log_info_repository = TABLE

relay-log-recovery = TRUE

skip-name-resolve

slave_parallel_workers = 8

slow_query_log = 1

#slow_query_log_timestamp_always = 1

#thread_cache = 1024

tmpdir = /srv/tmp

transaction_isolation = READ-COMMITTED

updatable_views_with_limit = 0

user = mysql

wait_timeout = 60

userstat

table_open_cache = 4096

innodb_open_files = 10240

open_files_limit = 10240

connect_timeout=60

thread_cache_size = 4096

sql_mode = NO_ENGINE_SUBSTITUTION

query_cache_size = 0

slave_pending_jobs_size_max=32M

range_optimizer_max_mem_size=8M

log_timestamps=SYSTEM

server-id= 172162 #use IP

userstat=1

...

As seen above, i have set innodb_buffer_pool_size to 3GB, but not sure if this is a good value given the system info provided above, especially that Total RAM is 7.6GB, with about 5GB allocated to SWAP. We have noticed in the mysql error logs a lot of timeouts and deadlocks frequently happening, and running SHOW ENGINE INNODB STATUS shows this:

    Status: 

=====================================

2018-11-20 07:46:14 0x7f6acc0f9700 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 8 seconds

-----------------

BACKGROUND THREAD

-----------------

srv_master_thread loops: 201141 srv_active, 0 srv_shutdown, 2836117 srv_idle

srv_master_thread log flush and writes: 3037214

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 32658

OS WAIT ARRAY INFO: signal count 33393

RW-shared spins 0, rounds 156414, OS waits 27395

RW-excl spins 0, rounds 222081, OS waits 1171

RW-sx spins 11313, rounds 167795, OS waits 1583

Spin rounds per wait: 156414.00 RW-shared, 222081.00 RW-excl, 14.83 RW-sx

------------------------

LATEST FOREIGN KEY ERROR

------------------------

2018-11-20 07:06:51 0x7f6aad2eb700 Transaction:

TRANSACTION 25842563, ACTIVE 1 sec updating or deleting

mysql tables in use 1, locked 1

12 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 3

MySQL thread id 577793, OS thread handle 140096148780800, query id 3550710 10.168.103.11 slashire_dev update

INSERT INTO member_bank_acct (`ba_member_id`,`ba_create_user`,`ba_update_user`,`ba_country`,`ba_name`,`ba_currency`,`ba_bank_name`,`ba_bank_acct_num`,`ba_swift_code`,`ba_city`,`ba_branch`,`ba_iban`,`ba_beneficiary_bank`) VALUES ('00000000000000000252','SYSTEM','SYSTEM','','','','','','','','','','') ON DUPLICATE KEY UPDATE `ba_country` = '',`ba_name` = '',`ba_currency` = '',`ba_bank_name` = '',`ba_bank_acct_num` = '',`ba_swift_code` = '',`ba_city` = '',`ba_branch` = '',`ba_iban` = '',`ba_beneficiary_bank` = ''

Foreign key constraint fails for table `slashire_dev`.`member_bank_acct`:

,

  CONSTRAINT `member_bank_acct_fk02` FOREIGN KEY (`ba_country`) REFERENCES `country` (`c_code`)

Trying to add in child table, in index member_bank_acct_fk02 tuple:

DATA TUPLE: 2 fields;

 0: len 0; hex ; asc ;;

 1: len 20; hex 3030303030303030303030303030303030323532; asc 00000000000000000252;;



But in parent table `slashire_dev`.`country`, in index PRIMARY,

the closest match we can find is record:

PHYSICAL RECORD: n_fields 9; compact format; info bits 0

 0: len 2; hex 3030; asc 00;;

 1: len 6; hex 00000000566c; asc     Vl;;

 2: len 7; hex b0000001eb0110; asc        ;;

 3: len 3; hex 416c6c; asc All;;

 4: len 2; hex 8000; asc   ;;

 5: len 5; hex 999f0c0000; asc      ;;

 6: len 6; hex 53595354454d; asc SYSTEM;;

 7: len 5; hex 999f0c0000; asc      ;;

 8: len 6; hex 53595354454d; asc SYSTEM;;



------------

TRANSACTIONS

------------

Trx id counter 25989367

Purge done for trx's n:o < 25989079 undo n:o < 0 state: running but idle

History list length 27

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 421576625901616, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625897008, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625900464, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625899312, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625895856, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625898160, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625890096, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625893552, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625888944, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625867056, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625894704, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625892400, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625891248, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625886640, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625887792, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625885488, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625884336, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625883184, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625882032, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625880880, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625879728, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625878576, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625877424, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625876272, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625875120, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625873968, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625872816, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625871664, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625870512, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625869360, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 421576625868208, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

--------

FILE I/O

--------

I/O thread 0 state: waiting for completed aio requests (insert buffer thread)

I/O thread 1 state: waiting for completed aio requests (log thread)

I/O thread 2 state: waiting for completed aio requests (read thread)

I/O thread 3 state: waiting for completed aio requests (read thread)

I/O thread 4 state: waiting for completed aio requests (read thread)

I/O thread 5 state: waiting for completed aio requests (read thread)

I/O thread 6 state: waiting for completed aio requests (read thread)

I/O thread 7 state: waiting for completed aio requests (read thread)

I/O thread 8 state: waiting for completed aio requests (read thread)

I/O thread 9 state: waiting for completed aio requests (read thread)

I/O thread 10 state: waiting for completed aio requests (read thread)

I/O thread 11 state: waiting for completed aio requests (read thread)

I/O thread 12 state: waiting for completed aio requests (read thread)

I/O thread 13 state: waiting for completed aio requests (read thread)

I/O thread 14 state: waiting for completed aio requests (read thread)

I/O thread 15 state: waiting for completed aio requests (read thread)

I/O thread 16 state: waiting for completed aio requests (read thread)

I/O thread 17 state: waiting for completed aio requests (read thread)

I/O thread 18 state: waiting for completed aio requests (read thread)

I/O thread 19 state: waiting for completed aio requests (read thread)

I/O thread 20 state: waiting for completed aio requests (read thread)

I/O thread 21 state: waiting for completed aio requests (read thread)

I/O thread 22 state: waiting for completed aio requests (read thread)

I/O thread 23 state: waiting for completed aio requests (read thread)

I/O thread 24 state: waiting for completed aio requests (read thread)

I/O thread 25 state: waiting for completed aio requests (read thread)

I/O thread 26 state: waiting for completed aio requests (read thread)

I/O thread 27 state: waiting for completed aio requests (read thread)

I/O thread 28 state: waiting for completed aio requests (read thread)

I/O thread 29 state: waiting for completed aio requests (read thread)

I/O thread 30 state: waiting for completed aio requests (read thread)

I/O thread 31 state: waiting for completed aio requests (read thread)

I/O thread 32 state: waiting for completed aio requests (read thread)

I/O thread 33 state: waiting for completed aio requests (read thread)

I/O thread 34 state: waiting for completed aio requests (read thread)

I/O thread 35 state: waiting for completed aio requests (read thread)

I/O thread 36 state: waiting for completed aio requests (read thread)

I/O thread 37 state: waiting for completed aio requests (read thread)

I/O thread 38 state: waiting for completed aio requests (read thread)

I/O thread 39 state: waiting for completed aio requests (read thread)

I/O thread 40 state: waiting for completed aio requests (read thread)

I/O thread 41 state: waiting for completed aio requests (read thread)

I/O thread 42 state: waiting for completed aio requests (read thread)

I/O thread 43 state: waiting for completed aio requests (read thread)

I/O thread 44 state: waiting for completed aio requests (read thread)

I/O thread 45 state: waiting for completed aio requests (read thread)

I/O thread 46 state: waiting for completed aio requests (read thread)

I/O thread 47 state: waiting for completed aio requests (read thread)

I/O thread 48 state: waiting for completed aio requests (read thread)

I/O thread 49 state: waiting for completed aio requests (read thread)

I/O thread 50 state: waiting for completed aio requests (read thread)

I/O thread 51 state: waiting for completed aio requests (read thread)

I/O thread 52 state: waiting for completed aio requests (read thread)

I/O thread 53 state: waiting for completed aio requests (read thread)

I/O thread 54 state: waiting for completed aio requests (read thread)

I/O thread 55 state: waiting for completed aio requests (read thread)

I/O thread 56 state: waiting for completed aio requests (read thread)

I/O thread 57 state: waiting for completed aio requests (read thread)

I/O thread 58 state: waiting for completed aio requests (read thread)

I/O thread 59 state: waiting for completed aio requests (read thread)

I/O thread 60 state: waiting for completed aio requests (read thread)

I/O thread 61 state: waiting for completed aio requests (read thread)

I/O thread 62 state: waiting for completed aio requests (read thread)

I/O thread 63 state: waiting for completed aio requests (read thread)

I/O thread 64 state: waiting for completed aio requests (read thread)

I/O thread 65 state: waiting for completed aio requests (read thread)

I/O thread 66 state: waiting for completed aio requests (write thread)

I/O thread 67 state: waiting for completed aio requests (write thread)

I/O thread 68 state: waiting for completed aio requests (write thread)

I/O thread 69 state: waiting for completed aio requests (write thread)

I/O thread 70 state: waiting for completed aio requests (write thread)

I/O thread 71 state: waiting for completed aio requests (write thread)

I/O thread 72 state: waiting for completed aio requests (write thread)

I/O thread 73 state: waiting for completed aio requests (write thread)

I/O thread 74 state: waiting for completed aio requests (write thread)

I/O thread 75 state: waiting for completed aio requests (write thread)

I/O thread 76 state: waiting for completed aio requests (write thread)

I/O thread 77 state: waiting for completed aio requests (write thread)

I/O thread 78 state: waiting for completed aio requests (write thread)

I/O thread 79 state: waiting for completed aio requests (write thread)

I/O thread 80 state: waiting for completed aio requests (write thread)

I/O thread 81 state: waiting for completed aio requests (write thread)

I/O thread 82 state: waiting for completed aio requests (write thread)

I/O thread 83 state: waiting for completed aio requests (write thread)

I/O thread 84 state: waiting for completed aio requests (write thread)

I/O thread 85 state: waiting for completed aio requests (write thread)

I/O thread 86 state: waiting for completed aio requests (write thread)

I/O thread 87 state: waiting for completed aio requests (write thread)

I/O thread 88 state: waiting for completed aio requests (write thread)

I/O thread 89 state: waiting for completed aio requests (write thread)

I/O thread 90 state: waiting for completed aio requests (write thread)

I/O thread 91 state: waiting for completed aio requests (write thread)

I/O thread 92 state: waiting for completed aio requests (write thread)

I/O thread 93 state: waiting for completed aio requests (write thread)

I/O thread 94 state: waiting for completed aio requests (write thread)

I/O thread 95 state: waiting for completed aio requests (write thread)

I/O thread 96 state: waiting for completed aio requests (write thread)

I/O thread 97 state: waiting for completed aio requests (write thread)

I/O thread 98 state: waiting for completed aio requests (write thread)

I/O thread 99 state: waiting for completed aio requests (write thread)

I/O thread 100 state: waiting for completed aio requests (write thread)

I/O thread 101 state: waiting for completed aio requests (write thread)

I/O thread 102 state: waiting for completed aio requests (write thread)

I/O thread 103 state: waiting for completed aio requests (write thread)

I/O thread 104 state: waiting for completed aio requests (write thread)

I/O thread 105 state: waiting for completed aio requests (write thread)

I/O thread 106 state: waiting for completed aio requests (write thread)

I/O thread 107 state: waiting for completed aio requests (write thread)

I/O thread 108 state: waiting for completed aio requests (write thread)

I/O thread 109 state: waiting for completed aio requests (write thread)

I/O thread 110 state: waiting for completed aio requests (write thread)

I/O thread 111 state: waiting for completed aio requests (write thread)

I/O thread 112 state: waiting for completed aio requests (write thread)

I/O thread 113 state: waiting for completed aio requests (write thread)

I/O thread 114 state: waiting for completed aio requests (write thread)

I/O thread 115 state: waiting for completed aio requests (write thread)

I/O thread 116 state: waiting for completed aio requests (write thread)

I/O thread 117 state: waiting for completed aio requests (write thread)

I/O thread 118 state: waiting for completed aio requests (write thread)

I/O thread 119 state: waiting for completed aio requests (write thread)

I/O thread 120 state: waiting for completed aio requests (write thread)

I/O thread 121 state: waiting for completed aio requests (write thread)

I/O thread 122 state: waiting for completed aio requests (write thread)

I/O thread 123 state: waiting for completed aio requests (write thread)

I/O thread 124 state: waiting for completed aio requests (write thread)

I/O thread 125 state: waiting for completed aio requests (write thread)

I/O thread 126 state: waiting for completed aio requests (write thread)

I/O thread 127 state: waiting for completed aio requests (write thread)

I/O thread 128 state: waiting for completed aio requests (write thread)

I/O thread 129 state: waiting for completed aio requests (write thread)

Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] ,

 ibuf aio reads:, log i/o's:, sync i/o's:

Pending flushes (fsync) log: 0; buffer pool: 0

8353 OS file reads, 2256885 OS file writes, 649203 OS fsyncs

0.00 reads/s, 0 avg bytes/read, 6.37 writes/s, 1.75 fsyncs/s

-------------------------------------

INSERT BUFFER AND ADAPTIVE HASH INDEX

-------------------------------------

Ibuf: size 1, free list len 0, seg size 2, 108 merges

merged operations:

 insert 1, delete mark 1, delete 0

discarded operations:

 insert 0, delete mark 0, delete 0

Hash table size 796871, node heap has 46 buffer(s)

Hash table size 796871, node heap has 8 buffer(s)

Hash table size 796871, node heap has 7 buffer(s)

Hash table size 796871, node heap has 14 buffer(s)

Hash table size 796871, node heap has 23 buffer(s)

Hash table size 796871, node heap has 16 buffer(s)

Hash table size 796871, node heap has 15 buffer(s)

Hash table size 796871, node heap has 94 buffer(s)

234.22 hash searches/s, 8.87 non-hash searches/s

---

LOG

---

Log sequence number 1427669147

Log flushed up to   1427669147

Pages flushed up to 1427669147

Last checkpoint at  1427669138

Max checkpoint age    434154333

Checkpoint age target 420587011

Modified age          0

Checkpoint age        9

0 pending log flushes, 0 pending chkp writes

452361 log i/o's done, 1.00 log i/o's/second

----------------------

BUFFER POOL AND MEMORY

----------------------

Total large memory allocated 3353346048

Dictionary memory allocated 11634313

Internal hash tables (constant factor + variable factor)

    Adaptive hash index 54687040        (50999744 + 3687296)

    Page hash           1107112 (buffer pool 0 only)

    Dictionary cache    24384249        (12749936 + 11634313)

    File system         1676640         (812272 + 864368)

    Lock system         8004712         (7969496 + 35216)

    Recovery system     0       (0 + 0)

Buffer pool size   196584

Buffer pool size, bytes 3220832256

Free buffers       146566

Database pages     49795

Old database pages 18321

Modified db pages  0

Pending reads      0

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 10000, not young 35358

0.00 youngs/s, 0.00 non-youngs/s

Pages read 7697, created 42280, written 1645380

0.00 reads/s, 0.00 creates/s, 5.00 writes/s

Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

LRU len: 49795, unzip_LRU len: 0

I/O sum[807]:cur[0], unzip sum[0]:cur[0]

----------------------

INDIVIDUAL BUFFER POOL INFO

----------------------

---BUFFER POOL 0

Buffer pool size   65528

Buffer pool size, bytes 1073610752

Free buffers       48863

Database pages     16588

Old database pages 6103

Modified db pages  0

Pending reads      0

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 3655, not young 13239

0.00 youngs/s, 0.00 non-youngs/s

Pages read 2611, created 14049, written 334207

0.00 reads/s, 0.00 creates/s, 0.50 writes/s

Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

LRU len: 16588, unzip_LRU len: 0

I/O sum[269]:cur[0], unzip sum[0]:cur[0]

---BUFFER POOL 1

Buffer pool size   65528

Buffer pool size, bytes 1073610752

Free buffers       48627

Database pages     16823

Old database pages 6190

Modified db pages  0

Pending reads      0

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 3253, not young 12647

0.00 youngs/s, 0.00 non-youngs/s

Pages read 2476, created 14408, written 1147163

0.00 reads/s, 0.00 creates/s, 3.87 writes/s

Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

LRU len: 16823, unzip_LRU len: 0

I/O sum[269]:cur[0], unzip sum[0]:cur[0]

---BUFFER POOL 2

Buffer pool size   65528

Buffer pool size, bytes 1073610752

Free buffers       49076

Database pages     16384

Old database pages 6028

Modified db pages  0

Pending reads      0

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 3092, not young 9472

0.00 youngs/s, 0.00 non-youngs/s

Pages read 2610, created 13823, written 164010

0.00 reads/s, 0.00 creates/s, 0.62 writes/s

Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

LRU len: 16384, unzip_LRU len: 0

I/O sum[269]:cur[0], unzip sum[0]:cur[0]

--------------

ROW OPERATIONS

--------------

0 queries inside InnoDB, 0 queries in queue

0 read views open inside InnoDB

0 RW transactions active inside InnoDB

Process ID=1574, Main thread ID=140097892615936, state: sleeping

Number of rows inserted 25413524, updated 63914, deleted 6172, read 1035018804

65.87 inserts/s, 0.12 updates/s, 0.00 deletes/s, 97.36 reads/s

----------------------------

END OF INNODB MONITOR OUTPUT

Are there any tell tell signs from the above info causing these timeouts and deadlocks? How can i find out the percentage of utilized InnoDB buffer pool against total RAM?

Ping Results

64 bytes from 10.1.5.100: icmp_seq=1 ttl=64 time=0.958 ms

64 bytes from 10.1.5.100: icmp_seq=2 ttl=64 time=1.09 ms

64 bytes from 10.1.5.100: icmp_seq=4 ttl=64 time=1.22 ms

64 bytes from 10.1.5.100: icmp_seq=5 ttl=64 time=2.24 ms

DEADLOCK INFO

*** Priority TRANSACTION:

TRANSACTION 165015546, ACTIVE 0 sec starting index read

mysql tables in use 1, locked 1

MySQL thread id 14, OS thread handle 140101363758848, query id 11358891 System lock



*** Victim TRANSACTION:

TRANSACTION 165015100, ACTIVE 1 sec

, undo log entries 1

MySQL thread id 1027074, OS thread handle 140096133596928, query id 11358888 10.168.103.11 membership wsrep: initiating replication for write set (-1)

COMMIT

*** WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 2481 page no 17 n bits 80 index PRIMARY of table `membership`.`member_token` trx id 165015100 lock_mode X locks rec but not gap

2018-12-06T03:12:23.732058-00:00 14 [Note] WSREP: --------- CONFLICT DETECTED --------

2018-12-06T03:12:23.732119-00:00 14 [Note] WSREP: cluster conflict due to high priority abort for threads:



2018-12-06T03:12:23.732331-00:00 14 [Note] WSREP: Winning thread: 

   THD: 14, mode: applier, state: executing, conflict: no conflict, seqno: 152121

   SQL: (null)



2018-12-06T03:12:23.732392-00:00 14 [Note] WSREP: Victim thread: 

   THD: 1027074, mode: local, state: committing, conflict: no conflict, seqno: -1

   SQL: COMMIT

SHOW ENGINE INNODB STATUS; is transient. It looks like you waited too long before running that. Try again. See below.

3G on a 7.5GB server is fine. If you had a bigger dataset, 5G would be slightly better.

back_log = 65535 -- drop this to 50



innodb_print_all_deadlocks = 1 -- look in the log file



max_user_connections = 2500  -- do you really expect to hit this?



slow_query_log = 1  -- what good stuff is in the slowlog (use pt-query-digest)

What is the ping time between the nodes? How many nodes?