Filip Krejci wrote:
> Hi,
>
> you are right, option --single-transaction does not accquire any lock on
> your innodb tables. Backup is fully on-line due to mvcc.
>
> You should look for another reason of this behavior.
>
> 1/ What says 'show full processlist' when backup is running
> 2/ What says 'show engine innodb\G' when backup is running
> 2/ Is your db server stopped or very slow only? (you will see in 1/)
> 3/ If it's very slow
> a/ what is your mysqldump config in my.cnf
> b/ what about remote backup (mysqldump --host x.x.x.x --master-data
> --single-transaction > dump.sql) which cause separating writes on
> another box?
I tried this again and gathered some more information.
Honestly, I'm not entirely sure what I'm looking for, so this mail
contains a lot of information from 'SHOW ENGINE INNODB STATUS'.
To summaries what I can see from this, is appears that the UPDATE on
table_a is locking that table, and that simple update had taken 8
seconds by the time I stopped the dump. At this point in time there
we're 10's of queries queued up, and the site had stopped functioning
entirely. As you can see, this only starts to be a problem about 2.5
minutes into the remote dump (here shown from remote.server, 153 seconds
in). This seems to be a consistent place for the dump to be a problem -
about 1.2 gig of data has been transfered to remote.server at this
point, which is the same as my previous attempts.
I can't understand why a simple primary key based UPDATE on a table
could slow down this much (note this is not the table being dumpped at
this point, but in my setup all InnoDB databases are in one file, so as
far as the OS is concerned I guess the fact it's different tables means
nothing)
Perhaps I should use iptables to rate limit traffic between the database
server and the remote.server - thats on the assumption that this is
simply an I/O problem... however I'm not sure that's what it is, as I
said above I'm not entirely sure how to full interpret what I'm looking at
below.
The below has had 10's of queries remove, and only shows the queries I
considered relevant.
------------------
---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,
--
Ian P. Christian ~ http://pookey.co.uk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
