From: Filip Krejci (krejcifgmail.com)
Date: Thu Mar 08 2007 - 10:02:50 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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 <krejcifgmail.com>

LINUX-for a better future

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]