…innodb_buffer_pool_size = 55G innodb_log_file_size = 1G innodb_log_files_in_group = 4 innodb_buffer_pool_…float NOT NULL, PRIMARY KEY (`transactionid`), KEY `marketsegment` (`price`,`customerid`), KEY `registersegment` (`cashregisterid`,`price`,`customerid`), KEY `pdc` (`price`,`dateandtime`,`…
Comment: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
… writing 4 times fewer B-tree values. The primary key and each secondary key each incur nearly the same cost, and the… an auto-increment key, I would expect Inno to fill the B-tree pretty efficiently. With a 55GB buffer pool, we would… RAM, Inno would hit a memory wall. Given that Memory sizes are perhaps 10 times bigger than they were 4 years…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… – 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… 425080 (406936 + 18144) Dictionary memory allocated 2623331 Buffer pool size 7864319 Buffer pool size, bytes 128849002496 Free buffers 1 Database pages 8252672 Old database pages…
Comment: Too many connections? No problem!
March 15, 2012 By 28 Comments
… query_cache_type=1 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…
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 is… 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…
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….5 w/ buffer size 6M MariaDB 5.5 (3) is for MariaDB 5.5 w/ buffer size 6M (Hash Join and Key-ordered Scan disabled….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_tmp_disk…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… 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
…, 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… just fine until you have less than 4GB worth of key cache used and then it would crash. If you do… not only fix was not made available but even maximum key_buffer_size was not restricted to safe limit. Former would be close…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
… “max size” and the corresponding buffers are allocated only as big as 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: 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, and then measure its size…
