… every partition would hold 100 million rows. Table Structure The table structure of the table with no secondary indexes is as follows: CREATE… ENGINE = InnoDB) */ While the structure of the table with secondary indexes is as follows: CREATE TABLE `purchases_index` ( `transactionid` int(11) NOT… 100 million rows inserted. I noticed that the insert rate drop was mainly caused by IO pressure caused by increase in…
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By Leave a Comment
… 10.00g Suppose sometime after the snapshot, I accidentally dropped the salaries tables from the employees database! (Oops, I can have an… (0.15 sec) mysql> drop table salaries; — Ooops! Query OK, 0 rows affected (0.49 sec) mysql> alter table employees add column age…=47 exec_time=1 error_code=0 SET TIMESTAMP=1329980395/*!*/; DROP TABLE `salaries` /* generated by server */ … Now, let’s restore our data…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
… will Create temporary table with modified structure, copy data to that table and when drop original table and rename such temporary table to original name….sql DROP TABLE IF EXISTS `C`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `C…
Comment: Performance problem with Innodb and DROP TABLE
March 15, 2012 By 23 Comments
… to your view, when you set innodb_table_per_file=1 and then you drop the table, so the tablespace be droped also… pool must be discarded too? in my opinion, when the table is droped ,then no statement will access the pages with… is full or other situation, not at the time when drop table.
Post: Introducing new type of benchmark
February 25, 2012 By 16 Comments
… on the server. For example, the famous problem with DROP TABLE performance. Does DROP TABLE, running in separate session, affect a response time of…
Comment: Best kept MySQLDump Secret
March 29, 2012 By 11 Comments
… is going , alter table was BLOCKED! Eventually mysqldump got the old table definition and old data in it. DROP TABLE IF EXISTS `z`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `z… table `z` — LOCK TABLES `z` WRITE; /*!40000 ALTER TABLE `z` DISABLE KEYS */; INSERT INTO `z` VALUES (‘a’); /*!40000 ALTER TABLE `z` ENABLE KEYS */; UNLOCK TABLES…
Comment: Best kept MySQLDump Secret
March 25, 2012 By 11 Comments
… file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… called the build step. After the hash table has been created, rows from table t2 are read and hash function is applied… the join condition and then a hash table lookup is performed, on the hash table we created earlier. This step is known…/mrr_buffer_size were set to 6M, the query time dropped to ~40s. So the maximum possible benefit is when size…
Comment: Percona Toolkit 2.1 with New Online Schema Change Tool
April 22, 2012 By 9 Comments
… Squeeze avalable on DotDeb.org, and I find my qps dropping by 2/3 – with no changes to what is happening… the board to everyone? Have some updates made to INNOdb tables really slowed down mySQL as measured by qps, in the…
Post: Slow DROP TABLE
June 16, 2009 By 20 Comments
… with DROP TABLE to minimize the effect, such as: TRUNCATE TABLE large_table; ALTER TABLE large_table ENGINE=…; DROP TABLE large_table; TRUNCATE TABLE large_table; OPTIMIZE TABLE large_table; DROP TABLE large_table; Unfortunately as it turned out each of the administrative commands like ALTER TABLE or OPTIMIZE TABLE…
