Hi,
I suppose this is really I/O problem.
What says vmstat during backup?
I see many fsyncs, so you have probably innodb_flush_log_at_trx_commit=1
Try to set innodb_flush_log_at_trx_commit=2
If it solve your problem and you need innodb_flush_log_at_trx_commit=1,
install battery backed write cache on your raid. If you have it already,
you probably need faster storage (better raid level, more disks, faster
disks, etc...)
Maybe you can figure out some aplication write cache for impression
counter. Something like grouping count's in shared memory, or memcache
server and flushing them in db on some timeout.
You can also use making backup on slave as somebody mentioned before.
Filip
------------------
---TRANSACTION 0 190439971, ACTIVE 7 sec, process no 23228, OS thread id
2296302480 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320
MySQL thread id 743938, query id 22854373 localhost 127.0.0.1 dbuser
Updating
UPDATE table_a SET impression_count = impression_count + 1 WHERE id = '93'
------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 52 n bits 104 index `PRIMARY` of table
`dbname/table_a` trx id 0 190439971 lock_mode X locks rec but not gap
waiting
Record lock, heap no 16 PHYSICAL RECORD: n_fields 12; compact format;
info bits 0
0: len 8; hex 800000000000005d; asc ];; 1: len 6; hex 00000b59e1bd; asc
Y ;; 2: len 7; hex 0000048018206d; asc m;; 3: len 8; hex
800000000000000e; asc ;; 4: len 8; hex 800000000000006d; asc m;; 5: len
8; hex 8000000045e61780; asc E ;; 6: SQL NULL; 7: len 8; hex
80000000000964d8; asc d ;; 8: len 8; hex 80000000000009fc; asc ;; 9: len
4; hex 80000001; asc ;; 10: len 4; hex 80000001; asc ;; 11: len 4; hex
c5e4e1c4; asc ;;
------------------
---TRANSACTION 0 190439869, ACTIVE 8 sec, process no 23228, OS thread id
2277473168, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 743922, query id 22850253 localhost 127.0.0.1 dbuser end
UPDATE table_a SET impression_count = impression_count + 1 WHERE id = '93'
---TRANSACTION 0 190435058, ACTIVE 153 sec, process no 23228, OS thread
id 2296707984 sleeping before joining InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 743227, query id 22843484 remote.server 123.123.123.123
root Sending data
SELECT /*!40001 SQL_NO_CACHE */ * FROM `really_large_table`
Trx read view will not see trx with id >= 0 190435059, sees < 0 190435059
--------
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: 1; buffer pool: 0
152168 OS file reads, 5020745 OS file writes, 4737490 OS fsyncs
216.14 reads/s, 37081 avg bytes/read, 25.87 writes/s, 25.14 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7,
20774 inserts, 20771 merged recs, 20694 merges
Hash table size 2212699, used cells 17023, node heap has 18 buffer(s)
759.38 hash searches/s, 88.03 non-hash searches/s
---
LOG
---
Log sequence number 17 1691567773
Log flushed up to 17 1691567579
Last checkpoint at 17 1690543049
1 pending log writes, 0 pending chkp writes
4694950 log i/o's done, 25.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 611637398; in additional pool allocated 3526400
Buffer pool size 32768
Free buffers 0
Database pages 32750
Modified db pages 188
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 315389, created 2063, written 474318
489.21 reads/s, 0.59 creates/s, 2.05 writes/s
Buffer pool hit rate 951 / 1000
--------------
ROW OPERATIONS
--------------
7 queries inside InnoDB, 1 queries in queue
2 read views open inside InnoDB
Main thread process no. 23228, id 2367634320, state: sleeping
Number of rows inserted 356882, updated 1963990, deleted 293832, read
875872021
2.05 inserts/s, 10.32 updates/s, 0.21 deletes/s, 48500.03 reads/s
Thanks again,
--
Filip Krejci <[EMAIL PROTECTED]>
LINUX-for a better future
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
