… innodb_flush_method = O_DIRECT innodb_max_dirty_pages_pct = 50 innodb_io_capacity = 800 innodb_read_io_threads = 8 innodb_write_io_threads = 4 innodb_file… indexes cause read performance to improve, but they have an impact on the write performance. Well most of the apps rely on read performance and…
Post: Testing Samsung SSD SATA 256GB 830 - not all SSD created equal
April 25, 2012 By 10 Comments
…. There are two reasons for that. First, MySQL/InnoDB uses async writes, so this will emulate database … every 10 sec to see how stable the performance is, and it really helps to observe some artifacts…I was totally surprised them in random reads also. The results for random read case: I do not have…
Post: Testing STEC SSD MACH16 200GB SLC
April 25, 2012 By 7 Comments
…. There are two reasons for that. First, MySQL/InnoDB uses async writes, so this will emulate database … every 10 sec to see how stable the performance is, and it really helps to observe some …internal garbage collector activity. The results for random read case: Very stable throughput on line 222 MiB…
Post: Load management Techniques for MySQL
May 6, 2012 By 3 Comments
One of the very frequent cases with performance problems with MySQL is what they happen every so often …
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… these lookups are performed in a single call to the storage engine and the counters Handler_read_key and Innodb_rows_read are incremented… perform the index record read and then to perform the MRR range scan on the PK. This causes the counters Handler_read_key and Innodb_rows_read to…
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: InnoDB's gap locks
March 27, 2012 By 14 Comments
… or SELECT FOR UPDATE. InnoDB provides REPEATABLE READ for read-only SELECT, but it behaves as if you use READ COMMITTED for all write… concurrency and the performance you can disable them in two different ways: 1- Change the ISOLATION level to READ COMMITTED. In this….mysql.com/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the default isolation level so…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… by PK column and then performing the lookup, and then there are other possibilities like InnoDB doing read_ahead by noticing the sequential….2G 2.2G Innodb_data_reads 329115 355323 143808 335526 16164 15506 Innodb_pages_read 329115 355323 143808 358308 144798 144881 Innodb_rows_read 4127318 6718351…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
… –single-transaction to get consistent backup for their Innodb tables without making database read only. In most cases it works, but did… with DDL, In particular ALTER TABLE. When ALTER TABLE is Performed in many cases it will Create temporary table with modified…` char(255) NOT NULL, `i` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs…
Comment: Instrumentation and the cost of Foreign Keys
April 19, 2012 By 9 Comments
… performance benefit because the optimizer has more information about what needs to be done and the plan is created faster. Does innodb get any such performance benefit in the same manner? One thing not… the only way to understand the entity relationships was to read through the application. FKs can provide a very intuitive view…
