• Please be aware: Kaspersky Anti-Virus has been deprecated
    With the upgrade to Plesk Obsidian 18.0.64, "Kaspersky Anti-Virus for Servers" will be automatically removed from the servers it is installed on. We recommend that you migrate to Sophos Anti-Virus for Servers.
  • The Horde webmail has been deprecated. Its complete removal is scheduled for April 2025. For details and recommended actions, see the Feature and Deprecation Plan.
  • We’re working on enhancing the Monitoring feature in Plesk, and we could really use your expertise! If you’re open to sharing your experiences with server and website monitoring or providing feedback, we’d love to have a one-hour online meeting with you.

Resolved Plesk Cpu Mariadb promlem

M

mahmut ozdemir

Basic Pleskian
Hello

[mysqld]
bind-address = 127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in This page has moved

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

sql_mode = 'NO_ENGINE_SUBSTITUTION'

tmpdir=/var/tmpfs
tmp_table_size=2K
table_open_cache=250
max_allowed_packet=5GB
max_heap_table_size=2GB
query_cache_type = 0
query_cache_size = 0
innodb_buffer_pool_size=10G
innodb_buffer_pool_instances=10G
max_connections=500



Ekran Alıntısı.PNG


Ekran Alıntısı.PNG


please help me
 
Quick look, I noticed you have the followings under [mysqld_safe]

sql_mode = 'NO_ENGINE_SUBSTITUTION'

tmpdir=/var/tmpfs
tmp_table_size=2K
table_open_cache=250
max_allowed_packet=5GB
max_heap_table_size=2GB
query_cache_type = 0
query_cache_size = 0
innodb_buffer_pool_size=10G
innodb_buffer_pool_instances=10G
max_connections=500

I think these should have come under [mysqld]

Also do you have this folder '/var/tmpfs'?
 
AusWeb said:
Quick look, I noticed you have the followings under [mysqld_safe]

sql_mode = 'NO_ENGINE_SUBSTITUTION'

tmpdir=/var/tmpfs
tmp_table_size=2K
table_open_cache=250
max_allowed_packet=5GB
max_heap_table_size=2GB
query_cache_type = 0
query_cache_size = 0
innodb_buffer_pool_size=10G
innodb_buffer_pool_instances=10G
max_connections=500

I think these should have come under [mysqld]

Also do you have this folder '/var/tmpfs'?
Click to expand...

Hello

okey

[root@server ~]# cd /var/tmpfs
[root@server tmpfs]

[mysqld]
bind-address = 127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in This page has moved

tmpdir=/var/tmpfs
tmp_table_size=2K
table_open_cache=250
max_allowed_packet=5GB
max_heap_table_size=2GB
query_cache_type = 0
query_cache_size = 0
innodb_buffer_pool_size=10G
innodb_buffer_pool_instances=10G
max_connections=500


[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
 
Hello

Type Plesk\Exception\Database
Message DB query failed: SQLSTATE[HY000] [2002] No such file or directory
File Mysql.php
Line 60

[mysqld]
bind-address = 127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in This page has moved

tmpdir=/var/tmpfs
tmp_table_size=2K
table_open_cache=250
max_allowed_packet=5GB
max_heap_table_size=2GB
query_cache_type = 0
query_cache_size = 0
innodb_buffer_pool_size=10G
innodb_buffer_pool_instances=10G
max_connections=500


[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
 
i'm not sure what is causing the issue. More info needed.

You can add this to [mysqld]
Code: 
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Followings are depend on your server,

tmp_table_size=2K
table_open_cache=250
max_allowed_packet=5GB
max_heap_table_size=2GB
query_cache_type = 0
query_cache_size = 0
innodb_buffer_pool_size=10G
innodb_buffer_pool_instances=10G
max_connections=500

I'm not sure how did you get this configuration.

Do you have about 64GB memory in your server?
 
AusWeb said:
i'm not sure what is causing the issue. More info needed.

You can add this to [mysqld]
Code: 
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Followings are depend on your server,

tmp_table_size=2K
table_open_cache=250
max_allowed_packet=5GB
max_heap_table_size=2GB
query_cache_type = 0
query_cache_size = 0
innodb_buffer_pool_size=10G
innodb_buffer_pool_instances=10G
max_connections=500

I'm not sure how did you get this configuration.

Do you have about 64GB memory in your server?
Click to expand...


No 16 GB


sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION


tmp_table_size=2K
table_open_cache=250
max_allowed_packet=5GB
max_heap_table_size=2GB
query_cache_type = 1
query_cache_size = 20GB
query_cache_limit = 20GB
innodb_buffer_pool_size=10G
innodb_buffer_pool_instances=10G
max_connections=500



okey ?
 
I'm sorry. I'm having trouble understanding your posts.

I would start the MariaDB with its default configuration.

Code: 
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[mysqld]
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
bind-address = 127.0.0.1
local-infile=0

Please take a backup of your my.cnf before you do it.

Then,

Troubleshooting slow performance of the MySQL on Plesk server

I'd increase innodb_buffer_pool_size to 4G (not GB)
 
AusWeb said:
I'm sorry. I'm having trouble understanding your posts.

I would start the MariaDB with its default configuration.

Code: 
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[mysqld]
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
bind-address = 127.0.0.1
local-infile=0

Please take a backup of your my.cnf before you do it.

Then,

Troubleshooting slow performance of the MySQL on Plesk server

I'd increase innodb_buffer_pool_size to 4G (not GB)
Click to expand...


okey

[root@server ~]# mysql -uadmin -p`cat /etc/psa/.psa.shadow` -i
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 89
Server version: 10.3.21-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> plesk db "SHOW FULL PROCESSLIST"
->
 
AusWeb said:
You can simply type,

#plesk db

and

MariaDB > SHOW FULL PROCESSLIST;

Also I can see the website mentioned (https://on5yirmi5.com/) is working, really fast now. If the problems are solved, Please mark this thread as solved.
Click to expand...


yesss

yes you have improved thank you

[root@server ~]# w
20:30:02 up 1:59, 1 user, load average: 1.77, 1.94, 3.91
USER TTY FROM LOGIN@ IDLE JCPU PCPU WHAT
root pts/0 46.98.16.75 20:08 2.00s 0.18s 0.01s w
[root@server ~]# w



MariaDB [(none)]> SHOW FULL PROCESSLIST;
+------+-------------+-----------+-------------+---------+------+--------------------------+-----------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+------+-------------+-----------+-------------+---------+------+--------------------------+-----------------------+----------+
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 7161 | admin | localhost | NULL | Query | 0 | Init | SHOW FULL PROCESSLIST | 0.000 |
| 7169 | wp_9hw0x | localhost | wp_on5yirmi | Sleep | 0 | | NULL | 0.000 |
| 7170 | wp_9hw0x | localhost | wp_on5yirmi | Sleep | 0 | | NULL | 0.000 |
+------+-------------+-----------+-------------+---------+------+--------------------------+-----------------------+----------+
8 rows in set (0.000 sec)

MariaDB [(none)]>



Okey ?
 
looks ok to me.

Please mark the thread as solved.

Also please note SQL optimization is not part of the Plesk as mentioned in the article.

if you do not understand fully, please do not copy and paste configuration from the internet. These setting are vary from server to server and website to website.
 
You must log in or register to reply here.

Similar threads

F
Question Mariadb down after update Plesk /var 100% full
Replies
8
Views
1K
flieg4bei
F
TimReeves
Resolved MariaDB Update to 10.11.7 via Plesk 18.0.60 on Debian 10
Replies
1
Views
801
TimReeves
TimReeves
C
Resolved Mariadb not working - DB query failed: SQLSTATE[HY000] [2002] No such file or directory
Replies
7
Views
2K
Crash79797
C
G
Question MariaDB 10.6 — MySQL Tuner Questions!
Replies
12
Views
3K
zed2007
Z
H
Issue 500 Plesk\Exception\Database
Replies
4
Views
1K
hostidex
H
Back
Top