5

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.

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
alfish
  • 3,004
  • 7
  • 21
  • 18

3 Answers3

5

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.

Community
  • 1
alfish
  • 3,004
  • 7
  • 21
  • 18
2

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.

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
1

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

Alireza
  • 3,676
  • 10
  • 38
  • 44