Question MariaDB innodb_buffer_pool_size

[heopas]

Hello, i am trying to change innodb_buffer_pool_size on mariadb/mysql but it seems that is not possible.
I have change my.cnf. and then "systemctl restart mariadb". DB restart successful but i can see 128M pool size using phpmyadmin.

Changed /etc/my.cnf. to following:

[mysqld]
#bind-address = ::
bind-address = 127.0.0.1
skip_name_resolve
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[mysqld]
innodb_buffer_pool_size = 10G

 

[Bitpalast]

I think only the first [mysqld] section is processed. Maybe it helps to move the instruction up into that first section and remove your additional section?

 

[heopas]

Already done that. Not working...

 

[Bitpalast]

Very strange. I think it's the correct way to add it to the mysqld section. Add it there, restart the service (# service restart mariadb), should work.

What's the output of
# plesk db
> show variables like 'innodb_buffer_pool%';
It should show the new variable value.

Verify that the configuration directive is not overwritten somehow:
# grep innodb /etc/my.cnf.d/*.*
(should return empty result)

Are you using the correct my.cnf location (/etc/my.cnf on Redhat, CentOS, but /etc/mysql/my.cnf on Debian, Ubuntu)?

Do you have a second DB service on your machine? It is thinkable that you have MariaDB on say port 3306 and a second MySQL on say 3333, so that you are not seeing the configuration values of the one that you have re-configured.

Any hints in your log files, e.g. invalid configuration directives (/var/log/mariadb/mariadb.log)?

 

[heopas]

Very strange. I think it's the correct way to add it to the mysqld section. Add it there, restart the service (# service restart mariadb), should work.

What's the output of
# plesk db
> show variables like 'innodb_buffer_pool%';
It should show the new variable value.

Verify that the configuration directive is not overwritten somehow:
# grep innodb /etc/my.cnf.d/*.*
(should return empty result)

Are you using the correct my.cnf location (/etc/my.cnf on Redhat, CentOS, but /etc/mysql/my.cnf on Debian, Ubuntu)?

Do you have a second DB service on your machine? It is thinkable that you have MariaDB on say port 3306 and a second MySQL on say 3333, so that you are not seeing the configuration values of the one that you have re-configured.

Any hints in your log files, e.g. invalid configuration directives (/var/log/mariadb/mariadb.log)?

1) plesk db

2) grep innodb /etc/my.cnf.d/*.*
return empty

3) Are you using the correct my.cnf location (/etc/my.cnf on Redhat, CentOS, but /etc/mysql/my.cnf on Debian, Ubuntu)?
I am using centos and file location on /etc/my.cnf. The strange thing is that it has an extra dot in the end if i remove the dot mariadb doesn' t start with error that file is missing.

4) Do you have a second DB service on your machine?
I have only one database

5) /var/log/mariadb/mariadb.log
It is empty. Logs are on other file: /var/lib/mysql/MY_SERVER_HOSTNAME.err and contain the following

161215 12:42:17 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
161215 12:42:17 [Note] /usr/libexec/mysqld (mysqld 5.5.52-MariaDB) starting as process 12603 ...
161215 12:42:17 InnoDB: The InnoDB memory heap is disabled
161215 12:42:17 InnoDB: Mutexes and rw_locks use GCC atomic builtins
161215 12:42:17 InnoDB: Compressed tables use zlib 1.2.7
161215 12:42:17 InnoDB: Using Linux native AIO
161215 12:42:17 InnoDB: Initializing buffer pool, size = 128.0M
161215 12:42:17 InnoDB: Completed initialization of buffer pool
161215 12:42:17 InnoDB: highest supported file format is Barracuda.
161215 12:42:17  InnoDB: Waiting for the background threads to start
161215 12:42:18 Percona XtraDB (http://www.percona.com) 5.5.49-MariaDB-38.0 started; log sequence number 1579476200
161215 12:42:18 [Note] Plugin 'FEEDBACK' is disabled.
161215 12:42:18 [Note] Server socket created on IP: '0.0.0.0'.
161215 12:42:18 [Note] Event Scheduler: Loaded 0 events
161215 12:42:18 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.52-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server

 

[heopas]

Another strange thing is that i can see many process

 

[heopas]

Another screenshot from plesk

 

[Bitpalast]

Thank you for your detailed reports.

I tested it on a test machine and could successfully change the innodb_buffer_size. I don't think that the issue can occur when you have done the entry correctly. Back to the roots. There has to be a problem with your my.cnf file, else the updated configuration must be read and applied correctly. Please verify that this is your /etc/my.cnf content:

[mysqld]
bind-address = ::
skip_name_resolve
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
innodb_buffer_pool_size=10G
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

Make sure that there is only a single [mysqld] section and that "innodb_buffer_pool_size" is part of that section and not placed in another section or in a second mysqld-section or that it is preceded by a "#". if your my.cnf file contains the lines shown above, it will set the variable correctly. Make sure that "service restart mariadb" has been done afterwards.

If all that is the case, the only reason for that it might not work, is a virtualization environment that is interfering with your "local" setting. But very unlikely. So if you verify that your file looks like above, have restarted your db service and yet don't see the change of the variable, I am out of ideas.

 

[Bitpalast]

...
3) ...
I am using centos and file location on /etc/my.cnf. The strange thing is that it has an extra dot in the end if i remove the dot mariadb doesn' t start with error that file is missing.

5) /var/log/mariadb/mariadb.log
It is empty. Logs are on other file: /var/lib/mysql/MY_SERVER_HOSTNAME.err and contain the following ...

Sorry, I overlooked these two inputs.

3) Yes, that is strange. The default setup is my.cnf, not my.cnf.. But it could still work.

5) In connection with (3) it indicates that another configuration is read, not the /etc/my.cnf, because in your configuration file that you had provided in your first post, the log location is set to
log-error=/var/log/mariadb/mariadb.log
and not
log-error=/var/lib/mysql/MY_SERVER_HOSTNAME.err

It seems to be a problem with the configuration file, still. The question remains why the entries of that file are not read/interpreted or where the "real" file that is used to run your database is located at. If you are on CentOS on a virtual machine, you may need to ask your provider for details, because in that case it could be that the real configuration values are read from an environment and not from the file we are viewing.

 

[Tonies]

Very strange. I think it's the correct way to add it to the mysqld section. Add it there, restart the service (# service restart mariadb), should work.

What's the output of
# plesk db
> show variables like 'innodb_buffer_pool%';
It should show the new variable value.

Verify that the configuration directive is not overwritten somehow:
# grep innodb /etc/my.cnf.d/*.*
(should return empty result)

Are you using the correct my.cnf location (/etc/my.cnf on Redhat, CentOS, but /etc/mysql/my.cnf on Debian, Ubuntu)?

Do you have a second DB service on your machine? It is thinkable that you have MariaDB on say port 3306 and a second MySQL on say 3333, so that you are not seeing the configuration values of the one that you have re-configured.

Any hints in your log files, e.g. invalid configuration directives (/var/log/mariadb/mariadb.log)?

Hi Peter,

I am trying to optimize the etc/my.cnf for my database.

I was reading some articles for example from a hosting providerto optimize Mariadb.

Based on your answer to use this command in CLI and it should return empty, it didn't: grep innodb /etc/my.cnf.d/*.*
It resulted to this:
/etc/my.cnf.d/enable_encryption.preset:loose-innodb-encrypt-log
/etc/my.cnf.d/enable_encryption.preset:loose-innodb-encrypt-tables
/etc/my.cnf.d/server.cnf:innodb_buffer_pool_size=6G
/etc/my.cnf.d/server.cnf:innodb_log_file_size=768M
/etc/my.cnf.d/server.cnf:#innodb_autoinc_lock_mode=2
/etc/my.cnf.d/server.cnf:#innodb_flush_log_at_trx_commit=0

Does this mean that my.cnf file is overwritten by /etc/my.cnf.d/server.cnf (I think this might be a global value) and should I still edit the et/my.cnf file with my settings after the [mysqld] section at top?

Some advice would be appreciate on where to edit the my.cnf file to get the settings I would like please.

Thanks in advance.

 

[G J Piper]

[mysqld]
innodb_buffer_pool_size = 10G

You might try removing the spaces from either side of the equals sign(s) In your settings.
So:

innodb_buffer_pool_size=10G

Also you should restart your DB service or reload the configs after changing the settings if you haven't tried that:

service mariadb restart

 

[Ankebut]

sorry,
i have add in etc/mysql my.cnf this

innodb_flush_log_at_trx_commit=2

after
service mariadb restart

not working in witch file must add this?

 

[Dukemaster]

Hi @Ankebut,
in my.cnf under daemon
This is my configuration as an example by the recommendations of mysql tuner, MariaDB logs/restarts and WoltLab software support.
The most depends of your unique hardware (e.g. RAM(32G)) and the whole server configuration.
/etc/mysql/my.cnf

# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
[mysqld]
bind-address=127.0.0.1
#bind-address = ::ffff:127.0.0.1
local-infile=0
innodb_buffer_pool_size=10G
innodb_log_file_size=256M
innodb_flush_log_at_trx_commit=2
innodb_autoextend_increment=1000

 

[Chris Ramires]

Hello
Must be specified in bytes

innodb_buffer_pool_size = 838860800

instead

innodb_buffer_pool_size = 800M