MySQL – Optimization: Is Increasing Innodb_pool_size Useful?

amazon-rdsdatabase-tuninginnodbMySQL

We run an AWS RDS instance with 8vCPUs and 64GB memory.
All tables are using InnoDB engine.

I have kept the default setup of innodb_pool_size to be half the memory.

What I see is that almost 30Gb of memory is used and no read I/O operation (except for some spikes).

Freeable memory:

Read IO:

I've seen some great blog post, answers (like this one How large should be mysql innodb_buffer_pool_size?) about tuning MySQL.

Would it be beneficial to increase the innodb_pool_size to something like 3/4 of the memory? As I don't see read IO.

Also, but I don't know if that is related: what can explain this huge storage drop in a couple of minutes? We don't store large objects, BLOBs, etc.

Free Storage:

Best Answer

It's good you don't see a lot of reads, ideally it should be zero if available memory permits.

When InnoDB needs a page it checks the buffer pool. If the page is in the buffer pool - done. If not - InnoDB will read it from the disk and cache it in the buffer pool. So, if the data set fits into the buffer pool there will be eventually no reads from disk.

If you already get no reads further buffer pool increase won't increase performance.

The storage drop - I don't know RDS is a black box, who know what's going on there.

Related Solutions

MySQL 5.5 Runs Out of Memory, Drops All Connections When Creating Many Databases

The first thing that comes to mind is the Server Model : db.m1.large

What limiting factors are placed on a MySQL RDS?

If you spin up an Amazon RDS instance of MySQL, you would subject yourself to whatever constraints are given. All models of MySQL Amazon RDS have the same major options but differ in only two aspects

max_connections
innodb_buffer_pool_size

Here is a Chart I posted Last Month

MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

I posted this in my other posts in the DBA StackExchange

You are using m1.large. Since the InnoDB Buffer Pool is 3/4 of the Instance. That's means you have 7.2G but only 1.8 is usable. That model is capable of having up to 623 connections. Each connection can consume memory because of

join buffers
sort buffers
read buffers
thread info

Amazon RDS is simply micromanaging resources. Since DB Connections can consume RAM, connections are probably being disallowed due to the lack of RAM needed.

SUGGESTIONS

Try reducing InnoDB log IO innodb_flush_log_at_trx_commit=0 during the mass creation
Make sure you are not doing large transactions during any automatic backups or snapshots
Try a bigger server model m1.xlarge or m2.xlarge

MySQL – Randomly Slow Inserts with InnoDB

I would suggest that you are 12ms away (latency) from the AWS datacenter. You are stuck with that overhead. Conclusion: Beware of the timings.
The "<1ms" is realistic only if client and server are in the same datacenter and the disk is SSD. What is "magnetic storage"? Perhaps that is an old, spinning, disk. In that case 10ms is a reasonable guess for one write. And it is hard to determine from the available numbers what the latency really is. (Presumably less than 12ms.) Conclusion: Be sure to use SSDs.
What does Amazon say about interference from other instances on the same hardware? You have a "small" instance; there are probably many others sharing the same I/O. If one of them has a spike, you might be slowed down. With "large", you might have fewer competing customers. And it might be SSD. Conclusion: Focus on average or 95th percentile, not spikes.
innodb_flush_log_at_trx_commit=1 requires at least 1 disk write per transaction. =0 delays and combines writes, hence the noticeable speedup. One way to soften it is to batch multiple queries into a single BEGIN...COMMIT. (Only do such where the logic makes sense.) Conclusion: Bigger transactions. (However, you will still be stuck with 12+ms per transaction instead of per insert.)
Updates to indexes (except for UNIQUE indexes) are delayed and can usually be treated as zero impact.
char(21) COLLATE utf8_unicode_ci -- Is it always 21 characters? If not, use VARCHAR. Perhaps it is always ascii, not utf8? If so, use CHARACTER SET ascii.
PRIMARY KEY (seqid), UNIQUE KEYid(id) -- Why have both? As already mentioned, it causes overhead included in the INSERT time, namely to verify uniqueness before committing.
The activity seems to be very low. Normally people ask questions like yours when they are having trouble with hundreds or thousands of queries per second.
Here's a way to get a 16-byte, chronologically ordered, UUID: http://mysql.rjweb.org/doc.php/uuid . (But not as easily displayed.)

Best Answer

Related Solutions

MySQL 5.5 Runs Out of Memory, Drops All Connections When Creating Many Databases

SUGGESTIONS

MySQL – Randomly Slow Inserts with InnoDB

Related Question