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
  • 1
  • 2
  • 7
  • 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 '12 at 01:36
  • It is the default lamp stack on top of Ubuntu server x64 – tgrosinger Mar 10 '12 at 06:13
  • There was another config file located in /opt/lampp/etc that I believe is going to work. – tgrosinger Mar 13 '12 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." https://stackoverflow.com/q/52620824/1066234 Find all possible variables at https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html – Avatar Apr 04 '21 at 10:32

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,052
  • 12
  • 19
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,676
  • 20
  • 38
  • 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 '12 at 06:17
  • You should post your my.cnf (/etc/my.cnf or on debian probably /etc/mysql/my.cnf) – thinice Mar 10 '12 at 07: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 '12 at 07: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 '12 at 08: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: http://hastebin.com/binaqeforu.vala Thanks so much! – tgrosinger Mar 10 '12 at 20:06
  • try removing lines 26-29 of your my.cnf (the `mysqld_safe` section) – thinice Mar 10 '12 at 21:53
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
  • 317
  • 3
  • 12
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
  • 573
  • 5
  • 14
  • 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 '12 at 06: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 '12 at 15:49