… using ALTER TABLE fast index creation (merge sort). For the MyISAM tests I used a 10GB key buffer. I used ALTER TABLE DISABLE… sort via ALTER TABLE ENABLE KEYS. my.cnf [mysqld] datadir=/mnt/mysql56/data basedir=/usr/local/mysql socket=/var/lib/mysql/mysql.sock user=justin innodb_buffer_pool_size=64G innodb_log_file_size=4G innodb_file_per_table…
Post: Percona XtraBackup 2.1.0 'release candidate' for MySQL available for download
May 7, 2013 By Leave a Comment
… MySQL 5.6. This option can be used to produce 5.6-style metadata files, that can be imported by ALTER TABLE IMPORT TABLESPACE on MySQL and Percona Server 5.6 as described in Exporting and Importing Tables guide. Bugs Fixed… not removed when incremental backups has been merged. Fixed by removing files corresponding to tables that are missing in the incremental backup…
Post: Percona XtraBackup 2.0.7 for MySQL available for download
May 6, 2013 By 1 Comment
… MySQL 5.6. This option can be used to produce 5.6-style metadata files, that can be imported by ALTER TABLE IMPORT TABLESPACE on MySQL and Percona Server 5.6 as described in Exporting and Importing Tables guide. Bugs Fixed… not removed when incremental backups has been merged. Fixed by removing files corresponding to tables that are missing in the incremental backup…
Post: AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
… data to: mysql> truncate table a2; ERROR 1105 (HY000): MyISAM table ‘a2′ is in use (most likely by a MERGE table). Try FLUSH TABLES. mysql> flush tables; Query OK, 0 rows affected (0.01 sec) mysql> truncate table…
Post: Merge Tables Gotcha
May 9, 2007 By 4 Comments
… 1 in merge table however select querying by primary index returns only one. Following EXPLAIN shows why this is the case – MySQL knows… merge table so we could not drop PRIMARY KEY so I thought I should try using key on the given column instead: mysql… NULL for the merge table: “create table tx (id int unsigned, key(id)) type=merge union(t1,t2)” If you create table this way it…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 10 Comments
… on the backup with innodb_fast_shutdown=0 and therefore merging all the insert buffers. # innobackupex –defaults-file=/etc/my.cnf…/mysql/data/employees/ Import the new tablespace: mysql> set FOREIGN_KEY_CHECKS=0; mysql> ALTER TABLE salaries IMPORT TABLESPACE; mysql> set FOREIGN_KEY_CHECKS=1; mysql…
Post: Is there room for more MySQL IO Optimization?
January 3, 2013 By 11 Comments
… cached — system files, binary log, FRM files, MySQL MyISAM system tables etc. Starting MySQL 5.5 MySQL uses asynchronous IO which should allow it… able to issue many outstanding requests which when can be merged on OS level or RAID controller so we should expect… reflect natural state of the table as it would be in the production over time: mysql> show table status like “post_channel” \G…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
…. I used 10mil row table which would look as following in terms of data and index size: mysql> show table status like “sbtest… not that bad with sequential IO, which is what file merge process uses. This is important illustration as in many cases…: If you’re having large tables and need to run ALTER TABLE which rebuilds the table or OPTIMIZE TABLE do not forget to enable…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…MySQL 5.5.30 and MySQL 5.6.10 to the different tables and ran the query: mysql [localhost] … | | OPTIMIZER_SWITCH | index_merge=on,index_merge_union=on,inde | index_merge=on,index_merge_union=on,inde | …
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
…MySQL 5.0 and later changed this situation with the introduction of the “index merge” optimizer plan. The basic idea behind index merge…*************************** 1. row *************************** id: 1 select_type: SIMPLE table: users type: index_merge possible_keys: PRIMARY,parent_id,status,user_…
