… only a few GB of main memory). The cost of querying this database will be 10 times higher with this partitioning… 100 disk seeks to answer a random query, assuming you can get each index query down to one disk seek.) I claim… graph correctly, you didn’t graph the performance on the first 100M rows). The minimum performance troughs occur just as the…
Comment: Multi Column indexes vs Index Merge
May 10, 2012 By 30 Comments
… the same SQL query with a couple of mins gap on the same mysql prod instance . The first SQL query executed in 0.03 secs whereas the second SQL query when using index merge executes… using a single index. I would greatly appreciate your answer. First explain —————– explain SELECT LinksTbl2.id, LinksTbl2.title, LinksTbl2.url, PersonTbl2…
Post: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
…. OK, let’s start off with the configuration details. Configuration First of all let me describe the EC2 instance type that…_threads = 4 innodb_file_per_table = 1 ## Disabling query cache query_cache_size = 0 query_cache_type = 0 You can see that the…. Other then that, I have also disabled query cache to avoid contention caused by query cache on write heavy workload. OK, so…
Post: Load management Techniques for MySQL
May 6, 2012 By 2 Comments
… first thing you need to know it is not MySQL problem, might be even not problem with your MySQL configuration, queries and… too much in this case throttling by having relatively short queries and introducing “sleeps” between them can be a good idea…
Comment: Joining many tables in MySQL - optimizer_search_depth
May 12, 2012 By 5 Comments
… hints surely help pushing the performance in case of particular queries but surely cannot be an all-around answer to inefficiencies… optimizer, that should be doing a good job in the first place. In addition, hints need to be built into the…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…. This join algorithm works best for highly selective queries, and obviously when the first operand used in the build step is such… Scale Factor of 2 (InnoDB dataset size ~5G). Note that query cache is disabled during these benchmark runs and that the… not make much of a difference in the query times. Conclusion BKA improves the query time by a huge margin for IO…
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 …
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By Leave a Comment
… employees add column age smallint unsigned not null default 0; Query OK, 300024 rows affected (13.28 sec) Records: 300024 Duplicates…())-YEAR(birth_date)) – (RIGHT(CURDATE(),5)
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… to share some troubleshooting techniques I use. Plot Memory Usage First I would like to see MySQL memory consumption plotted. I… memory. There are implicit MEMORY tables which are allocated for query execution, which size can be controlled by tmp_table_size…. In Percona Server you can do better as you can query temporary tables too: mysql> select sum(data_length+index_length…
Comment: InnoDB's gap locks
April 13, 2012 By 14 Comments
… | | 3 | andy | | 5 | aaron | | 8 | repls | +——+——–+ As I said, write queries behaves as if you were using READ COMMITTED in spite… new transaction again on the session 2 without closing the first one: session2> select * from t; +——+——-+ | i | c | +——+——-+ | 3 | andy | | 5…
