Been reading up on DB optimisation (I am an analyst, not really a Sysadmin), and it would seem the biggest adjustment one can make is change the innodb_buffer_pool_size, innodb_buffer_pool_instances and innodb_file_per_table.

Is there some point that an extremely large buffer pool just doesn't make sense anymore. For example, when using AWS, I can get a machine with 64 cores and 500GB RAM if I need to do analysis on data that is around 200 mil rows (indexed). Is it worth setting innodb_buffer_pool_size = 400G and innodb_buffer_pool_instances = 60:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld]
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
user                           = mysql
# applies only when running as root
#memlock                        = 1

table_open_cache               = 3072
table_definition_cache         = 4096
max_heap_table_size            = 64M
tmp_table_size                 = 64M
max_connections                = 505
max_user_connections           = 500
max_allowed_packet             = 16M
thread_cache_size              = 32
query_cache_size               = 64M

# InnoDB
default_table_type             = InnoDB

# 80% of ram that is dedicated for the database
innodb_buffer_pool_size        = 400G
# number of CPU cores dedicated to the MySQL InnoDB backend 
innodb_buffer_pool_instances = 60 

innodb_data_file_path          = ibdata1:128M:autoextend
innodb_file_per_table          = 1
innodb_log_file_size           = 1G
innodb_log_files_in_group      = 2
  • innodb_buffer_pool_instances=60 will be detrimental to your server. Do the math when your review the specifics of innodb_lru_scan_depth from the refman. If you are using the default of 1024 for innodb_lru_scan_depth x 60 (buffer_pool_instances) = 61440 work effort of CPU. If you lower innodb_buffer_pool_instances=8 AND lower innodb_lru_scan_depth=100 (the minimum, You have 8 * 100 = 800 work effort of CPU. You still have the advantage of avoiding mutex contention with 8 innodb_buffer_pool_instances. – Wilson Hauck Oct 11 at 22:11
  • Additional information request, please. Post on pastebin.com or here. RAM size of your MySQL Host server A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device for server tuning analysis. – Wilson Hauck Oct 17 at 14:26
  • Keep in mind that one connection will use only one core. The contention that 'instances' addresses only occurs will many connections. – Rick James Oct 18 at 20:51
up vote 1 down vote accepted

innodb_buffer_pool_instances will not give you a huge advantage unless you are suffering from a very specific write contention between multiple write threads. I have personally never needed to increase it over the default 8 instances.

I use 500GB servers for MySQL and dedicate around 384 GB to the buffer pool- more would also make sense - I optimize for huge concurrency and even then I am thinking to increase it to an 80% or 85% of the total available memory (but want to leave enough for the filesystem cache).

If that would be an advantage for you or not, it will depend on your access patterns- if you are doing analytics queries, your speed up may be minimal- and of course it has decreasing returns to the point of not being an advantage at all for memory larger than a dataset.

It is very difficult to provide a general advice, but if I had todo it would be this: 1) make sure you are using the right tool (vanilla mysql-InnoDB- may be not a great a great tool for analytics- plugins or external tool may fit better); 2) keep 10-20 percent of your data on memory (most needs follow a 90-10, or 80-rule for frequency of access; and 3) keep your buffer pool with a 75%-80% of your total memory if you only use InnoDB as your main engine. Stress on general advice, your needs will vary and so your configuration. See what is your bottleneck and try to optimize that.

  • Thanks for the insight. I need to do analytical queries (not worried about concurrency) and was hoping that by throwing a lot of RAM at the problem, it would help. Also looking into running Greenplum as an alternative because of the OLAP functionality. – Hanjo Jo'burg Odendaal Oct 12 at 9:23
  • 1
    @HanjoJo'burgOdendaal - Many Analytic queries benefit greatly from building and maintaining Summary Tables. Summary tables might give you 10x speedup; the discussion in this Q&A is probably limited 4% speedup. – Rick James Oct 18 at 20:49

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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