… half a TB innodb_buffer_pool size and I …buffer pool to be getting close to 100 millions of entries. So as result of this bug – if you’re running innodb…buffer pool. If you’re using Innodb tables as transient tables where you create and drop them frequently you may avoid such design or use innodb…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
…(`country_id`,`state_id`,`city`) ) In this benchmark we used only read (SELECT) queries with different typical data …backlog=128 MyISAM / InnoDB: libexec/mysqld –no-defaults –user=root –key-buffer-size=1500M –innodb-buffer-pool-size=1500M –innodb-log-file-size=100M –innodb-thread-concurrency=8…
Post: InnoDB benchmarks
January 3, 2007 By 15 Comments
…in InnoDB that were fixed: Thread trashing issues with count of theads 100+. In this case performance of InnoDB degraded dramatically…–with-innodb and next startup options were used: libexec/mysqld –no-defaults –user=root –innodb-buffer-pool-size=1500M –innodb-log-file-size=100M –innodb-thread-…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… 5.6 with a 4G buffer pool instead of the default 128M Rationale: Since O_DIRECT is not used by default, the file…) Here are the contents of the buffer pool in pages afterwards: mysql> select sq.*, pages / (@@innodb_buffer_pool_size / 16384) * 100 pct_buffer_pool from ( select table_name, index…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…, the InnoDB buffer pool size is set to 6G and the buffer pool was warmed up, so that the relevant pages were already loaded in the buffer pool…_custkey i_o_orderdate 4 NULL 232722 100.00 Using where; Rowid-ordered scan; Using temporary; Using filesort 1 SIMPLE customer eq_ref PRIMARY…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…Innodb uses fuzzy checkpointing so this line hold log sequence, all changes up to which has been flushed from buffer pool…BUFFER POOL AND MEMORY ———————- Total memory allocated 4648979546; in additional pool allocated 16773888 Buffer pool size 262144 Free buffers…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 118 Comments
… innodb_buffer_pool_size This is very important variable to tune if you’re using Innodb tables. Innodb tables are much more sensitive to buffer size compared…enough. Smaller installations should use smaller values. innodb_flush_log_at_trx_commit Crying about Innodb being 100 times slower than MyISAM …
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…INNODB_AUTOEXTEND_INCREMENT | 8 | 64 | | MAX_CONNECT_ERRORS | 10 | 100 | | SORT_BUFFER_SIZE…innodb_old_blocks_time now set to 1000 making Innodb Buffer Pool Size scan resistant by default. Very welcome change ! thread_cache_size…
Post: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 17 Comments
…used: ## 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…second, with noticeable spikes after every 100 million rows inserted. I noticed…
Comment: What to tune in MySQL Server after installation
December 15, 2009 By 118 Comments
… problems are, the mysql process always use 1 cpu core at 1 time and it used 100% of the cpu resource. I’am… innodb_data_home_dir = /var/lib/mysql/ #innodb_data_file_path = ibdata1:100M:autoextend set-variable = innodb_buffer_pool_size=100M set-variable = innodb_additional_mem_pool_size=10M innodb…
