• Views: better performance with condition pushdown

    May 20, 2010

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

  • A MyISAM backup is blocking as read-only, including mysqldump backup

    May 18, 2010

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

  • Replication configuration checklist

    May 18, 2010

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

  • EXPLAIN: missing db info

    May 11, 2010

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

  • Discovery of the day: GROUP BY ... DESC

    May 4, 2010

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

  • Reducing locks by narrowing primary key

    May 4, 2010

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

 
Powered by Wordpress and MySQL. Theme by openark.org