…to check though. Run “FLUSH TABLES” and see whenever memory usage goes down. Note though because of how…to see how many prepared statements are allocated on server and Com_stmt_send_long_data to see whenever sending long…exist for duration of query execution so it is usually easy to catch them. There …
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 Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…queries themselves. If the client has long-running queries, it can be pretty easy to catch. Maybe they’re long-running… checking for default users in the mysql.* tables, running mk-duplicate-key-checker to…the query plan. This is where you need to really know how to write queries and how EXPLAIN …
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 8 Comments
…to include others as well. Here’s how you can use our new monitoring plugins to check… long-running transactions, or other problems. The pmp-check-mysql-innodb plugin checks …checking that the server is alive and responds to a query. The pmp-check-mysql-status is also very flexible and can be used to…
Post: How to load large files safely into InnoDB with LOAD DATA INFILE
July 3, 2008 By 28 Comments
…to load data (especially if you disable unique key checks and foreign key checks…. Even simple SELECT queries might have to scan through lots …to roll back, it will take a Very Long Time to do — I hate to think how long. I’m sure it would be faster to… I also used ‘time’ to run the Perl fifo script,…
Post: Innodb undo segment size and transaction isolation
January 12, 2011 By 2 Comments
…long time and there could be a lot of garbage in undo segment. If you’re running Percona Server it is easy to check… pointer to appropriate blob version. Now lets move on to the main topic – how does…long running queries will cause problems in this case anyway, even if you set transaction isolation to…
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 53 Comments
… than run two separate queries: one – to get a result set, another – to count total number of rows. In this post I’ll try to check, is this true or not and when it is better to run two separate queries. For my tests I… sec to run this query first time and 0.00-0.02 sec for all consecutive runs. And now – we need too check how long our COUNT query…
Post: Slow Query Log analyzes tools
September 6, 2006 By 83 Comments
…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 12 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 69 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…
