… use FLUSH TABLES WITH READ LOCK to temporary make MySQL read only. In many cases the period for which server has to… long SELECT queries you can be potentially waiting for hours for this statement to complete. Here is example how it can look: mysql… situation like above and either kill FLUSH TABLES WITH READ LOCK and fail backup or kill long running SELECT queries to let backup to proceed…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…long_data to see whenever sending long…In Percona Server you can do better as you can query temporary tables too: mysql…in MySQL. Most suspected memory leaks end up being some run…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… it’s a long-running query. If that’s the case, then you’ll see the query in the processlist, and…, use mk-query-digest to convert this into a query log, and examine the log. In MySQL 5.1 and… pid=$! sleep 30 kill ${pid} fi If this captures anything, then you can simply run “mk-query-digest –type=…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…in MySQL. This is very helpful for measurement as otherwise we might not catch some long running…Thread_id: 1 Schema: test Last_errno: 0 Killed: 0 # Query_time: 0.117904 Lock_time: …queries in this fashion as opposed to the general query log, enables us to have the statistics available after the query…
Post: TPC-H Run on MySQL 5.1 and 6.0
April 10, 2008 By 16 Comments
… this box had. MySQL Also was very slow running some queries so we changed scripts a bit to kill extremely long running queries to get results for others this means we can’t really get a valid TPC-H result from MySQL… it in his MySQL 5.1 vs 6.0 in TPC-H Queries post As a Summary: We can see MySQL capabilities to run complex analytics queries, in…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual,….commanual long_query_timeblogpercona….queries_in_cacheblogpercona.commanual Qcache_total_blocksblogpercona.commanual Queriesblogpercona.commanual query_alloc_block_sizeblogpercona.commanual query_cache_limitblogpercona.commanual query_cache_min_res_unitblogpercona.commanual query…
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 8 Comments
…mysql-file-privs plugin checks whether MySQL owns every file and directory within its data directory. InnoDB is getting stuck, has long-running…MySQL on the same files in the data directory, and refusing to shut down MySQL so that it is killed… in a variable such as Queries and alert on a queries-…
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… Â Â Â # limit # of queries in report SLEEPTIME=1200Â Â Â …kill $PID; ssh $REMOTEHOST — “rm -f $TMP/$TMPfile”; exit’ INT TERM EXIT touch $TMP/$LOCKFILE # run…
Post: 10+ Ways to Crash or Overload MySQL
November 13, 2007 By 20 Comments
…MySQL to consume so much memory so it will swap badly until it runs out of space and gets killed. In… them using mysql_stmt_send_long_data call – …MySQL server to run out of file descriptors. The same is true for Partitioned tables in MySQL… which allows to run queries with uncontrolled resource usage…
Post: Should you move from MyISAM to Innodb ?
January 12, 2009 By 53 Comments
… slave available for all long reporting queries. In case MyISAM was chosen not …Operations What is good for MyISAM kills Innodb, such as copying binary … for you when you’re running MyISAM but instantly break upon …exercises not so common paths in the MySQL server – in particular Optimizer may have …
