…Locking behavior prior to MySQL 5.5.3 Metadata Locking behavior prior to MySQL 5.5.3 Prior to MySQL…query proceeds without being blocked on anything while the RENAME is still waiting for metadata locks…. SELECT query still blocks, and the status counter Opened_tables is also incremented once the query…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…queries without aggregate functions/group by and only to ones which examine one table only. What is about queries which query…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… without committing, and has modified data, then other transactions could block and fail with a lock wait… 1 fi host=$(mysql -ss -e ‘SELECT p.HOST FROM information_schema.innodb_lock_waits w INNER … others and had locks and undo log records, yet had only run SELECT queries since its inception. …
Post: MySQL Slow query log in the table
May 31, 2007 By 1 Comment
… TIME type which is used to store query execution time and lock time does not store fractions of …If you want to get new queries in the end you can do SELECT * FROM (SELECT * FROM slow_log ORDER … without need to have indexes would be quite handy I also should tell log table implementation in MySQL …
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… an exclusion-join query for each foreign key relationship: mysql> SELECT CONCAT( ‘SELECT ‘, GROUP_CONCAT(DISTINCT…our query generator script instead of INFORMATION_SCHEMA.KEY_COLUMN_USAGE. Conclusion Without real …so you can correct them. 1Innodb locking and Foreign Keys 2Instrumentation and the …
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
… write on every node without needing to worry about replication… 0 node3 mysql> commit; Query OK, 0 rows affected (0.00 sec) node3 mysql> select * from autoinc; +—+——-+ | i …mysql> commit; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction node1 mysql> select…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –skip-external-locking –port=3306 –socket=/var/db/mysql/mysql… something is disconnecting ungracefully (without closing the …query, I run EXPLAIN if possible (rewriting non-SELECT queries if needed, and using careful judgment for queries…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… cold The remaining results are runs without a server restart. For the default …query with only one join, I’ve tested performance of the query with the join in both directions. Explain for query Q1.1: mysql> select… 0 | 0 | 0 | 0 | 8 | 8 | | 7 | System lock | 0.000031 | 0.000031 | 0.000000 | 0.000000…
Post: Slow Query Log analyzes tools
September 6, 2006 By 84 Comments
…query_time without restarting server so it is easy to get sample of all queries…SELECT queries but slow down INSERT/UPDATE ones. Final Note: You do not have to have patched MySQL…
Post: Percona XtraDB Cluster: Failure Scenarios with only 2 nodes
July 25, 2012 By 6 Comments
…without any problem when the connection between the nodes is gone: percona1 mysql> insert into percona values (0,’percona1′,’jaime’); Query OK, 1 row affected (0.02 sec) percona1 mysql> select…
