4

The variable I am trying to change is innodb_buffer_pool_size. As you can see below the settings is 512M. But SELECT @@innodb_buffer_pool_size; returns 134217728.

When I execute: /usr/libexec/mysqld --help --verbose

My resulting config is:

Default options are read from the following files in the given order: 
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
  1. /etc/mysql/my.cnf - does not exist
  2. /etc/my.cnf - is shown below
  3. ~/.my.cnf - when I cd ~/ I am taken to /root where no my.cnf files exist. Does mysql run as root, would it be taken to the same directory? How can I tell?

Here is my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

skip-networking

innodb_thread_concurrency = 6
innodb_flush_log_at_trx_commit = 2
thread_concurrency = 12 
table_cache = 1024
query_cache_size = 64M
query_cache_type=1
query_cache_limit = 4M
expire_logs_days=10
max_binlog_size=100M
join_buffer_size = 8M
tmp_table_size =256M  
max_allowed_packet = 2M
thread_stack = 192K
thread_cache_size = 32
thread_concurrency = 4
table_open_cache  = 4096
myisam-recover=BACKUP
max_connections        = 2500
max_heap_table_size = 256M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
innodb_additional_mem_pool_size = 8M
innodb_log_buffer_size = 4M
innodb_log_file_size = 128M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 0
innodb_buffer_pool_size = 512M
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:256M:autoextend
innodb_autoextend_increment=512
slow_query_log = Off
slow_query_log_file = /var/www/html/log/mysql/slow-queries.log
long_query_time = 2
innodb_file_per_table = 1 

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


[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[isamchk]
key_buffer              = 32M
5
  • the options are as many as there are linux distributions, please update with which dist and version. But /etc/my.cnf should do it - have you reloaded mysqld after changing this ?
    – Spörri
    Commented May 8, 2015 at 21:35
  • To be 100% sure, please show cat /etc/my.cnf.d/* and cat $(grep mysql /etc/passwd | cut -d ":" -f 6)/my.cnf. You have also mysqld --print-defaults.
    – jynus
    Commented May 9, 2015 at 11:25
  • Apologies for sounding like the typical support person. It was the only thing that I could think of that matched your symptoms. (And you had not clearly stated that you had restarted.) Check SHOW VARIABLES LIKE 'basedir'; to see if it points to the directory with my.cnf (or my.ini). Check mysqld.err.
    – Rick James
    Commented May 9, 2015 at 19:23
  • 1
    Use strace mysqld --print-defaults. You will see which files are actually read by MySQL. If /etc/my.cnf is not open, check that it belongs to mysql user and is readable by its owner. Also check that no configuration file is read after that. Then you'll see the list of settings - values read from files and default values, check the value for innodb_buffer_pool_size. Commented Mar 4, 2018 at 1:07
  • @Federico Razzoli, very good suggestion! Commented Feb 7, 2019 at 23:17

2 Answers 2

1

I am also observing the same issues, I would suggest to please check the permissions to the mentioned path of my.cnf and the path mentioned by my.cnf.

1

Late answer, but: check /etc/init.d/mysql

Mariadb still (18.04) installs an init script with

MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"

and mysqladmin claims to read /etc/my.cnf, /etc/mysql/my.cnf, which is not true. Just remove the --defaults-file=/etc/mysql/debian.cnf part in /etc/init.d/mysql

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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