… live server. Mysqldump works well for this, but can quite slow on larger schemas. One of the downsides to mysqldump is the need… structures in place. Fortunately, this can be done easily using mysqldump –no-data. Now, enough with the theory, here is the…/2013-06-03_11-30/orig) Get the table structures: mysqldump –no-data orig > /tmp/orig.schema.sql Create the new…
Post: Side load may massively impact your MySQL Performance
November 13, 2011 By 7 Comments
… table drops more than 150 times when heavy mysqldump is running concurrently. mysqldump itself also slows down about 2x. What is going on… IO bound hence such drop in performance. The performance of mysqldump is impacted too because we now have 2 threads competing…. Running Sysbench and MySQLDump convurrently gives about 325 req/sec for sysbench and some 100 seconds for mysqldump which is a dramatic…
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 query… ask what happens now with Q2 ? yes, it’s getting slow now, as we made key “block_id” inserted not in… | 35904 | +——————————+——-+ 2 rows in set (0.00 sec) As for mysqldump you may use –order-by-primary options to force dump…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… to tmp table” part will not be affected by a slow tmpdir, but rebuilding the indexes will obviously take longer. Another… 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: Announcing Percona Server 5.1.66-14.2
January 15, 2013 By Leave a Comment
… will already clean them. Bug fixed #1076215 (Laurynas Biveinis). When mysqldump was used with –innodb-optimize-keys, it did not handle… cases where indexes with AUTO_INCREMENT columns where correctly detected, mysqldump prevented all such keys from optimization, even though it is… to increase for every query stored procedure logged to the slow query log. Bug fixed #719386 (Alexey Kopytov). Other bug fixes…
Post: Announcing Percona Server 5.5.28-29.3
January 8, 2013 By 2 Comments
… Rows_read would be identical in the Slow Query Log. This bug was introduced when slow_extended.patch was ported to Percona… been reserved. Bug fixed #1083700 (George Ormond Lorch III). When mysqldump was used with –innodb-optimize-keys, it did not handle… cases where indexes with AUTO_INCREMENT columns where correctly detected, mysqldump prevented all such keys from optimization, even though it is…
Post: Percona Server 5.5.16-22.0
October 16, 2011 By 2 Comments
… foreign key changes. This unnecessarily delays their creation in a mysqldump output, so –innodb-optimize-keys should ignore foreign key constrains… log the error number correctly. #830199 (Oleg Tsarev). Variable log-slow-admin-statements was not listed with SHOW VARIABLES. #830199 (Oleg…
Post: Predicting how long data load would take
May 24, 2007 By 14 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… makes it extremely hard to predict. Especially fact load can slow down dramatically as amount of data loaded in the table…
Post: How to create/restore a slave using GTID replication in MySQL 5.6
February 8, 2013 By 7 Comments
…-08002762b8af:1-2 | +—————+——————————————+ Now we take a backup with mysqldump from the master: # mysqldump –all-databases –single-transaction –triggers –routines –host=127… to restore a slave in a good and slow way The good way is mysqldump again. We take a dump from the…
Comment: Using LVM for MySQL Backup and Replication Setup
August 18, 2011 By 53 Comments
… performance goes for a toss and that causes slave to slow down and not sync up with the master . Now some… particular volume group they are residing in , now taking a mysqldump is a long process and can’t afford the slave… this mess that i haven’t thought about apart from mysqldump ??? Tariq
