Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
    
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 1:36
    
It is the default lamp stack on top of Ubuntu server x64 –  tgrosinger Mar 10 '12 at 6: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

3 Answers 3

up vote 1 down vote accepted

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

share|improve this answer
    
They are in the correct section, in [mysqld] right before [mysqldump] –  tgrosinger Mar 10 '12 at 6:15

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.

share|improve this answer
    
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 6:17
    
You should post your my.cnf (/etc/my.cnf or on debian probably /etc/mysql/my.cnf) –  thinice Mar 10 '12 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 '12 at 7:53
    
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 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 '12 at 20:06

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.

share|improve this answer
    
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 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 '12 at 15:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.