Mysql – Measuring IOPS for innodb_io_capacity

innodbMySQLperformance

Aurimas Mikalauskas's 17 Key MySQL Config File Settings gives the following advice regarding innodb_io_capacity and innodb_io_capacity_max:

measure random write throughput of your storage and set innodb_io_capacity_max to the maximum you could achieve, and innodb_io_capacity to 50-75% of it, especially if your system is write-intensive.

But how do I measure these IOPS? What block size do I use (I assume the page size, so 16K by default)? Do I fsync after every block?

A fio command like this one gives me around 300 IOPS maximum, which seems very slow for these drives NVMe SSDs these are supposed to be. Most advice I can find online has these variables in the thousands.

fio --name fio --readwrite=randwrite --ioengine=libaio --direct=1 --filename=fio --numjobs=1 --size=4G --gtod_reduce=1 --iodepth=64 --bs=16k --fsync=1

Best Answer

If you have a running instance of mysql, throw some data at it, then compute

( Innodb_pages_read + Innodb_pages_written + 
       Innodb_dblwr_writes + Innodb_buffer_pool_pages_flushed ) / Uptime

Something like:

SHOW GLOBAL STATUS;
run some heavy I/O queries
SHOW GLOBAL STATUS;

Tediously subtract the 5 values and evaluate the expression.

This will be a crude number, and you can probably set the innodb_io_capacity* higher than what the expression gives you.

YOUR QUERY

SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;

At first glance, that query should only touches 1.1597% (62510 out of 5390146) of the table. It should be fast given the key distribution of threadid 51506.

REALITY CHECK

No matter which version of MySQL (Oracle, Percona, MariaDB) you use, none of them can fight to one enemy they all have in common : The InnoDB Architecture.

InnoDB Architecture

CLUSTERED INDEX

Please keep in mind that the each threadid entry has a primary key attached. This means that when you read from the index, it must do a primary key lookup within the ClusteredIndex (internally named gen_clust_index). In the ClusteredIndex, each InnoDB page contains both data and PRIMARY KEY index info. See my post Best of MyISAM and InnoDB for more info.

REDUNDANT INDEXES

You have a lot of clutter in the table because some indexes have the same leading columns. MySQL and InnoDB has to navigate through the index clutter to get to needed BTREE nodes. You should reduced that clutter by running the following:

ALTER TABLE newbb_innopost
    DROP INDEX threadid,
    DROP INDEX threadid_2,
    DROP INDEX threadid_visible_dateline,
    ADD INDEX threadid_visible_dateline_index (`threadid`,`visible`,`dateline`,`userid`)
;

Why strip down these indexes ?

The first three indexes start with threadid
threadid_2 and threadid_visible_dateline start with the same three columns
threadid_visible_dateline does not need postid since it's the PRIMARY KEY and it's embedded

BUFFER CACHING

The InnoDB Buffer Pool caches data and index pages. MyISAM only caches index pages.

Just in this area alone, MyISAM does not waste time caching data. That's because it's not designed to cache data. InnoDB caches every data page and index page (and its grandmother) it touches. If your InnoDB Buffer Pool is too small, you could be caching pages, invalidating pages, and removing pages all in one query.

TABLE LAYOUT

You could shave of some space from the row by considering importthreadid and importpostid. You have them as BIGINTs. They take up 16 bytes in the ClusteredIndex per row.

You should run this

SELECT importthreadid,importpostid FROM newbb_innopost PROCEDURE ANALYSE();

This will recommend what data types these columns should be for the given dataset.

CONCLUSION

MyISAM has a lot less to contend with than InnoDB, especially in the area of caching.

While you revealed the amount of RAM (32GB) and the version of MySQL (Server version: 10.0.12-MariaDB-1~trusty-wsrep-log mariadb.org binary distribution, wsrep_25.10.r4002), there are still other pieces to this puzzle you have not revealed

The InnoDB settings
The Number of Cores
Other settings from my.cnf

If you can add these things to the question, I can further elaborate.

UPDATE 2014-08-28 11:27 EDT

You should increase threading

innodb_read_io_threads = 64
innodb_write_io_threads = 16
innodb_log_buffer_size = 256M

I would consider disabling the query cache (See my recent post Why query_cache_type is disabled by default start from MySQL 5.6?)

query_cache_size = 0

I would preserve the Buffer Pool

innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

Increase purge threads (if you do DML on multiple tables)

innodb_purge_threads = 4

GIVE IT A TRY !!!

Sql-server – Performance measuring on complex query

After the first execution the procedure ran for about 40 seconds while the old one ran for about 13 minutes after I again removed the caches. That seems amazing.

So it seems the new formulation has much better cold-cache performance, most likely due to more aggressive read-ahead I/O. This is typically seen for plans that feature (range) scans over point lookups, for example.

So now I wonder is there ANY real improvement in real life? Will anyone ever notice this change?

Well that depends. If the working set of data and index pages fits in memory, even after other needs are accounted for (like sorting and hashing, the plan cache...) then maybe not - except during ramp-up after a restart (or if the buffer cache is flushed for some reason).

That said, things can and do change over time. As the data volume grows and memory needs expand (as both tend to do) the chance that at least some queries will need to fetch information from persistent storage increases. Queries that make more effective use of read-ahead may benefit in that situation, and people may "notice".

Moreover, the current query has terrible cold-cache performance. Who knows when it might hit a performance cliff (currently masked by accessing only in-memory data). Why take that risk?

Is the way I measured this correct?

Everyone has their personal preference for performance testing. Certainly wrapping the query in a procedure is reasonable, especially if that is the way it will be used in production. I'm not so convinced of the need for WITH RECOMPILE. This can have unintended side-effects, and is presumably not the expected majority case (stored procedures promote plan reuse).

`STATISTICS IO, TIME` comparison

The data provided shows that the new query is better in several ways. Scan counts are lower on tables vOS and O, with fewer logical reads as well. Tables CWM, CRM, UGU, URR and UGM are not scanned at all. That said, the results may not be directly comparable since the second set shows no read-ahead at all. You need to ensure the tests are fair, with the same starting conditions, as far as possible.