Hi folks,
Has anyone got any advice / experience of tuning a DPM headnode - At
Glasgow we have a dual opteron box with 8G of memory running vanilla
SL4x
We have *not* applied any site-specific sysctl tuning apart from those
implemented by YAIM
kernel.core_uses_pid = 1
# Beginning of YAIM additions - do not delete/modify this line
# TCP buffer sizes
net.ipv4.tcp_rmem = 131072 1048576 2097152
net.ipv4.tcp_wmem = 131072 1048576 2097152
net.ipv4.tcp_mem = 131072 1048576 2097152
net.core.rmem_default = 1048576
net.core.wmem_default = 1048576
net.core.rmem_max = 2097152
net.core.wmem_max = 2097152
# SACK and timestamps - turn off
net.ipv4.tcp_dsack = 0
net.ipv4.tcp_sack = 0
net.ipv4.tcp_timestamps = 0
# Network backlog
net.core.netdev_max_backlog = 10000
# End of YAIM additions - do not delete/modify this line
however we're seeing a non-insignificant load on the box that I'd like
to understand better.
Looking at the mysqld as a possible candidate for improvement there
seems to be a substantial amount of file IO - I think (hey I'm not a
DBA) this culd be improved by throwing it all into memory as much as
possible
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
root 3792 0.0 0.0 53812 1324 ? S Apr22 0:00 /bin/
sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file=/var/
run/mysqld/mysqld.pid
mysql 3829 51.3 0.4 162160 39356 ? Sl Apr22 575:55 /usr/
libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/
var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --
skip-locking --socket=/var/lib/mysql/mysql.sock
so plenty of expansion available
$> mysql -e "SHOW INNODB STATUS\G"
*************************** 1. row ***************************
Status:
=====================================
080423 12:47:58 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 55 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 8004778, signal count 8003496
Mutex spin waits 51664455, rounds 131084687, OS waits 23731
RW-shared spins 16038032, OS waits 7804244; RW-excl spins 433643, OS
waits 50281
------------
TRANSACTIONS
------------
Trx id counter 0 75852334
Purge done for trx's n:o < 0 75852326 undo n:o < 0 0
History list length 5
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 3829, OS thread id
1155197280
MySQL thread id 3290, query id 1407272 localhost root
SHOW INNODB STATUS
---TRANSACTION 0 75660564, not started, process no 3829, OS thread id
1155397984
MySQL thread id 2741, query id 1135780 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
---TRANSACTION 0 75807048, not started, process no 3829, OS thread id
1152788832
MySQL thread id 987, query id 1338985 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
---TRANSACTION 0 75725378, not started, process no 3829, OS thread id
1152989536
MySQL thread id 986, query id 1219955 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
---TRANSACTION 0 75808912, not started, process no 3829, OS thread id
1153190240
MySQL thread id 985, query id 1341598 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
---TRANSACTION 0 75838893, not started, process no 3829, OS thread id
1152387424
MySQL thread id 386, query id 1387167 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
---TRANSACTION 0 75757688, not started, process no 3829, OS thread id
1152186720
MySQL thread id 263, query id 1266693 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
---TRANSACTION 0 75850351, not started, process no 3829, OS thread id
1151986016
MySQL thread id 83, query id 1404454 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
---TRANSACTION 0 75850361, not started, process no 3829, OS thread id
1151785312
MySQL thread id 82, query id 1404467 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
---TRANSACTION 0 75844682, not started, process no 3829, OS thread id
1151383904
MySQL thread id 62, query id 1395511 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
---TRANSACTION 0 75844592, not started, process no 3829, OS thread id
1151584608
MySQL thread id 59, query id 1395330 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
---TRANSACTION 0 75851943, not started, process no 3829, OS thread id
1151183200
MySQL thread id 27, query id 1406722 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
---TRANSACTION 0 75852038, not started, process no 3829, OS thread id
1150982496
MySQL thread id 26, query id 1406869 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
---TRANSACTION 0 75851630, not started, process no 3829, OS thread id
1150781792
MySQL thread id 24, query id 1406252 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
---TRANSACTION 0 75851999, not started, process no 3829, OS thread id
1150581088
MySQL thread id 23, query id 1406781 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
---TRANSACTION 0 75852265, not started, process no 3829, OS thread id
1150380384
MySQL thread id 22, query id 1407185 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
---TRANSACTION 0 75848656, not started, process no 3829, OS thread id
1150179680
MySQL thread id 21, query id 1401849 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
---TRANSACTION 0 75848334, not started, process no 3829, OS thread id
1149376864
MySQL thread id 20, query id 1401445 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
---TRANSACTION 0 75851634, not started, process no 3829, OS thread id
1149577568
MySQL thread id 19, query id 1406261 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
---TRANSACTION 0 75852120, not started, process no 3829, OS thread id
1149978976
MySQL thread id 18, query id 1406997 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
---TRANSACTION 0 75852269, not started, process no 3829, OS thread id
1147971936
MySQL thread id 17, query id 1407194 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
---TRANSACTION 0 75834920, not started, process no 3829, OS thread id
1149176160
MySQL thread id 16, query id 1380895 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
---TRANSACTION 0 75852324, not started, process no 3829, OS thread id
1149778272
MySQL thread id 15, query id 1407263 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
---TRANSACTION 0 75851729, not started, process no 3829, OS thread id
1148975456
MySQL thread id 9, query id 1406399 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
---TRANSACTION 0 75852315, not started, process no 3829, OS thread id
1148774752
MySQL thread id 8, query id 1407250 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
---TRANSACTION 0 75851341, not started, process no 3829, OS thread id
1148172640
MySQL thread id 5, query id 1405876 localhost monami
---TRANSACTION 0 75852333, not started, process no 3829, OS thread id
1147771232
MySQL thread id 3, query id 1407271 svr018.gla.scotgrid.ac.uk
130.209.239.18 dpmmgr
--------
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
145500196 OS file reads, 256777 OS file writes, 100789 OS fsyncs
2380.16 reads/s, 26475 avg bytes/read, 8.47 writes/s, 3.07 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 405, seg size 407, is empty
Ibuf for space 0: size 1, free list len 405, seg size 407,
20849 inserts, 20849 merged recs, 17438 merges
Hash table size 17393, used cells 1237, node heap has 3 buffer(s)
2768.17 hash searches/s, 10615.97 non-hash searches/s
---
LOG
---
Log sequence number 1 3501785060
Log flushed up to 1 3501785060
Last checkpoint at 1 3501785060
0 pending log writes, 0 pending chkp writes
81348 log i/o's done, 2.44 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 22563936; in additional pool allocated 1048576
Buffer pool size 512
Free buffers 0
Database pages 509
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 235254062, created 1396, written 182366
3846.15 reads/s, 0.05 creates/s, 6.35 writes/s
Buffer pool hit rate 919 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread process no. 3829, id 1147169120, state: sleeping
Number of rows inserted 23356, updated 62803, deleted 6614, read
2070611008
0.64 inserts/s, 1.87 updates/s, 0.20 deletes/s, 36375.45 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
it's that "2380.16 reads/s, 26475 avg bytes/read, 8.47 writes/s, 3.07
fsyncs/s" in the FILE I/O section that concerns me and the BUFFER POOL
being so small.
Anyone got any thoughts?
Andrew
--
Andrew Elwell
[log in to unmask]
Tel: +44 141 330 6439
Mob: +44 7952 922263
|