MySQL Forums
Forum List  »  InnoDB

Help on a mysql error log file
Posted by: Giovanni Chiozza
Date: August 18, 2018 03:01AM

Hi all,

I got a php application runnng on a PC 24 h on 7x7 and yesterday I got some issue with it. I'm using WAMP SERVER.
I got a query running every 2 seconds and I don't know if it can be the cause of issue. Memory , buffers....


I've downloaded the myslq log
Are there anybody who can explain me easily (I'n not a programmer) where the main issue is ?
Many Thanks

How can I attach the LOG FILE ???

2018-08-18T06:59:53.445766Z 0 [Warning] InnoDB: A long semaphore wait:
--Thread 6032 has waited at row0upd.cc line 2858 for 241.00 seconds the semaphore:
X-lock on RW-latch at 00000000178D6AE0 created in file buf0buf.cc line 1460
a writer (thread id 2108) has reserved it in mode SX
number of readers 0, waiters flag 1, lock_word: 10000000
Last time read locked in file btr0pcur.ic line 562
Last time write locked in file G:\ade\build\sb_0-26514852-1514435511.83\mysql-5.7.21\storage\innobase\buf\buf0flu.cc line 1198
InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
InnoDB: Pending preads 0, pwrites 0

=====================================
2018-08-18 09:00:02 0xa28 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 14 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 67298 srv_active, 0 srv_shutdown, 70686 srv_idle
srv_master_thread log flush and writes: 137984
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 473730
--Thread 6032 has waited at row0upd.cc line 2858 for 250.00 seconds the semaphore:
X-lock on RW-latch at 00000000178D6AE0 created in file buf0buf.cc line 1460
a writer (thread id 2108) has reserved it in mode SX
number of readers 0, waiters flag 1, lock_word: 10000000
Last time read locked in file btr0pcur.ic line 562
Last time write locked in file G:\ade\build\sb_0-26514852-1514435511.83\mysql-5.7.21\storage\innobase\buf\buf0flu.cc line 1198
OS WAIT ARRAY INFO: signal count 473045
RW-shared spins 0, rounds 124965, OS waits 62481
RW-excl spins 0, rounds 103548, OS waits 3434
RW-sx spins 5, rounds 105, OS waits 3
Spin rounds per wait: 124965.00 RW-shared, 103548.00 RW-excl, 21.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 810795
Purge done for trx's n:o < 810792 undo n:o < 0 state: running but idle
History list length 27
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 810792, ACTIVE 250 sec updating or deleting, thread declared inside InnoDB 4999
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 148218, OS thread handle 6032, query id 522946 localhost ::1 utente_tat updating
UPDATE do_update SET updated = 1 WHERE description = 'red_tubes'
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 1
434 OS file reads, 594258 OS file writes, 383465 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 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 69257, node heap has 1 buffer(s)
Hash table size 69257, node heap has 1 buffer(s)
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 3 buffer(s)
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 135438467
Log flushed up to 135438467
Pages flushed up to 135437908
Last checkpoint at 135437908
0 pending log flushes, 0 pending chkp writes
195400 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 274595840
Dictionary memory allocated 138910
Buffer pool size 16384
Free buffers 15898
Database pages 481
Old database pages 0
Modified db pages 8
Pending reads 0
Pending writes: LRU 0, flush list 3, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 402, created 79, written 332679
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: 481, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=4496, Main thread ID=1032, state: sleeping
Number of rows inserted 521, updated 68755, deleted 1, read 5127221
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

=====================================
2018-08-18 09:00:22 0xa28 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 20 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 67298 srv_active, 0 srv_shutdown, 70706 srv_idle
srv_master_thread log flush and writes: 138004
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 473730
--Thread 6032 has waited at row0upd.cc line 2858 for 270.00 seconds the semaphore:
X-lock on RW-latch at 00000000178D6AE0 created in file buf0buf.cc line 1460
a writer (thread id 2108) has reserved it in mode SX
number of readers 0, waiters flag 1, lock_word: 10000000
Last time read locked in file btr0pcur.ic line 562
Last time write locked in file G:\ade\build\sb_0-26514852-1514435511.83\mysql-5.7.21\storage\innobase\buf\buf0flu.cc line 1198
OS WAIT ARRAY INFO: signal count 473045
RW-shared spins 0, rounds 124965, OS waits 62481
RW-excl spins 0, rounds 103548, OS waits 3434
RW-sx spins 5, rounds 105, OS waits 3
Spin rounds per wait: 124965.00 RW-shared, 103548.00 RW-excl, 21.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 810795
Purge done for trx's n:o < 810792 undo n:o < 0 state: running but idle
History list length 27
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 810792, ACTIVE 270 sec updating or deleting, thread declared inside InnoDB 4999
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 148218, OS thread handle 6032, query id 522946 localhost ::1 utente_tat updating
UPDATE do_update SET updated = 1 WHERE description = 'red_tubes'
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,

Only initial part of log

Options: ReplyQuote


Subject
Views
Written By
Posted
Help on a mysql error log file
105
August 18, 2018 03:01AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.