…=128 MyISAM / InnoDB: libexec/mysqld –no-defaults –user=root –key-buffer-size=1500M –innodb-buffer-pool-size=1500M –innodb-log-file-size=100M –innodb-thread-concurrency=8… accessed column is stored in key. MyISAM and InnoDB handle this case and retrive the value only from key. InnoDB is better by 25…
Post: What MySQL buffer cache hit rate should you target
June 29, 2006 By 5 Comments
… typical question I’m asked. It could by MyISAM key_buffer or Innodb innodb_buffer_pool it does not really matter. In both cases trying… in this case global key hit ratio may be 99.99% but you would still have problems with key buffer efficiency. In the… writes” from SHOW INNODB STATUS for Innodb tables. In MySQL 5.0+ you can also use Innodb_data_reads, and Innodb_data_writes counters…
Post: Innodb Recovery Update - The tricks what failed.
May 24, 2007 By 3 Comments
… Innodb tables (for test purposes) Innodb still was crashing if we attempted to enable insert buffer. I think it would be nice for Innodb… are not caused by faulty hardware. Primary keys are typically simple integer keys in Innodb while secondary keys may have very complex structure. Compex…
Post: MyISAM Scalability and Innodb, Falcon Benchmarks
October 12, 2007 By 10 Comments
… than with single query. The problem in this case is key buffer contention which unlike popular belief not fully fixed by changes… second confirmation that key_cache is a problem is benchmark run with disabled key_cache (=0). Results for MyISAM with key_buffer_size=0… we proposed in this case was converting table t1 into InnoDB, and results: Threads Queries/sec 1 296 2 341 4…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 39 Comments
… MySQL buffers – query cache, key_buffer, mysql threads, temporary tables, per thread sort buffer which can be allocated. There are also things like innodb additional… for other things and if you will not make Innodb to bypass OS buffering Innodb TableSpace IO will wipe out cache because it…
Post: MySQL Crash Recovery
July 30, 2006 By 30 Comments
… may cause the problems. As I already wrote innodb_log_file_size and innodb_buffer_pool_size affect recovery time significantly as well as your workload. I should also mention if you have innodb_file… scenarios. Cold Start – If you restart MySQL server its caches (key_buffer, innodb_buffer_pool, query_cache,table_cache) are cleaned, so may be…
Post: Measuring the amount of writes in InnoDB redo logs
October 8, 2012 By 4 Comments
Choosing a good InnoDB log file size is key to InnoDB write performance. This can be done by measuring the amount … bytes to the log buffer At commit, InnoDB writes a 512-byte block at offset xxx and increments Innodb_os_log_written by 512 bytes Transaction 2 writes 200 bytes to the log buffer At commit, InnoDB appends those…
Post: Read Buffers, mmap, malloc and MySQL Performance
September 12, 2007 By 8 Comments
… ? Of course you can tune you malloc settings so more buffers are allocated from the pools rather than mmap. This though… at least increase memory consumptions as deallocating of these large buffers will not instantly return data to the OS. Plus I… use large pages. These already can be used for Innodb Buffer Pool and Key Buffer but could be used for other large allocations. Finally…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…: SELECT key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) Buffer each pk_column value fetched from step 1, and when the buffer…
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…
