Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using Amazon's RDS db.m1.small.

innodb_buffer_pool_size is set to 1125.00 MB.

Current index size is 1110.00 MB and growing slowly but steadily.

I do not need more hard drive space.

I want to know if it is necessary to upgrade to a larger instance (db.m1.medium) in order to keep my app working smoothly. Is it important that the index fits into memory? What should be my next steps?

share|improve this question
add comment

1 Answer 1

up vote 0 down vote accepted

It isn't the index size... it's the data and indexes. The InnoDB Buffer Pool:

...holds cached InnoDB data for both tables and indexes

http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_buffer_pool

The larger the buffer pool, the more InnoDB acts like an in-memory database, reading data from disk once and then accessing the data from memory during subsequent reads. The buffer pool even caches data changed by insert and update operations, so that disk writes can be grouped together for better performance.

http://dev.mysql.com/doc/refman/5.6/en/innodb-buffer-pool.html

Conversely, the smaller the buffer pool, the less InnoDB acts like an in-memory database.

In an ideal world, your buffer pool is larger than data size plus index size plus a little bit of overhead, because InnoDB does nothing to a page from a tablespace without storing that page in the buffer pool, it follows that when the pool is full, something less recently used gets evicted from the pool. Practicality, of course, often necessitates something smaller than the ideal, and the key to appropriate sizing is the working data set. The more infrequently-accessed data you have on your server, the fewer potential performance issues you should expect from an undersized pool.

There is, of course, no formula for this... familiarity with your unique working data set is the only "tool" you can use... but SHOW STATUS LIKE '%buffer%'; will give you the counters.

Note that Innodb_buffer_pool_pages_free is only an indication of completely unused space in the pool... it's not an emergency situation or even intensely meaningful if this value is very low or 0, because there is no process to spontaneously evict pages from the pool even when they are old and idle... but as long as they aren't "dirty" then replacing one page with something else is essentially no different in cost than loading a page into free space. Zero free only means that at some point since the server was started, the total data and indexes read into the pool exceeded the size of the pool and there have not been enough deletions or tables dropped since the last peak to free up any pages. Note also that running a backup will tend to fill an undersized pool, relatively harmlessly, depending on what's been loaded into the pool by the prior workload.

share|improve this answer
    
So basically it means performance will not drop instantly but decline gradually as more and more data needs to be read from disc instead of memory, right? I don't need to upgrade right now, just keep an eye on it? –  Sebastian Dec 21 '13 at 16:55
    
Right. Monitoring your global status values (not just the ones for InnoDB) and Cloudwatch metrics for general trends and patterns should give you meaningful indications that you're undersized, but as long as the data size and traffic grow at a reasonably steady pace, I would anticipate an eventual degradation not to be sudden... be proactive but if it is fine now, it's unlikey that you need to upgrade right away. –  Michael - sqlbot Dec 21 '13 at 17:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.