Archive for the ‘DBA’ Category

MyISAM key cache stats with information_schema.KEY_CACHES

Following on from a post last week on INNODB_BUFFER_PAGE queries I thought I’d look at the equivalent for the MyISAM key cache. The information_schema.KEY_CACHES is MariaDB only at the moment ?View Code MYSQLSELECT KEY_CACHE_NAME, FULL_SIZE / 1024 / 1024 AS key_buffer_mb, USED_BLOCKS * BLOCK_SIZE / 1024 / 1024 AS used_mb, UNUSED_BLOCKS * BLOCK_SIZE / 1024 / […]

innodb_buffer_page queries

If you want to get some high level statistics on the buffer pool in MySQL / MariaDB you can use the INNODB_BUFFER_POOL_STATS table in the information_schema database. If you need a little more detail then the INNODB_BUFFER_PAGE table is a good place to start. This view contains details about each page in the buffer pool. […]

Missing InnoDB information_schema Views in MariaDB

While working on a MariaDB 10.0.14 instance today I noticed the INNODB_% tables were missing from information_schema. I could tell the InnoDB plugin was loaded. ?View Code MYSQLSHOW PLUGINS; Name Status Type Library License InnoDB ACTIVE STORAGE ENGINE ha_innodb.so GPL Checking the documentation I could see that these views are supplied by the innodb plugin itself. […]

Spotting missing indexes for MariaDB & MySQL

Here’s a query I use for MySQL / MariaDB to spot any columns that might need indexing.  It uses a bunch of information_schema views to flag any columns that end with the characters “id” that are not indexed. This tests that the column is located at the head of the index through the ORDINAL_POSITION clause. So if […]

Moving an InnoDB Database with FLUSH TABLES .. FOR EXPORT

If we wanted to move a large InnoDB database our options were very limited. Essentially we had to mysqldump the single database or move the entire tablespace. I did have an idea for moving a single InnoDB database by copying files but only ever tried it out with TokuDB. This method worked but seemed to […]