Combating “data drift” In my first post in this series, I described materialized views (MVs). An MV is essentially a cached result set at one point in time. The contents of the MV will become incorrect (out of sync) when the underlying data changes. This loss of synchronization is sometimes called drift. This is conceptually [...]
Flexviews – part 3 – improving query performance using materialized views
April 4, 2011 By 20 Comments
Debugging problems with row based replication
May 6, 2010 By 12 Comments
MySQL 5.1 introduces row based binary logging. In fact, the default binary logging format in GA versions of MySQL 5.1 is ‘MIXED’ STATEMENT*; The binlog_format variable can still be changed per sessions which means it is possible that some of your binary log entries will be written in a row-based fashion instead of the [...]
Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
Accidentally me and Baron played with InfoBright (see http://www.mysqlperformanceblog.com/2009/09/29/quick-comparison-of-myisam-infobright-and-monetdb/) this week. And following Baron’s example I also run the same load against MonetDB. Reading comments to Baron’s post I tied to load the same data to LucidDB, but I was not successful in this. I tried to analyze a bigger dataset and I took public [...]
Detailed review of Tokutek storage engine
April 28, 2009 By 21 Comments
(Note: Review was done as part of our consulting practice, but is totally independent and fully reflects our opinion) I had a chance to take look TokuDB (the name of the Tokutek storage engine), and run some benchmarks. Tuning of TokuDB is much easier than InnoDB, there only few parameters to change, and actually out-of-box [...]
AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
How would you expect AUTO_INCREMENT to work with MERGE tables ? Assuming INSERT_METHOD=LAST is used I would expect it to work same as in case insertion happens to the last table… which does not seems to be the case. Alternatively I would expect AUTO_INCREMENT to be based off the maximum value across all tables, respecting [...]
Can Innodb Read-Ahead reduce read performance ?
June 26, 2007 By 2 Comments
I ran into pretty interesting behavior today. We needed to dump and reload large database and we had pretty good IO subsystem so we started number of mysqldump processes in parallel. Unlike in other case when we did load in parallel, dump in parallel did not increase IO rate significantly and we could still see [...]
MySQL Slow query log in the table
May 31, 2007 By 1 Comment
As of MySQL 5.1 get MySQL slow query log logged in mysql.slow_log table instead of the file as you had in previous versions. We rarely would use this feature as it is incompatible with our slow query analyses patch and tools Fixing this is not trivial while staying 100% compatible to standard format as TIME [...]
MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
Few days ago I wrote an article about using UNION to implement loose index scan. First I should mention double IN also works same way so you do not have to use the union. So changing query to:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql> SELECT sql_no_cache name FROM people WHERE age in(18,19,20) AND zip IN (12345,12346, 12347); +----------------------------------+ | name | +----------------------------------+ | ed4481336eb9adca222fd404fa15658e | | 888ba838661aff00bbbce114a2a22423 | +----------------------------------+ 2 rows in set (0.00 sec) mysql> explain SELECT sql_no_cache name FROM people WHERE age in(18,19,20) AND zip IN (12345,12346, 12347); +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | people | range | age | age | 4 | NULL | 9 | Using where | +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) |
So as you see there are really different types of ranges in MySQL. IN range allows [...]
SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
Many people asked me to publish a walk through SHOW INNODB STATUS output, showing what you can learn from SHOW INNODB STATUS output and how to use this info to improve MySQL Performance. To start with basics SHOW INNODB STATUS is command which prints out a lot of internal Innodb performance counters, statistics, information about [...]
