… DUPLICATE KEY UPDATE k=VALUES(k)+k; INSERT INTO sbtest_r1000 (id, k) VALUES ($ID/1000, $K) ON DUPLICATE KEY UPDATE k=VALUES(k)+k… = 16M 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…
Post: Beware: key_buffer_size larger than 4G does not work
February 14, 2007 By 17 Comments
… they set key_buffer_size to 16G… and every few days MySQL crashes. Why ? Because key_buffer_size over 4GB in size is not really… just fine until you have less than 4GB worth of key cache used and then it would crash. If you do not… maximum key_buffer_size was not restricted to safe limit. Former would be close to changing single line of code with variable values…
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 4 Comments
… how integer primary key is handled. I can’t see the index size difference between DEFAULT and 1 values for such key. However it I change it to “key” the size reduces if PACK_KEYS=1… is disk bound there are more reasons to have keys packed so cache hit is better. Especially if you can get hot…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… with query cache size of 0 which makes it disabled. I wish query_cache_size though would be larger by default as value of 1M… Buffer Pool Size scan resistant by default. Very welcome change ! thread_cache_size is enabled by default, though I wonder why default value is…=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…_trx_commit=2 innodb_flush_method=O_DIRECT query_cache_size=0 query_cache_type=0 optimizer_switch=’index_condition_pushdown=on’ optimizer… query_cache_size=0 query_cache_type=0 optimizer_switch=’index_condition_pushdown=on’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on… 5 rows from the table lineitem for each distinct key value. Also the joining key in this test case D is PK in…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 118 Comments
…_size. Also Innodb buffer pool caches both data and index pages so you do not need to leave space for OS cache so values up to 70-80% of memory often make sense for Innodb only installations. Same rules as for key_buffer… normal operation. query_cache_size If your application is read intensive and you do not have application level caches this can be great…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…MHz : 1995.022 cache size : 4096 KB physical … two key …size%’; +———————+———–+ | Variable_name | Value | +———————+———–+ | max_heap_table_size | 268435456 | | tmp_table_size | 268435456 | +———————+———–+ If these are not the same size…
Post: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 17 Comments
… ## Disabling query cache query_cache_size = 0 query_cache_type = 0 You can see that the buffer pool is sized at 55G…KEY (`transactionid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (transactionid) (PARTITION p0 VALUES LESS THAN (100000000) ENGINE = InnoDB, PARTITION p1 VALUES…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
…key_buffer_size is important for MyISAM temporary tables performance to avoid OS writes OS Write cache…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…values…cache skip-name-resolve sort-buffer-size=512K sql-mode=STRICT_TRANS_TABLES symbolic-links table-definition-cache=16384 table-open-cache=128 thread-cache-size…
