June 19, 2013

Post: Implementing SchemaSpy in your MySQL environment

June 4, 2013 By 2 Comments

…a schema in a database …database to see which tables are referenced the most, and which are candidates for dropping…MySQL driver=com.mysql.jdbc.Driver connectionSpec=jdbc:mysql://127.0.0.1:5610/schemaspy driverPath=/usr/share/java/mysql-connector-java.jar Example Schema…port or schema name information….

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

Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark

March 11, 2013 By 7 Comments

…mysql> select * from information_schema.innodb_buffer_pool_stats\G *************************** 1. row *************************** POOL_ID: 0 POOL_SIZE: 8191 FREE_BUFFERS: 1024 DATABASE_PAGES: 7162 OLD_DATABASE…d_year, s_city, p_brand; And the schema: DROP TABLE IF EXISTS customer; CREATE TABLE IF NOT…

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

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

December 5, 2012 By 2 Comments

… their database size on disk quickly without a lot of messy schema redesign and application recoding.  They didn’t want to drop any… use that information: mysql> drop view if exists droppable_indexes; mysql> create view droppable_indexes as select all_indexes.table_schema as table_schema, all_indexes…

Filed Under: Insight for DBAs, MySQL, Percona Software

Post: Find unused indexes

June 30, 2012 By Leave a Comment

… database. To accomplish our task of finding unused indexes we’re going to use INFORMATION_SCHEMA.INDEX_STATISTICS but first we should enable User Statistics: mysql> SET GLOBAL…– type:non-unique ALTER TABLE `tpcc`.`orders` DROP KEY `idx_orders`; — type:non-unique ALTER…

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

Post: How Percona does a MySQL Performance Audit

November 24, 2008 By 17 Comments

… see what’s up with the database. The first step is to find out what MySQL instances are on the machine. It… server is not heavily loaded, I may even do some INFORMATION_SCHEMA queries to help me find the biggest tables and so… whose .frm files exist but have been dropped from InnoDB, for example. (The INFORMATION_SCHEMA query will surely trigger errors about this.) There…

Filed Under: Insight for Developers Tagged With:

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

February 26, 2013 By 1 Comment

…database directory: -rw-rw—-. 1 mysql mysql 8632 Feb 20 15:54 dir_test_innodb.frm -rw-rw—-. 1 mysql mysql…mysql> set global innodb_ft_aux_table=’test/dir_test_innodb’; mysql> set global innodb_optimize_fulltext_only=1; mysql> select * from information_schema…

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

Post: Connecting orphaned .ibd files

May 13, 2011 By 19 Comments

… methods. The first is create/drop the table many times until …user(you can see them in information_scheme database in Percona Server though). …mysql> select * from `INNODB_SYS_TABLES` where `SCHEMA` = ‘sakila’ AND `NAME` = ‘actor’G *************************** 1. row *************************** SCHEMA…

Filed Under: MySQL

Post: MySQL extensions for hosting

April 24, 2008 By 11 Comments

… at all and could be candidates for dropping. All the patch features will surely be …. With simple MySQL SHOW command it gives you the basic statistics on the database activity broken … users to see their own statistics only information_schema support to allow SELECT query transformations, joins …

Filed Under: Percona Software Tagged With:

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

March 4, 2013 By 7 Comments

…we’re trying to emulate: mysql: SHOW CREATE TABLE information_schema.innodb_ft_default_stopword\G *************************** 1….database, and I used double quotes to group the search terms as a single phrase: With 5.5: mysql… to 1, dropped/rebuilt the InnoDB index, and tried again. mysql: SELECT id,…

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

Comment: Duplicate indexes and redundant indexes

January 19, 2012 By 38 Comments

…information_schema.table_constraints a INNER JOIN information_schema.key_column_usage b ON a.constraint_name = b.constraint_name AND a.table_schema…

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