June 19, 2013

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

…ALTER TABLE By temporarily dropping secondary indexes from the new table before copying the data, and then recreating them later, ALTER TABLE…(0.00 sec) mysql> ALTER TABLE t ADD COLUMN v VARCHAR(1); …> SHOW TABLE STATUS LIKE ‘t’\G *************************** 1. row *************************** Name: t …

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: Ultimate MySQL variable and status reference list

January 8, 2011 By 4 Comments

…column…name…table_definition_cacheblogpercona.commanual table_lock_wait_timeoutblogpercona.commanual Table_locks_immediateblogpercona.commanual Table_locks_waitedblogpercona.commanual table_open_cacheblogpercona.commanual table…

Filed Under: Insight for DBAs, MySQL

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

…column named “value”. OK, sounds easy enough: mysql: SHOW CREATE TABLE innodb_myisam_stopword\G *************************** 1. row *************************** Table: innodb_myisam_stopword Create Table: CREATE TABLE…ALTER TABLE…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: ANALYZE: MyISAM vs Innodb

September 3, 2008 By 9 Comments

…table as ANALYZE did not run yet. mysql> show index from antest; +——–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index | Column_name…

Filed Under: Insight for DBAs Tagged With: ,

Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs

February 23, 2012 By 3 Comments

… mysql> drop table salaries; — Ooops! Query OK, 0 rows affected (0.49 sec) mysql> alter table employees add column age … and skipping the DROP statement at position 336797160: mysql> show binary logs; +——————+———–+ | Log_name | File_size | +——————+———–+ … | …

Filed Under: Insight for DBAs

Post: Hijacking Innodb Foreign Keys

January 2, 2012 By 2 Comments

… create the table with same name again it will need to be able to satisfy foreign key conditions (have column child table is referencing… (0.00 sec) mysql> alter table parent rename parentx; Query OK, 0 rows affected (0.05 sec) mysql> drop table parentx; Query OK, 0…

Filed Under: Insight for DBAs, MySQL

Post: Knowing what pt-online-schema-change will do

November 1, 2012 By 1 Comment

… a simple table with a new column named “foo”.  Notice that the table has a PRIMARY key and another non-unique key named “guest_language…t_ins`; Dropping new table… DROP TABLE IF EXISTS `test`.`_t_new`; Dropped new table OK. Notice that the new, altered table created at the beginning is dropped.  …

Filed Under: Insight for DBAs, Percona Software Tagged With:

Post: Find unused indexes

June 30, 2012 By Leave a Comment

…ALTER TABLE `tpcc`.`order_line` DROP KEY `fkey_order_line_2`; — type:non-unique ALTER TABLE `tpcc`.`orders` DROP KEY `idx_orders`; — type:non-unique ALTER TABLE…

Filed Under: Insight for DBAs, Insight for Developers, MySQL, Percona Software

Post: High-Performance Click Analysis with MySQL

December 22, 2008 By 13 Comments

…’t save you from the downtime caused by ALTER TABLE — a typical reason to think about master-master…them. Did this gain us anything? We dropped one column, and we just moved those other values around…has a custom table, which is really constructed behind the scenes from the name-value tables in a …

Filed Under: Insight for Developers Tagged With: , , , ,

Post: Using MMM to ALTER huge tables

March 27, 2008 By 11 Comments

… to changes that really require table to be rebuilt – adding/dropping columns or indexes, changing data type,… columns named (bad e.g. “INSERT INTO table VALUES (id,value1,value2,..)”; good e.g. “INSERT INTO tables …at this stage. If you’re altering gigabyte size tables, this step may take some time….

Filed Under: Insight for DBAs Tagged With: , , ,
« Older Posts
Newer Posts »