… my tables to innodb and currently going into queries and db optimization. Could someone really confirm where should i use Innodb. Big table… be better once i tuned InnoDb settings? and add query cache? Thanks a lot for all the great info. Regards Jerome
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… you have the following query executed on the InnoDB table: SELECT non_key_column FROM tbl WHERE key_column…5.6 and MariaDB 5.5. Note that query cache is disabled during these benchmark runs and that the…rowid_refills = 0, which means that the buffer was big enough and only 1 MRR range scan was needed…
Post: Query Profiling with MySQL: Bypassing caches
September 12, 2007 By 6 Comments
… is also table_cache both MySQL side and Internal Innodb one which can affect query execution speed. OS Caches Operation Systems typically cache file… RAID cache is the one but more important SAN caches which can be pretty big. So How can you bypass these caches? For MySQL Caches… to clean all of the caches. You can do FLUSH TABLES to clean MySQL table cache (but not Innodb table meta data) or you can…
Post: Using VIEW to reduce number of tables used
September 28, 2007 By 12 Comments
… significant because table_cache can’t be made large enough and so a lot of table reopens needs to happen which requires table header… in the base table – generally there should be only few places when insert happens so it should not be big problem. Technically if you can’t make inserts to the base table but can have them going to the different table…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…cache_sizeblogpercona.commanual max_connect_errorsblogpercona.commanual max_connectionsblogpercona.commanual max_delayed_threadsblogpercona.commanual max_error_countblogpercona.commanual max_heap_table…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 27 Comments
… Key_reads? This assumption leads to “a cache miss is significantly slower than a cache hit,” which makes sense. If it were… amount of memory based on how important those tables are to you, how big the indexes are on disk, and so on… are more expensive than hits, then you care about cache misses, not cache hits. A Key_read is not guaranteed to be…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
…: 85368 Index_length: 154624 It’s a big fact table and two fairly small dimension tables, which is normal. Here is the query… dimension tables doesn’t slow the query perceptibly, because these tables are small and they stay in memory, in the OS cache. (They… to the dimension tables will go to disk instead of being read from cache. Now the STRAIGHT_JOIN becomes a table scan of…
Post: Caching techinques
August 8, 2006 By 11 Comments
… cache once underlying information is updated. This is how MySQL Query Cache works by removing all queries derived from the table if that table… about this approach is – you cache operations have constant complexity – you do not have to do big invalidation clean up on updates… it can work in practice. Lets say you’re running big blogging site which has many blogs published. You may store…
Comment: MySQL Query Cache
September 30, 2009 By 69 Comments
… LK, For your big table, every time it is updated, or inserted into, all queries in the cache which use that table, will be invalidated. Remember the Query Cache caches the result set packets, its not caching query execution… which use your big volatile table, and add SQL_NO_CACHE to them. This means mysql wont bother wasting time caching the results, only…
Post: State of MySQL Market and will Replication live ?
April 9, 2008 By 29 Comments
… started deploying their custom solutions – Sharding and Caching. Indeed Memcache is the most known caching technologies today, at large extent because Brad… and processing was using non SQL and not systems – Google Big Table, Amazon Dynamo and SimpleDB, Hadoop, CouchDB which comes computed with…
