June 19, 2013

Post: 5 Percona Toolkit Tools for MySQL That Could Save Your Day: April 3 Webinar

March 22, 2013 By Leave a Comment

…recording and the slides after the webinar. More info on “5 Percona Toolkit Tools for MySQL…MySQL database administration challenges, such as: Selecting…last only a few seconds How to run ALTER TABLE on your largest tables without downtime REGISTER NOW for this MySQL…

Filed Under: Events and Announcements, MySQL, Percona Toolkit Tagged With: , , , , , , ,

Post: How to recover table structure from InnoDB dictionary

April 22, 2013 By 4 Comments

…two things: media with records(ibdata1, *.ibd, disk image, …(255) DEFAULT NULL, `SPACE` int(10) unsigned DEFAULT NULL, PRIMARY KEY …indexes of the table. mysql> select * from SYS_TABLES WHERE NAME=’sakila/actor’; …| 3 | 738 | 3 | | 741 | 1680 | idx_actor_last_name | 1 | 0 | 738 | 4 …

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

Comment: MySQL Partitioning - can save you or kill you

April 30, 2013 By 45 Comments

…I have 10 years of data, but usually queries hit the last week …anywhere in any ‘free’ variant of MySQL. PARTITIONs are scanned one at a… argue that single-row queries (SELECT, INSERT, DELETE, UPDATE) are similar…drill down the BTree, (3) remove one record. PARTITIONing makes very little difference. * …

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

Post: Connecting orphaned .ibd files

May 13, 2011 By 19 Comments

…_NAME` varchar(255) default NULL, `SPACE` int(10) unsigned default NULL, PRIMARY KEY (`NAME`) ) … to 15 in all actor’s indexes: mysql> select * from INNODB_SYS_INDEXES WHERE TABLE_ID = 13G…_actor_last_name (last_name) )ENGINE=InnoDB DEFAULT CHARSET=utf8; This command will create respective records …

Filed Under: MySQL

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

February 26, 2013 By 1 Comment

…Records: 245051 Duplicates: 0 Warnings: 0 mysql> select * from information_schema.innodb_ft_config; +—————————+———+ | KEY | VALUE | +—————————+———+ | optimize_checkpoint_limit | 180 | | synced_doc_id | 1028261 | | last…

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

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

March 4, 2013 By 7 Comments

…, run against InnoDB on 5.6.10: mysql: SELECT id, title, MATCH(title, body) AGAINST (‘arizona business records‘ IN NATURAL LANGUAGE MODE) AS score… your current MyISAM FTS, the results may surprise you. That last point bears particular emphasis, as it also illustrates an important…

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

Post: Percona XtraDB Cluster: Failure Scenarios with only 2 nodes

July 25, 2012 By 6 Comments

…also add records: percona2 mysql> …mysql> select * from percona; +—-+—————+——–+ | id | inserted_from | name | +—-+—————+——–+ | 2 | percona1 | lefred | | 3 | percona2 | kenny | | 5 | percona1 | liz | | 9 | percona1 | ewen | | 10…total), act_id = 19, last_appl. = -1, …

Filed Under: MySQL, XtraDB Cluster

Post: Implications of Metadata Locking Changes in MySQL 5.5

February 1, 2013 By 9 Comments

…MySQL 5.5.3 Metadata Locking behavior prior to MySQL 5.5.3 Prior to MySQL…(0.57 sec) Records: 2 Duplicates: 0 Warnings: 0 session1 > select * from test … ALTER waits only at the last stages when its making changes …RENAME TABLE test_2 to test | | 10 | msandbox | localhost | test | Sleep | 37 | | NULL |…

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

Post: How to find MySQL queries worth optimizing ?

September 11, 2012 By 6 Comments

…mysql> explain select * from sbtest a,sbtest b where a.id=5 and b.id=a.k; +—-+————-+——-+——-+—————+———+———+——-+——+——-+ | id | select…

Filed Under: Insight for DBAs, MySQL

Post: AUTO_INCREMENT and MERGE TABLES

September 11, 2008 By Leave a Comment

…` ( `i` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`i`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`a1… sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into am values(null); Query OK, 1 row affected (0.00 sec) mysql> select * from…) Records: 9 Duplicates: 0 Warnings: 0 mysql> delete from a2 where i>2; Query OK, 9 rows affected (0.00 sec) mysql> select * from…

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