In my post on estimating query completion time, I wrote about how I measured the performance on a … what this post is about. Let’s start with the MySQL query optimizer. The optimizer tries to choose the best join order… cost for a query, then choose the query plan that has the lowest cost. The unit of cost for the MySQL query optimizer is…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… is the complete InnoDB schema from one shard. The schema is duplicated 20 times (but … from 1988 to 2010′; mysql> use ontime1; Database changed mysql> show table status like ‘ontime_… from the ./run_query command, called pivot_results cd shard-query/ $ ./run_query < queries.sql | tee raw |./pivot…
Post: What is exec_time in binary logs?
January 31, 2011 By 2 Comments
…query‘s execution time, but it is not. In most cases, the exec_time will be similar to the query execution time…time will look believable. But on replicas, exec_time can be completely…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
…MySQL Query Cache is the same as Oracle Query Cache – meaning cache where execution plans are cached. MySQL Query… any case it is completely different. Query cache is great…QUERY CACHE command can be used for query cache defragmentation but it may block query cache for rather long time for large query…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
…Query over Hive is that it works with existing MySQL data sets and queries. Another advantage is that it works with all MySQL…completely denormalized schema (single table). In order to demonstrate the power of Shard-Query it is important to test complex queries…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
…covering index technique, did actually take more time to execute: mysql> SELECT role_id FROM cast_info …hold the whole database (data and indexes fit completely in memory). Also, I was testing, as … and technical opinion about the new features of MySQL query planner? Are you not yet familiar with …
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 8 Comments
…read less than 1000 rows and doing it completely in memory. The plan optimizer picked was … this change I could bring optimization, and full query execution time to less than 50ms. Low values, … the following explanation from Timour Katchaounov in MySQL mailing list archives I have some recollection …
Post: The two even more fundamental performance metrics
May 5, 2011 By 6 Comments
… response times of individual queries. This is the completion time minus the arrival time (a duration). We can also compute the average concurrency, throughput, response time…typical response time is under a millisecond — maybe it’s 50 microseconds, maybe a bit longer. This is common in MySQL servers…
Post: TPC-H Run on MySQL 5.1 and 6.0
April 10, 2008 By 16 Comments
… case we set cut-off time to 3 hours to give MySQL more time to complete the queries: Query MySQL 5.0.23 MySQL 6.0.4Ratio Query13784… 100G data set MySQL 5.1 could only complete 10 out of 22 queries within 3hours run time allowed for each query and MySQL 6.0 has similar number of queries it…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… single thread/query here. So difference is 3.000 times! It might be a bit too much as there are few completely uncached… tables, which is completely disk bound can be very slow. One of the reasons elevating this problem in MySQL is lack of… exeption of MySQL Cluster) and MySQL issues IO requests one by one for query execution, which means if single query execution time is your concern…
