…, and hence, there is a slight increase in query time for MySQL 5.6, increase of 0.02s. But the query times for MariaDB 5.5 are… under a minute. The query time is reduced further when the buffer size is set to 4M. Note also that query time for MariaDB is still a… made a wrong choice. It looks like that there is still improvement and changes needed in the optimizer’s cost estimation algorithm…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look… in the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in… range condition is applied cannot be used for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey…
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
… the EXPLAIN output of the query was totally off. The actual number of rows was 18 times more than the number of… did a test for a SIMPLE SELECT to see the actual count of rows: mysql [localhost] {msandbox} (foo2) > select count(*) from test_estimate where… a test run for a SELECT involving RANGE SCAN: The actual count of rows is: mysql [localhost] {msandbox} (foo2) > select count(*) from test_estimate…
Post: Estimating Replication Capacity
July 20, 2010 By 4 Comments
…. So the question becomes how can we estimate replication capacity, so we can deal with …concurrency as reported by mk-query-digest is sum of query execution time vs time range the log file covers…typical for servers with cold cache to perform a lot slower then they are warmed up. Measuring times for …
Post: How to estimate query completion time in MySQL
April 22, 2008 By 14 Comments
… for an hour. Or a day and a half. Just when IS that query going to finish, anyway? There are actually a few ways to estimate how long it’ll take for the query to complete, depending on what the query is. One…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… my post on estimating query completion time, I wrote about how I measured the performance on a join between a few tables in a typical star… query optimizer. The optimizer tries to choose the best join order based on its cost metric; it tries to estimate the cost for a query, then choose the query plan that has the lowest cost. The unit of cost for the MySQL query optimizer is a…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
…a,id) when we extend index to (a,b) it really becomes (a,b,id). So if there is a query which used both a…a) and (a,b) at the same time…a=100. This looks like an optimizer glitch in this case because it estimates…
Post: Just do the math!
July 6, 2009 By 4 Comments
…for up to the whole year worth of data you find things not working any more with response times for individual queries…see MySQL Server (or any system really) as a black box magically providing you with results …numbers. For MyISAM table and longer rows as you see in Apache logs I’d estimate 500K rows…
Post: Using LoadAvg for Performance Optimization
December 4, 2006 By 4 Comments
…queries will run at once) and you have 4CPUs until your LoadAvg is below 4 you have low time spend waiting for… analyzes if you want good estimates. Ie you may want to check… as you need to do a lot of queuing performance starts to…so you can see as response time for your requests starts to grow. …
Post: InnoDB Flushing: a lot of memory and slow disk
March 31, 2011 By 57 Comments
…for a long time (minutes). This comes from the fact that MySQL performs changes in memory at a…a lot, like EC2 systems, or is affected by periodic tasks like backup or heavy background queries…
