…query blocks any READ queries to execute on the table. mysql> show processlist; … fail backup or kill long running SELECT queries to let backup to…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… on server and Com_stmt_send_long_data to see whenever sending long data feature is used as … you can do better as you can query temporary tables too: mysql> select sum(data_length+index_length) … things I do as it is least intrusive. Run SHOW ENGINE INNODB STATUS and look for memory information block…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…query was taking far too long to execute, ~11 hours in case of MySQL 5.5 and ~5 hours in case of MySQL… buffer size of 256K, MariaDB 5.5 shows that Handler_mrr_rowid_refills = 1, which …query runs the optimizer would switch to using plan ‘a’, which does not involve MRR, and the query time for MySQL…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… to figure out what the long-running transaction is doing. The most obvious is if it’s a long-running query. If that’s the… 3306 and port ${port} > /root/tcpdumps/`date +%s`-tcpdump & mysql -e ‘show innodb status\Gshow full processlist’ > /root/tcpdumps/`date +%s`-innodbstatus pid…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…_disk_tmp_tables, I’ll paste in mysql> show global variables like ‘%table_size%’; +———————+———–+ | Variable_name | Value | +———————+———–+ … queries themselves. If the client has long-running queries, it can be pretty easy to catch. Maybe they’re long-running…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… new connections as in MySQL. This is very helpful for measurement as otherwise we might not catch some long running connections. Now let… output ranks the queries and shows the top queries with largest impact – longest sum of run time which typically (not always) shows queries causing highest load…
Post: Using LVM for MySQL Backup and Replication Setup
August 21, 2006 By 53 Comments
… to MySQL and run FLUSH TABLES WITH READ LOCK Note – this command may take a while to complete if you have long running queries. The… complete, even selects. So be careful if you have any long running queries. If you’re using only Innodb tables and do not… without stopping them – you just need to use SHOW SLAVE STATUS instead of SHOW MASTER STATUS to find out appropriate binary log…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…querying: mysql> call flexviews.enable( -> flexviews.get_id(‘demo’,'dashboard_customer_sales’)); Query…long to refresh: mysql> call flexviews.refresh( -> flexviews.get_id(‘demo’, -> ‘dashboard_customer_sales’),’BOTH’,NULL); Query…show…
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 8 Comments
…-check-mysql-file-privs plugin checks whether MySQL owns every file and directory within its data directory. InnoDB is getting stuck, has long-running transactions, or other problems. The pmp-check-mysql-innodb plugin checks for several indicators of… query. The pmp-check-mysql-status is also very flexible and can be used to trigger an alert on almost anything from SHOW…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 53 Comments
… which uses VIEWs and was looking at the long running query which just joined 2 tables… I ran EXPLAIN … do not get any indexes. Let me now show couple of examples. Assume we have the comments… only in case they are used with caution. MySQL has long way to go getting queries with VIEWs properly …
