… bug #63144. CREATE TABLE or CREATE TABLE IF NOT EXISTS statements on an existing table could wait on a metadata lock instead of failing…_mutex acquisition. Bug fixed #1163262 (Alexey Kopytov). When mysqldump was used with –innodb-optimize-keys and –no-data options, all secondary key…
Post: Percona Server for MySQL 5.5.30-30.2 now available
April 10, 2013 By 10 Comments
… bug #63144. CREATE TABLE or CREATE TABLE IF NOT EXISTS statements on an existing table could wait on a metadata lock instead of failing…_mutex acquisition. Bug fixed #1163262 (Alexey Kopytov). When mysqldump was used with –innodb-optimize-keys and –no-data options, all secondary key…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 18 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: Best kept MySQLDump Secret
March 23, 2012 By 13 Comments
… 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 …
Post: Should you move from MyISAM to Innodb ?
January 12, 2009 By 59 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 …
Post: MySQL Backup tools used by Percona Remote DBA for MySQL
March 13, 2013 By 8 Comments
… logical backups – compared to mysqldump Consistent backups between myisam and innodb tables. Global read lock only held until myisam tables are dumped. We are researching into how we could further improve lock times…
Comment: Database problems in MySQL/PHP Applications
November 6, 2006 By 13 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…
