my.cnf configuration because of high cpu usage from mysql
Hi all, i am facing a problem with high cpu usage from mysql (over 130%).
This is my my.cnf file:
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld]
#user = root
#default-authentication-plugin=mysql_native_password
# Hector
sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
disable-log-bin=1
#default-authentication-plugin=mysql_native_password
performance-schema=0
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
log-error-verbosity=1
pid-file=/var/run/mysqld/mysqld.pid
innodb_buffer_pool_size=134217728
max_allowed_packet=268435456
open_files_limit=40000
innodb_file_per_table=1
mysqlx=0
event_scheduler=off
# query_cache_size=100M
#Καταγράφει τα αργά ερωτήματα στο παρακάτω αρχειο
slow_query_log = 1
slow_query_log_file="/var/log/mysql-slow.log"
long_query_time=10
Is there something that can be improved?
For example: the query_cache_size=100M it's in comments, slow_query_log = 1 Increases cpu usage?
Is there a guide for best configuration, because some links I found on this forum no longer work.
server is an AMD Ryzen™ 7 7700 processor with 64GB DDR5 ram with 88 accounts
-
on /var/log/mysql-slow.log, i found this query running again and again:
# Time: 2024-07-29T05:32:55.323554Z
# User@Host: root[root] @ localhost [] Id: 652936
# Query_time: 172.102876 Lock_time: 0.074250 Rows_sent: 121 Rows_examined: 74186
SET timestamp=1722231003;
SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables WHERE TABLE_TYPE NOT LIKE '%view%' GROUP BY TABLE_SCHEMA;in tweak setting i disabled "Use INFORMATION_SCHEMA to acquire database disk usage" and waiting for any change in server load and cpu use
0 -
From WHM > Process Manager after i "killed" mysql process cpu was around 28% and server load about 1.14
And an hour later, the cpu usage is up to 105% (server load 5.82) and still going up.
until next kill process.0 -
Maybe those sites using up the CPU (see the php-fpm entries) are what is taxing MySQL.
mysqladmin processlist # run this when you are seeing the high load -- to see what is hitting MySQL
0 -
Your configuration looks solid, but a few improvements could optimize performance:
-
Query Cache: The
query_cache_size=100Mis commented out. While query caching can improve read-heavy workloads, it can increase CPU usage for write-heavy workloads, so enable it if your workload suits it. -
Slow Query Log: Enabling
slow_query_log = 1helps identify problematic queries but may increase CPU usage if a large number of queries exceed thelong_query_time. You can adjust this setting based on the workload. -
Buffer Pool: Increase the
innodb_buffer_pool_sizeto take advantage of your 64GB RAM. A size around 50-70% of RAM is recommended for better performance.
0 -
Please sign in to leave a comment.
Comments
4 comments