Archive for the ‘DBA’ Category

Kibana splits on hostname

If you’re playing with Kibana and you notice any Pie charts splitting values incorrectly, i.e. on a hostname with hyphen characters, then here’s the fix you need to apply. It’s actually something elasticsearch does… curl -XPUT http://localhost:9200/_template/syslog -d ‘ { “template”: “*syslog*”, “settings” : { “number_of_shards” : 1 }, “mappings” : { “file” : { “properties” […]

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