… cases MyISAM is substantially faster than InnoDB both cold and hot. SSB Flight #4 There is one query in this flight, Q4…=justin innodb_buffer_pool_size=64G innodb_log_file_size=4G innodb_file_per_table innodb_stats_on_metadata=off innodb_file_format=barracuda innodb_log_buffer_size=32M innodb_buffer…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
… .frm files over taken from backups. But in some cases even .frm files is not an option: Table was dropped and innodb_file_per_table is ON Frm file corrupt, zeroed out, lost…_id | +———-+—–+———-+ Which means the primary key of the table is one field actor_id. Now we have all necessary information to…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… single socket x 2 threads per socket), while the second one use 16 cores (8 cores per socket per node). So this test also…_DIRECT innodb_log_buffer_size = 16M innodb_buffer_pool_size = 52G innodb_log_file_size = 2000M innodb_log_files_in_group = 2 innodb_file_per_table = true innodb_read_io…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
…again, vCache at 10% outperforms FlashCache at 10%. The one interesting item here is that vCache actually appears …fixed innodb options innodb_file_format = barracuda innodb_buffer_pool_size = 4G innodb_file_per_table = true innodb_data_file_path = ibdata1:100M innodb_flush_method = O_DIRECT innodb_…
Post: Percona XtraBackup 2.0.7 for MySQL available for download
May 6, 2013 By 1 Comment
…one were present in the full backup directory, and were not removed when incremental backups has been merged. Fixed by removing files…#1130145. Percona XtraBackup didn’t initialize per-thread data in the log …. innobackupex wasn’t handling the innodb_data_file_path option which could cause backup…
Post: Understanding the maximum number of columns in a MySQL table
April 8, 2013 By 5 Comments
… a radical departure from the MySQL philosophy, and one that has truly paid off in the Drizzle …80×24 terminal UI in your FRM file, you instead get some bunch of bytes per 19 columns). create_fields….create. If you try setting the storage engine to InnoDB however, you will get an error message! The …
Post: How to calculate a good InnoDB log file size
November 21, 2008 By 38 Comments
…Innodb_os_log_written from SHOW GLOBAL STATUS, too.) mysql> select (3838334638 – 3836410803) / 1024 / 1024 as MB_per_min; +————+ | MB_per…log files by default, divide that in half, and now you can set innodb_log_file_…the buffer pool size is just wrong. One final note: huge buffer pools or …
Post: How innodb_open_files affects performance
November 18, 2009 By 4 Comments
…similar variable – 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 … operation of opening file is rather fast on local file system – one can open/close file hundreds of thousands times per second. To …
Post: Choosing proper innodb_log_file_size
July 3, 2006 By 12 Comments
… on recovery and why large innodb_log_file_size slows down recovery. During startup after crash Innodb scans log files to find log records… to come up with any general guidelines, something like 1GB per 10 minutes of recovery time – instead you would need to…. The other important one is undo phase – after log file are applied and database is in “physically consistent” state, Innodb will need to…
Post: How much space does empty Innodb table take ?
December 16, 2008 By 7 Comments
… if you’re using innodb_file_per_table the per table tablespaces are not going to grow by innodb_autoextend_increment – instead file will grow by… not the following: Small Innodb tables will take more space on disk than one may anticipate. Using innodb_file_per_table can cause significant space…
