… 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…` table by primary key, the result was a query that took half the time. An important conclusion of my presentation today is…
Comment: MySQL Partitioning - can save you or kill you
June 3, 2013 By 45 Comments
Hi Great article and had fun reading all the responses. After implementing what was suggested I have also managed to reduce my query time greatly. I will bookmark this blog for future reference. Best regards Ian
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
… four sets of queries. Each set is known as a “flight”. 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…
Post: Choosing a MySQL HA Solution - Post-Webinar Q&A
June 7, 2013 By Leave a Comment
… is because virtual machines tend to handle process scheduling and timing much differently than traditional hardware, and I’ve seen situations… example, if you see a large number of long-running queries in the output of SHOW PROCESSLIST that might be a… and PRM simply attempt to connect and run a simple query, but it’s always possible to build something more involved…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
… complete performance schema, online DDL and several other InnoDB and query optimizer improvements. However, I plan to focus on a series… about this on my last webinar, but did not have time to analyze it in-depth. If you try to do… some of this redundancies could be necessary, depending on the query optimizer and the MySQL version, we always recommend to check…
Post: Experiences with the McAfee MySQL Audit Plugin
May 23, 2013 By 4 Comments
…_users : This one is undocumented on the wiki at the time of writing, and is a list of users for which…-sys-maint”,”host”:”localhost”,”cmd”:”Connect”,”query“:”Connect”} {“msg-type”:”activity”,”date”:”1369155747373″,”thread-id”:”6439″,”query-id”:”219309″,”user”:”debian-sys…-sys-maint”,”host”:”localhost”,”cmd”:”Quit”,”query“:”Quit”} {“msg-type”:”activity”,”date”:”1369155747383″,”thread-id”:”6440″,”query-id”:”0″,”user”:”debian-sys…
Post: Implementing SchemaSpy in your MySQL environment
June 4, 2013 By 2 Comments
… customers on a longer term basis which has given me time to explore new tools using their environments. One tool that… effectively when I know the relationships between tables versus observing queries only. An additional benefit is that SchemaSpy is command-line… John Currier asks for donations so he can justify the time spent maintaining SchemaSpy to his wife 
Finally don’t forget…
Post: High-load problems? Investigate them with 'pt-query-digest'
April 23, 2013 By 2 Comments
… no run time data available, but at least you get a nice ranking of queries sorted by the number of times they appear… queries. To my luck, one query stood out right away, always appearing many times more than any other one during peaks. Rewriting that query (actually the customer removed it altogether, by merging it with another query…
Post: Rotating MySQL slow logs safely
April 18, 2013 By 7 Comments
… with long_query_time set to 0. Here are a some lessons we learned when logging a high volume of queries to the…. Flushing tables can impact running queries. Disable MySQL slow logs during rotation Flushing logs takes time. Meanwhile, queries are still being executed. To….long_query_time into @lqt_save; set global long_query_time=2000; select sleep(2); FLUSH LOGS; select sleep(2); set global long_query_time=@lqt…
Comment: Rotating MySQL slow logs safely
April 18, 2013 By 7 Comments
… if you are using persistent-connections. The global variable long_query_time is checked when the connection is created, so if you… feature to solve this issue. You can set the “slow_query_log_use_global_control” in 5.5 or “use_global… 1 and the server will use the global variable long_query_time instead the local one. PS 5.1 http://www.percona…
