…to check which sessions have created which temporary tables (both in memory and not): mysql> select * from information_schema.global_temporary_tables…in set (0.00 sec) Innodb Memory Usage Finally it is often helpful to check how much memory Innodb has allocated. In…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in… new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
…to other sessions. How to troubleshoot gap locks? Is possible to detect those gap locks using SHOW ENGINE INNODB…MySQL thread id 3, OS thread handle 0x7f84a78ba700, query id 163 localhost msandbox TABLE LOCK table…
Post: How to change innodb_log_file_size safely
July 9, 2011 By 14 Comments
…to change MySQL’s innodb_log_file_size parameter (see How to calculate a good InnoDB log file size), you can’t just change the parameter in…and so on. Check the error log to ensure there was …in file… That really means that InnoDB hasn’t started, and MySQL is trying to access a table with the InnoDB…
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 8 Comments
…to include others as well. Here’s how you can use our new monitoring plugins to check…in the processlist. The pmp-check-mysql-processlist plugin checks SHOW FULL PROCESSLIST for patterns that indicate similar problems to those discussed within InnoDB…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
…to CHECK your MyISAM table you use for recovery after MySQL crashes to make sure indexes are not corrupted. So we looked at how to get your data back from simple Innodb Table Corruption. In…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 3 Comments
…tables and indexes (with their IDs) to the error log. mysql (employees) > CREATE TABLE innodb_table_monitor (id int) ENGINE=InnoDB; TABLE…to remove the innodb_table_monitor. C) Check the size on disk of every indes This is very dependent of the table…
Post: Aligning IO on a hard disk RAID – the Theory
June 9, 2011 By 15 Comments
…InnoDB storage: first – in Percona Server you can now customize page size and it may be good idea to check that page size… size and a misalignment in partition table – partition…size and how many stripe elements. It’s easy to check if you’re aligned – run a benchmark with an offset and compare to…
Post: How much space does empty Innodb table take ?
December 16, 2008 By 7 Comments
…How much space will Innodb take: mysql> create table test_innodb(a int, b int) engine=innodb; Query OK, 0 rows affected (0.30 sec) Check…in main tablespace for Innodb data dictionary. But that one is pretty small. This is the good reason to avoid having very small Innodb tables…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 116 Comments
…in MySQL Server straight after installation, assuming it was installed with default settings. I’m surprised how many people fail to…innodb_buffer_pool_size This is very important variable to tune if you’re using Innodb tables. Innodb tables are much more sensitive to buffer size compared to…
