…_RESULT hint if you need to release table locks early. It comes at cost of creating temporary table though which can be quite high. Sharing… do INSERT … SELECT to the temporary table with auto_increment column and fetch data from this table using auto increment column ranges instead… services and so on – in this case the overhead of creating yet another temporary table is not so large.
Post: Best kept MySQLDump Secret
March 23, 2012 By 13 Comments
…will Create temporary table with modified structure, copy data to that table and when drop original table and rename such temporary table to…– Dumping data for table `C` — LOCK TABLES `C` WRITE; /*!40000 ALTER TABLE `C` DISABLE KEYS */; /*!40000 ALTER TABLE `C` ENABLE KEYS */; UNLOCK TABLES; /*!…
Post: Copying InnoDB tables between servers
July 31, 2009 By 11 Comments
…lock or shutdown server. To export table you need XtraBackup, and you can just specify table or table…start_lsn=(3 763355707) xtrabackup: Temporary instance for recovery is …create empty table with the same table definition as on old servers: CREATE DATABASE “testimport”; USE “testimport” CREATE TABLE…
Post: xtrabackup-0.9.5rc
October 24, 2009 By 13 Comments
…: Changelog: Option –no-lock is added to innobackupex-1.5.1. Use it only while ALL your tables are InnoDB and you… 1.0.4. Barracuda file format as well as compressed tables are supported. We thank a well known Social Network site… Percona-XtraBackup: “tries to create temporary tables in relative ./tmp directory” Bug #417178 in Percona-XtraBackup: “can’t backup tables with $ in name” The…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…TABLE are ignored to enforce uniqueness where necessary when copying the data to a temporary table; ALTER TABLE and OPTIMIZE TABLE always process tables…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
…engines with table locks or if LOCK TABLES are used. So in practice this option is used for MyISAM and MEMORY tables. max_write_lock_count… be converted to on disk MyISAM table. It does not limit size of temporary table, neither it applies to tables created as TEMPORARY TABLE, even in memory tables.
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…created which temporary tables (both in memory and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE…system 294352 (82672 + 211680) Lock system 318875832 (318747272 + 128560…
Post: Paul McCullagh answers your questions about PBXT
November 20, 2009 By 2 Comments
… read locks. A normal SELECT does not lock at all. In addition, an UPDATE or DELETE only acquires a temporary row-lock. This lock is… into global locks when calling back into MySQL to do things like open a table, create a session structure (THD) or create a .frm… opening tables (reading a .frm file), which requires creating a THD. The code to create a THD in turn tries to acquire LOCK_plugin…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
…) uses memory to execute; so do temporary tables. The following query creates at least 2 temporary tables: select * from ( select * from ( select …rows affected (2.34 sec) mysql> select get_lock(@a, 1); +—————–+ | get_lock(@a, 1) | +—————–+ | 1 | +—————–+ 1 row in set…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
… a table. Full_join means any of the joins didn’t use indexes. If a temporary table is required for query execution Tmp_table will be set. Sometimes that table must be created on disk instead of…_bytes: 311296 InnoDB_IO_r_wait: 0.382176 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.067538 # InnoDB_pages…
