Topic: Mariadb105 change my.conf

[jameswilson]

Looking to modify some settings to (try lol) improve the performance of the mysql side. Its purly for a zencart ecomm site and that has to be the priority for all speed (will be looking at other things too)

server is on dedicated proxmox host with ssd's (2 x sme on the host only) in a data centre (not relevant i suppose)

Id like to edit some settings and test the result etc so id like to be able to just apply the changes too and not reconfig/reboot if possible

Many Thanks

James

[Jean-Philippe Pialasse]

for answer to those who will see this later :
we use a common /etc/my.cnf file for all server running. 
mariadb and mysql are able to parse the file depending on their version

[mysql] content is read by all

[mariadb10.5] only by mariadb 10.5.


so you can adjust either the  content of templates via variables or via new custom templates, or with corect placement only for one version.


this approach allow to have a more consistant setting accros the versions as long as they supports the same settings.

so you can start do the settings with db configuration setprop mariadb PROPERTY VALUE.
If not ok then use the key for maria105. 

if not available for  what you want then create custom template with correct placement in the order to target all or only the version you want.

[jameswilson]

Thanks
I thought i shouldnt edit files in the /etc folder

How do i create a template so i can put my changed settings into it?

Then can i just restart mariadb rather than doing a full reconfigure and reboot?

James

[Jean-Philippe Pialasse]

Thanks
I thought i shouldnt edit files in the /etc folder

How do i create a template so i can put my changed settings into it?

Then can i just restart mariadb rather than doing a full reconfigure and reboot?

James

indeed do not edit the file, use custom-templates as suggested in my answer.

how to use  templates-custom? ask the wiki.

https://wiki.koozali.org/Template_Tutorial

[jameswilson]

Ok Thanks used the wiki as noted above and did the following

Code: [Select]

mkdir -p /etc/e-smith/templates-custom/etc/my.cnf/

Code: [Select]

cp /etc/e-smith/templates/etc/my.cnf/005mysqld /etc/e-smith/templates-custom/etc/my.cnf/005mysqld
then edited
 /etc/e-smith/templates/etc/my.cnf/005mysqld /etc/e-smith/templates-custom/etc/my.cnf/005mysqld

added
[mariadb10.5]
to the file so not to mess with built in MariaDB

my.conf settings entered

expanded the template with

Code: [Select]

expand-template /etc/my.cnf

then restarted mariadb10.5 with

Code: [Select]

systemctl restart mariadb105-mariadb.service
used https://github.com/major/MySQLTuner-perl for inspiration

Thanks

[ReetP]

  

Well done!!

[jameswilson]

 

Well done!!

I did a thing lol (well with lots of help)

Just to conffirm the entry in the fragment is [mariadb10.5] and not mariadb105 (no .) like on the command line?

Thanks again

[Jean-Philippe Pialasse]

close but no cigar

I suggest you change a little the approach

Code: [Select]

# ll /etc/e-smith/templates/etc/my.cnf/
total 76
-rw-r--r-- 1 root root 1324 18 déc  2003 000readme
-rw-r--r-- 1 root root    9  5 jun  2002 005mysqld
-rw-r--r-- 1 root root   38 11 mar  2022 006pid-file
-rw-r--r-- 1 root root   13 18 déc  2003 007basedir
-rw-r--r-- 1 root root   23  5 jun  2002 008datadir
-rw-r--r-- 1 root root  492 11 mar  2022 009innodb
-rw-r--r-- 1 root root  178 11 mar  2022 009socket
-rw-r--r-- 1 root root   11  6 oct  2008 011user
-rw-r--r-- 1 root root  198 23 nov 23:38 012open_files_limit
-rw-r--r-- 1 root root 2914 11 mar  2022 014server_system_variables
-rw-r--r-- 1 root root   41 11 mar  2022 015logerror
-rw-r--r-- 1 root root  137 16 déc  2003 040mysqld_safe
-rw-r--r-- 1 root root   16 23 nov 23:38 105-05mariadb
-rw-r--r-- 1 root root   39 23 nov 23:38 105-07basedir
-rw-r--r-- 1 root root   44 23 nov 23:38 105-08datadir
-rw-r--r-- 1 root root  219 23 nov 23:38 105-09socket
-rw-r--r-- 1 root root  257 23 nov 23:38 105-10port
-rw-r--r-- 1 root root 3156 23 nov 23:38 105-14server_system_variables
-rw-r--r-- 1 root root   45 23 nov 23:38 105-15logerror
restart using 105- as a prefix
you just removed all the configuration for mariadb 55 and also add potential non compatible configuration for mariadb 10.5 by hidding the default [mysql] section

I also suggest you  not to hide an existing fragment but create a new one with your own settings you want.
so just do:

mv /etc/e-smith/templates-custom/etc/my.cnf/005mysqld /etc/e-smith/templates-custom/etc/my.cnf/105-99localtweaks

then edit and remove the [mariadb10.5] in it and whatr was part of the default content you kept.

[jameswilson]

close but no cigar

lol

thanks JP ive done

Code: [Select]

mv /etc/e-smith/templates-custom/etc/my.cnf/005mysqld /etc/e-smith/templates-custom/etc/my.cnf/105-99localtweaksand removed the [mariadb10.5]

but have

Code: [Select]

[mysqld]
query_cache_limit=10M
query_cache_size=0
query_cache_type=0
join_buffer_size=2M
tmp_table_size=40M
max_heap_table_size=40M
table_definition_cache=900
performance_schema=ON
key_buffer_size=4M
innodb_buffer_pool_size=1300M
innodb_log_file_size=352M
innodb_log_buffer_size=80M

[Jean-Philippe Pialasse]

this section [mysql] is for the base mariadb 5.5 that is in use for core services. some of them are not compatible with higher version of maria.
so again put your fragment a the right 105-99yourname position and do not use any section header,

you should see something like that in /etc/my.cnf after expanding

Code: [Select]


#------------------------------------------------------------
#              !!DO NOT MODIFY THIS FILE!!
#
# Manual changes will be lost when this file is regenerated.
#
# Please read the developer's guide, which is available
# at http://www.contribs.org/development/
#
# Copyright (C) 1999-2006 Mitel Networks Corporation
#------------------------------------------------------------
###########################################################
# Here is a cheat-sheet for this file as of MySQL 4.0.14
# 2003-12-18 - tony_clayton@mitel.com
#
# 1. The mysqld binary reads options from the [mysqld] section,
#    and then overrides those values with any passed-in
#    arguments.
#
# 2. The mysqld_safe script reads options from the following
#    sections in order, overriding any previous values with newer
#    ones:
#       [mysqld] [server] [mysqld_safe] [safe_mysqld]
#    It then overrides those values with any passed-in
#    arguments, and invokes the mysqld binary (see #1 above) with
#    the resulting argument list. 
#
#    * The server and safe_mysqld sections are deprecated in
#      favour of mysqld and mysqld_safe respectively.
#
# 3. The mysql or mysqld initscript reads options from the
#    following sections in order, overriding any previous values
#    with newer ones:
#       [mysqld] [mysql_server] [mysql.server]
#    It then overrides those values with any passed-in arguments,
#    and invokes the mysqld_safe script (see #2 above) with the
#    resulting argument list.
#
#    * The SMEServer doesn't have any need for initscript-only
#      options, so we don't use mysql_server and mysql.server
#      sections.
#
###########################################################

[mysqld]
pid-file=/var/run/mariadb/mariadb.pid
basedir=/usr
datadir=/var/lib/mysql
# innodb custom template

socket=/var/lib/mysql/mysql.sock
# networking is enabled
user=mysql
open_files_limit=32186

query_cache_size=1M
query_cache_type=1
thread_cache_size=256
join_buffer_size=262144
max_connect_errors=100
max_allowed_packet=16M



log_error=/var/log/mariadb/mariadb.log


[mysqld_safe]

[mariadb-10.5]
basedir=/opt/rh/rh-mariadb105/root/usr
datadir=/var/opt/rh/rh-mariadb105/lib/mysql
socket=/var/lib/mysql/mariadb105.sock
skip-networking
#no port as skip-networking

query_cache_size=1M
query_cache_type=1
thread_cache_size=256
join_buffer_size=262144
max_connect_errors=100
max_allowed_packet=16M



log_error=/var//log/mariadb105/mariadb.log

### your templates goes there




[jameswilson]

Yes i sort of have that but should i not be using the mariadb-10.5

Code: [Select]

#    * The SMEServer doesn't have any need for initscript-only
#      options, so we don't use mysql_server and mysql.server
#      sections.
#
###########################################################

[mysqld]
pid-file=/var/run/mariadb/mariadb.pid
basedir=/usr
datadir=/var/lib/mysql

#innodb
innodb_large_prefix=1
innodb_file_format=barracuda
innodb_file_per_table=1

socket=/var/lib/mysql/mysql.sock
skip-networking
user=mysql
open_files_limit=32186

query_cache_size=1M
query_cache_type=1
thread_cache_size=256
join_buffer_size=262144
max_connect_errors=100
max_allowed_packet=16M



log_error=/var/log/mariadb/mariadb.log


[mysqld_safe]

[mariadb-10.5]
basedir=/opt/rh/rh-mariadb105/root/usr
datadir=/var/opt/rh/rh-mariadb105/lib/mysql
socket=/var/lib/mysql/mariadb105.sock
skip-networking=0
# networking is enabled
port=3308

query_cache_size=1M
query_cache_type=1
thread_cache_size=256
join_buffer_size=262144
max_connect_errors=100
max_allowed_packet=16M



log_error=/var//log/mariadb105/mariadb.log

[mysqld]
query_cache_limit = 2M
query_cache_size = 0
query_cache_type = 0
join_buffer_size = 3096M
table_definition_cache = 840
tmp_table_size = 48M
max_heap_table_size = 48M
performance_schema = ON
key_buffer_size = 28M
innodb_buffer_pool_size = 3600M
innodb_log_file_size = 1024M
innodb_log_buffer_size = 7492M


[jameswilson]

also i rebooted this sme10 box and it wanted name etc setting on the reboot as if it was a new install.
Also had to force lets encrpyt and re-enable port for mariadb.
phpmyadmin is broken too but im going to leave it alone tonight as it seems the web server is running with mariadb etc.

Im lost as to why a reboot reconfigure broke so much, never seen that before.

[Jean-Philippe Pialasse]

Yes i sort of have that but should i not be using the mariadb-10.5

as i pointed, edit your template, and :
do not use any section header

in other words DO NOT ADD [mysqld] in your fragment

if you place it at the right place (ie 105-99yourname) it will use the previous section header. 

[jameswilson]

as i pointed, edit your template, and :
do not use any section header

in other words DO NOT ADD [mysqld] in your fragment

if you place it at the right place (ie 105-99yourname) it will use the previous section header.

Got it

Thanks very much JP