…_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… 5 concurrent connections writing to the table, with each instance of iiBench writing 200 million single row inserts, for a total of…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… | 2359297 | | 10290 | root | localhost | NULL | Query | 317 | Waiting for table flush | flush tables with read lock | 0 | 0 | 1 | | 10291 | root | localhost… result this means single run away select can effectively cause downtime if you use backup solution which does FLUSH TABLES WITH READ…’re just using Innodb tables and you’re not actively changing users, stored procedures etc (which are stored in MyISAM tables anyway) you…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… example. Suppose you have the following query executed on the InnoDB table: SELECT non_key_column FROM tbl WHERE key_column=x… tuples. If the buffer size is large enough only a single range lookup will be needed, however if the buffer size… dataset (InnoDB tables) with a Scale Factor of 2 (InnoDB dataset size ~5G). I did not use Scale Factor of 40 (InnoDB dataset size…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
Many people use mysqldump –single-transaction to get consistent backup for their Innodb tables without making database read only. In most cases it…=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; — – Dumping data for table `C` — LOCK TABLES `C` WRITE; /*!40000 ALTER TABLE…
Comment: How to recover a single InnoDB table from a Full Backup
March 22, 2012 By 7 Comments
… (called tools). But I am not able to do a SINGLE DATABASE restore using the copy-back command. I am able… I am able to view/access ONLY MYISAM table – but none of the InnoDB tables. Is there something which I am missing? Do… any directive in the my.cnf file to access the InnoDB table? Please help. Thanks in advance. Abhilasj
Comment: How to recover a single InnoDB table from a Full Backup
March 22, 2012 By 7 Comments
… recover a single table # xtrabackup_55 –backup –innodb-file-per-table –target-dir=/mnt/mysql/export/ –tables=data # xtrabackup_55 –prepare –export –innodb-file-per-table –target-dir=/mnt/mysql/export ALTER TABLE…
Post: Announcing Percona XtraBackup 2.0.0 GA
April 4, 2012 By 11 Comments
… was required to uncompress the entire backup to restore a single table). Percona XtraBackup now supports streaming incremental backups. In previous versions… for compiling and running Percona XtraBackup against debug versions of InnoDB. This is only for very advanced users. The new features…
Comment: COUNT(*) vs COUNT(col)
April 3, 2012 By 41 Comments
… table where condition1 and condition2; select col1,col2 from table where condition1 and condition2 limit 10,20; OR Just fire a single query and get all the data and then get the count at PHP end like:- select col1,col2 from table where condition1 and condition2; Please suggest which one is better idea in both INNODB and MyIsam
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 7 Comments
… is faster to recover single tables than a full backup. This is easy with MyISAM but if your tables are InnoDB the process is… alter the schema of the table after the backup has been taken. The variable innodb_file_per_table must be enabled. Then, our… back again! Conclusion: As we learned , you can also recover single InnoDB table as with MyISAM but knowing in advance that there are…
Post: Impossible - possible, moving InnoDB tables between servers
June 8, 2009 By 9 Comments
… we need to restore only single table from backup (sometimes developers kill only single table, not whole database 
) – to copy single table from production to QA… as with MyISAM tables when you just copy table.frm, table.MYD, table.MYI files, but so nice here – just can copy InnoDB tables in fully…
