June 19, 2013

Post: The small improvements of MySQL 5.6: Duplicate Index Detection

May 31, 2013 By Leave a Comment

…, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected… rows affected (0.34 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE test ADD INDEX (col2, col3); Query OK, 0 rows affected… that use MySQL, for which we at Percona tend to find redundant indexes. And second, because in some cases, we may…

Filed Under: Insight for DBAs, MySQL, On Ground Training, Percona Toolkit, Training Tagged With: , , , , , , , , ,

Post: Debugging problems with row based replication

May 6, 2010 By 12 Comments

….  Since row-based logs only contain information about changed rows, a replication slave which does not find a row referenced in a row-based log…, 1 row affected (0.02 sec) Insert a single “seed” row into the table: INSERT INTO repl.t1 VALUES (); Query OK, 1 row affected (0… rows affected (0.01 sec) Records: 2  Duplicates: 0  Warnings: 0 … insert into repl.t1 select NULL from t1; Query OK, 32768 rows affected…

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

Post: How to find MySQL queries worth optimizing ?

September 11, 2012 By 6 Comments

… looking at pt-query-digest report it is easy to find slow queries or queries which cause the large portion of… # Query_time: 9.031233 Lock_time: 0.000086 Rows_sent: 0 Rows_examined: 10000000 Rows_affected: 0 Rows_read: 0 # Bytes_sent: 213 Tmp_tables… # Query_time: 5.391612 Lock_time: 0.000121 Rows_sent: 2 Rows_examined: 10000000 Rows_affected: 0 Rows_read: 2 # Bytes_sent: 75 Tmp_tables…

Filed Under: Insight for DBAs, MySQL

Post: How to recover deleted rows from an InnoDB Tablespace

February 20, 2012 By 4 Comments

… will find and extract all the pages of the tablespace to an output directory. We only need to specify the row format… the salaries.recovery you can find lot of deleted rows, not only rows deleted accidentally. You should manually find the data you need to…’ (emp_no, from_date, salary, to_date); Query OK, 3 rows affected (0.01 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings…

Filed Under: Insight for DBAs, MySQL

Post: Innodb row size limitation

April 7, 2011 By 12 Comments

… problem, but due to it’s nature, it may only affect you after you already have a system running in production… Â  ‘First stylesheet’, ->   ‘First terms’ -> ); Query OK, 1 row affected (0.03 sec) All goes ok. Now you begin using… BLOB instead of a TEXT column for storage. You can find more information about this functions here. Split the table in…

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

Post: Quickly finding unused indexes (and estimating their size)

December 5, 2012 By 2 Comments

…_NAMES: last_name 1 row in set (0.03 sec) Now I need a way to find the set of indexes in….02 | | sakila | staff | idx_fk_store_id | 0.02 | +————–+—————+—————————–+—————+ 22 rows in set (0.02 sec) Recovering filesystem space Now astute… and tips about doing a long blocking schema change without affecting your production environment apply here and is out of scope…

Filed Under: Insight for DBAs, MySQL, Percona Software

Post: Find and remove duplicate indexes

June 20, 2012 By 9 Comments

… explain the different types of duplicate indexes and how to find and remove them. Duplicate keys on the same column This… add index key_for_first_name(name); Query OK, 0 rows affected (0.01 sec) mysql> alter table t add index key…, its name is pt-duplicate-key-checker and it can find the three different types of keys explained before. Lets try…

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

Post: Recovering Innodb table Corruption

July 4, 2008 By 22 Comments

…; Query OK, 0 rows affected (0.03 sec) mysql> insert into test2 select * from test; Query OK, 229376 rows affected (0.91 sec) Records… OK, 573140 rows affected (7.79 sec) Records: 573140 Duplicates: 0 Warnings: 0 So we tried to skip 30 rows and it was too little while skipping 80 rows was OK. Again using binary search you can find out how many rows…

Filed Under: Insight for DBAs Tagged With:

Post: AUTO_INCREMENT and MERGE TABLES

September 11, 2008 By Leave a Comment

… was 1 it finds out what other subtable had value 2 and assigns the value 3 to the new row. Let us…> flush tables; Query OK, 0 rows affected (0.01 sec) mysql> truncate table a2; Query OK, 0 rows affected (0.00 sec) mysql> insert into am values(null); Query OK, 1 row affected (0.00 sec) mysql…

Filed Under: Insight for DBAs Tagged With: ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…_innodb ADD FULLTEXT KEY (full_name, details); Query OK, 0 rows affected, 1 warning (39.73 sec) Records: 0 Duplicates: 0 Warnings… | 0 | +—————————+———+ 10 rows in set (0.00 sec) mysql> delete from dir_test_innodb LIMIT 200000; Query OK, 200000 rows affected (8.65… of the other I_S tables, I do find that the number of rows in I_S.innodb_ft_index_table is…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,
« Older Posts