Understanding config variables in MySQL

Question

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

Answer 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.