Skip to main content

Help with optimization of my.cnf based on mysqltuner

Nermin
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 clients 56 The number of connections that were aborted because the client died without closing the connection properly.
Aborted connects 254 The number of failed attempts to connect to the MySQL server.
Created tmp disk tables 13.1 M The 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 rnd 16.1 G The 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 next 46.9 G The 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 dirty 192.2 k The number of pages currently dirty.
Innodb buffer pool reads 581 k The number of logical reads that InnoDB could not satisfy from buffer pool and had to do a single-page read.
Innodb row lock waits 20.2 k The number of times a row lock had to be waited for.
Key reads 345 k The 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 tables 347.2 k The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.
Qcache free blocks 1 The 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 join 644.1 k The 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 check 1.8 k The 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 queries 61 The number of queries that have taken more than long_query_time seconds.
Sort merge passes 68 The 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 waited 156 The 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
Issue Recommendation
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 * Major Hayden >> 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)

Comments

13 comments

Date Votes
  • cPRex Jurassic Moderator
    Hey there! Honestly, that all looks good to me. For a 10-core CPU those numbers seem normal and realistic.
    0
  • Nermin
    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 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
    0
  • cPRex Jurassic Moderator
    Where are you trying to edit those values? If you adjust that in the /etc/my.cnf file, there is no reason you wouldn't be able to enter higher values than those.
    0
  • Nermin
    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: [root@server1 ~]# /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?
    0
  • cPRex Jurassic Moderator
    Can you run this command on the server and see if that is where the limit is being set? grep -i LimitNOFILE /etc/systemd/system/mysqld.service
    0
  • Nermin
    Here it is: [root@server1 ~]# grep -i LimitNOFILE /etc/systemd/system/mysqld.service grep: /etc/systemd/system/mysqld.service: No such file or directory [root@server1 ~]#
    0
  • cPRex Jurassic Moderator
    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"
    0
  • Nermin
    [root@server1 ~]# /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 [root@server1 ~]#
    0
  • cPRex Jurassic Moderator
    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.
    0
  • Nermin
    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=
    0
  • cPRex Jurassic Moderator
    Can you open a ticket with our team so we can take a look?
    0
  • Nermin
    I've opened a ticket. Id #94767413 Thank you!
    0
  • cPRex Jurassic Moderator
    Thanks for that - I'm following along with that on my end now.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post