…. A star schema presents some unique challenge to the database optimizer. The SSB benchmark consists of four sets of queries. Each…_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_pool_instances=16 metadata_locks_hash_instances=32 table_open…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… the scope of descriptors optimization. Server Configuration: [mysqld] user=root port=3306 innodb_status_file=0 innodb_data_file_path=ibdata1:100M:autoextend…_buffer_pool_size = 52G innodb_log_file_size = 2000M innodb_log_files_in_group = 2 innodb_file_per_table = true innodb_read_io_threads = 8…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
…d determined that configuration C appeared to produce the most optimal results, I moved on to reviewing FlashCache performance versus …below: #####fixed innodb options innodb_file_format = barracuda innodb_buffer_pool_size = 4G innodb_file_per_table = true innodb_data_file_path = ibdata1:100M innodb…
Post: Percona XtraBackup 2.0.7 for MySQL available for download
May 6, 2013 By 1 Comment
… has been merged. Fixed by removing files corresponding to tables that are missing in the incremental …tables and slow IO then XtraBackup can spend a lot of time opening all the tablespaces. Optimization…Bug fixed #1130145. Percona XtraBackup didn’t initialize per-thread data in the log copying thread…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 75 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 23 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 20 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: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… 5.5.24 Configuration innodb_file_per_table=1 innodb_file_format=barracuda innodb_log_file_size=512M innodb_log_files_in_group=2 innodb_flush…_size=32M optimizer_switch=’batched_key_access=on’ join_buffer_size=32M MariaDB 5.5.20 Configuration innodb_file_per_table=1 innodb_file_format=barracuda innodb_log_file_size=512M innodb_log_files_in_group=2 innodb_flush…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… innodb_file_per_table is now ON by default. This is very big change and is good one. We have been running innodb_file_per_table=1 for most of workloads for years now. The exceptions have been when you have large number of tables or if you do many create/drop of innodb tables. optimizer_switch is the catch all variable for a lot of optimizer…
