…cache on write heavy workload. OK, so that was all about the configuration of the EC2 instance and MySQL…float NOT NULL, PRIMARY KEY (`transactionid`), KEY `marketsegment` (`price`,`customerid`), KEY `registersegment` (`cashregisterid`,`price`,`customerid`), KEY `pdc` (`price`,`dateandtime`,`…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… 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 more memory than… Check for Table Cache Related Allocations There are cases when MySQL will allocate a lot of memory for table cache, especially if you…
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
… as a first-class member of the MySQL family of databases. Finally, High Performance MySQL, Third Edition mentions MariaDB in many places… segmented key cache for improved scalability That’s really just a sampling. A good starting point for learning more is the MySQL versus… more choice than ever — there are three great versions of MySQL that suit different needs and personalities, with different characteristics, and…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… post. Now let me briefly explain these optimizations. Batched Key Access Traditionally, MySQL always uses Nested Loop Join to join two or…’ optimizer_switch=’join_cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache_bka=on’ join_cache_level=8 join_buffer…
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: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… the combined size of the secondary key tuples fetched, and N is the buffer size. In MySQL 5.6 the buffer size… case of MySQL 5.5 and ~5 hours in case of MySQL 5.6 and MariaDB 5.5. Note that query cache is… where b. Good Plan id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE orders range PRIMARY…
Comment: Too many connections? No problem!
March 15, 2012 By 28 Comments
…/log/mysql-slow-queries.log long_query_time=15 log-queries-not-using-indexes 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…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
… but Key Cache Mutex will still hunt you. If you aware of MySQL history you may think Key Cache scalability was fixed with new Key Cache in MySQL… to mysql directly as explained here to create key caches. Now you can use much more simple command to assign tables to the key caches: mysql> select concat(“CACHE INDEX “,table…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 27 Comments
… tuning MySQL‘s key cache hit ratio is wrong, even when you hear it from experts. There are two major problems with the key… key buffer, key_buffer, and key cache interchangeably. However, I will be careful about the difference between “rate” and “ratio”. In this article, the key cache… scientific and authoritative — the “key cache hit ratio.” This is a shame. When you are new to MySQL, trying to configure my.cnf…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual, …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_write_requestsblogpercona.commanual Key…
