June 18, 2013

Post: Implementing SchemaSpy in your MySQL environment

June 4, 2013 By 2 Comments

…of a schema in a database and generates a visual representation of it in a browser-displayable format. It lets you click through the hierarchy of database…MySQL, but note that SchemaSpy works for pretty much all…

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

Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency

May 22, 2013 By Leave a Comment

…. In my previous blog post I compared MySQL 5.5.30 to MySQL 5…database software is restarted after the hot test. All OS caches are dropped at this time as well. These set of…index is improving performance of InnoDB significantly during the hot run, as hash indexes are faster than a b-tree index…

Filed Under: Benchmarks, MySQL Tagged With: , , , , , , ,

Post: Percona Server for MySQL 5.5.30-30.2 now available

April 10, 2013 By 10 Comments

…in this blogpost. (Ignacio Nin) This release of Percona Server for MySQL has fixed a number of performance bugs. (Alexey Kopytov) Drop…index…MySQL would re-create the test database when using rpm on server upgrade, even if the database…

Filed Under: MySQL

Post: How to recover table structure from InnoDB dictionary

April 22, 2013 By 4 Comments

…database_name/table_name e.g. sakila/actor. `ID` is a table identifier. We will need the table id to find indexes of…index gets created it will have minimal `ID` among the indexes of the table. mysql> select * from SYS_INDEXES…

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

Post: Percona Server 5.5.30-30.2 rerelease fixes non-restart issue

April 24, 2013 By Leave a Comment

…in this blogpost. (Ignacio Nin) This release of Percona Server for MySQL has fixed a number of performance bugs. (Alexey Kopytov) Drop… secondary index with the…MySQL would re-create the test database when using rpm on server upgrade, even if the database…-data options, all secondary key definitions…

Filed Under: MySQL, Percona Software

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

December 5, 2012 By 2 Comments

…mysql> drop view if exists droppable_indexes; mysql> create view droppable_indexes as select all_indexes.table_schema as table_schema, all_indexes.table_name as table_name, all_indexes.index…

Filed Under: Insight for DBAs, MySQL, Percona Software

Post: Find unused indexes

June 30, 2012 By Leave a Comment

… picture of our users are doing in the database. To accomplish our task of finding unused indexes we’re going to use INFORMATION_SCHEMA.INDEX_STATISTICS… is happening in the server. To generate some load on the database I’m going to use the benchmark software tpcc-mysql. After… performance. You won’t need to drop all such indexes but it gives you an idea of which of them you should evaluate. Good tools…

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

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

February 26, 2013 By 1 Comment

… tablespace files in our database directory: -rw-rw—-. 1 mysql mysql 8632 Feb 20 15:54 dir_test_innodb.frm -rw-rw—-. 1 mysql mysql 213909504… drop a FT index, the its corresponding FTS_*_DOC_ID.ibd file will also be removed. HOWEVER, even if you drop all of the FT indexes for a given table, you’re still left with all of the other FTS_*.ibd files…

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

…in the database, and I used double quotes to group the search terms as a single phrase: With 5.5: mysql…dropped/rebuilt the InnoDB index, and tried again. mysql: SELECT id, full_name, MATCH(full_name, details) AGAINST (‘”Thomas B Smith”‘ IN…

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

Post: The Optimization That (Often) Isn't: Index Merge Intersection

December 14, 2012 By 6 Comments

…optimizer_switch=”index_merge_intersection=off”; This is the database equivalent of kicking the door in … satisfied in some other way. After all, if an index isn’t there, MySQL can…dropped several orders of magnitude and CPU utilization on the machine had dropped by about 2/3. Not a bad outcome at all…

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