Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC

Bug #1421282
Comment #5

Comment 5 for bug 1421282

Revision history for this message

Rodrigo (rodri-bernardo) wrote on 2017-03-09:

Hello there,

I got the same error message:
This is my innodb status:
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2017-03-09 14:41:30 7f026040d700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 0 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 97570 srv_active, 0 srv_shutdown, 18126 srv_idle
srv_master_thread log flush and writes: 115696
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 344389
OS WAIT ARRAY INFO: signal count 437392
Mutex spin waits 1075408, rounds 10292671, OS waits 197701
RW-shared spins 282032, rounds 5294988, OS waits 104907
RW-excl spins 116485, rounds 2223412, OS waits 25415
Spin rounds per wait: 9.57 mutex, 18.77 RW-shared, 19.09 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-03-08 19:09:26 7f02606d8700
*** (1) TRANSACTION:
TRANSACTION 58539970169, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 9 lock struct(s), heap size 1184, 5 row lock(s), undo log entries 4
MySQL thread id 1, OS thread handle 0x7f02b6a2d700, query id 30422585 System lock
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2534440687 page no 90557 n bits 88 index `PRIMARY` of table `ft_prod`.`files` trx id 58539970169 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 58539970171, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 6628, OS thread handle 0x7f02606d8700, query id 30422588 System lock
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2534440687 page no 90557 n bits 88 index `PRIMARY` of table `ft_prod`.`files` trx id 58539970171 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2534440685 page no 1072 n bits 128 index `PRIMARY` of table `ft_prod`.`transfers` trx id 58539970171 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 58561579736
Purge done for trx's n:o < 58561578109 undo n:o < 0 state: running but idle
History list length 852
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 16722, OS thread handle 0x7f026040d700, query id 61116102 stockport.dhcp.dneg.com 10.21.32.238 root init
show engine innodb status
---TRANSACTION 0, not started
MySQL thread id 16508, OS thread handle 0x7f0260552700, query id 61116100 stockport.dhcp.dneg.com 10.21.32.238 root cleaning up
---TRANSACTION 58561578037, not started
MySQL thread id 1, OS thread handle 0x7f02b6a2d700, query id 61106780 committed 2630136305
---TRANSACTION 58561578111, ACTIVE 713 sec
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
MySQL thread id 14927, OS thread handle 0x7f02606d8700, query id 61107336 wsrep in pre-commit stage
--------
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 (write thread)
I/O thread 11 state: waiting for completed aio requests (write thread)
I/O thread 12 state: waiting for completed aio requests (write thread)
I/O thread 13 state: waiting for completed aio requests (write thread)
I/O thread 14 state: waiting for completed aio requests (write thread)
I/O thread 15 state: waiting for completed aio requests (write thread)
I/O thread 16 state: waiting for completed aio requests (write thread)
I/O thread 17 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0, 0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
4210663 OS file reads, 26942925 OS file writes, 950785 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 8612, seg size 8614, 744849 merges
merged operations:
insert 1036143, delete mark 422307, delete 27122
discarded operations:
insert 0, delete mark 0, delete 0
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 61957122556541
Log flushed up to 61957122556541
Pages flushed up to 61957122556541
Last checkpoint at 61957122556541
Max checkpoint age 3396675134
Checkpoint age target 3290529037
Modified age 0
Checkpoint age 0
0 pending log writes, 0 pending chkp writes
21468113 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 4395630592; in additional pool allocated 0
Total memory allocated by read views 320
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 97127120 (75692648 + 21434472)
    Page hash 1107208 (buffer pool 0 only)
    Dictionary cache 23322966 (17702416 + 5620550)
    File system 1146064 (812272 + 333792)
    Lock system 10627144 (10625704 + 1440)
    Recovery system 0 (0 + 0)
Dictionary memory allocated 5620550
Buffer pool size 262140
Buffer pool size, bytes 4294901760
Free buffers 4096
Database pages 256734
Old database pages 94689
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1072508, not young 87488258
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4209926, created 652540, written 5240558
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 256734, unzip_LRU len: 2
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 65535
Buffer pool size, bytes 1073725440
Free buffers 1024
Database pages 64188
Old database pages 23674
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 270948, not young 24276306
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1175644, created 561352, written 1823848
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 64188, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 65535
Buffer pool size, bytes 1073725440
Free buffers 1024
Database pages 64188
Old database pages 23674
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 267832, not young 20470950
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1005373, created 30608, written 1127951
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 64188, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 65535
Buffer pool size, bytes 1073725440
Free buffers 1024
Database pages 64180
Old database pages 23671
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 273513, not young 21102963
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1001161, created 30551, written 1098514
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 64180, unzip_LRU len: 1
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 65535
Buffer pool size, bytes 1073725440
Free buffers 1024
Database pages 64178
Old database pages 23670
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 260215, not young 21638039
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1027748, created 30029, written 1190245
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 64178, unzip_LRU len: 1
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
1 RW transactions active inside InnoDB
0 RO transactions active inside InnoDB
1 out of 1000 descriptors used
Main thread process no. 34156, id 139636355041024, state: sleeping
Number of rows inserted 1854449, updated 20243031, deleted 736850, read 85405516
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

I' am on 5.6.24-72.2-56-log Percona XtraDB Cluster

Hello there,

I got the same error message:
This is my innodb status:
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2017-03-09 14:41:30 7f026040d700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 0 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 97570 srv_active, 0 srv_shutdown, 18126 srv_idle
srv_master_thread log flush and writes: 115696
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 344389
OS WAIT ARRAY INFO: signal count 437392
Mutex spin waits 1075408, rounds 10292671, OS waits 197701
RW-shared spins 282032, rounds 5294988, OS waits 104907
RW-excl spins 116485, rounds 2223412, OS waits 25415
Spin rounds per wait: 9.57 mutex, 18.77 RW-shared, 19.09 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-03-08 19:09:26 7f02606d8700
*** (1) TRANSACTION:
TRANSACTION 58539970169, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 9 lock struct(s), heap size 1184, 5 row lock(s), undo log entries 4
MySQL thread id 1, OS thread handle 0x7f02b6a2d700, query id 30422585 System lock
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2534440687 page no 90557 n bits 88 index `PRIMARY` of table `ft_prod`.`files` trx id 58539970169 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 58539970171, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 6628, OS thread handle 0x7f02606d8700, query id 30422588 System lock
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2534440687 page no 90557 n bits 88 index `PRIMARY` of table `ft_prod`.`files` trx id 58539970171 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2534440685 page no 1072 n bits 128 index `PRIMARY` of table `ft_prod`.`transfers` trx id 58539970171 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 58561579736
Purge done for trx's n:o < 58561578109 undo n:o < 0 state: running but idle
History list length 852
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 16722, OS thread handle 0x7f026040d700, query id 61116102 stockport.dhcp.dneg.com 10.21.32.238 root init
show engine innodb status
---TRANSACTION 0, not started
MySQL thread id 16508, OS thread handle 0x7f0260552700, query id 61116100 stockport.dhcp.dneg.com 10.21.32.238 root cleaning up
---TRANSACTION 58561578037, not started
MySQL thread id 1, OS thread handle 0x7f02b6a2d700, query id 61106780 committed 2630136305
---TRANSACTION 58561578111, ACTIVE 713 sec
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
MySQL thread id 14927, OS thread handle 0x7f02606d8700, query id 61107336 wsrep in pre-commit stage
--------
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 (write thread)
I/O thread 11 state: waiting for completed aio requests (write thread)
I/O thread 12 state: waiting for completed aio requests (write thread)
I/O thread 13 state: waiting for completed aio requests (write thread)
I/O thread 14 state: waiting for completed aio requests (write thread)
I/O thread 15 state: waiting for completed aio requests (write thread)
I/O thread 16 state: waiting for completed aio requests (write thread)
I/O thread 17 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0, 0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
4210663 OS file reads, 26942925 OS file writes, 950785 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 8612, seg size 8614, 744849 merges
merged operations:
 insert 1036143, delete mark 422307, delete 27122
discarded operations:
 insert 0, delete mark 0, delete 0
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 61957122556541
Log flushed up to   61957122556541
Pages flushed up to 61957122556541
Last checkpoint at  61957122556541
Max checkpoint age    3396675134
Checkpoint age target 3290529037
Modified age          0
Checkpoint age        0
0 pending log writes, 0 pending chkp writes
21468113 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 4395630592; in additional pool allocated 0
Total memory allocated by read views 320
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 97127120 	(75692648 + 21434472)
    Page hash           1107208 (buffer pool 0 only)
    Dictionary cache    23322966 	(17702416 + 5620550)
    File system         1146064 	(812272 + 333792)
    Lock system         10627144 	(10625704 + 1440)
    Recovery system     0 	(0 + 0)
Dictionary memory allocated 5620550
Buffer pool size        262140
Buffer pool size, bytes 4294901760
Free buffers            4096
Database pages          256734
Old database pages      94689
Modified db pages       0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1072508, not young 87488258
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4209926, created 652540, written 5240558
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 256734, unzip_LRU len: 2
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size        65535
Buffer pool size, bytes 1073725440
Free buffers            1024
Database pages          64188
Old database pages      23674
Modified db pages       0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 270948, not young 24276306
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1175644, created 561352, written 1823848
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 64188, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size        65535
Buffer pool size, bytes 1073725440
Free buffers            1024
Database pages          64188
Old database pages      23674
Modified db pages       0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 267832, not young 20470950
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1005373, created 30608, written 1127951
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 64188, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size        65535
Buffer pool size, bytes 1073725440
Free buffers            1024
Database pages          64180
Old database pages      23671
Modified db pages       0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 273513, not young 21102963
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1001161, created 30551, written 1098514
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 64180, unzip_LRU len: 1
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size        65535
Buffer pool size, bytes 1073725440
Free buffers            1024
Database pages          64178
Old database pages      23670
Modified db pages       0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 260215, not young 21638039
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1027748, created 30029, written 1190245
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 64178, unzip_LRU len: 1
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
1 RW transactions active inside InnoDB
0 RO transactions active inside InnoDB
1 out of 1000 descriptors used
Main thread process no. 34156, id 139636355041024, state: sleeping
Number of rows inserted 1854449, updated 20243031, deleted 736850, read 85405516
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

I' am on 5.6.24-72.2-56-log Percona XtraDB Cluster