…Specific options key_buffer_size = 8M read_buffer_size = 1M read_rnd_buffer_size = 4M bulk_insert_buffer_size = 8M myisam_sort_buffer_…myisam_repair_threads = 1 myisam_recover socket=/var/lib/mysql/mysql.sock user=root skip-grant-tables TokuDB-…out-of-box, but I am ready to tune something if there …
Post: MySQL 5.6 - InnoDB Memcached Plugin as a caching layer
March 29, 2013 By 10 Comments
…standard memcached instance and a minimally tuned MySQL 5.6 instance running the memcached plugin… ($i = 0; $i < 100000; $i++) { $obj = $cache->load(“key_$i”); } } $totalTimeFetch = $timer->stop(); $avgTimeFetch = $totalTimeFetch /…entire data set fits into the buffer pool, so there are no reads from …
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 28 Comments
… tuning MySQL‘s key cache hit ratio is wrong, even when you hear it from experts. There are two major problems with the key buffer… care about the key cache hit ratio. Bad advice #2: you should set your key_buffer_size according to this ratio. Tuning by ratio… to your IO system’s capabilities. MySQL doesn’t have good instrumentation for scientifically choosing a key_buffer_size setting, but there are…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 118 Comments
… work as MySQL DBAs or be involved with MySQL Performance in some way is to ask them what should be tuned in MySQL Server…. 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 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
…: cast_info type: ref possible_keys: role_id_note key: role_id_note key_len: 4 ref: const rows:… which means you need more manual care and tuning now. MySQL is conservative about “Using index” -in most…when the condition is very selective and the buffer pool is effective. There is no workaround, using…
Post: MySQL Server Memory Usage
May 17, 2006 By 21 Comments
… always where – these are key_buffer_size, innodb_buffer_pool_size, innodb_additional_memory_pool_size, innodb_log_buffer_size, query_cache_size. If… allocations which are not tuned as buffers. Watch for these especially now with many storage engines being released for MySQL by various parties…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 39 Comments
… needs you also have MySQL needs – these include MySQL buffers – query cache, key_buffer, mysql threads, temporary tables, per thread sort buffer which can be …MySQL to use Large Pages for allocating Innodb Buffer Pool and few other buffers, which may have other performance benefits as well. Tuning…
Post: Read Buffers, mmap, malloc and MySQL Performance
September 12, 2007 By 8 Comments
… together with strace) are great tools for MySQL Performance analyses. Too many people end tuning on plans and indexes while there well… large pages. These already can be used for Innodb Buffer Pool and Key Buffer but could be used for other large allocations. Finally… buffers in the pool you could just grab one having allocation very quick. Also MySQL should get smarter in terms of which buffer…
Post: Full Text Search Webinar Questions Followup
August 24, 2012 By 5 Comments
… tested MyISAM FT index, I reallocated that memory to into key_buffer_size. The other thing that happened with regards to memory… FT index before declaring the `FTS_DOC_ID` primary key column properly, MySQL crashed with an out of memory fatal error, and… with MySQL and it wouldn’t be as flexible and customizable as Solr. With Solr, I was able to fine tune search…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…BUFFER_SIZE | 2097152 | 262144 | | LC_MESSAGES_DIR | /mnt/nfs/dist/mysql-5.5.30-linux2.6-x86_ | /mnt/nfs/dist/mysql…is one of the component of purging fine tuning in MySQL 5.6 innodb_file_per_table is…cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on…
