…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…
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: 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…
Post: Beware: key_buffer_size larger than 4G does not work
February 14, 2007 By 17 Comments
… MyISAM, so they set key_buffer_size to 16G… and every few days MySQL crashes. Why ? Because key_buffer_size over 4GB in size is not really supported (checked with latest and greatest MySQL Enterprise 5…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
When examining MySQL configuration, we quite often want to know how various buffer sizes are used. This matters because some buffers (sort_buffer_size for example… needed (key_buffer_size). There are many examples of this. What about join_buffer_size? I saw a my.cnf with a 128M join_buffer_size the…
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…
