Bug #7744 Server does not recognize parameters set in my.ini

• Submitted: 8 Jan 2005 14:18
• Modified: 8 Jan 2005 19:57
• Reporter: [ name withheld ] (Basic Quality Contributor)
• Status: Can't repeat
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 4.1.8-nt-max
• OS: Windows (Windows 2000 server)
• Assigned to: Heikki Tuuri
• CPU Architecture: Any

[8 Jan 2005 14:18] [ name withheld ]

Description:
Hi, when trying to tune innodb parameters in my.ini it seems to be insensible to changes of the innodb_buffer_pool_size param, it seems to be hardcoded.

How to repeat:
The first run:
(relevant section of my.ini file)
# Additional memory pool that is used by InnoDB to store metadata
# information.  If InnoDB requires more memory for this purpose it will
# start to allocate it from the OS.  As this is fast enough on most
# recent operating systems, you normally do not need to change this
# value. SHOW INNODB STATUS will display the current amount used.
innodb_additional_mem_pool_size=8M

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system.  Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
innodb_buffer_pool_size=16M

Results:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.8-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like 'innodb%pool_size';
+---------------------------------+---------+
| Variable_name                   | Value   |
+---------------------------------+---------+
| innodb_additional_mem_pool_size | 8388608 |
| innodb_buffer_pool_size         | 8388608 |
+---------------------------------+---------+
2 rows in set (0.00 sec)

The second run:
(relevant section of my.ini file):

# Additional memory pool that is used by InnoDB to store metadata
# information.  If InnoDB requires more memory for this purpose it will
# start to allocate it from the OS.  As this is fast enough on most
# recent operating systems, you normally do not need to change this
# value. SHOW INNODB STATUS will display the current amount used.
innodb_additional_mem_pool_size=16M

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system.  Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
innodb_buffer_pool_size=32M

(YOU SEE I'VE DOUBLED THE AVAILABLE MEMORY)
Restarted the service.
Results:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.8-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like 'innodb%pool_size';
+---------------------------------+----------+
| Variable_name                   | Value    |
+---------------------------------+----------+
| innodb_additional_mem_pool_size | 16777216 |
| innodb_buffer_pool_size         | 8388608  |
+---------------------------------+----------+
2 rows in set (0.00 sec)

it seems to be affected by changes to innodb_additional_mem_pool_size, BUT NOT TO CHANGES TO innodb_buffer_pool_size.

Thanks in advance for your help.

[8 Jan 2005 19:57] MySQL Verification Team

I wasn't able to repeat please see below:

C:\mysql\bin>type d:\windows\my.ini
[mysqld]
basedir= c:/mysql
datadir= c:/mysql/data
port   = 3306

innodb_data_home_dir = c:/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = c:/mysql/data
innodb_log_arch_dir = c:/mysql/data

innodb_additional_mem_pool_size = 8M
innodb_buffer_pool_size = 16M

C:\mysql\bin>mysqld-max-nt --install
Service successfully installed.

C:\mysql\bin>net start mysql
O serviço de MySQL está sendo iniciado.
O serviço de MySQL foi iniciado com êxito.

C:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.8-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like 'innodb%pool_size';
+---------------------------------+----------+
| Variable_name                   | Value    |
+---------------------------------+----------+
| innodb_additional_mem_pool_size | 8388608  |
| innodb_buffer_pool_size         | 16777216 |
+---------------------------------+----------+
2 rows in set (0.00 sec)

mysql> exit
Bye

C:\mysql\bin>net stop mysql
O serviço de MySQL está sendo finalizado ..
O serviço de MySQL foi finalizado com êxito.

C:\mysql\bin>type d:\windows\my.ini
[mysqld]
basedir= c:/mysql
datadir= c:/mysql/data
port   = 3306

innodb_data_home_dir = c:/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = c:/mysql/data
innodb_log_arch_dir = c:/mysql/data

innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 32M

C:\mysql\bin>net start mysql
O serviço de MySQL está sendo iniciado.
O serviço de MySQL foi iniciado com êxito.

C:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.8-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like 'innodb%pool_size';
+---------------------------------+----------+
| Variable_name                   | Value    |
+---------------------------------+----------+
| innodb_additional_mem_pool_size | 16777216 |
| innodb_buffer_pool_size         | 33554432 |
+---------------------------------+----------+
2 rows in set (0.00 sec)

mysql>