June 18, 2013

Post: MySQL Query Cache

July 27, 2006 By 70 Comments

…is other story. Lets talk a bit about features and limitations of Query Cache: Transparent Caching – Caching is fully transparent to the application, and what is…

Filed Under: Insight for DBAs Tagged With:

Post: Choosing innodb_buffer_pool_size

November 3, 2007 By 39 Comments

… also need some cache. The number can be a lot different depending on system workload but generally I’d see values from… do not want OS to cache what Innodb is caching already. Innodb cache is more efficient compared to OS cache because there is no copying, due to adaptive… that pages in case of memory pressure. But what is more important Innodb algorithms are finely tuned with consideration what is in memory and what is on disk…

Filed Under: Insight for DBAs Tagged With:

Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5

April 4, 2012 By 4 Comments

…additional optimization that is used in conjunction with BKA and that is MRR Key-ordered Scan. Let’s see what this optimization actually is. Key-ordered… optimizer_switch=’join_cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache_bka=on’ join_cache_level=8 join_…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , , , , , , , , ,

Post: How much overhead is caused by on disk temporary tables

August 16, 2007 By 26 Comments

… worse performance for large data sets. So I decided to see what happens if I create a larger table – 4M rows. I… you can see there is a lot of dirty blocks flushing going on with pretty random IO. Clearly OS write caching is not effective… than MyISAM on disk table even in case temporary table is small enough to fit in OS cache and key_buffer. My next intension…

Filed Under: Benchmarks, Insight for DBAs Tagged With:

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… While it is true that InnoDB full-text search in MySQL 5.6 (part 1) in MySQL 5.6 is syntactically … Feb 20 16:09 FTS_00000000000000ad_BEING_DELETED_CACHE.ibd -rw-rw—-. 1 mysql mysql 98304 Feb … run an OPTIMIZE or two, delete some data, and see what happens: mysql> set global innodb_ft_aux_table=’…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Why you should ignore MySQL's key cache hit ratio

February 28, 2010 By 28 Comments

…is widely accepted because of “proof by repeated assertion,” but as you know, that doesn’t make it valid. Let’s see…in the operating system’s cache, then a Key_read is a disk read — but if it is cached…over it? What about InnoDB tuning? You might be wondering, what about InnoDB tuning? What is the …

Filed Under: Insight for DBAs, MySQL Tagged With: , , ,

Post: table_cache negative scalability

November 16, 2009 By 23 Comments

… by lowering table_cache variable. So I decided to investigate what is really happening here. The “common sense” approach to tuning caches is to get… to see if the problem was fixed in MySQL 5.1 – in this version table_cache was significantly redone and split in table_open_cache and… problem is real and it is not fixed even in Performance focused MySQL 5.4. As we can see large table_cache (or table_open_cache…

Filed Under: Benchmarks Tagged With:

Post: Getting annoyed with MyISAM multiple key caches.

May 1, 2009 By 11 Comments

… is key cache parameters – via structured variables In particular I would like to: See the list of created caches Right now I can create key caches… list what you have created. See the mappings I can now map tables to be cached in different key caches but there is no way to see the current mappings or to see where each given table is cached. See other key cache stats…

Filed Under: Insight for DBAs Tagged With:

Post: MySQL 6.0 vs 5.1 in TPC-H queries

March 25, 2008 By 17 Comments

…in 5.1, and 39.39 sec in 6.0 (after warpm-ups and fully cached in key_cache) What is difference in 6.0? Here is… See there are new Extra notes USING INDEX condition; USING MRR. It would be interesting to know what is ‘Using Index condition’, what…

Filed Under: Benchmarks Tagged With:

Post: How does MySQL Replication really work?

January 9, 2013 By 9 Comments

… are still cached in OS cache on the master, so there is not going to be any physical disk reads on the master in order… bump at that point. On the replica Now let’s see what is happening on the slave. When you start replication, two threads… understand what is the reason and that you can usually observe by vmstat. Monitor server activity over time and see if it is “r…

Filed Under: Insight for DBAs, MySQL Tagged With: , ,
« Older Posts
Newer Posts »