…_capacity = 800 innodb_read_io_threads = 8 innodb_write_io_threads = 4 innodb_file_per_table = 1 ## Disabling query cache query_cache… downward spikes in performance. Other then that, I have also disabled query cache to avoid contention caused by query cache on… I was mostly concerned with write performance, so I went ahead to choose a table structure with no secondary indexes. Other…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
….6 and MariaDB 5.5. Note that query cache is disabled during these benchmark runs and that the disks are 4…_read_ahead which shows that the access pattern was sequential and hence InnoDB decided to do read_ahead, while in MySQL 5.5 no read_ahead was done because the access pattern was not sequential. Another thing to note is that more read_ahead is done…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… Handler_read_next 2651500 2628103 2586036 2628103 2592816 2615612 Handler_read_rnd_next 23078 22780 22757 22867 23049 23221 Innodb_buffer_pool_read_ahead 1152 23231 130919 23228 130731 131497 Innodb_buffer_pool_read_requests 12947073 10228611 7816154 10251697 9319281 9393396 Innodb_buffer_pool_reads… Joins enabled is doing less Handler reads as compared to when the Hash Joins are disabled, but that did not make much…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition …
Post: read_ahead (disabled) as steroid
May 12, 2009 By 4 Comments
… of “read_ahead“. And if in the same benchmark you disable read-ahead (innodb_read_ahead=none), you can see improvement about 15% Actually control of read_ahead is… – in the same time gaps can be smoother with disabled read_ahead, however disabling it does not show such improvement like on RAID10 I…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 17 Comments
…. Q10: When Innodb decides to schedule sequential read-ahead, random read ahead ? HT: InnoDB schedules a sequential read-ahead when it notices an ascending access pattern… for semaphores in SHOW INNODB STATUS), you can try to disable thread throttling completely by setting the value 0. But if…
Post: The performance effects of new patches
September 9, 2008 By 14 Comments
…. innodb_read_ahead (default 3) This controls [enable/disable] of read-ahead. 3: normal 2: enable linear read-ahead only 1: enable random read-ahead only 0: disable both innodb… may help the performance. * This test uses “innodb_read_ahead = 0″ (The both of read-ahead are disabled) “innodb_ibuf_contract_const = 50000″ “innodb_ibuf…
Post: MySQL 5.5.8 and Percona Server on Fast Flash card (Virident tachIOn)
December 21, 2010 By 11 Comments
…_flush_neighbor_pages=0 innodb_adaptive_checkpoint=keep_average innodb_read_ahead=none For MySQL 5.5.8, I used: innodb_log… Disabling flushing of neighborhood pages: innodb_flush_neighbor_pages=0 New adaptive checkpointing algorithm innodb_adaptive_checkpoint=keep_average Disabled read-ahead logic: innodb_read_ahead…
Post: Aligning IO on a hard disk RAID – the Benchmarks
June 9, 2011 By 11 Comments
… of choice was XFS. Barriers and physical disk cache was disabled. The tool I used was sysbench 0.4.10 that… bad idea File IO benchmark results Sequential read results are expectedly the least interesting. Read-ahead kicked in immediately giving ~9’600 iops… what exactly happened with setting #3 when WB cache was disabled, what I do know though is that, based on IO…
Post: MySQL 5.5.8 and Percona Server: being adaptive
December 20, 2010 By 25 Comments
…. This method requires setting innodb_flush_neighbor_pages=0 , to disable flushing of neighborhood pages (not available in MySQL 5.5…_method = O_DIRECT innodb_read_ahead = none innodb_flush_neighbor_pages = 0 innodb_write_io_threads=8 innodb_read_io_threads=8 innodb…
