Re: Memory/cache usage with innodb_flush_method values
Posted by:
Rick James
Date: October 02, 2015 09:41PM
I can't explain what you are seeing. But it should help to clear away some of the things that are confusing the benchmarks...
A potential problem in the test... The buffer_pool is a cache. The UPDATE probably had to read and rewrite all 1GB of data. But, since you have 512MB of cache, nearly half the UPDATE was "finished" before the first I/O _write_ occurred. At that point, the buffer pool was mostly full of "dirty" blocks.
Then for the other half of the table, InnoDB was going back and forth between making room in the buffer_pool (by writing dirty pages) and reading + updating more blocks and leaving them dirty in the buffer_pool. When the UPDATE "finished", half the table was still sitting in the buffer pool, waiting to be flushed to disk by a background thread.
To get a better feel for what I am saying, change your test to UPDATE 10% of the table at a time (be sure to use a range of the PRIMARY KEY, not LIMIT or some other technique).
Does the table have a PRIMARY KEY? All InnoDB tables should be given an explicit PK, even it if is an artificial AUTO_INCREMENT.
Other than watching vmstat or iostat _after_ you have finished the UPDATE(s), I don't know how to 'see' that there are writes after the UPDATE terminates and `mysql` declares how long it took.
To test the READs, be sure to have no leftover writes and nothing cached in the buffer_pool.
You did not mention what filesystem (ZFS, XFS, EXT4, etc) you are using -- this could make a difference.
Another approach is to shrink innodb_buffer_pool_size to only 50M (and hope that is not too small). That way most of the time (95%) of the time your test will be flushing dirty pages and reading new pages.
512M is dangerously high for the tiny 2GB VM. Was there any swapping? Swapping is deadly for performance.
You apparently have binlog_format = ROW.
Turn off log_bin -- it perturbs the situation, especially since 60% of the writes seem to be to the binlog. Oh, furthermore, since the single UPDATE is a "transaction" with an implicit "commit", all the 1.5GB of binlog writes will happen _after_ the last row is updated.
Was double-write turned on?
Were the disks spinning? Or SSDs? Spinning drives need to "seek"; SSDs do not.