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
Best Answer
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.
What is the ping time between the nodes? How many nodes?