… original table and rename such temporary table to original name. How does data visibility works in this case ? DDLs are not… row in set (0.00 sec) SESSION2: root@ubuntu:~/dump# mysqldump –single-transaction dumptest > dump.sql SESSION1: (before dump has completed… at the same time as mysqldump was running we got table empty table with new structure in mysqldump instead of table with…
Post: An argument for not using mysqldump
November 8, 2010 By 24 Comments
… take 30×10=5 hours to restore. Right? Wrong. Mysqldump recovery time is not linear. Bigger tables, or tables…, xtrabackup, innodb hot backup), it is very easy to model how much longer recovery time will take: Backup is 80G Copy…) * 1024)/70/60 = ~17 minutes I can tell progress with mysqldump by monitoring the rate at which show global status like…
Post: Predicting how long data load would take
May 24, 2007 By 12 Comments
… you can use Multiple value insert (standard mysqldump output) or LOAD DATA INFILE (–tab mysqldump output). Generally LOAD DATA can be optimized better and a bit faster because of easier parsing. Mysqldump however takes… 3600; du -sh” in the database data directory to see how much data is loaded per hour (assuming your Innodb tablespace…
Post: Introducing new type of benchmark
February 25, 2012 By 16 Comments
… page, you expect to get response within second or two. How we should change the benchmark to base on this model…, but rather distributed by Exponential distribution, with λ = M. That how it goes if to believe to the Poisson process. For… time of queries that handle user load ? The same for mysqldump, how does it affect short user queries ? In fact I have…
Post: How would you compress your MySQL Backup
June 5, 2008 By 29 Comments
… overhead naturally added. UPDATE: It looks like people are wondering how BZIP2 compares so I should check it before I delete… using dedicated server for backups. I should also note for mysqldump backup typically tools with better and slower compression make sense…
Post: Side load may massively impact your MySQL Performance
November 13, 2011 By 7 Comments
…_sandbox5516.sock prepare Running Sysbench and MySQLDump. Note we run them in the loop to see how result stabilizes. [root@localhost msb… small table drops more than 150 times when heavy mysqldump is running concurrently. mysqldump itself also slows down about 2x. What is…. Running Sysbench and MySQLDump convurrently gives about 325 req/sec for sysbench and some 100 seconds for mysqldump which is a dramatic…
Post: Using INFORMATION_SCHEMA instead of shell scripting
November 24, 2008 By 7 Comments
…,”.sql”) | +——————————————————————————+ | mysqldump art73 article73 >> art73.sql | | mysqldump art73 author73 >> art73.sql | | mysqldump art73 forum73 >> art73.sql | | mysqldump art73 forum_stats73 >> art73.sql | | mysqldump art73 img…. How to make it easily runable ? Well just use INTO OUTFILE to create very simple shell script: mysql> select concat(“mysqldump “,table…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… rows in set (0.00 sec) Now let’s see how performance is affected when turning expand_fast_index_creation on… the table I used in my previous examples): $ mysqldump -uroot test > dump_unoptimized.sql $ mysqldump -uroot test –innodb-optimize-keys > dump_optimized… data from a dump would actually make the restore slower; mysqldump –innodb-optimize-keys ignores indexes on AUTO_INCREMENT columns, because…
Post: InnoDB: look after fragmentation
November 5, 2009 By 12 Comments
… that after restoring table from mysqldump, the query that scans data by primary key was slow. How slow ? Let me show. The… | 35904 | +——————————+——-+ 2 rows in set (0.00 sec) As for mysqldump you may use –order-by-primary options to force dump…
Post: The story of one MySQL Upgrade
October 8, 2010 By 8 Comments
….1 As the database size is relatively small we do mysqldump and load which is a safest way, considering we’re… go over binary logs for the last month to see how frequently this functionality is used. Happily there are only few… data match ? (This also would cover data improperly loaded with mysqldump if there is such). We stopped 5.0 and 5…
