…_threads = 4 innodb_file_per_table = 1 ## Disabling query cache query_cache_size = 0 query_cache_type = 0 You can see that the… cause downward spikes in performance. Other then that, I have also disabled query cache to avoid contention caused by query cache on write… cause read performance to improve, but they have an impact on the write performance. Well most of the apps rely on read performance and…
Comment: Multi Column indexes vs Index Merge
May 8, 2012 By 30 Comments
… index on a table improve high perfomance in select queries, but worsen perfomance in Insert, Update and Delete queries. Is it true? And if is it, how could i balance the choice??? too many indexes and high performance select…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…_buffer_size is not going to provide the best possible query performance. As I mentioned at the start that BKA uses the… not make much of a difference in the query times. Conclusion BKA improves the query time by a huge margin for IO bound… much of a performance improvement from using Hash Join in MariaDB 5.5 or from Key-ordered Scan for TPCH query #3, in…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… incremented by TWO. It does not actually mean that queries with MRR are performing badly. The interesting thing is that though both… improvement and changes needed in the optimizer’s cost estimation algorithm. MariaDB 5.5 expands the concept of MRR to improve the performance… is no performance improvement when the workload is in-memory, in fact MRR adds extra sorting overhead which means that the queries are…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition …
Comment: MariaDB 5.3 is released as GA!
March 6, 2012 By 17 Comments
Don’t forget that Timour and Sergei are giving a 3 hour tutorial on MariaDB 5.3 (and 5.5?) and MySQL 5.6 optimizers: http://www.percona.com/live/mysql-conference-2012/sessions/improving-mysqlmariadb-query-performance-through-optimizer-tuning
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
…queries through speedier joins, faster subqueries, and elimination of useless tables in joins Thread pooling and a segmented key cache for improved… needs and personalities, with different characteristics, and feature and performance improvements are moving rapidly forward for the MySQL world as …
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 5 Comments
… EXPLAIN phase yet making it unusable to check the optimizer performance. Solution for this problem was to use set optimizer_search…, and full query execution time to less than 50ms. Low values, such as 3,4 provided a bit better performance but I… better handling joins of many tables as optimizer heuristics are improved so much higher search depths are feasible now.
Post: Introducing new type of benchmark
February 25, 2012 By 16 Comments
… process. For example, if our target is arrival rate 2 queries per second, then exponential distribution will give us following intervals… two nodes instead of three nodes, will it help to improve the response time ? – Do we need to increase number of… problem with DROP TABLE performance. Does DROP TABLE, running in separate session, affect a response time of queries that handle user load…
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 70 Comments
Hi, I am facing major issues with slow query output. The tables are in InnoDB with MySQL 5 and … in set (15 min 54.29 sec) Please help to improve performance and suggest if any config changes are to be made…
