… query time for MySQL 5.6, increase of 0.02s. But the query times for MariaDB 5.5 are greater than both MySQL 5.5 and MySQL… MRR, and the query time for MySQL 5.6 and MariaDB 5.5 jumps to ~11min (this is the query time for MySQL 5.5) While… still improvement and changes needed in the optimizer’s cost estimation algorithm. MariaDB 5.5 expands the concept of MRR to…
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… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… data to estimate capacity of MySQL system ? We can look at CPU and IO consumption per Query and compare it to estimated (or benchmarked… 0 # Users user # Query_time distribution # 1us # 10us # # 100us ################################################################ # 1ms ############# # 10ms ###### # 100ms # # 1s # 10s+ We can see this query takes 2ms to respond… needs an increase. The query time distribution histogram is also very interesting here we can see this query which analyzes no more than…
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
…query was totally off. The actual number of rows was 18 times more than the number of rows reported by MySQL…
Post: Estimating Replication Capacity
July 20, 2010 By 4 Comments
… catch up quickly. So the question becomes how can we estimate replication capacity, so we can deal with replication load before… of queries which load replication the most. It however works only with statement level replication. You need to set long_query_time=0 and log_slave_slow_statements=1 for this method to work. Get the log file which will include all queries MySQL…
Post: How to estimate query completion time in MySQL
April 22, 2008 By 14 Comments
… estimate how long it’ll take for the query to complete, depending on what the query is. One of the simplest is to estimate how many rows the query needs…’ll write more about that later) I convinced MySQL to perform the query as a table scan of the fact table, then…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
In my post on estimating query completion time, I wrote about how I measured the performance on a … the MySQL 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…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… for statements in FROM clause separately before looking at main query. Estimated number of rows may be very inaccurate It is well… rows is estimated performing btree dive with certainly structured tree you still can get number of rows off 3-10 times. It… about functions cost Since MySQL 5.0 you may use user functions in your query which may also perform queries themselves. These actions…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… row in set (0.00 sec) We can see here MySQL estimates 20% more rows to traverse, which is wrong of course…) Note MySQL puts i1 and combined into “possible_key” while it has no way to use them for this query. The query takes 3370ms if this plan is used. Note the query takes about 5 times longer even though…
Post: MySQL Sizing questions
June 23, 2006 By 4 Comments
… questions along the lines of “how many transactions per second MySQL can do” or “how many servers I need to handle… this example to give estimate which is correct at least to the order of magnitude. Seriously in MySQL Consulting practice it would…. We need estimates, however we need to spend time doing serious capacity planning, probably look at real data try tunning sample queries etc…
