…cache on write heavy workload. OK, so that was all about the configuration of the EC2 instance and MySQL… PRIMARY KEY (`transactionid`), KEY `marketsegment` (`price`,`customerid`), KEY `registersegment` (`cashregisterid`,`price`,`customerid`), KEY `pdc` … B+tree, having multiple buffer pool instances …
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… contention, better diagnostics, improvements to partitioning, and the addition of multiple InnoDB buffer pools. Best of all, MariaDB 5.5 might…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…same page multiple times into the buffer pool. So considering this drawback, Batched Key Access (BKA)…_cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache_bka=on’ join_cache_level…the status counters. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ join_buffer_size…
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… also use multiple Key Caches to reduce or virtually eliminate key cache contention. Too bad you can only map single table to single key cache – it would…
Post: Crashes while using MyISAM with multiple key caches
July 18, 2009 By Leave a Comment
… couple of years I have ran into random MySQL crashes in production when multiple key caches were used. Unfortunately this never was frequent or… related to multiple key caches but to online key cache resize. It is just this code most actively used in case you’re using multiple key caches. It…
Post: Getting annoyed with MyISAM multiple key caches.
May 1, 2009 By 11 Comments
As I’ve wrote few times using multiple key caches is a great way to get CPU scalability if you’… problem with multiple key caches and mapping of tables to the different files is – there is no way to see the existing key caches, their… this is surely sad to see the feature of multiple key caches first introduced in MySQL 4.1 was newer quite polished in terms…
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… possibility of having multiple key caches. But I’ll give the simple version here. In my opinion, you should choose a key_buffer_size… scientific and authoritative — the “key cache hit ratio.” This is a shame. When you are new to MySQL, trying to configure my.cnf…
Post: Beware of MyISAM Key Cache mutex contention
August 12, 2008 By 10 Comments
… rate. Hundreds of millions rows are loaded daily into single MySQL instance with bursts up to 100K of records/sec which… discussed MyISAM key cache has serious mutex contention issue as there is global mutex which is held for the time of key_block being copied from key_cache to the thread local space. Happily MyISAM allows you to create multiple key caches . We use…
Post: MySQL Server Memory Usage
May 17, 2006 By 18 Comments
… to be impractical. Here is why: List of rarely considered MySQL Server Memory Requirements Thread buffers can be allocated more than… local values to run some queries. There can be mutiple key caches. Multiple key caches can be created to accomodate query executions Query Parsing and…
Post: High Rate insertion with MySQL and Innodb
January 7, 2011 By 16 Comments
… it used MyISAM and the system was built using multiple tables. Using multiple key caches was the good solution at that time and we… spinlocks (per oprofile), which went down to about 15% in MySQL 5.5.8 with 8 concurrent threads. This both shows… has good suggestions on tuning MySQL 5.5 and this is what I used for start. Using multiple buffer pools with innodb…
