… will place a limit on amount of queries system can ran, for example if we have query which requires 1 CPU second and… happen so such histogram also gives us a good clue how many queries needed no io, needed 1 IO which was not queued… close look at your workload and get an understanding how much your queries (at least most important ones) cost you in terms…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… rebooted the machine and fired up 5.6.10. I ran the test, and to my surprise MySQL 5.6.10… a query with only one join, I’ve tested performance of the query with the join in both directions. Explain for query Q1… set (0.00 sec) After running the query, see how many pages were read from disk versus how many page requests their were: mysql> select…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… purpose of this benchmark, I have used TPC-H Query #10 and ran it on TPC-H dataset (InnoDB tables) with a….6 and MariaDB 5.5. Handler_mrr_rowid_refills counts how many times the buffer used by MRR had to be reinitialized… query times remain under a minute. So when the correct query execution plan is not used, there is no difference in query times…
Post: Reasons for MySQL Replication Lag
July 29, 2011 By 9 Comments
… but more subtle things such as “small” batch job being ran which does a lot of updates can also be the… check the types and frequencies of queries but also how much IO they are causing and how many rows they analyzes. Changes could be in the data which is being accessed by the queries or query execution plans…
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: How to load large files safely into InnoDB with LOAD DATA INFILE
July 3, 2008 By 29 Comments
…file and let us load with many transactions instead of one huge transaction…bloated and slow. Even simple SELECT queries might have to scan through lots…screen session in two and then ran (approximately — edited for clarity) the … per second. You can see how the curves are roughly logarithmic,…
Post: How expensive is USER_STATISTICS?
June 2, 2012 By 14 Comments
… is very helpful information. It lets you run queries to see which tables get the most …is how much more memory it uses. The second question is important on servers with many tables. Actually, “many… warmup and benchmark, and captured another snapshot. We ran this in a couple of configurations. This …
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
…) as b ) as c; You can have many of those per query. Sorting and grouping and DISTINCT and UNION may…packet bigger and ran the following on my laptop: mysql> set @a := repeat(‘a’, 1024 * 1024 * 100); Query OK, …100M user variable and a 100M lock key. Note how long it took to run these statements! And …
Post: The two even more fundamental performance metrics
May 5, 2011 By 6 Comments
… completions into buckets, a second at a time, and count how many there are in each bucket, we should see roughly the… application sent a burst of extra queries (not just the normal number of queries), and these ran longer than usual. This is not… that little spike. We could also classify queries together and analyze the classes of queries, rather than lumping all requests into one…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… # Bail out if the disk has less than this many MB free. MB_THRESHOLD=100 # Make sure the disk …continuing to execute more queries, then it won’t work. In addition, I will admit that I ran into a case … run SELECT queries since its inception. That case is still open and I am still stumped as to how a …
