June 18, 2013

Post: Implementing SchemaSpy in your MySQL environment

June 4, 2013 By 2 Comments

…schema name information. Viewing SchemaSpy output A completed Relationships view of these three tables should look like this: Final Thoughts While I find…

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

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

December 5, 2012 By 2 Comments

…of it into our schema with one command: mysql> create table index_analysis.used_indexes select * from information_schema.index…

Filed Under: Insight for DBAs, MySQL, Percona Software

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

March 11, 2013 By 7 Comments

…indexes have been pushed out of the buffer pool. mysql> select * from information_schema.innodb_buffer_pool_stats\G *************************** 1. row *************************** POOL_ID: 0 POOL_SIZE…

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

Post: Researching your MySQL table sizes

March 17, 2008 By 28 Comments

…few more aspects of data sizes so here it goes: Find total number of tables, rows, total data in index size for given MySQL Instance SELECT count… total_size, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES GROUP BY table_schema ORDER BY sum(data_length+index_length…

Filed Under: Insight for DBAs Tagged With: , ,

Post: Find unused indexes

June 30, 2012 By Leave a Comment

… 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 userstat… in account that depending on the size of the log the server would need lot of process time and cpu power so I…

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

Post: Finding out largest tables on MySQL Server

February 4, 2008 By 27 Comments

…size, ROUND(index_length / data_length, 2) idxfrac FROM information_schema.TABLES ORDER BY data_length + index_length DESC LIMIT 10; +————————————-+——–+——–+——–+————+———+ | concat(table_schema…

Filed Under: Insight for DBAs Tagged With:

Post: Troubleshooting MySQL Memory Usage

March 21, 2012 By 5 Comments

…size variable which allows you to limit size of…information_schema to see how much memory is being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema…

Filed Under: Insight for DBAs, MySQL

Post: How Percona does a MySQL Performance Audit

November 24, 2008 By 17 Comments

…find the biggest tables and so forth. If the server is heavily loaded or has a lot of data, touching the INFORMATION_SCHEMA…mysql.* tables, running mk-duplicate-key-checker to find redundant indexes…

Filed Under: Insight for Developers Tagged With:

Post: How to recover deleted rows from an InnoDB Tablespace

February 20, 2012 By 4 Comments

…of them: A) INNODB_SYS_INDEXES Percona Server has some extra tables in INFORMATION_SCHEMA that can help us to find the different indexes and types. mysql (information_schema…table_monitor. C) Check the size on disk of every indes This is very dependent of the table schema, but normally the primary …

Filed Under: Insight for DBAs, MySQL

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

February 26, 2013 By 1 Comment

… than the size of my DIR data. So, with all of that out of the way…mysql> set global innodb_ft_aux_table=’test/dir_test_innodb’; mysql> set global innodb_optimize_fulltext_only=1; mysql> select * from information_schema…of the other I_S tables, I do find that the number of rows in I_S.innodb_ft_index…

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