…SEQUENTIAL inserts into Primary Key, that this is quite suitable for InnoDB, and in it we have random…performance steadily declines from 24000 tps to 18000 tps, but InnoDB can’t make 5h run. After 3h the disk…_threads = 1 myisam_recover socket=/var/lib/mysql/mysql.sock user=root skip-grant-tables …
Post: Aligning IO on a hard disk RAID – the Benchmarks
June 9, 2011 By 13 Comments
…performance with different IO alignment, not different MySQL configurations, I didn’t try out different MySQL…performance by 5-10%. Sequential write results were a bit more interesting. With WT (write-through) cache enabled, performance… – random reads and random writes – IO alignment on a 4-disk …
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
…The unit of cost for the MySQL query optimizer is a single random 4k data page read. …performing index lookups into the fact table, which is big. An index lookup is a random I/O. A modern disk… is basically the difference between scanning data sequentially on disk and random disk I/O. So now you know …
Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 1
October 15, 2009 By 16 Comments
…sequentially accessing this data will destroy the use of cache: get record 1, miss, read from disk… generally mean and lean and performs well. The issue is even… can then compare using MySQL to using MySQL + Memcached, and then to…successful. This application selects a random # between 1 and 2 Million…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… of Random I/O. The purpose of MRR is to reduce this Random I/O and make it more sequential, by … captured when performing the benchmark on IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 … 5.5 w/ mrr_buffer_size=4M Created_tmp_disk_tables 1 1 1 1 1 Created_tmp_tables …
Post: InnoDB Flushing: a lot of memory and slow disk
March 31, 2011 By 57 Comments
… from the fact that MySQL performs changes in memory at a faster rate than it can write to disks. Eventually it gets into… was that flushing neighbor pages was implemented to perform sequential writes where possible and avoid random writes, which is quite critical for hard…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
…,Technocarati MySQL has installations with many billions of rows and delivers great performance. …of rows are accessed, while for disk IO bound accessess we might…random places – this may affect index scan/range scan speed dramatically. The rows referenced by indexes also could be located sequentially…
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
…: Using index) is a very interesting performance optimization, a full index scan (type: index…see how the optimizer will execute it: mysql> EXPLAIN SELECT * FROM employees ORDER BY first_name\G… stored on spinning disks, the assumption that random reads are much slower than sequential reads is correct…
Post: How multiple disks can benefit for single client workload ?
April 26, 2008 By 4 Comments
… bound or CPU bound, while to understand if MySQL goes through more rows than it needs to… these reads a bit – there is sequential read ahead and random read ahead which are designed to spot… have any seeks you will be far from sequential disk access performance. First you should consider what has to …
Post: Evaluating IO subsystem performance for MySQL Needs
March 5, 2008 By 23 Comments
… the system. What I usually look for MySQL is performance in random reads and random writes. Sequential reads and writes are rarely the problem for… are just stored in controller cache and flushed to the disk in background – in this case there is little difference if… also can see BBU is helpful even for random writes if they are performed from single thread – so it does not only…
