… MySQL is troubleshooting memory usage. The problem usually starts like this – you have configured MySQL to use reasonable global buffers, such as innodb_buffer_size, key_buffer_size etc, you have reasonable amount of connections but yet MySQL takes…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
When examining MySQL configuration, we quite often want to know how various buffer sizes are used. This matters because some buffers (sort_buffer_size… the corresponding buffers are allocated only as big as needed (key_buffer_size). There are many examples of this. What about join_buffer_size…
Post: Getting real life query speeds with MySQL
January 10, 2007 By 10 Comments
… check for query performance improvements followed indexing/query changes or MySQL configuration changes our customers often decide to run the query… page caches inside MySQL (key_buffer and innodb_buffer_pool_size) which can be cleared by MySQL restart. Even if you restart MySQL server there is…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 28 Comments
… MySQL‘s key cache hit ratio is wrong, even when you hear it from experts. There are two major problems with the key buffer… 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… to your IO system’s capabilities. MySQL doesn’t have good instrumentation for scientifically choosing a key_buffer_size setting, but there are…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
…. So first MySQL versions did not bother naming variables with storage engines prefix. This is why we have key_buffer, not “myisam_key_buffer” for…. 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…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…`), KEY `DepDelay` (`DepDelay`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT=’Contains all avaialble data from 1988 to 2010′; mysql> use ontime1; Database changed mysql… socket=/tmp/mysql-inno.sock [mysqld] socket=/tmp/mysql-inno.sock default-storage-engine=INNODB innodb-buffer-pool-instances=2 innodb-buffer-pool-size…-sys-stats-table innodb-write-io-threads=4 join-buffer-size=16M key-buffer-size=64M local-infile=on lock-wait-timeout=300…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
…of MySQL history you may think Key Cache scalability was fixed with new Key Cache in MySQL 4… | cmd | +———————————————+ | SET GLOBAL test_a.key_buffer_size=39514112; | | SET GLOBAL test_b.key_buffer_size=28390400; | +———————————————+ 2 rows…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 118 Comments
… question during Interview for people to work as MySQL DBAs or be involved with MySQL Performance in some way is to ask…. Check however if all of key_buffer is used over time – it is not rare to see key_buffer being set to 4G while… with default key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool…
Post: MySQL Server Memory Usage
May 17, 2006 By 21 Comments
… a look at global buffers which are allocated at start and always where – these are key_buffer_size, innodb_buffer_pool_size, innodb_additional_memory_pool_size, innodb_log_buffer_size, query_cache_size. If… privilege should be able to run MySQL server out of memory MyISAM buffers. MyISAM may allocate buffer which is large enough to contain…
Post: Query Profiling with MySQL: Bypassing caches
September 12, 2007 By 6 Comments
…, which are MySQL Caches – Innodb Buffer Pool and Key Buffer are best example though Falcon, PBXT and other storage engines have similar buffers. There is also table_cache both MySQL… clean MySQL table cache (but not Innodb table meta data) or you can do “set global key_buffer_size=0; set global key_buffer_size…
