… 65000 tables using innodb_file_per_table so “InnoDB: Reading tablespace information from the .ibd files…” stage was taking most of the time. Happily Innodb only needs to scan .ibd files… restarts as well – “Opening Tables“. As Innodb has to recompute the stats first time it opens the table this can take significant amount…
Post: Adjusting Innodb for Memory resident workload
March 25, 2009 By 11 Comments
… issues for XTRADB. Preload It is possible to preload all innodb tables (ibdata, .ibd files) on the system start – this would avoid warmup problem and also make crash recovery fast even with very large log file… pool can be flushed about once per 30 minutes. It should be possible to just size Innodb logs so they are not…
Post: Quickly preloading Innodb tables in the buffer pool
May 1, 2008 By 9 Comments
… would be great if Innodb would implement command to preload table to Innodb buffer pool, which would simply go through .ibd file sequentially and inject pages in the buffer pool. This would make preload done using sequential file scan even if…_col like “%0%” for each index. Only one such query per index is enough even if it is multiple column index…
Post: Performance Schema tables stats
May 2, 2011 By 2 Comments
….01 sec) 2. There is table with statistic per index: mysql> select * from table_io_waits_summary_by_…FILE_NAME like ‘%sbtest55%’ limit 5G *************************** 1. row *************************** FILE_NAME: /data/tachion/sb/sbtest/sbtest55.ibd EVENT_NAME: wait/io/file/innodb/innodb_data_file…
Comment: Benchmarking single-row insert performance on Amazon EC2
October 13, 2012 By 17 Comments
… have to worry about that. Do you drop tables often? If you do, with innodb_file_per_table you can quickly reclaim space however, with… then you need innodb_file_per_table. Now 350,000 tables is a large number of tables, having innodb_file_per_table=1 could mean a lot of .ibd files, if they…
Comment: How to recover a single InnoDB table from a Full Backup
March 22, 2012 By 10 Comments
… –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_table_from_xtrabackup = 1; ALTER TABLE…
Comment: table_cache negative scalability
April 14, 2010 By 23 Comments
…Innodb innodb_status_file = 1 innodb_file_per_table = 1 innodb_data_home_dir = /db2/innodb innodb_log_group_home_dir = /db-logs/ibdlogs innodb_data_file_path = ibdata1:10M:autoextend innodb…
Comment: table_cache negative scalability
April 14, 2010 By 23 Comments
…Innodb innodb_status_file = 1 innodb_file_per_table = 1 innodb_data_home_dir = /db2/innodb innodb_log_group_home_dir = /db-logs/ibdlogs innodb_data_file_path = ibdata1:10M:autoextend innodb…
Comment: How to recover a single InnoDB table from a Full Backup
March 22, 2012 By 10 Comments
… innodb_file_per_table directive set in the my.cnf configuration file. I have the backup folder which contains xtrabackup log files, xtrabackup checkpoint file and a folder for my database (called tools) which contains .MYD, .ibd, .frm, .TRG files. I…
Comment: Should you move from MyISAM to Innodb ?
January 12, 2009 By 59 Comments
… answer WHY you need to restore single table. What condition causes this ? Typically there are … with $5000 cost per minute you could pay us for few minutes of downtime and get .ibd files movable for … would have recommended you to stay with Innodb reviewing your application. Just recognize it is not…
