… innodb_buffer_pool_instances=16 metadata_locks_hash_instances=32 table_open_cache_instances=8 sort_buffer_size=128k read_rnd_buffer_size=8M join_buffer_size=8M default_tmp_storage_engine=myisam tmpdir=/dev/shm innodb_undo_logs=32 innodb…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… innodb_flush_method = O_DIRECT innodb_log_buffer_size = 256M innodb_flush_log_at_trx_commit = 1 innodb_buffer_pool_size = 36G innodb_log_file_size = 4G innodb…_cache_size = 16M max_heap_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1000 query_cache_size…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
…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…_size = 16M max_heap_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1000 query_cache_…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…_rnd_buffer_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…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
…_switch=’join_cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache_bka=on’ join_cache_level=8 join_buffer_size=6M mrr_buffer… 5.5 w/ join_buffer_size=6M & mrr_buffer_size=6M MariaDB 5.5 Hash Join Disabled w/ join_buffer_size=4M & mrr_buffer_size=4M Created… are values for Innodb_buffer_pool_reads and Innodb_data_read. We can see that with appropriately sized buffers less no. of Innodb_buffer_pool_reads…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… thread is not runniing inside innodb kernel status could be “waiting in InnoDB queue” or “sleeping before joining InnoDB queue”. Latest one is quite… can check if your innodb_log_buffer_size is optimal – if you see more than 30% of log buffer size being unflushed you… Buffer pool hit rate 999 / 1000 This section shows Buffer pool activity and memory usage. You can see total memory allocated by Innodb…
Post: XtraDB/InnoDB CPU bound benchmarks on 24cores server
December 29, 2008 By 6 Comments
…=2 innodb_log_buffer_size=8M innodb_log_files_in_group=2 innodb_log_file_size=512M innodb_status_file=0 innodb_thread_concurrency=0 innodb_io_capacity=1000 innodb_write_io_threads = 16 innodb_read_io_threads = 16 join_buffer_size…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 75 Comments
… swap out MySQL out of memory. MySQL Innodb Settings The most important ones are: innodb_buffer_pool_size 70-80% of memory is… looking for more details, check out detailed guide on tuning innodb buffer pool innodb_log_file_size – This depends on your recovery speed… to use it in joins), large unpacked indexes (try to be easy on indexes). With these basic innodb performance tunings you will…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
….commanual innodb_buffer_pool_instancesblogpercona.commanual Innodb_buffer_pool_pages_datablogpercona.commanual Innodb_buffer_pool_pages_dirtyblogpercona.commanual Innodb_buffer_pool_pages_flushedblogpercona.commanual Innodb_buffer_pool_pages_freeblogpercona.commanual Innodb_buffer… innodb_use_native_aioblogpercona.commanual innodb_use_sys_mallocblogpercona.commanual innodb_versionblogpercona.commanual innodb_write_io_threadsblogpercona.commanual insert_idblogpercona.commanual interactive_timeoutblogpercona.commanual join_buffer…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…5.6.10-linux-glibc2. | | MAX_ALLOWED_PACKET | 1048576 | 4194304 | | JOIN_BUFFER_SIZE | 131072 | 262144 | | TMPDIR | /mnt/data/sandboxes/msb_5_5…30 | 5.6.10 | | INNODB_CONCURRENCY_TICKETS | 500 | 5000 | | INNODB_PURGE_THREADS | 0 | 1 | | INNODB_OPEN_FILES | 300 | 2000 | | INNODB_DATA_FILE_PATH | …
