Grok expression for MariaDB Audit Log

Here’s a grok expression for the MariaDB Audit Plugin Log. This has only been tested against CONNECT/DISCONNECT/FAILED_CONNECT events and will likely need modification for other event types.

^%{YEAR:year}%{MONTHNUM:month}%{MONTHDAY:day} %{TIME:time},%{GREEDYDATA:host},%{GREEDYDATA:username},%{GREEDYDATA:client_hostname},%{INT:connection_id},%{INT:query_id},%{GREEDYDATA:operation},%{GREEDYDATA:schema},%{GREEDYDATA:object},%{INT:return_code}

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" : {
                                                	"host" : {
                                                              	"type" : "string",
                                                            	"index" : "not_analyzed"
                                                     }
                                                }
                                }
                }
}
'

This will instruct elasticsearch not to break the fieldname “host” into tokens for any index with “syslog” in the name. Note this will only apply to new indexes. You’ll need to delete all the current indexes and re-import your data if you need the backlog corrected too.


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

SELECT 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 / 1024 AS  unused_mb,
	  100 - ((`READS` / READ_REQUESTS) * 100) AS read_cache_hit_rate,
	  DIRTY_BLOCKS / (USED_BLOCKS + UNUSED_BLOCKS) AS dirty_percentage,
	  READ_REQUESTS / (`READ_REQUESTS` + WRITE_REQUESTS) AS read_write_ratio
FROM information_schema.`KEY_CACHES`;

Most of the data returned should be self explanatory. The read_write_ratio indicates the number of reads versus writes, for example 1.0 indicates 100% reads whole 0.75 would indicate 75% read and 25% writes.


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. So we can use this to get database level details all the way down to individual indexes. Here’s a few queries to get you started…

Buffer pool consumption by database

SELECT bp.POOL_ID, 
		NULLIF(SUBSTRING(bp.TABLE_NAME, 1, LOCATE(".", bp.TABLE_NAME) - 1), '') AS db_name, 
		(COUNT(*) * 16) / 1024 / 1024 AS buffer_pool_consumption_gb
FROM `INNODB_BUFFER_PAGE` bp
GROUP BY bp.POOL_ID, db_name
ORDER BY buffer_pool_consumption_gb DESC;

The ‘NULL’ database here will consist of various INNODB internal structures, for example the UNDO LOG. You can inspect these pages individually with..

SELECT *
FROM `INNODB_BUFFER_PAGE` bp
WHERE bp.TABLE_NAME IS NULL;

Buffer pool consumption by database/table & page type

SELECT bp.POOL_ID, NULLIF(SUBSTRING(bp.TABLE_NAME, 1, LOCATE(".", bp.TABLE_NAME) - 1), '') AS db_name, bp.PAGE_TYPE, bp.TABLE_NAME, (COUNT(*) * 16) / 1024 / 1024 AS buffer_pool_consumption_gb
FROM `INNODB_BUFFER_PAGE` bp
GROUP BY bp.POOL_ID, db_name, bp.PAGE_TYPE, bp.TABLE_NAME
ORDER BY bp.POOL_ID, db_name, bp.PAGE_TYPE, bp.TABLE_NAME, buffer_pool_consumption_gb DESC;

Buffer pool consumption by index

SELECT bp.POOL_ID, 
		NULLIF(SUBSTRING(bp.TABLE_NAME, 1, LOCATE(".", bp.TABLE_NAME) - 1), '') AS db_name, 
		bp.TABLE_NAME, 
		bp.INDEX_NAME, (COUNT(*) * 16) / 1024 / 1024 AS buffer_pool_consumption_gb
FROM `INNODB_BUFFER_PAGE` bp
GROUP BY bp.POOL_ID, db_name, bp.TABLE_NAME, bp.INDEX_NAME
ORDER BY bp.POOL_ID, db_name, bp.PAGE_TYPE, buffer_pool_consumption_gb DESC;

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.

SHOW 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. So the solution should be in the form of an INSTALL PLUGIN statement. I executed the following query on another MariaDB instance, of the same version, that had the INNODB_% table in info schema…

SELECT CONCAT("INSTALL PLUGIN ", PLUGIN_NAME, " SONAME 'ha_innodb.so';") 
FROM information_schema.PLUGINS
WHERE PLUGIN_NAME LIKE 'INNODB_%';

This generated the following statements. I executed these on the other instance to create the INNODB tables…

INSTALL PLUGIN INNODB_TRX SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_LOCKS SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_LOCK_WAITS SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_CMP SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_CMP_RESET SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_CMPMEM SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_CMPMEM_RESET SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_CMP_PER_INDEX SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_CMP_PER_INDEX_RESET SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_BUFFER_PAGE SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_BUFFER_PAGE_LRU SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_BUFFER_POOL_STATS SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_METRICS SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_FT_DEFAULT_STOPWORD SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_FT_DELETED SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_FT_BEING_DELETED SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_FT_CONFIG SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_FT_INDEX_CACHE SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_FT_INDEX_TABLE SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_SYS_TABLES SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_SYS_TABLESTATS SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_SYS_INDEXES SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_SYS_COLUMNS SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_SYS_FIELDS SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_SYS_FOREIGN SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_SYS_FOREIGN_COLS SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_SYS_TABLESPACES SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_SYS_DATAFILES SONAME 'ha_innodb.so';
INSTALL PLUGIN INNODB_CHANGED_PAGES SONAME 'ha_innodb.so';