… For each table, run: ALTER TABLE tblname DISCARD TABLESPACE Copy the .exp and .ibd files from the snapshot to the new instance: cp /tmp/snapshot…/lib/mysql/orig_old Make sure that the files are owned by mysql: chown mysql:mysql /var/lib/mysql/orig_old/* For each table, run: ALTER TABLE…
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
May 22, 2013 By 22 Comments
… 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 SELECT ld…) group by ld.label_id, cai.inventory_id All 4 tables in the query are locked!
Post: Percona XtraBackup 2.1.0 'release candidate' for MySQL available for download
May 7, 2013 By Leave a Comment
…copied into backup directory during the backup stage. During the copy back stage (restore) it is copied back to…to produce 5.6-style metadata files, that can be imported by ALTER TABLE IMPORT TABLESPACE on MySQL…
Post: Percona XtraBackup 2.0.7 for MySQL available for download
May 6, 2013 By 1 Comment
…corresponding to tables that are missing in the incremental backup directory. Bug fixed #856400. Percona XtraBackup would leave stale xtrabackup_tmp…copying thread which could cause XtraBackup to crash. Bug fixed #1166888. Package dependency has been changed from abstract mysql to real /usr/bin/mysql…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
…prior to MySQL 5.5.3 Metadata Locking behavior prior to MySQL 5.5.3 Prior to MySQL 5.5.3 a statement that opened a table…tables | 0.000045 | | System lock | 0.000006 | | setup | 0.000016 | | creating table | 0.168283 | | After create | 0.000061 | | copy to tmp table | 0.165808 | | rename result table…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
… sample database to demonstrate: mysql> SET profiling=1; mysql> pager cat > /dev/null mysql> SELECT * FROM… Calls | R/Call | +———————-+———-+——-+——-+————–+ | removing tmp table | 0.095135 | 55.10 | 3 | 0.0317116667 | | Copying to tmp table | 0.046175 | 26.74 |…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…: 0 Warnings: 0 mysql> SHOW PROFILE; +——————————+————+ | Status | Duration | +——————————+————+ . . . | copy to tmp table | 184.694432 | . . . +——————————+————+ 18 rows in set (0.00 sec) mysql> SET expand_fast_index…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 10 Comments
… going to recover only the “salaries” table: Discard the tablespace of the salaries table: mysql> set FOREIGN_KEY_CHECKS=0; mysql> ALTER TABLE salaries DISCARD TABLESPACE; Copy the salaries.ibd files from the backup to the database data directory: # cp /tmp/2012-01…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… to other things; for example, when I’m examining the status values for Created_tmp_tables and Created_disk_tmp_tables, I’ll paste in mysql> show global variables like ‘%table_size%’; +———————+———–+ | Variable_name | Value | +———————+———–+ | max_heap_table_size | 268435456 | | tmp_table… a copy of the table, make the proposed modifications to it, and show the difference afterwards. Or we might have a test server to…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… to select all text on the page and then CTRL-C to copy it. Paste this into a text file on shard1 called “/tmp… table-definition-cache=16384 table-open-cache=128 thread-cache-size=32 thread-stack=256K tmp-table-size=64M transaction-isolation=READ-COMMITTED user=mysql wait-timeout=86400 To…
