Hello,
we encounter a strange behaviour in one of our Database Server.
Our setup consists of a SUN V440 which has mounted a NFS Share from a Network
Appliance File Server.
This setup have been running a long term without complications although we knew
that there are many statements concerning not using the NFS for storage.
But in the last few Months we occasionally notice some sort of "running
threads" accumulation and very slow queries, on tables with many
inserts/updates, as if the Server locks itself.
Sometimes we have also corrupt tables.
however we can eliminate this condition by "refreshing" the Server via
"mysqladmin refresh".
So we got one or two questions ;o)
- what does "mysqladmin refresh" do exactly - regarding the setup with NFS,
etc.
- is this some case of the well known "lock contention" - we can't figure
that out for now
- could it be that we just tampering with some bad configuration, although
this setups runs trouble-free for "ages"
- what part should we take for a closer inspection (NFS, Locking, Lock
Contention, etc.)
For those of you whishing to have moe information an that, continue reading ;o)
output of MySQL - server status
Created tmp disk tables 357243
Created tmp tables 46643862
Created tmp files 0
Delayed insert threads 2
Delayed writes 380091
Delayed errors 352
Flush commands 7
Handler commit 0
Handler delete 2117679
Handler read first 123506
Handler read key 3719247462
Handler read next 1733660317
Handler read prev 864031371
Handler read rnd 479522668
Handler read rnd next 662387797
Handler rollback 0
Handler update 21722296
Handler write 562227857
Key blocks used 1869200
Key read requests 2110396355
Key reads 2791032
Key write requests 48615181
Key writes 19078871
Max used connections 2241
Not flushed key blocks 0
Not flushed delayed rows 0
Open tables 1169
Open files 1261
Open streams 0
Opened tables 52579
Qcache queries in cache 49303
Qcache inserts 142463949
Qcache hits 271540313
Qcache lowmem prunes 38858
Qcache not cached 227747419
Qcache free memory 968456720
Qcache free blocks 38868
Qcache total blocks 138417
Rpl status NULL
Select full join 9344
Select full range join 124
Select range 11702375
Select range check 0
Select scan 2146643
Slave open temp tables 0
Slave running OFF
Slow launch threads 0
Slow queries 597768
Sort merge passes 0
Sort range 4423868
Sort rows 572734169
Sort scan 47110770
Table locks immediate 838476154
Table locks waited 6643354
Threads cached 5
Threads created 5098289
Threads connected 24
Threads running 2
Servertraffic:
Traffic ø per hour
Received 460.159 KB 935.627 Bytes
Send 26.752 KB 54.394 Bytes
Total 486.912 KB 990.022 Bytes
Connections ø per hour %
Failed 27.771 55,14 0,07 %
Aborted 28.056 55,71 0,07 %
Total 41.856.775 83.111,36 100,00 %
Query statistic:
Total ø per hour ø per min ø per sec
810.808.855 1.609.952,71 26.832,55 447,21
Query type ø per hour %
admin commands 328.082 651,44 0,04 %
alter table 36 0,07 0,00 %
analyze 4 0,01 0,00 %
backup table 0 0,00 0,00 %
begin 0 0,00 0,00 %
change db 107.083.472 212.626,35 13,93 %
change master 0 0,00 0,00 %
check 31 0,06 0,00 %
commit 0 0,00 0,00 %
create db 0 0,00 0,00 %
create function 0 0,00 0,00 %
create index 0 0,00 0,00 %
create table 72 0,14 0,00 %
delete 1.760.259 3.495,19 0,23 %
delete multi 0 0,00 0,00 %
drop db 0 0,00 0,00 %
drop function 0 0,00 0,00 %
drop index 0 0,00 0,00 %
drop table 64 0,13 0,00 %
flush 8 0,02 0,00 %
grant 0 0,00 0,00 %
ha close 0 0,00 0,00 %
ha open 0 0,00 0,00 %
ha read 0 0,00 0,00 %
insert 3.571.899 7.092,41 0,46 %
insert select 20 0,04 0,00 %
kill 1.024 2,03 0,00 %
load 79 0,16 0,00 %
load master data 0 0,00 0,00 %
load master table 0 0,00 0,00 %
lock tables 11.793 23,42 0,00 %
optimize 313 0,62 0,00 %
purge 0 0,00 0,00 %
rename table 0 0,00 0,00 %
Query type ø per hour %
repair 1 0,00 0,00 %
replace 3.172.513 6.299,38 0,41 %
replace select 0 0,00 0,00 %
reset 0 0,00 0,00 %
restore table 0 0,00 0,00 %
revoke 0 0,00 0,00 %
rollback 0 0,00 0,00 %
savepoint 0 0,00 0,00 %
select 372.294.236 739.232,32 48,42 %
set option 1.834 3,64 0,00 %
show binlog events 0 0,00 0,00 %
show binlogs 146 0,29 0,00 %
show create 464 0,92 0,00 %
show databases 1.134 2,25 0,00 %
show fields 3.144 6,24 0,00 %
show grants 0 0,00 0,00 %
show keys 741 1,47 0,00 %
show logs 0 0,00 0,00 %
show master status 0 0,00 0,00 %
show new master 0 0,00 0,00 %
show open tables 0 0,00 0,00 %
show processlist 55.121 109,45 0,01 %
show slave hosts 0 0,00 0,00 %
show slave status 0 0,00 0,00 %
show status 320.818 637,02 0,04 %
show innodb status 0 0,00 0,00 %
show tables 595.313 1.182,06 0,08 %
show variables 241.676 479,88 0,03 %
slave start 0 0,00 0,00 %
slave stop 0 0,00 0,00 %
truncate 29 0,06 0,00 %
unlock tables 0 0,00 0,00 %
update
Database System has been running for about half a day, as we made this
Information dump
The hardware itself consist of an SUN V440 with 4 x 1.2 GHz CPUs and 16 GB RAM
The System Usage is about 40% average
The Storage System (NetApp Filer) is far from being "under load"
Dirk
Dirk Steinmeyer
Systemadministrator / ITS SystemService
SevenOne Intermedia GmbH
Ein Unternehmen der ProSiebenSat.1 Media AG
Medienallee 6
D-85774 Unterföhring
E-Mail: [EMAIL PROTECTED]
Internet:
http://www.ProSieben.de, http://www.Sat1.de,
http://www.Kabel1.de, http://www.Wetter.com,
http://www.N24.de
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
