… engine innodb status: —TRANSACTION BB0E42A, ACTIVE 3 sec fetching rows, thread declared inside InnoDB 334 mysql tables in use 4, locked 4 3264 lock struct(s), heap size 440760, 799203 row lock(s) MySQL thread id 38, OS thread handle 0x2b3c1c776940, query id 16838101 localhost root Copying to tmp table create table t as…
Post: ZFS on Linux and MySQL
May 24, 2013 By 17 Comments
… ZFS does snapshot, like LVM but… since it is a copy on write filesystem, the snapshots are free, no performance penalty… work best with InnoDB, with MyISAM you’ll have to start the snapshot while holding a “flush tables with read lock” and the… ray flip a bit on a drive, instead of crashing InnoDB, it will be caught by ZFS and the data will…
Post: Percona XtraBackup 2.0.7 for MySQL available for download
May 6, 2013 By 1 Comment
… pool dump files). Percona XtraBackup has implemented support for the InnoDB Buffer Pool Preloading introduced in MySQL 5.6. Starting with… with FLUSH TABLES WITH READ LOCK even if xtrabackup would fail when copying logs. Fixed by terminating xtrabackup process immediately on log copying failure…
Post: Percona XtraBackup 2.1.0 'release candidate' for MySQL available for download
May 7, 2013 By Leave a Comment
… pool dump files). Percona XtraBackup has implemented support for the InnoDB Buffer Pool Preloading introduced in MySQL 5.6. Starting with… with FLUSH TABLES WITH READ LOCK even if xtrabackup would fail when copying logs. Fixed by terminating xtrabackup process immediately on log copying failure…
Post: MyISAM Scalability and Innodb, Falcon Benchmarks
October 12, 2007 By 10 Comments
… should be executed without exclusive table locking. Even on 16 threads we’…lock is held, while lock is still held when key block is copied to processing thread local storage on Key Read Request. This lock…table t1 into InnoDB, and results: Threads Queries/sec 1 296 2 341 4 544 8 493 16 498 InnoDB…
Post: Should you move from MyISAM to Innodb ?
January 12, 2009 By 59 Comments
… do not get to deal with recovering tables on the crash or partially executed statements. Table locks is no more problem, hot backups… good for MyISAM kills Innodb, such as copying binary tables between the servers. It is important the team understands Innodb and knows how to… storage engine (typically Innodb) and when use other tables when it really gives substantial gains. I would not switch table to MyISAM because…
Post: InnoDB auto-inc scalability fixed
September 26, 2007 By 18 Comments
… with InnoDB auto-increment field. For details check Bug 16979. In short words the problem is in case of insert into table with auto-increment column the special AUTO_INC table level lock is obtained, instead of usual row-level locks. With many… new system variable innodb_autoinc_lock_mode which determines behavior of InnoDB for tables with autoinc. I do not want to copy-paste MySQL documentation…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… | | Opening tables | 0.000118 | | System lock | 0.000007 | | setup | 0.000027 | | creating table | 0.002255 | | After create | 0.000050 | | copy to tmp table | 61.816063… time required to copy the data to a temporary table is constant. The reason is that when using merge sort, InnoDB has to…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… | | Opening tables | 0.000045 | | System lock | 0.000006 | | setup | 0.000016 | | creating table | 0.168283 | | After create | 0.000061 | | copy to tmp table | 0.165808… to the table metadata, a table ALTER that alters a big table can keep executing without any hindrance, copying rows from the table with the… > set session lock_wait_timeout=1;CREATE TABLE t1(i int(11) not null auto_increment primary key) ENGINE=InnoDB; However, I feel…
Post: Copying InnoDB tables between servers
July 31, 2009 By 11 Comments
… want to copy order_line table to different server. Note I am going to do it online, no needs to lock or shutdown… followings. xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files…
