… like SQL can produce an infinite variety of queries. The query optimizer analyzes queries for common patterns the MySQL designers know can be… spent developing and testing the query optimizer. The developers have to make a decision about which query patterns the optimizer recognizes, and… result was a query that took half the time. An important conclusion of my presentation today is that sometimes you find unexpected differences…
Post: Implementing SchemaSpy in your MySQL environment
June 4, 2013 By 2 Comments
… which has given me time to explore new tools using their environments. One tool that I am finding very helpful is called… queries only. An additional benefit is that SchemaSpy is command-line driven and builds the html after each run, so I find… John Currier asks for donations so he can justify the time spent maintaining SchemaSpy to his wife 
Finally don’t forget…
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
…”. I have labeled each query as Q{FLIGHT_NUMBER}.{QUERY_NUMBER}. In general, each flight examines different time periods or different regions. The…. All OS caches are dropped at this time as well. These set of queries were tested on the SSB at SCALE FACTOR… 12GB of data in the largest table. You can find the individual SSB query definitions in my previous blog post. Test environment…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
…performance schema, online DDL and several other InnoDB and query optimizer improvements. However, I plan to focus on … on my last webinar, but did not have time to analyze it in-depth. If you try …use MySQL, for which we at Percona tend to find redundant indexes. And second, because in some cases, …
Post: MySQL 5.6 Compatible Percona Toolkit 2.2 Released
March 25, 2013 By 2 Comments
….2 retries every critical operation, and its tries and wait time between tries for all operations are configurable. Also, we removed… you shouldn’t have a problem finding queries to run and compare. pt-query-digest is simpler pt-query-digest 2.2 has fewer…, we re-focused it on its primary objective: analyzing MySQL query logs. So the ability to parse memcached, Postgres, Apache, and…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
… scanned one at a time. (Please provide specific references if you find otherwise.) I would argue that single-row queries (SELECT, INSERT, DELETE… levels. A “point query” in a billion-row (non-partitioned) table will, at worst, hit the disk only 5 times. (Usually 4 of the 5 are cached from previous queries.) (Re #2) I have rambled…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…rerun tests on several our boxes. You can find setup/config details at the very end …amount of updates to the database, all SELECT queries, even those participating in read-only transactions, start…=1 –rand-type=uniform –forced-shutdown=1 –max-time=120 –max-requests=0 –percentile=99 –mysql-user=…
Post: How to Identify Bad Queries in MySQL
January 10, 2011 By 16 Comments
… — finding queries that block other queries — yet. The missing piece, part 3 — finding queries that block other queries — is best done by observing clusters in query end times. If there is a sudden burst of queries completing…
Post: Slow Query Log analyzes tools
September 6, 2006 By 84 Comments
… specify slow query time in microseconds rather than seconds and allows you to log all queries in slow query log by setting long_query_time=0… rows examined. Aggregating slow query log As I already mentioned besides finding slowest queries it is important to find queries which cause largest load on the server, which is with certain level of accuracy queries which take most time to…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… one can find out queries which should be optimized. By looking at pt-query-digest report it is easy to find slow queries or queries which… query log: # Time: 120911 17:18:05 # User@Host: root[root] @ localhost [] # Thread_id: 65005 Schema: sbtest Last_errno: 0 Killed: 0 # Query_time… the query: # Time: 120911 17:31:48 # User@Host: root[root] @ localhost [] # Thread_id: 65144 Schema: sbtest Last_errno: 0 Killed: 0 # Query_time…
