…to see if only in certain cases query takes long time to execute, for example if plan is different based on constants. How to…to logging only very slow queries. Once you have created full log – parse it and check queries…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… to convert it. You can easily check that it parses by running the SELECT portion, adding LIMIT 0 to the SELECT part of the query… the SQL necessary to compute this list would take over 40 minutes to execute, because that is how long it takes to create the monthly… does not take long to refresh: mysql> call flexviews.refresh( -> flexviews.get_id(‘demo’, -> ‘dashboard_customer_sales’),’BOTH’,NULL); Query OK, 0 rows…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
…query – Query Cache does simple optimization to check if query can be cached. As I mentioned only SELECT queries…to be cached. Query must be deterministic – Query might provide same result no matter how many times it is run…
Post: Using LVM for MySQL Backup and Replication Setup
August 21, 2006 By 53 Comments
… able to do atomic snapshot of many volumes. Lets speak a bit about how LVM and… perform recovery, check tables or do whatever else you might need to do to ensure your backup …to MySQL and run FLUSH TABLES WITH READ LOCK Note – this command may take a while to complete if you have long running queries…
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
…yesterday a bit with INSERT … SELECT I decided to check is Innodb locks are relly as efficient in… rows in this table and see how long does it take: mysql> begin; Query OK, 0 rows affected (0…(I repeated runs many time to ensure it is repeatable) so exclusive locks seems to be more expensive to set….
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… includes some very long SELECT queries you can be potentially waiting for hours for this statement to complete. Here is example how it can… check for situation like above and either kill FLUSH TABLES WITH READ LOCK and fail backup or kill long running SELECT queries to let backup to…
Post: How to use tcpdump on very busy hosts
April 18, 2011 By 17 Comments
…query-digest –filter ‘$event->{time} && $event->{time} > 1′” to exclude all queries which it believes took longer than the servers long-query…to capture tcpdump data in seconds (1200 = 20 min) TMP=’/tmp’ LOCKFILE=”$self.lockfile” TMPfile=”$self.temp” RESfile=”$self.result” # check…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
… DBAs and developers to accurately time their queries and to catch those which run less than…long_query_time value as seconds. In effect one could only choose to log all queries to see those which take less than 1s. Now long_query… you can see how much time (in seconds) it took InnoDB to actually read…
Post: Sell-an-Elephant-to-your-Boss-HOWTO
August 28, 2012 By 3 Comments
…long, so I decided to go with the elephant instead. System performance optimization is what I do day to…to suggest that you can check if optimizations at hand should be prioritized and if so – how to…to better hardware, optimizing indexes or queries…
Comment: How to Monitor MySQL with Percona's Nagios Plugins
February 23, 2012 By 11 Comments
… you have to. Many many times I have put a Nagios check in place for a ratio or threshold (say, long-running query) just to get some data (for example, how many times per day a query runs for more than 10 minutes on a DW server). The checks…
