… typical question I’m asked. It could by MyISAM key_buffer or Innodb innodb_buffer_pool it does not really matter. In both cases… in this case global key hit ratio may be 99.99% but you would still have problems with key buffer efficiency. In the… done. In partuclar you should be looking at Key_reads and Key_writes for MyISAM tables (note this does not include row data…
Post: Beware: key_buffer_size larger than 4G does not work
February 14, 2007 By 17 Comments
… or RAM running MyISAM, so they set key_buffer_size to 16G… and every few days MySQL crashes. Why ? Because key_buffer_size over 4GB… just fine until you have less than 4GB worth of key cache used and then it would crash. If you do… not only fix was not made available but even maximum key_buffer_size was not restricted to safe limit. Former would be…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… we have key_buffer, not “myisam_key_buffer” for example. The other excurse Monty used to make was – it is not really MyISAM specific. key_buffer was also…. key_cache_age_threshold, key_cache_block_size, key_cache_division_limit Key Cache/Key Buffer replacement algorithm configuration variables. Obviously only apply to MyISAM as key_buffer itself…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 27 Comments
… use 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… to do a Key_read as a Key_read_request, what use would the key buffer be anyway? Let’s trust MyISAM‘s creators on… the key_buffer_size until, when the buffer is full, Key_reads/Uptime reduces to a number you’re comfortable with. Set key_buffer_size really…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
…_write_timeout=30 –backlog=128 MyISAM / InnoDB: libexec/mysqld –no-defaults –user=root –key-buffer-size=1500M –innodb-buffer-pool-size=1500M –innodb-log… similar to previous READ_KEY_POINT with only different the values of accessed column is stored in key. MyISAM and InnoDB handle this…
Post: Using MyISAM in production
June 17, 2006 By 35 Comments
… you have decent myisam_sort_buffer_size and large myisam_max_sort_file_size otherwise recovery may be done by key_cache rather than… still watch out. Lack of row cache. MyISAM tables only have indexes cached in key_buffer while data is cached in OS cache… in certain cases by avoiding flushing dirty index blocks from key_buffer to disk, but it also comes at great danger if…
Post: MyISAM Scalability and Innodb, Falcon Benchmarks
October 12, 2007 By 10 Comments
… than with single query. The problem in this case is key buffer contention which unlike popular belief not fully fixed by changes… second confirmation that key_cache is a problem is benchmark run with disabled key_cache (=0). Results for MyISAM with key_buffer_size=0 Threads… is comparison of MyISAM Innodb and Falcon results in the graphical form: I’ve created bug, for MyISAM key cache contention issue and…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
…- MyISAM Does Not Scale, or it does quite well…+———————————————+ | cmd | +———————————————+ | SET GLOBAL test_a.key_buffer_size=39514112; | | SET GLOBAL test_b.key_buffer_size=28390400; | +———————————————+ 2 rows…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 38 Comments
… MySQL needs – these include MySQL buffers – query cache, key_buffer, mysql threads, temporary tables, per thread sort buffer which can be allocated. There are… I only described Innodb Buffer Pool selection for dedicated Innodb system. If you have fair amount of MyISAM, Archive, PBXT, Falcon or…
Post: FreeBSD tests
June 15, 2006 By 15 Comments
… is related to InnoDB’s implementation of syncronious primitives. For MyISAM Suse is stable better. Configuration params: Box: Dual Core Athlon….0.22 params for InnoDB: –innodb-buffer-pool-size=500M –max-connections=500 params for MyISAM: –key-buffer-size=500M –max-connections=500 –skip…
