Optimising MariaDB for large AWS machines 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 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.