Help with optimization of my.cnf based on mysqltuner

Operating System & Version
108.0.13
cPanel & WHM Version
CentOS v7.9.2009 STANDARD standard

Nermin

Active Member
Mar 7, 2017
25
5
53
Tuzla
cPanel Access Level
Root Administrator
Hello I am currently running this server:
Intel(R) Xeon(R) CPU E5-2630 v4 @ 2.20GHz
256GB RAM
2x250GB SSD RAID 1 (only for operating system)
2x2TB SSD in RAID 1 (only for accounts) (/home4)
2TB HDD for 10 accounts with a lot of emails (/home5)
6TB HDD for incremental backups and compressed backups (compressed ones are made and downloaded once a week to my PC) (/home3)
MariaDB 10.6.12-MariaDB
mod_mpm_prefork Installed 2.4.55-2.2.3.cpanel
PHP Handler is mod_lsapi and I am using Wordpress W3 Total Cache on resource demanding websites (~15 of them) so it can use memcached and APCu. I still haven't got time to install it on all Wordpress websites.
I am also having all ConfigServer products installed and working just fine.

I have 150 websites, 99% of them are Wordpress, and almost all of them are on Cloudflare.
Server load is around 2-5. Sometimes it goes to 10-15 for couple of minutes, but mostly 2-5.
RAM usage is around 60-90GB.

I would like to state that server runs just fine, but it bugs me for months now it this the correct setup, or is there anything that I could improve, since this server has great hardware. Do I have some bottlenecks somewhere in MySQL.
I've tried to increase table_open_cache = 524123 and open_files_limit = 1048576 since phpMyAdmin Advisor says I should increase these, but for some reason it will not allow me to increase more than values I wrote.

Could someone suggest what could be improved, and what did I wrong?

Thank you!

Nermin

Stats are bellow.

This is my disk usage:

/dev/sda1 5.5T 4.0T 1.2T 78% /home3
/dev/sdb2 2.0G 323M 1.6G 18% /boot
/dev/sdb4 219G 143G 65G 69% /
/dev/sdc1 1.8T 1.4T 406G 77% /home4
/dev/sdd1 1.8T 1.6T 162G 91% /home5

When I open phpMyAdmin in WHM and go see "Query statistics" it says
Questions since startup: 270,074,994
ø per hour: 1,136,867
ø per minute: 18,948
ø per second: 316


In phpMyAdmin "All status variables" (ones in alert values) are:
Aborted clients56The number of connections that were aborted because the client died without closing the connection properly.
Aborted connects254The number of failed attempts to connect to the MySQL server.
Created tmp disk tables13.1 MThe number of temporary tables on disk created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based.
Handler read rnd16.1 GThe number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.
Handler read rnd next46.9 GThe number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
Innodb buffer pool pages dirty192.2 kThe number of pages currently dirty.
Innodb buffer pool reads581 kThe number of logical reads that InnoDB could not satisfy from buffer pool and had to do a single-page read.
Innodb row lock waits20.2 kThe number of times a row lock had to be waited for.
Key reads345 kThe number of physical reads of a key block from disk. If Key_reads is big, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.
Opened tables347.2 kThe number of tables that have been opened. If opened tables is big, your table cache value is probably too small.
Qcache free blocks1The number of free memory blocks in query cache. High numbers can indicate fragmentation issues, which may be solved by issuing a FLUSH QUERY CACHE statement.
Select full join644.1 kThe number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.
Select range check1.8 kThe number of joins without keys that check for key usage after each row. (If this is not 0, you should carefully check the indexes of your tables.)
Slow queries61The number of queries that have taken more than long_query_time seconds.
Sort merge passes68The number of merge passes the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.
Table locks waited156The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.


Now in phpMyAdmin Advisor section says these:
Possible performance issues
IssueRecommendation
Suboptimal caching method.You are using the MySQL Query cache with a fairly high traffic database. It might be worth considering to use memcached instead of the MySQL Query cache, especially if you have multiple slaves.
There are lots of rows being sorted.While there is nothing wrong with a high amount of row sorting, you might want to make sure that the queries which require a lot of sorting use indexed columns in the ORDER BY clause, as this will result in much faster sorting.
There are too many joins without indexes.This means that joins are doing full table scans. Adding indexes for the columns being used in the join conditions will greatly speed up table joins.
The rate of reading the first index entry is high.This usually indicates frequent full index scans. Full index scans are faster than table scans but require lots of CPU cycles in big tables, if those tables that have or had high volumes of UPDATEs and DELETEs, running 'OPTIMIZE TABLE' might reduce the amount of and/or speed up full index scans. Other than that full index scans can only be reduced by rewriting queries.
The rate of reading data from a fixed position is high.This indicates that many queries need to sort results and/or do a full table scan, including join queries that do not use indexes. Add indexes where applicable.
The rate of reading the next table row is high.This indicates that many queries are doing full table scans. Add indexes where applicable.
Many temporary tables are being written to disk instead of being kept in memory.Increasing max_heap_table_size and tmp_table_size might help. However some temporary tables are always being written to disk, independent of the value of these variables. To eliminate these you will have to rewrite your queries to avoid those conditions (Within a temporary table: Presence of a BLOB or TEXT column or presence of a column bigger than 512 bytes) as mentioned in the beginning of an Article by the Pythian Group
MyISAM key buffer (index cache) % used is low.You may need to decrease the size of key_buffer_size, re-examine your tables to see if indexes have been removed, or examine queries and expectations about what indexes are being used.
The % of indexes that use the MyISAM key buffer is low.You may need to increase key_buffer_size.
The rate of opening tables is high.Opening tables requires disk I/O which is costly. Increasing table_open_cache might avoid this.
Too many connections are aborted.Connections are usually aborted when they cannot be authorized. This article might help you track down the source.
The InnoDB log file size is inadequately large.It is usually sufficient to set innodb_log_file_size to 25% of the size of innodb_buffer_pool_size. A very big innodb_log_file_size slows down the recovery time after a database crash considerably. See also this blog entry
The query cache is not enabled.The query cache is known to greatly improve performance if configured correctly. Enable it by setting query_cache_size to a 2 digit MiB value and setting query_cache_type to 'ON'. Note: If you are using memcached, ignore this recommendation.

This is my.cnf setup that I have found on Github and modified it for several months now:
[mysqld]
performance-schema = 1

basedir = /usr
bind_address = 127.0.0.1 # Change to 0.0.0.0 to allow remote connections
datadir = /var/lib/mysql

max_allowed_packet = 512M
max_connect_errors = 1000000
pid_file = /var/lib/mysql/mysql.pid
port = 3306
skip_external_locking
socket = /var/lib/mysql/mysql.sock
tmpdir = /tmp
user = mysql

default_storage_engine = InnoDB
innodb_buffer_pool_instances = 64 # Use 1 instance per 1GB of InnoDB pool size - max is 64
innodb_buffer_pool_size = 175G # Use up to 70-80% of RAM
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M
innodb_log_file_size = 44800M
innodb_sort_buffer_size = 8M # UPD - Defines how much data is read into memory for sorting operations before writing to disk (default is 1M / max is 64M)
innodb_stats_on_metadata = 0

innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 2000 # Depends on the storage tech - use 2000 for SSD, more for NVMe

query_cache_type = 0 # Enabled by default

key_buffer_size = 15M

low_priority_updates = 1
concurrent_insert = 2

max_connections = 300

back_log = 512
thread_cache_size = 256
thread_stack = 192K

interactive_timeout = 180
wait_timeout = 180

join_buffer_size = 1M
read_buffer_size = 2M
read_rnd_buffer_size = 4M # UPD
sort_buffer_size = 4M # UPD

table_open_cache_instances = 2
table_definition_cache = 40000 # UPD
table_open_cache = 524123 # UPD
open_files_limit = 1048576 # UPD

max_heap_table_size = 4095M
tmp_table_size = 4095M

ft_min_word_len = 3 # Minimum length of words to be indexed for search results

disable_log_bin = 1 # Binary logging disabled by default

log_error = /var/lib/mysql/mysql_error.log
log_queries_not_using_indexes = 0 # Disabled on production
long_query_time = 5
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql_slow.log

unix_socket = OFF
[mysqldump]

quick
quote_names
max_allowed_packet = 1024M


This is mysqltuner log:

>> MySQLTuner 2.0.9
* Jean-Marie Renouard <[email protected]>
* Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.pl/
>> Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.6.12-MariaDB-log
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/lib/mysql/mysql_error.log exists
[--] Log file: /var/lib/mysql/mysql_error.log (23M)
[OK] Log file /var/lib/mysql/mysql_error.log is not empty
[OK] Log file /var/lib/mysql/mysql_error.log is smaller than 32 Mb
[OK] Log file /var/lib/mysql/mysql_error.log is readable.
[!!] /var/lib/mysql/mysql_error.log contains 3145 warning(s).
[!!] /var/lib/mysql/mysql_error.log contains 9861 error(s).
[--] 81 start(s) detected in /var/lib/mysql/mysql_error.log
[--] 1) 2023-02-22 22:55:38 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 2) 2023-02-20 20:48:05 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 3) 2023-02-20 20:44:56 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 4) 2023-02-17 21:41:25 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 5) 2023-02-17 21:40:21 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 6) 2023-02-17 21:39:22 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 7) 2023-02-17 21:38:36 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 8) 2023-02-17 21:36:18 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 9) 2023-02-17 21:33:19 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 10) 2023-02-17 21:31:40 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 80 shutdown(s) detected in /var/lib/mysql/mysql_error.log
[--] 1) 2023-02-20 20:48:03 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 2) 2023-02-20 20:44:52 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 3) 2023-02-17 21:41:23 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 4) 2023-02-17 21:40:18 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 5) 2023-02-17 21:39:19 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 6) 2023-02-17 21:38:34 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 7) 2023-02-17 21:36:15 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 8) 2023-02-17 21:33:16 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 9) 2023-02-17 21:31:37 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 10) 2023-02-17 21:30:58 0 [Note] /usr/sbin/mariadbd: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in Aria tables: 32.0K (Tables: 1)
[--] Data in MyISAM tables: 510.2M (Tables: 297)
[--] Data in InnoDB tables: 12.8G (Tables: 16332)
[--] Data in CSV tables: 0B (Tables: 2)
[--] Data in MEMORY tables: 0B (Tables: 86)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Views Metrics -----------------------------------------------------------------------------

-------- Triggers Metrics --------------------------------------------------------------------------

-------- Routines Metrics --------------------------------------------------------------------------

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 620 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 9d 21h 41m 47s (274M q [320.919 qps], 4M conn, TX: 1647G, RX: 54G)
[--] Reads / Writes: 92% / 8%
[--] Binary logging is disabled
[--] Physical Memory : 251.6G
[--] Max MySQL memory : 332.4G
[--] Other process memory: 0B
[--] Total buffers: 179.2G global + 523.2M per thread (300 max threads)
[--] Performance_schema Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 258.3G (102.67% of installed RAM)
[!!] Maximum possible memory usage: 332.4G (132.11% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (60/274M)
[OK] Highest usage of available connections: 51% (155/300)
[OK] Aborted connections: 0.01% (252/4279274)
[!!] CPanel and Flex system skip-name-resolve should be on
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (67 temp sorts / 37M sorts)
[!!] Joins performed without indexes: 644370
[!!] Temporary tables created on disk: 40% (13M on disk / 32M total)
[OK] Thread cache hit rate: 99% (155 created / 4M connections)
[OK] Table cache hit rate: 99% (286M hits / 287M requests)
[OK] table_definition_cache (40000) is greater than number of tables (17014)
[OK] Open file limit used: 0% (708/1M)
[OK] Table locks acquired immediately: 99% (388K immediate / 388K locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by Performance_schema: 72B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 36.1% (5.4M used / 15.0M cache)
[!!] Key buffer size / total MyISAM indexes: 15.0M/23.0M
[!!] Read Key buffer hit rate: 90.7% (3M cached / 345K reads)
[!!] Write Key buffer hit rate: 76.7% (475K cached / 364K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 175.0G / 12.8G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 43.8G * 1/175.0G should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk: 1400 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (257374941102 hits / 257375522076 total)
[!!] InnoDB Write Log efficiency: 78.55% (372858 hits / 474658 total)
[OK] InnoDB log waits: 0.00% (0 waits / 847516 writes)

-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/520.0K
[OK] Aria pagecache hit rate: 97.1% (13B cached / 396M reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Check warning line(s) in /var/lib/mysql/mysql_error.log file
Check error line(s) in /var/lib/mysql/mysql_error.log file
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
name resolution is enabled due to cPanel doesn't support this disabled.
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
(specially the conclusions at the bottom of the page).
Temporary table size is already large: reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
skip-name-resolve=0
join_buffer_size (> 1.0M, or always use indexes with JOINs)
key_buffer_size (~ 5M)
key_buffer_size (> 23.0M)
 
Last edited by a moderator:

Nermin

Active Member
Mar 7, 2017
25
5
53
Tuzla
cPanel Access Level
Root Administrator
Can somebody help me try to solve this issue in phpMyAdmin with table open cache and open files limit.

Issue:
The rate of opening tables is high.
Recommendation:
Opening tables requires disk I/O which is costly. Increasing table_open_cache might avoid this.
Justification:
Opened table rate: 15.18 per minute, this value should be less than 10 per hour
Used variable / formula:
Opened_tables / Uptime
Test:
value*60*60 > 10

Also this:

Issue:
The rate of opening files is high.
Recommendation:
Consider increasing open_files_limit, and check the error log when restarting after changing open_files_limit.
Justification:
Opened files rate: 6.58 per hour, this value should be less than 5 per hour
Used variable / formula:
Open_files / Uptime
Test:
value * 60 * 60 > 5

1678711705031.png
How can I increase table_open_cache more than value 524123 and open_files_limit more than value: 1048576. I cant go any higher for some reason.
I've searched forums but for some reason it will not allow me higher values than these.

Thank you!

Nermin
 

Nermin

Active Member
Mar 7, 2017
25
5
53
Tuzla
cPanel Access Level
Root Administrator
Hello,
I have entered these values in my.cnf
table_definition_cache = 40000 # UPD
table_open_cache = 1024123 # UPD
open_files_limit = 3048576 # UPD

Old values were:
table_definition_cache = 40000 # UPD
table_open_cache = 524123 # UPD
open_files_limit = 1048576 # UPD

and restarted mysql.

This is the output:
[[email protected] ~]# /scripts/restartsrv_mysql
Waiting for “mysql” to restart ……waiting for “mysql” to initialize ………finished.

Service Status
mariadb (/usr/sbin/mariadbd) is running as mysql with PID 6644 (systemd+/proc check method).

Startup Log
Mar 15 17:05:15 server1.xxx.xx systemd[1]: Starting MariaDB 10.6.12 database server...
Mar 15 17:05:15 server1.xxx.xx mariadbd[6644]: 2023-03-15 17:05:15 0 [Warning] Could not increase number of max_open_files to more than 1048576 (request: 4096843)
Mar 15 17:05:15 server1.xxx.xx mariadbd[6644]: 2023-03-15 17:05:15 0 [Warning] Changed limits: max_open_files: 1048576 max_connections: 300 (was 300) table_cache: 524123 (was 1024123)

Is there some hard limit in CentOS7 that I am unaware of?
 

cPRex

Jurassic Moderator
Staff member
Oct 19, 2014
15,307
2,440
363
cPanel Access Level
Root Administrator
It's possible there are other MySQL configuration files on the system, such as in /root/my.cnf, or elsewhere on the system. Running this command on the server will show you what configuration files are currently loaded:

/usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"
 

Nermin

Active Member
Mar 7, 2017
25
5
53
Tuzla
cPanel Access Level
Root Administrator
[[email protected] ~]# /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"
2023-03-15 17:43:56 0 [Warning] Could not increase number of max_open_files to more than 4096 (request: 4096843)
2023-03-15 17:43:56 0 [Warning] Changed limits: max_open_files: 4096 max_connections: 300 (was 300) table_cache: 1883 (was 1024123)
2023-03-15 17:43:56 0 [Warning] 'innodb-buffer-pool-instances' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
Default options are read from the following files in the given order:
/etc/my.cnf ~/.my.cnf
[[email protected] ~]#
 

cPRex

Jurassic Moderator
Staff member
Oct 19, 2014
15,307
2,440
363
cPanel Access Level
Root Administrator
Thanks for that. The limit is likely controlled by the service file for MySQL, which would be located at this file:

/usr/lib/systemd/system/mariadb.service

or the mysqld.service file in the same directory if you are using a MySQL version.

Edit that file, restart the service, and I would expect things to work well.
 

Nermin

Active Member
Mar 7, 2017
25
5
53
Tuzla
cPanel Access Level
Root Administrator
I've edited that file.
No mather what I put in:
# Number of files limit. previously [mysqld_safe] open-files-limit
LimitNOFILE=infinity
LimitMEMLOCK=infinity
it will not change the value more than table open cache: 524,123 or open files limit: 1,048,576

I've also made "/etc/systemd/system/mariadb.service.d/override.conf" file and put inside
LimitNOFILE=infinity
LimitMEMLOCK=infinity
LimitNPROC=infinity
but nothing happens.

Here is mariadb.service:
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades. If you want to customize, the
# best way is to create a file "/etc/systemd/system/mariadb.service",
# containing
# .include /usr/lib/systemd/system/mariadb.service
# ...make your changes here...
# or create a file "/etc/systemd/system/mariadb.service.d/foo.conf",
# which doesn't need to include ".include" call and which will be parsed
# after the file mariadb.service itself is parsed.
#
# For more info about custom unit files, see systemd.unit(5) or
# systemd
#
# Copyright notice:
#
# This file is free software; you can redistribute it and/or modify it
# under the terms of the GNU Lesser General Public License as published by
# the Free Software Foundation; either version 2.1 of the License, or
# (at your option) any later version.

[Unit]
Description=MariaDB 10.6.12 database server
Documentation=man:mariadbd(8)
Documentation=systemd
After=network.target

[Install]
WantedBy=multi-user.target


[Service]

##############################################################################
## Core requirements
##

Type=notify

# Setting this to true can break replication and the Type=notify settings
# See also bind-address mariadbd option.
PrivateNetwork=false

##############################################################################
## Package maintainers
##

User=mysql
Group=mysql

# CAP_IPC_LOCK To allow memlock to be used as non-root user
# CAP_DAC_OVERRIDE To allow auth_pam_tool (which is SUID root) to read /etc/shadow when it's chmod 0
# does nothing for non-root, not needed if /etc/shadow is u+r
# CAP_AUDIT_WRITE auth_pam_tool needs it on Debian for whatever reason
CapabilityBoundingSet=CAP_IPC_LOCK CAP_DAC_OVERRIDE CAP_AUDIT_WRITE

# PrivateDevices=true implies NoNewPrivileges=true and
# SUID auth_pam_tool suddenly doesn't do setuid anymore
PrivateDevices=false

# Prevent writes to /usr, /boot, and /etc
ProtectSystem=full



# Doesn't yet work properly with SELinux enabled
# NoNewPrivileges=true

# Prevent accessing /home, /root and /run/user
ProtectHome=true

# Execute pre and post scripts as root, otherwise it does it as User=
PermissionsStartOnly=true



# Perform automatic wsrep recovery. When server is started without wsrep,
# galera_recovery simply returns an empty string. In any case, however,
# the script is not expected to return with a non-zero status.
# It is always safe to unset _WSREP_START_POSITION environment variable.
# Do not panic if galera_recovery script is not available. (MDEV-10538)
ExecStartPre=/bin/sh -c "systemctl unset-environment _WSREP_START_POSITION"
ExecStartPre=/bin/sh -c "[ ! -e /usr/bin/galera_recovery ] && VAR= || \
VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] \
&& systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1"

# Needed to create system tables etc.
# ExecStartPre=/usr/bin/mysql_install_db -u mysql

# Start main service
# MYSQLD_OPTS here is for users to set in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf
# Use the [Service] section and Environment="MYSQLD_OPTS=...".
# This isn't a replacement for my.cnf.
# _WSREP_NEW_CLUSTER is for the exclusive use of the script galera_new_cluster
ExecStart=/usr/sbin/mariadbd $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION

# Unset _WSREP_START_POSITION environment variable.
ExecStartPost=/bin/sh -c "systemctl unset-environment _WSREP_START_POSITION"



KillSignal=SIGTERM

# Don't want to see an automated SIGKILL ever
SendSIGKILL=no

# Restart crashed server only, on-failure would also restart, for example, when
# my.cnf contains unknown option
Restart=on-abort
RestartSec=5s

UMask=007

##############################################################################
## USERs can override
##
##
## by creating a file in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf
## and adding/setting the following under [Service] will override this file's
## settings.

# Useful options not previously available in [mysqld_safe]

# Kernels like killing mariadbd when out of memory because its big.
# Lets temper that preference a little.
# OOMScoreAdjust=-600

# Explicitly start with high IO priority
# BlockIOWeight=1000

# If you don't use the /tmp directory for SELECT ... OUTFILE and
# LOAD DATA INFILE you can enable PrivateTmp=true for a little more security.
PrivateTmp=false

# Set an explicit Start and Stop timeout of 900 seconds (15 minutes!)
# this is the same value as used in SysV init scripts in the past
# Galera might need a longer timeout, check the KB if you want to change this:
# systemd
TimeoutStartSec=900
TimeoutStopSec=900

##
## Options previously available to be set via [mysqld_safe]
## that now needs to be set by systemd config files as mysqld_safe
## isn't executed.
##

# Number of files limit. previously [mysqld_safe] open-files-limit
#LimitNOFILE=infinity
#LimitMEMLOCK=infinity
#LimitNPROC=infinity

# Maximium core size. previously [mysqld_safe] core-file-size
# LimitCore=

# Nice priority. previously [mysqld_safe] nice
# Nice=-5

# Timezone. previously [mysqld_safe] timezone
# Environment="TZ=UTC"

# Library substitutions. previously [mysqld_safe] malloc-lib with explicit paths
# (in LD_LIBRARY_PATH) and library name (in LD_PRELOAD).
# Environment="LD_LIBRARY_PATH=/path1 /path2" "LD_PRELOAD=

# Flush caches. previously [mysqld_safe] flush-caches=1
# ExecStartPre=sync
# ExecStartPre=sysctl -q -w vm.drop_caches=3

# numa-interleave=1 equalivant
# Change ExecStart=numactl --interleave=all /usr/sbin/mariadbd......

# crash-script equalivent
# FailureAction=