…_DIRECT innodb_max_dirty_pages_pct = 50 innodb_io_capacity = 800 innodb_read_io_threads = 8 innodb_write_io_threads = 4 innodb_file_per_table = 1… = InnoDB, PARTITION p9 VALUES LESS THAN (1000000000) ENGINE = InnoDB, PARTITION p10 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ While the structure of the table with… 100 million rows inserted. I noticed that the insert rate drop was mainly caused by IO pressure caused by increase in…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… and ran it on TPC-H dataset (InnoDB tables) with a Scale Factor of 2 (InnoDB dataset size ~5G). Note that query cache…/mrr_buffer_size were set to 6M, the query time dropped to ~40s. So the maximum possible benefit is when size… 2.2G Innodb_data_reads 329115 355323 143808 335526 16164 15506 Innodb_pages_read 329115 355323 143808 358308 144798 144881 Innodb_rows_read…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
… use mysqldump –single-transaction to get consistent backup for their Innodb tables without making database read only. In most cases it works… 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…
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. so… 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.
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… across the board to everyone? Have some updates made to INNOdb tables really slowed down mySQL as measured by qps, in the…
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By Leave a Comment
… repositories i.e. updates on CentOS. If you are using InnoDB, it is also important that your transaction logs (ib_logfile… (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…
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`; /*!…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: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
… or delete any data (though you can create or drop Innodb tables): mysql> optimize table test; +———–+———-+———-+———————————-+ | Table | Op | Msg_type | Msg_text | +———–+———-+———-+———————————-+ | test.test | optimize…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 13 Comments
… 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 bringing box down. You can also use this trick for ALTER TABLE which requires table rebuild. Dropping all…
Post: Beware the Innodb Table Monitor
November 29, 2011 By 2 Comments
… table (in any database you choose), like this: CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB; This, primitively, tells Innodb that it should start the Table… can see every table, every column, and every index in that table. Once you get the output, you just DROP the table and it…
