… use mysqldump –single-transaction to get consistent backup for their Innodb tables without making database read only. In most cases…– — Dumping data for table `C` — LOCK TABLES `C` WRITE; /*!40000 ALTER TABLE `C` DISABLE KEYS */; /*!40000 ALTER TABLE `C` ENABLE KEYS */; UNLOCK TABLES; /*!40103 …
Comment: Best kept MySQLDump Secret
March 29, 2012 By 11 Comments
…while mysqldump is going , alter table was BLOCKED! Eventually mysqldump got the old table definition and old data in it. DROP TABLE IF…InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; — – Dumping data for table `z` — LOCK TABLES `z` WRITE; /*!40000 ALTER TABLE…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 13 Comments
… it is OK table is exposed without indexes for some time. Note though nothing stops you from using LOCK TABLES on Innodb table to ensure there is not ton of queries starting reading table with no indexes and… implemented but majority of high level commands or tools (like mysqldump) do not get advantage of it and will use old…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…TABLE STATUS versus 265 MB index size with the optimization disabled. OPTIMIZE TABLE OPTIMIZE TABLE is mapped to ALTER TABLE … ENGINE=InnoDB for InnoDB tables…mysqldump –innodb-optimize-keys ignores foreign keys because InnoDB requires a full table…
Post: InnoDB: look after fragmentation
November 5, 2009 By 12 Comments
…=InnoDB Table has 11864696 rows and takes Data_length: 698,351,616 bytes on disk The problem is that after restoring table from mysqldump…: 38530 InnoDB_IO_r_bytes: 631275520 InnoDB_IO_r_wait: 0.204893 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000 # InnoDB_pages_distinct…
Post: Should you move from MyISAM to Innodb ?
January 12, 2009 By 53 Comments
… to deal with recovering tables on the crash or partially executed statements. Table locks is no more problem… time to do restore for Innodb. On large scale installations mysqldump does not work anyway but …be as much as 10-50 times for Innodb tables in particular for write intensive workloads. Check …
Comment: Database problems in MySQL/PHP Applications
November 6, 2006 By 12 Comments
… are using BDB tables #bdb_cache_size = 384M #bdb_max_lock = 100000 # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir… pool size innodb_log_file_size = 1000M #innodb_log_buffer_size = 32M #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 [mysqldump] quick…
Post: 10 things you need to know about backup solutions for MySQL
March 3, 2009 By 23 Comments
…, etc? What technique is used for the backup? Is it mysqldump or a custom product that does something similar? Is it… cannot back up InnoDB by simply copying its files? Does the backup use FLUSH TABLES, LOCK TABLES, or FLUSH TABLES WITH READ LOCK? These all interrupt…? How does the system guarantee consistency with the binary log, InnoDB logs, and replication? Can you use the system to set…
Post: Detailed review of Tokutek storage engine
April 28, 2009 By 21 Comments
… is almost linear even when table size bigger than available memory. For the last 10M rows inserted, InnoDB averaged 1,555 rows… do a backup is mysqldump/mysqlhotcopy. It is not fully transparent backup, as it applied TABLE LOCK on copying table. When recovery logs are… binary form and mysqld binary does not contain InnoDB. Tokutek tells me that InnoDB will be included in a future release. With…
Comment: What to tune in MySQL Server after installation
January 30, 2009 By 116 Comments
…/access.frm’ mysql> exit; mysqldump: Got error: 1033: Incorrect information in file: ‘./db1/access.frm’ when using LOCK TABLES Surprising thing is that of the above 3, only civicrm_price_set_entity is an innodb table. The other…
