Forum Moderators: phranque

Message Too Old, No Replies

mysqltuner configuration and tuneup

Mysqltuner

         

anthonyinit 2017

12:20 pm on Mar 20, 2018 (gmt 0)

5+ Year Member Top Contributors Of The Month



Hello all,

I need help with mysqltuner script. i ran this script for 48hrs and not im getting

General recommendations:
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
root@server:~#


I have already added the line (query_cache_type = 1, query_cache_size = 256M) in /etc/mysql/mariadb.conf.d but why is that i'm still getting this recommendations?

My VPS - Debian 9 , 12GB RAM, 4Cores, Virtualmin

Thank you

TravisDGarrett

12:28 pm on Mar 20, 2018 (gmt 0)



You can run the SQL command : SHOW VARIABLES

And verify that your query_cache_xxx parameters are correctly taken in consideration . It's possible you didn't add them at the right place. MariaDB might read another conf file from elsewhere, or in a different order, things like that ...

anthonyinit 2017

2:11 pm on Mar 20, 2018 (gmt 0)

5+ Year Member Top Contributors Of The Month



Hello @TravisDGarrett

Thank you for your reply. i ran the command SHOW VARIABLES
Output :
query_alloc_block_size
16384
query_cache_limit
67108864
query_cache_min_res_unit
4096
query_cache_size
268435456
query_cache_strip_comments
OFF
query_cache_type
ON
query_cache_wlock_invalidate
OFF
query_prealloc_size
24576


Also i checked the /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/


According to my.cnf i checked /etc/mysql/conf.d/ directory and i can see 2 file (mysql.cnf and mysqldump.cnf) in "mysql.cnf" i only see
[mysql] 
nothing else

and in mysqldumo.cnf

[mysqldump]
quick
quote-names
max_allowed_packet= 16M


when i open the mariadb.conf.d directory 4 different files ( 50-client.cnf,50-mysql-clients.cnf,50-mysqld_safe.cnf,50-server.cnf )

all my config are in "50-server.cnf"


[server]


[mysqld]

user= mysql
pid-file= /var/run/mysqld/mysqld.pid
socket= /var/run/mysqld/mysqld.sock
port= 3306
basedir= /usr
datadir= /var/lib/mysql
tmpdir= /tmp
lc-messages-dir= /usr/share/mysql
skip-external-locking
skip-name-resolve = 1
performance_schema = ON


bind-address= 127.0.0.1


key_buffer_size= 16M
max_allowed_packet= 16M
thread_stack= 192K
thread_cache_size = 8


myisam_recover_options = BACKUP

query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 64M

log_error = /var/log/mysql/error.log


expire_logs_days= 10
max_binlog_size = 100M


character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
innodb_file_per_table = 1
innodb_buffer_pool_instances =1
innodb_log_file_size = 16M

[embedded]


[mariadb]


[mariadb-10.1]


I'm not sure which file is being used

anthonyinit 2017

10:29 am on Mar 21, 2018 (gmt 0)

5+ Year Member Top Contributors Of The Month



i adjust the variable to = 0 and now the recommendations are gone.