Re: how to tune for innodb_io_capacity?
Posted by:
Rick James
Date: November 27, 2014 11:56AM
innodb_io_capacity = 200 for normal disks; more for RAID striping. Perhaps 2000 for SSD.
But lots of other things factor into INSERT and read performance.
INSERTing in PRIMARY KEY order at the 'end' of the table (eg, with AUTO_INCREMENT) is much more efficient than random (eg, MD5, GUID, UUID) order. (perhaps >10x improvement)
Batched INSERT (INSERT ... VALUES (1,2,3), (4,5,6), ...) is much more efficient than one row at a time. (~10x improvement for 100 rows at once)
BEGIN; -- lots of INSERTs/UPDATEs/etc --; COMMIT; is a lot more efficient than committing after each statement. (like batched inserts)
innodb_flush_log_at_trx_commit = 1 is the default and the safest, but limits you to 100 IOPs on standard disks. =2 gives a big boost be avoiding an fsync for each COMMIT (explicit or implicit).
Setting innodb_buffer_pool_size to ~70% of available RAM is very important for all operations. (various improvement, sometimes 10x for SELECTs)
As for SELECTs, there are lots (LOTS) of techniques to improve speed by any from a few percent, to 100x or more. The Performance forum is littered with such.
If are using Replication, there are other settings to consider.
If you INSERT a one-byte row in a random place in a table, there will be a 16KB read and a 16KB write to update the block that will contain that row. If there is a block split, even more I/O is needed. On the other hand, if that 16KB can be cached, the cost is amortized over a number of INSERTs. In your metric, are you counting that as 1 byte? Or 16KB? or some other amount? In other words, GB/s may not be the right "metric" to study.
Out-of-the-box, and naively used, InnoDB is "good enough" for most users, but fails miserably for stressful benchmarks. However, the tips I have above (plus others) can easily make InnoDB run 10 times as "fast", possibly more. After that, I question the design of the schema and the application. I am commenting on another thread where I am advising some 'minor' changes to speed up the system 600-fold.
Out-of-the-box (trx_commit=1, autocommit, one row per INSERT, random key, etc) -- 100 rows INSERTed per second.
Tuned and changes to design: 5000-10000 rows/sec. There are 2 threads (Newbie and Performance) on that topic now.
Don't dwell on GB/s, focus on avoiding I/O. Or sharing I/O by effective use of cache.
Having pondered my comments, would you like to reconsider your real goal? Assuming it is radically different, you could start a new thread.