… these queries compare to the size of the tables on disk? For example, using file_per_table, if the total_size column equals 1GB, how will this relate to the actual size of the table.ibd file on… to run OPTIMIZE and thus reclaim some hard disk space? Taking real-world data, I have for example a table that looks…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 70 Comments
… it with basics of Hardware OS And Application optimization. I call this Innodb Performance Optimization Basics so these are general guidelines which… and nodiratime options if you’re using innodb_file_per_table and a lot of tables though benefit of these is minor. Also make… as when write IO may suffer. innodb_file_per_table – If you do not have too many tables use this option, so you will…
Post: How innodb_open_files affects performance
November 18, 2009 By 4 Comments
… 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 purely… in terms of optimizations in this space. To keep test alligned to my previous experiments I was running with table_open_cache…
Post: Spreading .ibd files across multiple disks; the optimization that isn't
December 25, 2010 By 22 Comments
… you enable innodb_file_per_table, each table is it’s own .ibd file. You can then relocate the heavy hit tables to a different… new temporary table is created (in the original location!), the symlink is destroyed, and the temporary table is renamed. Your “optimization” is lost. Striping (with RAID) is usually a far better optimization. Striping a table across…
Post: Learning about MySQL Table Fragmentation
May 1, 2008 By 19 Comments
… the same – OPTIMIZE TABLE tbl – this command recreates the table by writing the new .ibd file (if you’re using innodb_file_per_table=1) which normally would be much less fragmented because it is written at once. Too bad however it requires table…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 4 Comments
…: 1 ticket per row examined + 1 ticket per row deleted ALTER: (2 * rows in the table) – 1 As with any performance optimization effort, you…_file_path=ibdata1:10M:autoextend innodb_file_per_table=1 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 8M innodb_log_files_in_group=2 innodb_log_file…
Post: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
… becomes managable, your ALTER TABLE or OPTIMIZE TABLE now locks small table for few seconds rather than giant 100GB table for few hours so can… problems with many tables some are OS and File System dependent, others correspond to Innodb storage engine or using innodb_file_per_table option in…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…file_formatblogpercona.commanual innodb_file_format_checkblogpercona.commanual innodb_file_format_maxblogpercona.commanual innodb_file_per…
Post: MySQL 5.5.8 and Percona Server: being adaptive
December 20, 2010 By 25 Comments
… additional optimization we have for SSDs is big log files. For this run, I used innodb_log_file_size=4G (innodb_log_files_in…_file_path=ibdata1:10M:autoextend innodb_file_per_table=1 innodb_flush_log_at_trx_commit=2 innodb_log_buffer_size=8M innodb_log_files_in_group=2 innodb_log_file…
Post: New SpecJAppServer results at MySQL and Sun.
November 12, 2008 By Leave a Comment
… to boost tmp_table_size to deal with implicit memory tables. innodb_file_per_table – so I assume storing each table in its own file worked better… it is surely better for operations. innodb_log_file_size = 1600M – another Benchmark optimization. Such large logs typically will cause too long…
