• If you are still using CentOS 7.9, it's time to convert to Alma 8 with the free centos2alma tool by Plesk or Plesk Migrator. Please let us know your experiences or concerns in this thread:
    CentOS2Alma discussion

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
346
flieg4bei
F
TimReeves
Resolved MariaDB Update to 10.11.7 via Plesk 18.0.60 on Debian 10
Replies
1
Views
180
TimReeves
TimReeves
G
Question MariaDB 10.6 — MySQL Tuner Questions!
Replies
12
Views
3K
zed2007
Z
H
Issue 500 Plesk\Exception\Database
Replies
4
Views
938
hostidex
H
M
Resolved Server Error 500 Plesk\Exception\Database
Replies
2
Views
1K
mrcx
M
Back
Top