…extra optimizations by optimizer knowing index is UNIQUE the drawback is insert buffer will not…key significantly fragmented. I also would note there are some MySQL optimizer restrictions in how well it can deal with primary key…them which are accessed frequently. The size of this “working set” can …
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
…that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections”. This was never …the memory needed to parse and optimize and execute queries; mysqld creates … Query OK, 0 rows affected (2.34 sec) mysql> select get_lock(@a, 1); +—————–+ | get_lock(@a, 1…
Post: Aligning IO on a hard disk RAID – the Benchmarks
June 9, 2011 By 13 Comments
…Optimal WT, RA PhysiclDev Type State Errors Vendor Model Size …MySQL configurations, I didn’t try out different MySQL…size = 16M max_heap_table_size = 64M thread_cache_size = 32 query_cache_size = 0 tmp_table_size = 64M key_buffer_size = 8M bulk_insert_buffer_size = 8M myisam_sort_buffer_size…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
…pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_cost_based=off’ optimizer_switch=’batched_key_access=on’ join_buffer_size=6M read_rnd_buffer_size=6M Also…MySQL 5.6 MySQL 5.6 w/ join_buffer_size=6M & read_rnd_buffer_size=6M MariaDB 5.5 MariaDB 5.5 w/ join_buffer_size=6M & mrr_buffer_size…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…’ optimizer_switch=’mrr_sort_keys=on’ (only on MariaDB 5.5) optimizer_switch=’mrr_cost_based=off’ read_rnd_buffer_size=4M (only on MySQL 5.6) mrr_buffer_size=4M (only on MariaDB 5.5) We have turned off ICP optimization for the…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one… post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5… for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l_partkey`,`l_quantity`,`l_shipmode…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
…MySQL 5.1.14-beta sources for MyISAM / InnoDB and MySQL 5.1.14-falcon bitkeeper tree bk://mysql.bkbits.net/mysql… –user=root –key-buffer-size=1500M –innodb-buffer-pool-size=1500M –innodb-log-file-size=100M –innodb-thread…’ is very common optimization we use in our practice. READ_KEY_POINT_NO_DATA…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 75 Comments
… optimization. I call this Innodb Performance Optimization…MySQL out of memory. MySQL Innodb Settings The most important ones are: innodb_buffer_pool_size…performance innodb_log_buffer_size=4M 4M is…key, having primary key in all indexes (so keep primary key short), fast lookups by primary keys…
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
…_load_master_table’, ’0′ ‘Com_lock_tables’, ’102′ ‘Com_optimize‘, ’0′ ‘Com_preload_keys‘, ’0′ ‘Com_prepare_sql’, ’0′ ‘Com_purge’, ’0…/mysql/slow-queries.log max_connections=650 open_files_limit=6144 max_connect_errors=10000 interactive_timeout=3600 wait_timeout=3600 key_buffer_size…
Post: Predicting how long data load would take
May 24, 2007 By 14 Comments
… set for optimal load speed. Depending on load type MyISAM may benefit from bulk_insert_tree_size increase myisam_sort_buffer_size or key_buffer_size increase…_buffer_pool_size and large innodb_log_file_size to perform load effectively Load Options There two main ways to load data in MySQL…
