1

I have a couple of questions about tuning a MySQL (5.5) database. It seems the clients database server has never had any performance tuning done. Upon reading around, it seems the one thing is to set innodb_buffer_pool_size to "80% of the available RAM". Now, the physical size of the database is about 5Gig, and the available RAM on the server is 32Gigs. It seems to make no sense to me to make the innodb_buffer_pool_size = 25G.

At the moment I have set

innodb_buffer_pool_size: "8G" innodb_buffer_pool_instances: "8" innodb_log_files_in_group: "2" innodb_log_file_size: "1G"

Which, reading around (and running mysql-tuner script) seems about sane.

My question is: Is it a bad idea to have a buffer_pool_size greater than the size of the database itself. Is it bad to effectively have all the data in the buffer_pool ?

Warming the cache

The other part is warming the cache. Reading other posts on the net, the suggestion is to run a bunch of select count (*) from <table> to warm it. If I do that, I get the amount of RAM at point (1) in the picture below.

If instead I run select * from <table> I get a heap more RAM used. Which, I assume means that I have more data loaded into the cache. Which seems good to me.

ram profile for counts

Is it better to run select * or select count(*) for warming the cache?

2 Answers 2

3

Warmup

If you have a secondary index on the table, SELECT COUNT(*)... will use that. So the first "warming" fetched that index. The second "warming" fetched the data; note that the hump is higher, since the data is bigger?

That is, COUNT(*) works only for an InnoDB table with no secondary keys.

This might work: SELECT COUNT(*) FROM t FORCE INDEX(PRIMARY);

You are overdue for an upgrade. With 5.6.3, there is innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup, which is better than your warmup script; it efficiently restores the buffer pool to whatever was in it.

Buffer pool size

5GB of data; 32GB of RAM --> 8GB sounds like a good buffer_pool_size:

  • Less than 5GB probably leads to extra I/O (depending on "working set size").
  • 8GB leaves enough room on top of the 5GB for the "Change Buffer" and other overhead.
  • 8-25GB -- the rest would be idle.
  • over 25GB -- threaten to do swapping, which would be really bad for performance.
0
0

No, it is NOT a bad idea to have innodb_buffer_pool_size greater than the size of the database.

Some reasons why, A: innodb_change_buffer_max_size default of 25% is set aside for data management in RAM. If you ask for 100M, only 75M is effectively available. You can manage this percentage in your my.cnf/ini - be conservative and analyze GLOBAL STATUS Innodb_buffer_pool_pages_misc / Innodb_buffer_pool_pages_total for percentage of miscellaneous pages in use at this time.

B. Are you going to add/modify data to your database each day? Then you will want to have a reasonable cushion for activity.

Reasonable limits? (Database size * 1.25) * 1.2 ) if you expect 10% growth in the near term. Just so you have some cushion. Review capacity in 90 days.

Database size * 3 = likely overkill. Remember you probably have only so much RAM and simple math must be tempered with reality of available RAM. Or acquire more RAM.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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