I have innodb tables in my production database and i have set files_per_table variable. i executed the following query to know the fragmentation SELECT TABLE_SCHEMA, TABLE_NAME, CONCAT(ROUND(data… abc engine=innodb and re-executed SELECT TABLE_SCHEMA, TABLE_NAME, CONCAT(ROUND(data_length / ( 1024 * 1024 ), 2), ‘MB’) DATA, CONCAT(ROUND(data_free…
Comment: How to change innodb_log_file_size safely
May 2, 2012 By 14 Comments
…:16 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files… InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer…
Comment: How to change innodb_log_file_size safely
May 2, 2012 By 14 Comments
… x 4 = 128M innodb_buffer_pool_size). According to my previous posts in this blog a dynamic innodb log file RECREATE did NOT seem to work if you are going to delete the log files later (or… was: I could only use mysqldump to import all the data into a clean/fresh innodb system! Good luck! Thomas
Post: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
…: ## InnoDB options innodb_buffer_pool_size = 55G innodb_log_file_size = 1G innodb_log_files_in_group = 4 innodb_buffer_pool_instances = 4 innodb_adaptive_flushing = 1 innodb_adaptive_flushing_method = estimate innodb_flush… so that we can see the impact of growth in data-set on the insert rate. We can see that adding…
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 70 Comments
… param for InnoDB are [root@ng-lg-mdb1 ~]# cat /etc/my.cnf|grep innodb innodb_data_home_dir = innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data innodb_buffer_pool_size = 2000M innodb_additional…
Comment: How to recover a single InnoDB table from a Full Backup
March 22, 2012 By 7 Comments
… 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 data DISCARD TABLESPACE; # replace ibd with the export files SET GLOBAL innodb_import…
Comment: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 25, 2012 By 20 Comments
… READ LOCK. 1. copy meta data files, like frm file. 2. copy data files other than InnoDB. 3. get the binlog file and binlog postion information. And… use InnoDB as our engine, no other engines used. 3. get binlog file and binlog postion information from xtrabackup_binlog_pos_innodb file since InnoDB…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition …
Post: Announcing Percona XtraBackup 2.0.0 GA
April 4, 2012 By 11 Comments
… other limitations (e.g. could not be used with parallel file copying, it was required to uncompress the entire backup to… done by the xtrabackup binary which calculated deltas by scanning data files. Which meant those two feature were mutually exclusive, i.e… for compiling and running Percona XtraBackup against debug versions of InnoDB. This is only for very advanced users. The new features…
Comment: How to calculate a good InnoDB log file size
April 24, 2012 By 33 Comments
What if the calculation above shows that I should have an innodb log file size of 1-2 MB? I’m running Percona server on an ec2 small instance with the XFS filesystem for the mysql data directory.
