…lock, anything else that currently holds the lock must finish what it’s doing. That means that every currently running query, including SELECT queries…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
… using query_cache_wlock_invalidate=ON locking table for write would not invalidate query cache so you can get results even if table is locked and… comments in the query, have extra space or use different case – these would be different queries for query cache. Only SELECT queries are cached SHOW…
Post: Slow DROP TABLE
June 16, 2009 By 21 Comments
…Query | 7 | rename result table | ALTER TABLE large_table ENGINE=MyISAM | | 679 | root | localhost | test | Query | 6 | Opening tables | select * from other_table…
Post: Resyncing table on MySQL Slave
June 29, 2008 By 11 Comments
…: Assuming your table schema does not change you can do something like this on the master: LOCK TABLE tbl WRITE; SELECT * FROM table INTO OUTFILE ‘/tmp/tbl.txt’; DELETE FROM tbl; LOAD DATA INFILE ‘tmp/tbl.txt’ INTO TABLE tbl; UNLOCK TABLES… table rep to rep_maint; Query OK, 0 rows affected (0.01 sec) mysql> select * from rep_maint into outfile ‘/tmp/rep.txt’; Query…
Post: Index lock and adaptive search - next two biggest InnoDB problems
February 25, 2010 By 12 Comments
… key select queries and write queries at the same time. InnoDB again uses single global mutex for adaptive_search (single mutex for ALL table and ALL indexes), so write query blocks ALL select queries. Usually first action is to disable…: I think some hashing algorithms may be applied to not lock select queries. We may look how to implement it. Until that InnoDB…
Post: PHP Large result sets and summary tables.
July 6, 2007 By 8 Comments
… cause things breakage Table Locks – Table locks are not cleared until you fetch whole result set if you’re reading from tables directly (if you… if you need to query all result at once. It is rather efficient bur can cause problems with table locks and other issues… queries to process data by certain objects (ie City by one City at the time) or do INSERT … SELECT to the temporary table…
Post: MySQL optimizer: ANALYZE TABLE and Waiting for table flush
February 27, 2013 By 3 Comments
… running query – Run an ANALYZE TABLE on a table accessed by the long running query So first we need a long running query against table t: SELECT… reopen it using FLUSH. Therefore the table will be locked until all queries that are using that table finish. There are only two solutions…
Post: Analyzing Slow Query Table in MySQL 5.6
February 18, 2013 By 1 Comment
…longlong query_time= (longlong) (query_utime/1000000); 611 longlong lock_time= (longlong) (lock_utime/1000000); All the times recorded in slow query log tables are rounded… SELECT CONCAT( ‘# Time: ‘, DATE_FORMAT(start_time, ‘%y%m%d %H:%i:%s’), CHAR(10), ‘# User@Host: ‘, user_host, CHAR(10), ‘# Query…
Post: Slow Query Log analyzes tools
September 6, 2006 By 84 Comments
…queries before queries are executed (and even parsed) so it can’t contain query execution information such as execution and lock…queries but hurt others, for example adding indexes often help SELECT queries…
Comment: SELECT LOCK IN SHARE MODE and FOR UPDATE
January 31, 2010 By 51 Comments
…->db->query(‘SET AUTOCOMMIT=0′); $this->db->query(“LOCK TABLES hotels READ, location WRITE”); $this->db->query(“SELECT hotel_id FROM hotels WHERE hotel_id = 1 “); $query = $this->db->get(); $this->db->query…
