MySQL - my.cnf config on Debian 9

adik2

New member
Joined
Mar 26, 2020
Messages
1
My Linux is Debian 9, memory: 8GB. The system load average is 26.06. Plz help me for fix error.

my.cnf file is:


Code:
[mysqld]
local-infile=0
innodb_file_per_table
wait_timeout=60
interactive_timeout=60
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

max_allowed_packet=256M
# MyISAM #
key-buffer-size                = 8M
expire_logs_days=3
# SAFETY #
#max-allowed-packet             = 64M
#max-connect-errors             = 1000000

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 64M
query_cache_type               = 1
query_cache_size               = 64M
#max-connections                = 500
thread-cache-size              = 2
open-files-limit               = 65535
table-definition-cache         = 2048
table-open-cache               = 2048
join_buffer_size               = 1M
innodb_buffer_pool_instances   = 1

# INNODB #
innodb-log-files-in-group      = 2
innodb-log-file-size           = 64M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 256M

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log


innodb_buffer_pool_instances = 1
innodb_buffer_pool_size=1024M
innodb_log_file_size = 160M
innodb_log_buffer_size = 32M


[client]
socket=/usr/local/mysql/data/mysql.sock

The system load average is 26.06

Code:
This is an automated message notifying you that the 5 minute load average on your system is 26.06.
This has exceeded the 10 threshold.

One Minute      - 73.83
Five Minutes    - 26.06
Fifteen Minutes - 11.39

top - 19:20:02 up 14 days, 22:20,  0 users,  load average: 70.56, 26.17, 11.51
Tasks: 424 total,  78 running, 257 sleeping,   0 stopped,   0 zombie
%Cpu(s): 15.8 us, 11.3 sy,  0.1 ni, 68.5 id,  2.9 wa,  0.0 hi,  1.4 si,  0.0 st
KiB Mem : 32884628 total,   573632 free,  7424272 used, 24886724 buff/cache
KiB Swap:  2093040 total,  1620248 free,   472792 used. 24458348 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
17016 mysql     20   0 5867564 3.131g  20048 S 119.5 10.0   4195:19 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/var/lib/mysql/mysql-error.log --open-files-limit=65535 --pid-file=/usr/local/mysql/data/mysqld.pid --socket=/usr/local/mysql/data/mysql.sock
17591 apache    20   0 5665016 216116  10416 S  78.0  0.7   0:21.95 /usr/sbin/httpd -DFOREGROUND
 8474 imperium  20   0  322980  55412  42792 R  14.6  0.2   0:38.40 php-fpm: pool imperium
 8566 imperium  20   0  322932  54972  42856 R  14.6  0.2   0:40.41 php-fpm: pool imperium
 8701 imperium  20   0  322552  55012  42728 R  14.6  0.2   0:37.88 php-fpm: pool imperium
 8344 imperium  20   0  323500  56124  42856 R  12.2  0.2   0:40.93 php-fpm: pool imperium
 8713 imperium  20   0  322460  54520  42792 R  12.2  0.2   0:33.85 php-fpm: pool imperium
 8730 imperium  20   0  327068  59532  42924 R  12.2  0.2   0:40.95 php-fpm: pool imperium
 8761 imperium  20   0  323384  55756  43272 R  12.2  0.2   0:39.78 php-fpm: pool imperium
 8911 imperium  20   0  322560  55372  43016 R  12.2  0.2   0:42.64 php-fpm: pool imperium
 8951 imperium  20   0  323324  55984  43076 R  12.2  0.2   0:41.85 php-fpm: pool imperium
12114 tapety    20   0  437916  99248  37864 R  12.2  0.3   1:03.53 php-fpm: pool tapety
15155 imperium  20   0  322460  54132  42408 S  12.2  0.2   0:14.98 php-fpm: pool imperium
15522 imperium  20   0  322336  54688  42728 S  12.2  0.2   0:17.27 php-fpm: pool imperium
17301 tapety    20   0  330032  39148  10388 R  12.2  0.1   0:12.52 php-fpm: pool tapety
17586 imperium  20   0  322172  54140  42288 R  12.2  0.2   0:02.72 php-fpm: pool imperium
17587 imperium  20   0  319860  48756  37676 R  12.2  0.1   0:03.24 php-fpm: pool imperium
 7191 imperium  20   0  323384  56164  43076 R   9.8  0.2   0:42.80 php-fpm: pool imperium
 8453 imperium  20   0  323560  56016  42728 R   9.8  0.2   0:47.41 php-fpm: pool imperium
 8457 imperium  20   0  322336  54176  42664 R   9.8  0.2   0:40.85 php-fpm: pool imperium
 8473 imperium  20   0  323556  55360  42792 R   9.8  0.2   0:41.40 php-fpm: pool imperium
 8582 imperium  20   0  322464  54524  42796 R   9.8  0.2   0:42.19 php-fpm: pool imperium
 8621 imperium  20   0  401168  59824  43276 R   9.8  0.2   0:41.31 php-fpm: pool imperium
 
 Top Memory Usage:
mysql    17016 28.4  9.9 5867564 3283340 ?     Sl   Mar10 4195:21 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/var/lib/mysql/mysql-error.log --open-files-limit=65535 --pid-file=/usr/local/mysql/data/mysqld.pid --socket=/usr/local/mysql/data/mysql.sock
root       611  0.0  2.7 1161228 907088 ?      Ssl  Mar05   4:14 /usr/local/sbin/clamd --foreground=yes
apache   17591 29.7  0.6 5666636 221632 ?      Sl   19:18   0:22 /usr/sbin/httpd -DFOREGROUND
apache   12983 20.3  0.6 5795572 223912 ?      Sl   18:59   4:15 /usr/sbin/httpd -DFOREGROUND
apache   18186 40.2  0.5 5664272 189712 ?      Sl   19:18   0:28 /usr/sbin/httpd -DFOREGROUND
apache   17377 10.6  0.5 5663704 174132 ?      Sl   19:18   0:08 /usr/sbin/httpd -DFOREGROUND
obrazyn+ 17167  2.4  0.4 467836 138448 ?       R    19:17   0:03 php-fpm: pool obrazynowoczesne
obrazyn+ 17134  3.0  0.4 462548 154104 ?       R    19:17   0:04 php-fpm: pool obrazynowoczesne
apache   18007 15.2  0.4 5663624 151956 ?      Sl   19:18   0:10 /usr/sbin/httpd -DFOREGROUND
tapety   15087  3.0  0.3 432536 117012 ?       S    19:08   0:21 php-fpm: pool tapety

Virtual Memory Info:
procs -----------------------memory---------------------- ---swap-- -----io---- -system-- --------cpu-------- -----timestamp-----
 r  b         swpd         free         buff        cache   si   so    bi    bo   in   cs  us  sy  id  wa  st                 CET
25  0       472792       561800      1213576     23673888    0    0    55   133    3    1  16  13  69   3   0 2020-03-20 19:20:04
30  0       472792       568376      1213576     23674864    0    0   576   124 3206 5157  74  26   0   0   0 2020-03-20 19:20:05
28  0       472792       511472      1213656     23674808   12    0    92   540 2778 10261  72  28   0   0   0 2020-03-20 19:20:06
 
Hello,

MySQL tuning won't help against brute-force attacks and heavy- and not-optimized MySQL queries which require too much CPU power. There are SQL queries which can not be speed-up whatever you set in my.cnf. So you might need to stop heavy requests before they reach MySQL at all.

There are tools to stop brute-force attacks, there is a good idea to start using caching at application level too.
 
Also check apache statistics and domain access/error logs to check out what exactly causes so high load, also install opcache via custombuild.
Mysqltuner can give you recommentaions to tune mysql, but in your case it will not give large impact.
 
Back
Top