…come to the MySQL configuration. MySQL Configuration I used …buffer_pool_size = 55G innodb_log_file_size = 1G innodb_log_files_in_group = 4 innodb_buffer… PRIMARY KEY (`transactionid`), KEY `marketsegment` (`price`,`customerid`), KEY `registersegment` (`cashregisterid`,`price`,`customerid`), KEY `pdc`…
Comment: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
…increment key, I would expect Inno to fill the B-tree pretty efficiently. With a 55GB buffer pool,… would hit a memory wall. Given that Memory sizes are perhaps 10 times bigger than they …that Mark wrote http://bazaar.launchpad.net/~mdcallag/mysql-patch/mytools/annotate/head%3A/bench/ibench/iibench….
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… MySQL is troubleshooting memory usage. The problem usually starts like this – you have configured MySQL to use reasonable global buffers, such as innodb_buffer_size, key_buffer_size etc, you have reasonable amount of connections but yet MySQL takes much…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…=’mrr_cost_based=off’ optimizer_switch=’batched_key_access=on’ join_buffer_size=6M read_rnd_buffer_size=6M Also note that the following changes… MySQL 5.5 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
… is the combined size of the secondary key tuples fetched, and N is the buffer size. In MySQL 5.6 the buffer size used by MRR can… read_rnd_buffer_size, while MariaDB introduces a different variable to control the MRR buffer size mrr_buffer_size. Both buffer sizes default to 256K in MySQL 5.6…_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…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take… for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l_partkey`,`l_quantity`,`l_shipmode…
Comment: Too many connections? No problem!
March 15, 2012 By 28 Comments
… query_cache_size=32M thread_cache_size=30 table_cache=4096 join_buffer_size=6M key_buffer_size=12M server-id = 1 log-bin=/data/mysql/drbd expire_logs_days = 5 max_binlog_size = 100M [client] socket=/data/mysql/mysql…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… amazing MySQL manual, especially the option and variable…key_buffer_sizeblogpercona.commanual key_cache_age_thresholdblogpercona.commanual key_cache_block_sizeblogpercona.commanual key_cache_division_limitblogpercona.commanual Key_read_requestsblogpercona.commanual Key_readsblogpercona.commanual Key…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 27 Comments
…. Raise the key_buffer_size until, when the buffer is full, Key_reads/Uptime reduces to a number you’re comfortable with. Set key_buffer_size really big… to your IO system’s capabilities. MySQL doesn’t have good instrumentation for scientifically choosing a key_buffer_size setting, but there are many…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
…. So first MySQL versions did not bother naming variables with storage engines prefix. This is why we have key_buffer, not “myisam_key_buffer” for… by SQL layer so applies to all storage engines. key_buffer_size Key Buffer used to buffer Index blocks (row data is not cached). Used only…
