… question during Interview for people to work as MySQL DBAs or be involved with MySQL Performance in some way is to ask… improvements. key_buffer_size – Very important if you use MyISAM tables. Set up to 30-40% of available memory if you use MyISAM tables…. Check however if all of key_buffer is used over time – it is not rare to see key_buffer being set to 4G while combined…
Post: MySQL Server Memory Usage
May 17, 2006 By 21 Comments
… are key_buffer_size, innodb_buffer_pool_size, innodb_additional_memory_pool_size, innodb_log_buffer_size, query_cache_size. If you’re using MyISAM… more for MySQL Server code and various small static buffers. This is memory which you can consider used when you just start MySQL Server…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
…keys: role_id_note key: role_id_note key_len: 4 ref: const rows: 10259274 Extra: Using where; Using… and tuning now. MySQL is conservative about “Using index” -in …buffer pool is effective. There is no workaround, using FORCE-like commands or optimizer_switch flags- we can disable ICP, but not “using…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
… crash: “It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections”. This was never true… * 1024 * 100); Query OK, 0 rows affected (2.34 sec) mysql> select get_lock(@a, 1); +—————–+ | get_lock(@a, 1) | +—————–+ | 1…
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… 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… out key buffer but there is no way to clean Innodb Buffer Pool without restart. For OS Caches on Linux you can use drop…
Post: Using MyISAM in production
June 17, 2006 By 36 Comments
… of items you need to keep into account while using MyISAM tables. Recovery. MySQL was running stable for us, giving us false… of row cache. MyISAM tables only have indexes cached in key_buffer while data is cached in OS cache. It is performance… flushing dirty index blocks from key_buffer to disk, but it also comes at great danger if MySQL Server crashes or power goes…
Post: Side load may massively impact your MySQL Performance
November 13, 2011 By 7 Comments
… understand it we should take a look at the buffer pool contents. mysql [localhost] {msandbox} (information_schema) > select concat_ws(‘.’, t.schema… this test system. It is worth to note MySQL actually uses midpoint insertion for its buffer pool replacement policy . Unfortunately by default it… see now the small table PRIMARY KEY (which is what used by benchmark) is not pushed from buffer pool at all. For advanced…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
…. I used 10mil row table which would look as following in terms of data and index size: mysql> …_index_creation=1 which builds table with primary key only when creating a table and when builds … required more IO than if only 128MB were used for buffer pool. The difference was rather small – less …
Post: What MySQL buffer cache hit rate should you target
June 29, 2006 By 5 Comments
… good for optimal MySQL Performance” is typical question I’m asked. It could by MyISAM key_buffer or Innodb innodb_buffer_pool it does… in this case global key hit ratio may be 99.99% but you would still have problems with key buffer efficiency. In the… SHOW INNODB STATUS for Innodb tables. In MySQL 5.0+ you can also use Innodb_data_reads, and Innodb_data_writes counters…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… index is UNIQUE the drawback is insert buffer will not be able to be used, which can be important for large, heavily… primary key significantly fragmented. I also would note there are some MySQL optimizer restrictions in how well it can deal with primary key… more details. Q: how mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table…
