12

I am having difficulty setting the buffer pool size and log file size for MySql InnoDB. I am far from a MySql expert but have been reading around and it seems that to change this I just add these lines to my /etc/mysql/my.cnf

# Set buffer pool size to 50-80% of your computer's memory
innodb_buffer_pool_size=2048M
innodb_additional_mem_pool_size=512M
#
# Set the log file size to about 25% of the buffer pool size
innodb_log_file_size=256M
innodb_log_buffer_size=128M

The server has about 7GB of memory and is also running a web server so I think these numbers should be an okay starting point. After saving and restarting the server however it does not seem that the changes have taken effect. I tried running Mysqltuner which reported that the buffer pool is still at 16.0M. Any idea what I am doing wrong? Let me know if you would like to see more of the config file. Thanks!

tgrosinger
  • 231

4 Answers4

7

Make sure those lines are within the [mysqld] section i.e. after [mysqld] but before any other [section] such as [mysqldump].

xofer
  • 3,092
2

Check your MySQL error log for information - most likely located in /var/lib/mysql - also, is this a 32-bit system with the bigmem kernel? If so, you can't address more than 2GB for MySQL.

Also - you'll want to confirm from MySQL itself about the buffer pool size - 16MB sounds a bit off, considering the default is 128M. You can confirm this by typing 'SHOW VARIABLES LIKE 'innodb_buffer_pool_size' into a MySQL session for that server. Result is in bytes.

thinice
  • 4,746
  • 23
  • 38
2

In my case the problem was innodb_buffer_pool_instances.

Since I was reducing innodb_buffer_pool_size, it became less than one 1GB per instance, so it ended up rounding it.

When I also reduced the instances, it finally changed the pool size!

carla
  • 347
0

Might be you start mysql with --no-defaults or (-no-defaults), which causes it to not load a config at all. Theres also --defaults-file (or -default-file, not sure), which causes it to load a specific config. Also check if that is to a different config than the one you edit.

You can try starting with:

--innodb_buffer_pool_size=2048M --innodb_log_buffer_size=512M

Or chech the paramters you're starting with already and see if one of them causes it to omit the config.

juwi
  • 591