Re: mysqldump --single-transaction causes database to become unusable
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 805d; asc ];; 1: len 6; hex 0b59e1bd; asc Y ;; 2: len 7; hex 048018206d; asc m;; 3: len 8; hex 800e; asc ;; 4: len 8; hex 806d; asc m;; 5: len 8; hex 800045e61780; asc E ;; 6: SQL NULL; 7: len 8; hex 800964d8; asc d ;; 8: len 8; hex 89fc; asc ;; 9: len 4; hex 8001; asc ;; 10: len 4; hex 8001; 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]
Re: mysqldump --single-transaction causes database to become unusable
Filip Krejci wrote: > Hi, > > I suppose this is really I/O problem. > You're right, it looks like it was just an I/O problem - your suggestion was spot on. I've now managed to dump my master data, and can get my slave back online! Thanks a lot for your suggestion, -- 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]
Re: mysqldump --single-transaction causes database to become unusable
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 805d; asc ];; 1: len 6; hex 0b59e1bd; asc Y ;; 2: len 7; hex 048018206d; asc m;; 3: len 8; hex 800e; asc ;; 4: len 8; hex 806d; asc m;; 5: len 8; hex 800045e61780; asc E ;; 6: SQL NULL; 7: len 8; hex 800964d8; asc d ;; 8: len 8; hex 89fc; asc ;; 9: len 4; hex 8001; asc ;; 10: len 4; hex 8001; 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 lo
Re: mysqldump --single-transaction causes database to become unusable
In news:[EMAIL PROTECTED], "Ian P. Christian" <[EMAIL PROTECTED]> wrote: > This database I'm dumping has something like 17 million rows, all but > 1 table (which uses FULLTEXT, and only has 3-4k rows) run innodb. > There is only one table of any real size, and this table has all but > about 100k of the total rows in. My understanding of this command is > that the database should not be locked whilst this command is running. > > However, here's my problem... > When the dump starts to read from large table, the database just > grinds to a halt - my website running from the database just stops, > and the dump (which I was watching progress with a privative `watch > ls -la`) slows down a bit. Unless the isolation level on your server is set to serializable, no locks should be set during the dump. My guess is that in a given period of time your web applications use only a fraction of the data stored in tables. Therefore most of the needed information is constantly cached in the internal buffers and thus the queries are exectued quickly and efficiently. Since InnoDB only reads data through those buffers, when you request all the rows from a multi-million table, the buffer pool contents is overwritten by random data pages. In consequence the execution of most queries can no longer be fulfilled with a fast memory access as the data is no longer there. The workload becomes heavly disk-bound, which is not efficient enough for your web traffic. Maciek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump --single-transaction causes database to become unusable
I have few information, but i suppose that you are on performance border of your db server. So you haven't reserve for doing backup. Send some few rows of command vmstat 1, before backup process and through backup process. How are these numbers: - queries per second ? - updates / selects rate ? What is you settings of variables ? - sync_binlog - innodb_flush_log_at_trx_commit Have you raid with write cache enabled ? What are box params? Data size ? Index size ? 'show engine innodb status' isn't demanding query and it could say very usefull informations about state of your server. -- Filip Krejci <[EMAIL PROTECTED]> Linux - und Spass dabei! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump --single-transaction causes database to become unusable
On 7 Mar 2007, at 09:30, Ian P. Christian wrote: --single-transaction Creates a consistent snapshot by dumping all tables in a single transaction. Works ONLY for tables stored in storage engines which support multiversioning (currently only InnoDB does); the dump is NOT guaranteed to be consistent for other storage engines. Option automatically turns off --lock-tables. I'll accept my interpritation of the above could be very wrong however... That does indeed sound promising - however, you said that not all your tables are InnoDB, so maybe it just gets ignored? Whilst I'm here and talking about slaves... is it possible to have a slave to 2 different databases on 2 different hosts? Apparently not: http://www.nabble.com/one-slave-with-multiple-masters- t2063846.html Having said that, I don't see any reason you couldn't run multiple instances of mysqld on one machine, perhaps on different ports or interfaces. Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ [EMAIL PROTECTED] | http://www.synchromedia.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump --single-transaction causes database to become unusable
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 It shows mostly inserts/updates into tables, these tables being tables other then the huge 16-17 million row one. These tables are used for session storage, and as such, if this block (which goes on as almost the first thing my site does), then no SELECT statements for site content are executed 2/ What says 'show engine innodb\G' when backup is running I've actually not checked - I shall do that and report shortly - it's nearly midday for me, and that means lots of traffic on the site, not a good time to be playing :) 2/ Is your db server stopped or very slow only? (you will see in 1/) I *think* it's slow to the point where it's unusable. Until the backup gets round to the huge table, inserts/updates/selects are all going on just fine,. 3/ If it's very slow a/ what is your mysqldump config in my.cnf [mysqldump] quick max_allowed_packet = 16M that's all there is... I'm not sure what hte 'quick' does, I'll go and check the manual on that, perhaps that's the issue. I'm guessing I might have more luck increasing the packet size? 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 did do this under the assumption it could simply be an I/O problem - however the problem persists. It might be because the network connection between the two hosts is pretty fast Thanks Filip! -- 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]
Re: mysqldump --single-transaction causes database to become unusable
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? -- Filip Krejci <[EMAIL PROTECTED]> Vini, vidi, Linux! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump --single-transaction causes database to become unusable
Marcus Bointon wrote: Hi Marcus :) > On 7 Mar 2007, at 08:44, Ian P. Christian wrote: > > --single-transaction doesn't _do_ the dump as a transaction, it simply > wraps the dump in begin/commit statements so it's atomic when restoring. > > If the dump is to preserve relational integrity then it has to lock > tables or disable access (or writes/deletes can happen during the dump). > There are two alternatives: One is to use innoDB's commercial hotbackup > utility (which I've not used, but it's apparently 'the way'). I was under the impression that with multi-versioning of InnoDB, that it wouldn't need to do a write lock? Sorry to quote this much from 'mysqldump --help' --master-data[=#] This causes the binary log position and filename to be appended to the output. If equal to 1, will print it as a CHANGE MASTER command; if equal to 2, that command will be prefixed with a comment symbol. This option will turn --lock-all-tables on, unless --single-transaction is specified too (in which case a global read lock is only taken a short time at the beginning of the dump - don't forget to read about --single-transaction below). In all cases any action on logs will happen at the exact moment of the dump.Option automatically turns --lock-tables off. --single-transaction Creates a consistent snapshot by dumping all tables in a single transaction. Works ONLY for tables stored in storage engines which support multiversioning (currently only InnoDB does); the dump is NOT guaranteed to be consistent for other storage engines. Option automatically turns off --lock-tables. I'll accept my interpritation of the above could be very wrong however... > The other > is one of the reasons for using a slave - stop the slave, do the dump, > restart the slave and it will catch up anything it missed. It helps if > you can dedicate a slave for this. Yes...I'm aware of this one, but alas.. this was my only slave, and it managed to become out of sync somehow (something to do with a bug when using 'mysqladmin kill'). Now I know that things like this happen, I'll take weekly snapshots of the slave data, but like I said - you live and learn :) Whilst I'm here and talking about slaves... is it possible to have a slave to 2 different databases on 2 different hosts? -- 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]
Re: mysqldump --single-transaction causes database to become unusable
Hi , --single-transaction will execute the same nature of mysqldump command with begin and end transaction. How ever the table is locked for the backup your site may be slow. -- Praj Ian P. Christian wrote: Recently my one and only slave went down, and stupidly I don't have a dump suitable for reseeding (is that's the right term...) the slave, so need to make a snapshot of the master database again. This time I'll make sure I keep this datafile for future restores should I need to - you live and learn. So... I'm doing a database dump: mysqldump --master-data --single-transaction database > dump.sql This database I'm dumping has something like 17 million rows, all but 1 table (which uses FULLTEXT, and only has 3-4k rows) run innodb. There is only one table of any real size, and this table has all but about 100k of the total rows in. My understanding of this command is that the database should not be locked whilst this command is running. However, here's my problem... When the dump starts to read from large table, the database just grinds to a halt - my website running from the database just stops, and the dump (which I was watching progress with a privative `watch ls -la`) slows down a bit. Last time I had to do this (for the first 'seeding' of my slave), I eventually gave up trying to dump from the database whilst the site remained live, and took the site down for 15 minutes whilst the dump ran. As I'm sure you'll understand I'm not too keen on taking the website down again. Any suggestions as to why my database is stopping (could be I/O related maybe? it's on a good RAID setup though), and what I could do about it? Many Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump --single-transaction causes database to become unusable
On 7 Mar 2007, at 08:44, Ian P. Christian wrote: mysqldump --master-data --single-transaction database > dump.sql This database I'm dumping has something like 17 million rows, all but 1 table (which uses FULLTEXT, and only has 3-4k rows) run innodb. There is only one table of any real size, and this table has all but about 100k of the total rows in. My understanding of this command is that the database should not be locked whilst this command is running. --single-transaction doesn't _do_ the dump as a transaction, it simply wraps the dump in begin/commit statements so it's atomic when restoring. If the dump is to preserve relational integrity then it has to lock tables or disable access (or writes/deletes can happen during the dump). There are two alternatives: One is to use innoDB's commercial hotbackup utility (which I've not used, but it's apparently 'the way'). The other is one of the reasons for using a slave - stop the slave, do the dump, restart the slave and it will catch up anything it missed. It helps if you can dedicate a slave for this. I reserve the right to be completely wrong though ;^) Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ [EMAIL PROTECTED] | http://www.synchromedia.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
