… of data and index size: mysql> show table status like “sbtest” \G *************************** 1….indexing, such as inserting data in random order can become very slow … disks are not that bad with sequential IO, which is what file merge… TABLE which rebuilds the table or OPTIMIZE TABLE do not forget …
Post: MLC SSD card lifetime and write amplification
November 15, 2011 By 8 Comments
… equal or very close to 1 (meaning there is no overhead) for sequential writes and it gets a maximum value for fully random writes. This value can be 2 – 5 or more and…/sec, which corresponds to write throughput 273.45MB/sec from MySQL to disk. And it helps to calculate the lifetime of…
Post: How InnoDB handles REDO logging
February 3, 2011 By 19 Comments
… after image of a page is logged or by logging logical changes (Logical logging) to the…, your transaction will never complete. Both the MySQL configuration and hardware will affect the speed … therefore what would be a random write is translated into a much faster sequential write. The background …
Post: InnoDB thread concurrency
June 5, 2006 By 4 Comments
…’ll try to explain how it works. MySQL has pluginable architecture which divides work between mysql common code (parser, optimizer) and storage… simplified): mysql calls storage engine’s methods: read_row (there are a couple of methods like read by index or sequential read or random read…
Post: MySQL Partitioning - can save you or kill you
December 11, 2010 By 45 Comments
… to write about using MySQL Partitioning for Performance Optimization and I…key you will look at one (or some of) partitions, however lookups …to partition data to 32 partitions placing sequential ranges of 10M values in the …not exist, which reduced performance for random selects by UUID from 400 to…
Post: Announcing Percona Server 5.5.19-24.0
January 13, 2012 By 4 Comments
… here and from the Percona Software Repositories). Based on MySQL 5.5.19, including all the bug fixes in … of neighbor pages, thus performing the flush by sequential instead of random I/O. (Yasufumi Kinoshita, Laurynas Biveinis) Improvements to … OS X platform, and you can download tar.gz or dmg there
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
…. Q9: Do you favour database “self tuning” or prefer to expose tuning options to be … Innodb decides to schedule sequential read-ahead, random read ahead ? HT: InnoDB schedules a sequential read-ahead when …HT: ha_innodb.cc in 5.1: static MYSQL_SYSVAR_ULONG(thread_concurrency, srv_thread_concurrency, …
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
… scan. Let’s see how the optimizer will execute it: mysql> EXPLAIN SELECT * FROM employees ORDER BY first_name\G *************************** 1. row… then lots of random reads to fetch rows by index value. And random reads are orders of magnitude slower than sequential reads. The… basically the difference between sequential reads and random reads: it can be close if you have fast storage or it can be very…
Post: InnoDB Flushing: a lot of memory and slow disk
March 31, 2011 By 57 Comments
… of the existing flushing implementations (including both MySQL native adaptive flushing and that in Percona… was implemented to perform sequential writes where possible and avoid random writes, which is …like EC2 systems, or is affected by periodic tasks like backup or heavy background queries.) …
Post: FusionIO 320GB MLC benchmarks
January 11, 2010 By 19 Comments
… 32150.9491.41457.6718.81 64146.08486.97387.17740.21 or in chart form: (if legend is unreadable, it is: 32GB… get 700 MB/s in sequential reads ( 400 MB/s in random reads) and 500 MB/s for random writes. 95% response time… in random writes ( and 1.2GB/s in sequential reads) Now having these cards and numbers, I am going to benchmark MySQL with…
