Posts Tagged ‘InnoDB’

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

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

Identify Cross Database Foreign Keys

I’ve blogged before about cross-database foreign keys and what I think of them. I had a developer wanting to check for such references between two databases today. Here’s what I came up with to do this… ?View Code MYSQLSELECT t.NAME AS TABLE_NAME, i.NAME AS INDEX_NAME, f.* FROM information_schema.`INNODB_SYS_FOREIGN` f INNER JOIN information_schema.`INNODB_SYS_INDEXES` i ON i.`NAME` […]

MySQL Storage engine benchmarking

Here’s a stored procedure I use to perform some simple benchmarking of inserts for MySQL. It takes three parameters; p_table_type which should be set to the storage engine you wish to benchmark i.e. ‘MyISAM’, ‘InnoDB’, p_inserts; set this to the number of inserts to perform. p_autocommit; set the autocommit variable (relevant to InnoDB only) to […]