… PRIMARY KEY (`transactionid`), KEY `marketsegment` (`price`,`customerid`), KEY `registersegment` (`cashregisterid`,`price`,`customerid`), KEY `pdc`…performance is using partitioning to reduce the size of the B+tree, having multiple buffer pool instances to reduce contention problems caused by buffer…
Comment: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
… The primary key and each secondary key each incur nearly the same cost, and the performance difference is …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 …
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 …the join_buffer_size and the read_rnd_buffer_size/mrr_buffer_size and both of these buffers should be increased appropriately for the best possible performance gain…
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… MRR range scans had to be performed. As in the table above you can with default buffer size of 256K, MariaDB 5.5… further when buffer size is set large enough so that the index tuples fit in the buffer. But there is no performance improvement when…
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: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 27 Comments
… might be causing a performance problem. But I haven’t shown you how to choose an appropriate key_buffer_size. Let’s look at…. 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…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
… the sum key buffer size (4000000000 in this case) and actual index size: select concat(“SET GLOBAL “, t.table_schema,”_”,t.table_name,”.key_buffer_size=”,round… (“a”,”b”); +———————————————+ | cmd | +———————————————+ | SET GLOBAL test_a.key_buffer_size=39514112; | | SET GLOBAL test_b.key_buffer_size=28390400; | +———————————————+ 2 rows in set (2 min 31… about 20 tables I’ve seen performance gains as much as 10x compared to using single shared key cache of the same size.
Post: What exactly is read_rnd_buffer_size
July 24, 2007 By 4 Comments
Looking for documentation for read_rnd_buffer_size you would find descriptions such as “The read_rnd_buffer_size is used after a sort… this can improve performance” which is cool but it does not really tell you how exactly read_rnd_buffer_size works as well… sort is performed it can be performed having only row pointers together with key value – which are offsets for MyISAM and primary key values…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…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: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… Shard-Query will perform on each database. To do the size comparison, I used the du utility: InnoDB file size on disk: 42GB…-use-sys-stats-table innodb-write-io-threads=4 join-buffer-size=16M key-buffer-size=64M local-infile=on lock-wait-timeout=300 log-error=/var/log/mysqld-innodb.log max-allowed-packet=1M net-buffer…
