…working…innodb_flush_method = O_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…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…working with large blobs ? Using user variables ? Prepared Statements ? memory tables…not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE_NAME: my ENGINE: InnoDB… per… file used…
Post: Performance problem with Innodb and DROP TABLE
February 3, 2011 By 23 Comments
…’ve been working with an application which does a lot of CREATE and DROP table for Innodb tables and we’ve discovered DROP TABLE can… variable online in Innodb Plugin, MySQL 5.5 and Percona Server). Yes. if you’re not using innodb_file_per_table you’re not affected because in this case tablespace does not need to be dropped so…
Post: MySQL File System Fragmentation Benchmarks
March 21, 2008 By 28 Comments
… value insert statements taking over 5 seconds… So it does not work very well. Note: As I checked later contrary to my… larger amount of rows read performance for 10000 tables would be close. Innodb with innodb_file_per_table=1 had the following results: [root@DB10 ~]# for… Innodb – Innodb extent allocation works (perhaps would be good option for MyISAM as well) – Innodb suffers fragmentation less if it stores different tables in different files…
Post: How innodb_open_files affects performance
November 18, 2009 By 4 Comments
… – innodb_open_files which defines how many files Innodb will keep open while working in innodb_file_per_table mode. Unlike MyISAM Innodb does not have to keep open file descriptor when table is open – open table is…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 70 Comments
…. They are tiny but often faster than bigger ones. RAID10 works well for data storage and for read-mostly cases when… do not have battery backed up RAID cache as when write IO may suffer. innodb_file_per_table – If you do not have too many tables use this option, so you will not have uncontrolled innodb main tablespace growth…
Post: Lost innodb tables, xfs and binary grep
November 9, 2010 By 11 Comments
… lost: MySQL had a dedicated partition on XFS file system Server was running innodb_file_per_table There was a production master and two slaves… the file recovery tools for xfs – apparently they are all targeting specific file types and sure enough InnoDB is not one of the supported files… while matching on numbers as a sequence of characters would not work for many reasons. This is where I found bgrep which…
Post: Reasons for run-away main Innodb Tablespace
June 10, 2010 By 1 Comment
So you’re running MySQL With innodb_file_per_table option but your ibdata1 file which holds main (or system) tablespace have grown dramatically from… up. This however does not work in all cases. If you’re running XtraDB you can also use innodb_use_purge_thread to use dedicated purge thread, which works a bit faster as it does not need to…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 7 Comments
… You must not drop, truncate or alter the schema of the table after the backup has been taken. The variable innodb_file_per_table must be…only works if table was “exported” with Xtrabackup as this exports essential information from main tablespace which is not stored in .ibd file. innodb_import_table…
Post: Connecting orphaned .ibd files
May 13, 2011 By 12 Comments
…files. A well known innodb_file_per_table option brings the second one. Tables and system areas are split into different files…tables is following: SYS_TABLES: CREATE TABLE `SYS_TABLES` ( `NAME` varchar(255) NOT NULL default ”, `ID` bigint(20) unsigned NOT… to work on all versions of MySQL/InnoDB…
