June 19, 2013

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

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

Post: How to recover table structure from InnoDB dictionary

…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 …

Comment: MySQL Partitioning - can save you or kill you

…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. * …

Post: Connecting orphaned .ibd files

…_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

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

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

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

…, 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…

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

…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, …

Post: How to find MySQL queries worth optimizing ?

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

Post: AUTO_INCREMENT and MERGE TABLES

…` ( `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…