Good Practice / Bad Practice: CREATE TABLE and the Storage Engine

When you write your create table statements, always make sure that you make them non-ambiguous. That way even though other servers might have different configurations, you make sure your table will be created in the same way. Imagine for instance you are developing an application on a development server, nicely storing all the scripts you […]

How many files does InnoDB have open?

If you use┬áinnodb_file_per_table = 1 and┬áinnodb_open_files = X (whatever amount is suitable for your server) there’s no way internal to MySQL for finding out how many IBD files InnoDB actually has open. Neither SHOW GLOBAL STATUS LIKE ‘innodb%’ nor SHOW ENGINE INNODB STATUS provide this information. Many sites do have a growing number of tables, […]

What to do with the Falcon engine?

Keep it. Make sure it gets correctly positioned in the coming months. It appears that with the Oracle acquisition, the reason-to-exist for Falcon is regarded as gone (a non-Oracle-owned InnoDB replacement), previously seen as a strategic imperative – much delayed though. But look, each engine has unique architectural aspects and thus a niche where it […]

InnoDB lock timeout before query execution

I found this yesterday while tracking down a locking issue for a client. They had a connection time out on a lock, but before it times out, SHOW PROCESSLIST had the status ‘statistics’ so it wasn’t actually executing the query yet. So, what was it doing and why did it time out there? The answer […]