… why do not just default at 2000 ? innodb_file_per_table =1 Great! We found it is better choice in 95%+ of installations innodb_log_file_size = 48M This is still too small for any real load but a lot better than 5MB. Good to see innodb log re-sizing is made automatic now. innodb_old_blocks_time = 1000 Great. This is…
Post: Tuning for heavy writing workloads
October 14, 2009 By 9 Comments
…innodb_buffer_pool_size = 16G innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 128M innodb_log_file_size = 512M innodb_log_files… based on these performances using XtraDB specific options! Why do you make XtraDB slower than Plugin?…
Post: Disaster: MySQL 5.5 Flushing
September 18, 2011 By 27 Comments
…InnoDB configured for this hardware innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_log_buffer_size = 16M innodb_buffer_pool_size = 52G innodb_log_file_size = 1900M innodb_log_files…. Why this happens: referring to previous InnoDB Flushing theory and solutions post, InnoDB …
Post: Which adaptive should we use?
September 15, 2009 By 14 Comments
…innodb_buffer_pool_size = 16G innodb_max_dirty_pages_pct = 90 innodb_log_file_size = 512M innodb_log_files_in_group = 2 innodb_io_capacity = 4000 innodb_read_io_threads = 8 innodb… new “innodb_adaptive_checkpoint = estimate” seems to be more stable than before. The next question is, “Why is estimate…
Post: XtraDB: The Top 10 enhancements
August 13, 2009 By 5 Comments
… on building our training workshop. Last week I talked about why you don’t want to shard. This week I’m… InnoDB, the crash recovery process is sometimes best measured in hours and days – this restricts users to using very small transaction log files (innodb_log_file_size), which is worse for performance. Â In a simple test, XtraDB recovered ten times faster (see docs). InnoDB Plugin…
Post: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 17 Comments
…InnoDB options innodb_buffer_pool_size = 55G innodb_log_file_size = 1G innodb_log_files_in_group = 4 innodb_buffer_pool_instances = 4 innodb_adaptive_flushing = 1 innodb_adaptive_flushing_method = estimate innodb_flush_log…Per Second” on the y-axis. The reason why I have chosen to show “millions of …
Post: XtraDB benchmarks - 1.5X gain in IO-bound load
December 18, 2008 By 30 Comments
…size = 2G innodb_data_file_path = ibdata1:10M:autoextend innodb_file_io_threads = 4 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 8M innodb_log_file_size = 256M innodb_log_files… CPUs better. Finally let me show you why I took NOOP IO scheduler instead of …
Comment: How to calculate a good InnoDB log file size
December 22, 2008 By 38 Comments
… VARIABLES LIKE ‘innodb_log_file_size‘; +———————-+———–+ | Variable_name | Value | +———————-+———–+ | innodb_log_file_size | 536870912 | +———————-+———–+ 1 row in set (0.00 sec) The error message in the log states that… 966363956. I’m not sure why there capacity is a bit smaller than two times the innodb_log_file_size, but I assume there are…
Post: Replaying database load with Percona Playback
October 25, 2012 By 9 Comments
….1 –mysql-user=root –mysql-schema=my_db –query-log-file=slow.log Executed 429467 queries Spent 00:00:59.668649 executing queries… ol_o_id < 3415 AND ol_o_id >= (3415 – 20); Why there are some queries returns different number of rows? Usually… going to ignore that. Second try, add 2GB of InnoDB Log Files (innodb_log_file_size=1G, innodb_log_files_in_group=2): Executed 429467 queries Spent 00:00:56…
Comment: InnoDB Flushing: a lot of memory and slow disk
April 1, 2011 By 57 Comments
… allowable to change is innodb_log_file_size, as it will define balance between performance and recovery time. So setting innodb_log_file_size as small as possible… stable.. around 5-10 NOTPM > Then I’m curious: >– why XtraDB “estimate†mode was not ok here?.. We still… test without O_DIRECT ?.. Nope > – did you test with innodb_flush_log_at_trx_commit=1 or other?.. Nope
