Justin's A workaround for the performance problems of TEMPTABLE views post on mysqlperformanceblog.com reminded me of a solution I once saw on a customer's site. The customer was using nested views structure, up to depth of some 8-9 views. There were a lot of aggregations along the way, and even the simplest query resulted with […]
Actually this is (almost) all I wanted to say. This is intentionally posted with all related keywords in title, in the hope that a related search on Google will result with this post on first page. I'm just still encountering companies who use MyISAM as their storage engine and are unaware that their nightly backup […]
This post lists the essential and optional settings for a replication environment. It does not explain how to create replicating slaves. See How To Setup Replication for that. However, not all configuration options are well understood, and their roles in varying architectures can change. Here are the settings for a basic Master/Slave(s) replication architecturee. Essential […]
I'm further developing a general log hook, which can stream queries from the general log. A particular direction I'm taking is to filter queries by their type of actions. For example, the tool (oak-hook-general-log) can be instructed to only stream out those queries which involve creation of a temporary table; or those which cause for […]
I happened on a query where, by mistake, an was written as And it took me by surprise to realize GROUP BY x DESC is a valid statement. I looked it up: yep! It's documented. In MySQL, GROUP BY results are sorted according to the group statement. You can override this by adding ORDER BY […]
In a period of two weeks, I had two cases with the exact same symptoms. Database users were experiencing low responsiveness. DBAs were seeing locks occurring on seemingly normal tables. In particular, looking at Innotop, it seemed that INSERTs were causing the locks. In both cases, tables were InnoDB. In both cases, there was a […]