… usually a symptom of innodb_io_capacity set too low for the workload and system. For an SSD setup with high write… it’s best not to set it much higher than required. MySQL 5.5 server dirty page flushing respects the innodb_io_capacity setting, Percona server flushing doesn’t. Set innodb_io_capacity too low and MySQL server will have…
Post: Aligning IO on a hard disk RAID – the Theory
June 9, 2011 By 15 Comments
… to distribute writes properly so they make the best use of the underlying storage, it also makes… This is the most frustrating part of the IO alignment and I think the reason people get…-bytes internally. This however gets more complicated with SSD drives which often operate in 4kbyte units, though…
Post: When would you use SAN with MySQL ?
March 9, 2009 By 18 Comments
…does it great. My Advice for Scaling IO capacity with SAN (this applies to directly… a poor choice, with adding memory or SSD being a better choice. High Availability Some…MySQL replication with something as MMM or Flipper. I think this actually works best for most cases (unless async nature of MySQL…
Post: Should I buy a Fast SSD or more memory?
April 8, 2010 By 10 Comments
… scale-out solution has traditionally been popular for MySQL, it’s interesting to see what room we now…is when data fits completely in the buffer pool (best performance). It’s important to point out that …graph a Fusion-IO card improves performance by about five times (or 2x with an Intel SSD). To …
Post: MySQL 5.5.8 and Percona Server: being adaptive
December 20, 2010 By 25 Comments
…it important not only to have the best peak performance, but also stable and predictable…. An additional optimization we have for SSDs is big log files. For this …io_threads=8 innodb_io_capacity=500 max_connections=3000 query_cache_size=0 skip-name-resolve table_cache=10000 [mysql] socket=/tmp/mysql…
Post: MySQL Performance on Memory Appliance
March 31, 2008 By 23 Comments
…. Next I went on testing MySQL. My goal was to simulate as much IO as possible so I use Sysbench…which Innodb team will put on their roadmap. With SSD coming this will be important. And it is also…for this workload avoiding to avoid contention. In the best conditions I got about 13.5K queries/sec from…
Post: Guidance for MySQL Optimizer Developers
September 20, 2009 By 4 Comments
…new MySQL Version. Make Cost Model Adjustable MySQL Optimizer looks at query plan in terms of disk IOs/…others keep database on SSD which has completely different ratio between CPU and IO cost. Focus on … “cost” but the one which actually gives best performance). I also recognize there are always …
Post: Looking on 5.4 - IO bound benchmarks
April 30, 2009 By 5 Comments
… benchmarks. For first shoot I took tpcc-like IO-bound benchmark (100W, ~10GB of data, 3GB buffer_pool…update with my next CPU benchmarks, and benchmark on SSD & FusionIO), also I post graph there: Results are … I can confirm that MySQL team did great job with 5.4 and it shows the best results. Some …
Comment: MySQL 5.5.8 and Percona Server on Fast Flash card (Virident tachIOn)
December 22, 2010 By 11 Comments
…_io_capacity=20000 seems to be the best option here. But in your previous post (http://www.mysqlperformanceblog.com/2010/12/20/mysql-5-5-8-and-percona-server-being-adaptive/) in the 3rd graph innodb_io_capacity=500 was the best option while innodb_io… 2 tests? What value of innodb_io_capacity would you recommend for SSD? Also is the innodb_io_capacity available for Percona Server?
Post: The relationship between Innodb Log checkpointing and dirty Buffer pool pages
February 17, 2012 By 5 Comments
… across the tablespaces. Sequential IO trumps Random IO. However, even today in …and what does it tell us? mysql> SHOW ENGINE INNODB STATUS\G — LOG — …good default) and ‘keep_average’ designed for SSD type storage. Running Percona Server … age. To be clear, the best we have today (IMO) is …
