Hi Andrew
I have a few questions regarding your MySQL DB:
1. currently how big is your DB? Size of ibdata1 + nb of rows in
cns_file_replica + dpm_req;
2. output of show variables (from mysql command line);
3. your MySQL DB sits on the DPM head node, doesn't it? If not then I'd like
to know the hw characteristics of your mysql server.
By the way did you notice some performance downgrade in your DPM or it is
just a general question?
Cheers
Lana
PS:
-----Original Message-----
From: Andrew Elwell [mailto:[log in to unmask]]
Sent: Wednesday, April 23, 2008 1:53 PM
To: dpm-users-forum; LHC Computer Grid - Rollout
Subject: DPM headnode tuning (MySQL)
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
|
