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!

Improve this question
4
  • what distribution? are you using the distro's packages or something else? How are you starting mysql? In short, what did you install, what did you install it on, and how did you install it?
    – navaho
    Mar 10, 2012 at 1:36
  • It is the default lamp stack on top of Ubuntu server x64
    – tgrosinger
    Mar 10, 2012 at 6:13
  • There was another config file located in /opt/lampp/etc that I believe is going to work.
    – tgrosinger
    Mar 13, 2012 at 13:54
  • Sidenote: "innodb_use_sys_malloc and innodb_additional_mem_pool_size were deprecated in MySQL 5.6 and removed in MySQL 5.7." stackoverflow.com/q/52620824/1066234 Find all possible variables at dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html
    – Avatar
    Apr 4, 2021 at 10:32

4 Answers 4

Reset to default
7

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

Improve this answer
1
  • They are in the correct section, in [mysqld] right before [mysqldump]
    – tgrosinger
    Mar 10, 2012 at 6:15
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.

Improve this answer
6
  • For some reason I can not get into that directory. I can see it when I do ls on /var/lib but when I try to cd into it I get "permission denied". If I try to cd in as sudo it says "No such file or directory". Thoughts? It is a 64bit installation of Ubuntu Server.
    – tgrosinger
    Mar 10, 2012 at 6:17
  • You should post your my.cnf (/etc/my.cnf or on debian probably /etc/mysql/my.cnf)
    – thinice
    Mar 10, 2012 at 7:48
  • 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
    Mar 10, 2012 at 7:53
  • 1
    sudo cd won't work because sudo creates a subshell with the right privileges and closes it when finished, leaving you where you started. Try sudo -s instead.
    – Ladadadada
    Mar 10, 2012 at 8:07
  • So when I use SHOW VARIABLES LIKE... it says the buffer pool size is 16777216 which I believe is in fact 16M. Here is th my.cnf: hastebin.com/binaqeforu.vala Thanks so much!
    – tgrosinger
    Mar 10, 2012 at 20:06
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!

Improve this answer
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.

Improve this answer
2
  • How can I see how it is starting up in ubuntu? I am used to arch were I would just add it to the rc.d file, but I don't see anything like that in here.
    – tgrosinger
    Mar 10, 2012 at 6:21
  • Arch uses BSD Style Init, whereas Ubuntu uses SysV Init. So there is a file in /etc/init.d to start it and possibly a file in /etc/defaults where options are being set. By using something along the lines of 'ps -ef | grep mysql' you can see what options it is currently running with.
    – juwi
    Mar 10, 2012 at 15:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .