… using a blocking SST method like rsync or mysqldump. So, flow control kicks in when the …node by simply by running “FLUSH TABLES WITH READ LOCK“, or perhaps by “LOCK TABLE“, in which case flow …without flow control if they are blocked by the underlying SST method, i.e., by FLUSH TABLES WITH READ LOCK…
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: 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
…tables | 0.000118 | | System lock | 0.000007 | | setup | 0.000027 | | creating table | 0.002255 | | After create | 0.000050 | | copy to tmp table… trigger table rebuilds without affecting the table size….table I used in my previous examples): $ mysqldump -uroot test > dump_unoptimized.sql $ mysqldump…
Comment: How fast is FLUSH TABLES WITH READ LOCK?
April 24, 2010 By 30 Comments
…if that would fix it without any further downtime. It did,…TABLES WITH READ LOCK. It immediately acquired locks on all tables — effectively write locks, as you say — except this one log table. It held those locks…to InnoDB for everything, and use mysqldump –single-transaction for backups. Hopefully…
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…number of tables, slow data load and ALTER TABLE and others. Another big one is COUNT(*) without where … Operations aspects a lot – for example using mysqldump as a backup may well work for …
Comment: Database problems in MySQL/PHP Applications
November 6, 2006 By 13 Comments
…32M # table_cache=20M # open-files-limit=20000 table_cache = 3072 open_files_limit = 9216 tmp_table_…named pipes. # Note that using this option without enabling named pipes on Windows # (via the …_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M […
