I use MySQL 5.5.23-1~dotdeb.0 (Debian) and here is the relevant part of my.cnf

default-storage-engine = innodb
innodb_buffer_pool_size = 3G
innodb_log_file_size = 256M
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 4M
innodb_additional_mem_pool_size = 20M

The problem is that no matter what value I choose for innodb_buffer_pool_size (I tried also 4G and 8G and 8000M). I get the following from mysql> SHOW global VARIABLES;

| innodb_buffer_pool_size | 134217728  

Result from the Tuning primer script:

Current InnoDB index space = 621 M
Current InnoDB data space = 3.82 G
Current InnoDB buffer pool free = 0 %
Current innodb_buffer_pool_size = 128 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory 

I appreciate your hints to fix this.

  • 1
    Did you restart mysql ? – ypercubeᵀᴹ Apr 26 '12 at 6:55
  • innodb_log_file_size is not a dynamic variable, so restarting is needed. – ypercubeᵀᴹ Apr 26 '12 at 8:04
  • Of course. However, the parameter's value did not change after several restarts – alfish Apr 26 '12 at 11:15
  • Can you check the initialization parameters that start the mysql service? It's probably in a file in directory: /etc/init.d/. – ypercubeᵀᴹ Apr 26 '12 at 11:48
  • Also: Try this: mysqld --verbose --help and this command: mysqld --print-defaults. The first will have a line where it says which option files it is reading. ( I guess it is reading from more than one and the second is overriding your settings) – ypercubeᵀᴹ Apr 26 '12 at 11:56

Ok, thanks this answer I figured out that I needed to put the config WITHIN [mysqld] block in my.cnf to take effect.

My mistake was that I just had appended the commands at the end of my.cnf file.

Then at mysql restart I encounterd

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes

To resolve this error, I needed to deleted log

rm -f /var/lib/mysql/ib_logfile[01]

and restart as Rolando told here.

  • 1
    +1 for finding you own answer and examining my.cnf. That mistake has bitten a couple of my clients. I caught something similar for someone in ServerFault back in January 2012 : serverfault.com/a/347079/69271 – RolandoMySQLDBA Apr 27 '12 at 14:47

DISCLAIMER : Not a Debian User

Based on the comments, I would suggest the following

cp /etc/mysql/my.cnf /etc/my.cnf
cp /etc/mysql/my.cnf /usr/etc/my.cnf 
cp /etc/mysql/my.cnf /var/lib/mysql/my.cnf (or wherever datadir is)

If after making these my.cnf file and restarting mysql you still cannot change innodb_buffer_pool_size, then I would suspect the mysql binary mysqld. It is most likely source compiled and probably has the values for InnoDB hardcoded.

To verify if the mysqld is source compiled, check the processlist of the OS while mysqld is up and running. If you see dozens of mysqld connections, chances are the mysqld was source compied.

From there, I would suggest uninstalling mysql and downloading an RPM or tar installltion. Then, see if my.cnf responds.

  • copying my.cnf around did not solve the problem. Running 'ps aux | grep mysql' yeilds two processes: one for 'root' at /bin/sh /usr/bin/mysqld_safe and the other for mysql /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/lib/mysql/debian.pid --socket=/var/run/mysqld/mysqld.sock --port=3306. Any other hints? Thanks – alfish Apr 27 '12 at 10:24

If you want MySQL to understand your new parameters in My.cnf file you have to restart mySQL.

  • Probably I should explicitly say that at the first place: After restarting mysql daemon the value does not take effect. – alfish Apr 26 '12 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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