June 19, 2013

Post: What MySQL buffer cache hit rate should you target

June 29, 2006 By 5 Comments

…What cache hit rate is good for optimal MySQL Performance” is typical question I’m asked. It could by MyISAM key_buffer or… was OS or SAN cache hit rate – something you would not see otherwise. The bad luck is – MySQL currently does not provide… should be looking at Key_reads and Key_writes for MyISAM tables (note this does not include row data access, so…

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

Post: Cache Performance Comparison

August 9, 2006 By 74 Comments

… are: Cache Type Cache Gets/sec Array Cache 365000 APC Cache 98000 File Cache 27000 Memcached Cache (TCP/IP) 12200 MySQL Query Cache (TCP/IP) 9900 MySQL Query Cache (Unix… MySQL Cache to work CREATE TABLE `test` ( `k` varchar(60) NOT NULL default ”, `val` varchar(255) default NULL, PRIMARY KEY (`k`) ) ENGINE=MyISAM…

Filed Under: Insight for Developers Tagged With:

Post: MySQL 5.5 and MySQL 5.6 default variable values differences

February 21, 2013 By 8 Comments

… more cores available these days. query_cache_type and query_cache_size. The behavior is “no cache” by default still but it is… size of 1MB while in MySQL 5.5 and before it was “ON” by default with query cache size of 0 which… getting MyISAM because Innodb was disabled for some reason was very error prone gotcha. Note this is as far as MySQL 5…

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

Post: Beware large Query_Cache sizes

March 23, 2007 By 3 Comments

… of consulting projects with MySQL Query Cache being source of serious problems. One would see queries both for MyISAM and Innodb tables mysteriously… time check how many queries do you have in cache – Qcache_queries_in_cache – if it is in hundreds of thousands it… many cases you may want to disable query cache at all and use external cache such as memcached which does not have…

Filed Under: Insight for DBAs Tagged With:

Post: Using MyISAM in production

June 17, 2006 By 36 Comments

… “traditional” databases you’re likely to be shocked by MyISAM behavior (and default MySQL behavior due to this) – it will be corrupted… items you need to keep into account while using MyISAM tables. Recovery. MySQL was running stable for us, giving us false sense… out. Lack of row cache. MyISAM tables only have indexes cached in key_buffer while data is cached in OS cache. It is performance issue…

Filed Under: Insight for DBAs Tagged With: ,

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

February 26, 2013 By 1 Comment

….ibd -rw-rw—-. 1 mysql mysql 98304 Feb 20 16:09 FTS_00000000000000ad_DELETED_CACHE.ibd -rw-rw—-. 1 mysql mysql 98304 Feb 20 16…:17 dir_test_myisam.frm -rw-rw—-. 1 mysql mysql 155011048 Feb 19 17:17 dir_test_myisam.MYD -rw-rw—-. 1 mysql mysql 153956352 Feb…:11 seo_test_myisam.frm -rw-rw—-. 1 mysql mysql 21561096 Feb 20 16:11 seo_test_myisam.MYD -rw-rw—-. 1 mysql mysql 14766080 Feb…

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

Post: What to tune in MySQL Server after installation

September 29, 2006 By 118 Comments

… you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload – remember MyISAM uses OS cache to cache the… you do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which… MySQL Server crashes. Value 2 only cause data loss with full OS crash. table_cache – Opening tables can be expensive. For example MyISAM…

Filed Under: Insight for DBAs Tagged With:

Post: MySQL Crash Recovery

July 30, 2006 By 30 Comments

… care of or which may seriously affect MySQL Performance. MyISAM Corruption – If you’re writting to MyISAM tables there is very large chance… very good idea to run MySQL with myisam_recover option which will make sure all improperly closed MyISAM tables are checked first time… – If you restart MySQL server its caches (key_buffer, innodb_buffer_pool, query_cache,table_cache) are cleaned, so may be OS caches. This may…

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

Post: MySQL Server Variables - SQL layer or Storage Engine specific.

June 8, 2006 By 14 Comments

…does it mean MyISAM variables are not important ? Not really. You still have some tables in MyISAM format in “mysql” database. Plus …cache_age_threshold, key_cache_block_size, key_cache_division_limit Key Cache/Key Buffer replacement algorithm configuration variables. Obviously only apply to MyISAM …

Filed Under: Insight for DBAs Tagged With: ,

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

February 28, 2010 By 28 Comments

… that somewhere around of 99% of advice about tuning MySQL‘s key cache hit ratio is wrong, even when you hear it… be anyway? Let’s trust MyISAM‘s creators on this one, because they designed a cache hit to be faster than a… and authoritative — the “key cache hit ratio.” This is a shame. When you are new to MySQL, trying to configure my.cnf…

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