Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

INNODB STATUS
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.

share|improve this question
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
show 2 more comments

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer
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
    
You should accept your own answer on this one !!! –  RolandoMySQLDBA Apr 27 '12 at 14:48
add comment

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.

share|improve this answer
    
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
add comment

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

share|improve this answer
    
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
add comment

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.