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? Commented Mar 10, 2012 at 1:36
  • It is the default lamp stack on top of Ubuntu server x64 Commented Mar 10, 2012 at 6:13
  • There was another config file located in /opt/lampp/etc that I believe is going to work. Commented 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 Commented 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] Commented 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. Commented Mar 10, 2012 at 6:17
  • You should post your my.cnf (/etc/my.cnf or on debian probably /etc/mysql/my.cnf) Commented 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. Commented 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. Commented 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! Commented 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. Commented 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. Commented Mar 10, 2012 at 15:49

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.