… now – you have to specify compressed block size, this is the size to which you expect each page to compress… in INSERT IGNORE and ON DUPLICATE KEY UPDATE cases. Now In MySQL 5.1+ it is also possible …large stripes are better and why battery backed up cache is so important. I also usually speak about …
Post: Getting real life query speeds with MySQL
January 10, 2007 By 10 Comments
… often using SELECT SQL_NO_CACHE or disabling QueryCache for the test. This works by blocking query cache but still repeated runs execute… of 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…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…cache is cold The remaining results are runs without a server restart. For the default size BP, this means the FS cache…blocks_time=0; mysql> set global innodb_old_blocks_time=0; Query OK, 0 rows affected (0.00 sec) mysql…(10), KEY(C_Name), KEY(C_City), KEY(C_Region), KEY(C_Phone), KEY(C_MktSegment)…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
…mysql> show global status like “key%”; +————————+———+ | Variable_name | Value | +————————+———+ | Key_blocks_not_flushed | 49361 | | Key_blocks_unused | 61201 | | Key_blocks_used | 49361 | | Key_read_requests | 4007329 | | Key_reads | 49361 | | Key…
Post: Side load may massively impact your MySQL Performance
November 13, 2011 By 7 Comments
…mysql –mysql-host=localhost –mysql-table-engine=innodb –mysql-db=test –oltp-table-name=md_cache_test_small –oltp-table-size=1100000 –mysql-user=msandbox –mysql-password=msandbox –mysql… innodb_old_blocks_time which…cache_test_big.k 0.01491451 As you can see now the small table PRIMARY KEY…
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… which are being repaired, each may allocate myisam_sort_buffer_size and MySQL could crash or go swapping. Second – table will be… avoiding flushing dirty index blocks from key_buffer to disk, but it also comes at great danger if MySQL Server crashes or power…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… the combined size of the secondary key tuples fetched, and N is the buffer size. In MySQL 5.6 the buffer size used…of MySQL 5.5 and ~5 hours in case of MySQL 5.6 and MariaDB 5.5. Note that query cache…it in my next post which will be on Block Access Join Algorithms. Conclusion There is a huge speedup…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…-blocking …size : 64 cache_alignment : 64 address sizes…mysql> show global variables like ‘%table_size%’; +———————+———–+ | Variable_name | Value | +———————+———–+ | max_heap_table_size | 268435456 | | tmp_table_size…mysql.* tables, running mk-duplicate-key…
Post: MyISAM Scalability and Innodb, Falcon Benchmarks
October 12, 2007 By 10 Comments
… in MySQL 4.1 As Monty explained us in MySQL 4.1 the change to key cache locking …when key block is copied to processing thread local storage on Key Read Request. This lock is per key cache … key_cache is a problem is benchmark run with disabled key_cache (=0). Results for MyISAM with key_buffer_size=…
Comment: MySQL Server Memory Usage
December 20, 2012 By 21 Comments
…size = 8M bulk_insert_buffer_size = 8M query_cache_limit = 254M query_cache_size = 254M query_cache_type = 1 query_prealloc_size = 65536 query_alloc_block_size…
