Koozali.org: home of the SME Server
Contribs.org Forums => Koozali SME Server 10.x Contribs => Topic started by: jameswilson on May 10, 2024, 12:54:16 AM
-
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
-
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.
-
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
-
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
-
Ok Thanks used the wiki as noted above and did the following
mkdir -p /etc/e-smith/templates-custom/etc/my.cnf/
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
expand-template /etc/my.cnf
then restarted mariadb10.5 with
systemctl restart mariadb105-mariadb.service
used https://github.com/major/MySQLTuner-perl for inspiration
Thanks
-
:hammer: :pint:
Well done!!
:-)
-
:hammer: :pint:
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
-
close but no cigar
I suggest you change a little the approach
# 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.
-
close but no cigar
lol
thanks JP ive done
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
[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
-
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
#------------------------------------------------------------
# !!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
-
Yes i sort of have that but should i not be using the mariadb-10.5
# * 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
-
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.
-
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.
-
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