0

I have a database that is hosted on AWS Aurora. Now let's say it has been assigned particular memory, RAM, buffer pool size, etc.

I also have my.ini file on my local machine. What happens if I change the pool size to 16M in my.ini? Would that affect the performance of my query when I run on my machine? Or will it still use the buffer size set up in aurora?

My current variables are like this:

show variables;
innodb_buffer_pool_size 20915945472

Is this number from the aurora? My local my.ini file reads like this:

innodb_buffer_pool_size=2.5G

1 Answer 1

1

Settings

There is a pecking order of what config files are used. I hesitate to say exactly what Aurora does, since it is offshoot of MySQL. First will be /etc/my.chf of /etc/my.ini; last will be .my.cnf in your home directory. (Note the leading dot.) Later values will override earlier values.

Be sure to put things in the [mysqld] group in each file.

Be aware that the various settings become visible at different times -- after restart; after re-login; etc. Also, note that some are "global", some are "session" (and initialized to the global value); etc. Also note that some can be changed dynamically with a SET command; but, again, there are various variants due to global/session.

Buffer_pool

The main use of memory is for the buffer_pool.

  • Bigger = faster
  • Too big --> swapping, and slowdown, or even crash

The default for Aurora is probably optimal for the amount of RAM in the server. Do not change it. In the absence of a decent default, set it to about 70% of available RAM for MySQL.

3
  • But what about the my.ini file number? What if I change that to let's say 16M? Will it still use the Aurora settings? Sep 15, 2019 at 23:22
  • @Hello.World - I added to my Answer. innodb_buffer_pool is only global, and (but depending on the version) can only be set in a config file.
    – Rick James
    Sep 15, 2019 at 23:34
  • If you are using Engine=InnoDB, 16M for innodb_buffer_pool_size is dangerously tiny. The system is likely to run slowly and possibly crash.
    – Rick James
    Sep 16, 2019 at 15:54

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.