…_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_cache_instances=8 sort_buffer_size=128k read_rnd_buffer_size=8M join_buffer_size…
Comment: Percona XtraBackup 2.1.3 for MySQL available for download
May 30, 2013 By 3 Comments
… reporting any bugs at http://bugs.percona.com/ key_buffer_size=52428800 read_buffer_size=131072 max_used_connections=13 max_threads=2002 thread… It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 4432877 K bytes of memory Hope that…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…_table_size = 64M server-id = 10 #*** MyISAM Specific options key_buffer_size = 8M read_buffer_size = 1M read_rnd_buffer_size = 4M bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 10G #myisam_max_extra_sort_file_size…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
…_packet = 16M binlog_cache_size = 16M max_heap_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1000 query_cache_size = 0 query_cache…_table_size = 64M serverid = 101 key_buffer_size = 8M read_buffer_size = 1M read_rnd_buffer_size = 4M bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_max_sort_file_size…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
… performance with fast index creation and without it with different buffer pool sizes. Results are pretty interesting. I used modified Sysbench table… look at results of Adding and Dropping column for 128M buffer pool, sized to illustrate the case of index being significantly larger… to memory any more. Creating Index by sort is great Creating index by sort can provide over 20x performance improvement to the…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 39 Comments
… MySQL needs – these include MySQL buffers – query cache, key_buffer, mysql threads, temporary tables, per thread sort buffer which can be allocated. There are… good estimates for this number. Eliminate Double Buffering – This is again very important for buffer pool size choice. You do not want OS…
Post: Innodb Recovery - Is large buffer pool always better ?
July 17, 2007 By 3 Comments
How does Buffer Pool size affects Innodb Performance ? I always expected the effect to be positive, Innodb with large buffer pool to performing… overly smart inserting pages to the flush list – keeping simple sorted linked list, so being forced to scan good portion of… all recoveries out where as 20GB buffer pool is not way more than typical size – I keep buffer pool at 12GB on 16GB…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
… to know how various buffer sizes are used. This matters because some buffers (sort_buffer_size for example) are allocated to their full size immediately as soon as they are needed, but others are effectively a “max size…
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
… “sort%”; +——————-+——-+ | Variable_name | Value | +——————-+——-+ | Sort_merge_passes | 10 | | Sort_range | 0 | | Sort_rows | 10 | | Sort_scan | 1 | +——————-+——-+ 4 rows in set (0.00 sec) set sort_buffer_size… get rid of sort merge passes – may be it is sort merge which is inefficient with large sort_buffer_size ? mysql> set sort_buffer_size=100000000; Query OK…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… is the importance of the size of the buffer used for sorting the secondary key tuples. If the buffer size is large enough only a single range lookup will be needed, however if the buffer size…’ optimizer_switch=’mrr_sort_keys=on’ (only on MariaDB 5.5) optimizer_switch=’mrr_cost_based=off’ read_rnd_buffer_size=4M (only…
