… no caching of data at the filesystem level. The InnoDB indexes were built using ALTER TABLE fast index creation (merge sort). For… used a 10GB key buffer. I used ALTER TABLE DISABLE KEYS and built the keys with sort via ALTER TABLE ENABLE KEYS. my.cnf [mysqld…_instances=16 metadata_locks_hash_instances=32 table_open_cache_instances=8 sort_buffer_size=128k read_rnd_buffer_size=8M join…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…)+k; COMMIT; So this workload produces SEQUENTIAL inserts into Primary Key, that this is quite suitable for InnoDB, and in it… binlog_cache_size = 16M max_heap_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1000 query_cache_size = 0 query_cache_type = 0 ft…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
… binlog_cache_size = 16M max_heap_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1000 query_cache_size = 0 query_cache_type = 0 ft_min_word_len = 4 thread_stack = 192K tmp_table_size = 64M serverid = 101 key…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… PRIMARY KEY index entry for that row). This means typically on a single Innodb node we can have all sorts modifications floating… with by fsyncing to memory (Battery or capacitor-backed write cache), etc., but the principle is basically the same. If we…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…key_buffer_sizeblogpercona.commanual key_cache_age_thresholdblogpercona.commanual key_cache_block_sizeblogpercona.commanual key_cache_division_limitblogpercona.commanual Key_read_requestsblogpercona.commanual Key_readsblogpercona.commanual Key_write_requestsblogpercona.commanual Key…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… 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…use this variable. sort_buffer_size Buffer used to sort result set (allocated by each thread once sorting needs to …
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… secondary key on the common join column, let’s suppose col1, then with Key-ordered scan, the secondary key on col1 would be sorted…_sort_keys=on’ optimizer_switch=’mrr_cost_based=off’ optimizer_switch=’join_cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache…
Post: Using MyISAM in production
June 17, 2006 By 36 Comments
… decent myisam_sort_buffer_size and large myisam_max_sort_file_size otherwise recovery may be done by key_cache rather than sort which can… 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… may go ahead and wipe OS cache which you hoped for causing unexpected slowdowns. delay_key_writes Whatever way you enable this…
Post: MySQL Server Memory Usage
May 17, 2006 By 21 Comments
… – these are key_buffer_size, innodb_buffer_pool_size, innodb_additional_memory_pool_size, innodb_log_buffer_size, query_cache_size. If… values to run some queries. There can be mutiple key caches. Multiple key caches can be created to accomodate query executions Query Parsing and… changing various variables. For example you may find out increasing sort_buffer_size from 1MB to 4MB and 1000 max_connections…
Post: MySQL caching methods and tips
April 4, 2011 By 5 Comments
… old query results remain available in the cache, but they are now “stale”. Until the cache key expires, the contents are available immediately and you avoid performing work in the database. Eventually the cache contents expire… expensive, and even if the disk bottleneck is eliminated, the sorting, aggregation and join operations are still CPU intensive and single…
