List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:November 11 2010 4:30pm
Subject:buf/buf0buf.c:597 hi count
View as plain text  
Anyone has idea if this is normal?

I have a rather hi count on some mutexes on one of my servers:

Find below:

   - server status
   - last section of mutex status
   - full innodb status.


Thanks

Claudio




mysql> status;
--------------
mysql  Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (x86_64) using
readline 5.1

Connection id:          4319976
Current database:
Current user:           cnanni@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.44-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /tmp/mysqlg3.sock
Uptime:                 100 days 17 hours 2 min 14 sec

Threads: 3  Questions: 1018296737  Slow queries: 817553  Opens: 486433
Flush tables: 2  Open tables: 512  Queries per second avg: 117.27


mysql> show mutex status;
...
...
...
| InnoDB | buf/buf0buf.c:547     | os_waits=0        |
| InnoDB | buf/buf0buf.c:545     | os_waits=1        |
| InnoDB | buf/buf0buf.c:547     | os_waits=0        |
| InnoDB | buf/buf0buf.c:545     | os_waits=0        |
| InnoDB | buf/buf0buf.c:547     | os_waits=0        |
| InnoDB | buf/buf0buf.c:545     | os_waits=0        |
| InnoDB | buf/buf0buf.c:547     | os_waits=0        |
| InnoDB | buf/buf0buf.c:545     | os_waits=0        |
| InnoDB | buf/buf0buf.c:547     | os_waits=0        |
| InnoDB | buf/buf0buf.c:545     | os_waits=0        |
| InnoDB | buf/buf0buf.c:547     | os_waits=0        |
| InnoDB | buf/buf0buf.c:545     | os_waits=1        |
| InnoDB | buf/buf0buf.c:547     | os_waits=0        |
| InnoDB | buf/buf0buf.c:545     | os_waits=0        |
| InnoDB | buf/buf0buf.c:597     | os_waits=14107661 |
| InnoDB | fil/fil0fil.c:1313    | os_waits=15487    |
| InnoDB | srv/srv0start.c:1234  | os_waits=0        |
| InnoDB | srv/srv0start.c:1227  | os_waits=0        |
| InnoDB | srv/srv0start.c:1205  | os_waits=0        |
| InnoDB | dict/dict0mem.c:90    | os_waits=0        |
| InnoDB | dict/dict0mem.c:90    | os_waits=0        |
| InnoDB | srv/srv0srv.c:888     | os_waits=2        |
| InnoDB | srv/srv0srv.c:886     | os_waits=1987771  |
| InnoDB | thr/thr0loc.c:227     | os_waits=19       |
| InnoDB | mem/mem0pool.c:206    | os_waits=100246   |
| InnoDB | sync/sync0sync.c:1336 | os_waits=0        |
+--------+-----------------------+-------------------+
524667 rows in set, 1 warning (0.37 sec)

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
101111 17:20:22 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 22 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 48478335, signal count 42451353
Mutex spin waits 0, rounds 8815343059, OS waits 19199213
RW-shared spins 41005267, OS waits 15171494; RW-excl spins 37475092, OS
waits 3827085
------------
TRANSACTIONS
------------
Trx id counter 0 884763960
Purge done for trx's n:o < 0 884763426 undo n:o < 0 0
History list length 153
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 25247, OS thread id 1190668608
MySQL thread id 4319976, query id 1018250617 localhost cnanni
show engine innodb status
---TRANSACTION 0 884763959, not started, process no 25247, OS thread id
1206376768
MySQL thread id 4318685, query id 1018250616 Has read all relay log; waiting
for the slave I/O thread to update it
---TRANSACTION 0 0, not started, process no 25247, OS thread id 1178954048
MySQL thread id 8168, query id 1018247620 localhost 127.0.0.1 mysqlagent
--------
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 (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
36754202 OS file reads, 766794698 OS file writes, 741205810 OS fsyncs
3.41 reads/s, 28180 avg bytes/read, 54.32 writes/s, 53.32 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 287, seg size 289,
7992065 inserts, 7991987 merged recs, 717873 merges
Hash table size 8850487, node heap has 4996 buffer(s)
427.94 hash searches/s, 123.45 non-hash searches/s
---
LOG
---
Log sequence number 465 1466637443
Log flushed up to   465 1466637443
Last checkpoint at  465 1382185700
0 pending log writes, 0 pending chkp writes
736518756 log i/o's done, 53.09 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 4767047170; in additional pool allocated 18110464
Dictionary memory allocated 666112
Buffer pool size   262144
Free buffers       0
Database pages     257148
Modified db pages  11311
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 210179269, created 42642785, written 101055578
5.86 reads/s, 0.32 creates/s, 2.64 writes/s
Buffer pool hit rate 997 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 25247, id 1177090368, state: sleeping
Number of rows inserted 702797334, updated 189838054, deleted 302616536,
read 205646392165
13.86 inserts/s, 12.45 updates/s, 4.64 deletes/s, 398.80 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

Thread
buf/buf0buf.c:597 hi countClaudio Nanni11 Nov