Thanx a lot for the response
We restarted MySQL couple of days ago, so it not reached “maximum memory usage” level yet, but it already use much more, than expected. Thus :
[COLOR=#252C2F]ps aux |grep mysqld :
root 4196 0.0 0.0 3956 672 ? S Oct07 0:00 /bin/sh /usr/bin/mysqld_safe
mysql 4714 0.4 35.9 7663028 5813656 ? Sl Oct07 12:12 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/mysql-error.log --open-files-limit=65535 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
[COLOR=#252C2F]SHOW ENGINE INNODB STATUS\G :
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
131009 20:42:06 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 61 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 116769 1_second, 116756 sleeps, 10894 10_second, 7974 background, 7974 flush
srv_master_thread log flush and writes: 160562
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 9310, signal count 9332
Mutex spin waits 16251, rounds 44952, OS waits 809
RW-shared spins 8010, rounds 239120, OS waits 7963
RW-excl spins 23, rounds 16181, OS waits 523
Spin rounds per wait: 2.77 mutex, 29.85 RW-shared, 703.52 RW-excl
--------
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)
I/O thread 18 state: waiting for completed aio requests (write thread)
I/O thread 19 state: waiting for completed aio requests (write thread)
I/O thread 20 state: waiting for completed aio requests (write thread)
I/O thread 21 state: waiting for completed aio requests (write thread)
I/O thread 22 state: waiting for completed aio requests (write thread)
I/O thread 23 state: waiting for completed aio requests (write thread)
I/O thread 24 state: waiting for completed aio requests (write thread)
I/O thread 25 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, 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
10212 OS file reads, 235493 OS file writes, 70791 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 2.70 writes/s, 0.70 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 252 merges
merged operations:
insert 268, delete mark 1, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 4425293, node heap has 923 buffer(s)
298.62 hash searches/s, 66.21 non-hash searches/s
---
LOG
---
Log sequence number 824252591
Log flushed up to 824252348
Last checkpoint at 824251628
Max checkpoint age 216721613
Checkpoint age target 209949063
Modified age 963
Checkpoint age 963
0 pending log writes, 0 pending chkp writes
65814 log i/o's done, 0.72 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 2205155328; in additional pool allocated 0
Total memory allocated by read views 200
Internal hash tables (constant factor + variable factor)
Adaptive hash index 50528992 (35402344 + 15126648)
Page hash 2213656 (buffer pool 0 only)
Dictionary cache 9743324 (8851984 + 891340)
File system 121120 (82672 + 38448)
Lock system 5314168 (5313416 + 752)
Recovery system 0 (0 + 0)
Dictionary memory allocated 891340
Buffer pool size 131071
Buffer pool size, bytes 2147467264
Free buffers 119742
Database pages 10406
Old database pages 3822
Modified db pages 10
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 30, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 10114, created 292, written 160504
0.00 reads/s, 0.00 creates/s, 1.90 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: 10406, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
0 transactions active inside InnoDB
0 out of 1000 descriptors used
---OLDEST VIEW---
Normal read view
Read view low limit trx n:o 1BBD7AC
Read view up limit trx id 1BBD7AC
Read view low limit trx id 1BBD7AC
Read view individually stored trx ids:
-----------------
Main thread process no. 4714, id 140382332544768, state: flushing log
Number of rows inserted 16030, updated 25429, deleted 2103, read 161400366
0.07 inserts/s, 0.39 updates/s, 0.00 deletes/s, 505.29 reads/s
------------
TRANSACTIONS
------------
Trx id counter 1BBD7C0
Purge done for trx's n:o < 1BBD7A5 undo n:o < 0
History list length 2784
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 57179, OS thread handle 0x7fad4e041700, query id 1846813 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 1BB96CA, not started
MySQL thread id 56150, OS thread handle 0x7fad4e000700, query id 1822848 localhost 127.0.0.1 XXXXXXXXXXXX
----------------------------
END OF INNODB MONITOR OUTPUT
============================
[COLOR=#252C2F]Do you have large blob type columns maybe in any tables?
PHP application actually is typical CMS system. text and longtext columns are present in several tables. Such column can contain relative big peace of text. maximum - near 50000 characters.
[COLOR=#252C2F]Also any reason table cache is 4k when there are less then 100 of them?
No any special reason. Simple, it is one of the trace of our fight