Issue Mysql server under pressure (too much ?)

[Teal_cfr]

Server operating system version

Ubuntu 20.04.6 LTS

Plesk version and microupdate number

Plesk Obsidian 18.0.54 Update #4

Hi,

I'm having issue with a server than hosting 5 Wordpress and 1 staging
Including on Woocommerce and one Bbpress with huge db.
The server got 120Go Ram with 500Mo swap set.

I still have 8 db running and one of them is 24 Go of data, but this one is not use anymore, I will remove it soon (I'm not sure that keep this database consumes cache memory or not)
my server just tell me that MySQL make things worst I guess :

top - 12:32:13 up 63 days, 6:34, 2 users, load average: 15.13, 15.97, 16.15
Tasks: 323 total, 10 running, 313 sleeping, 0 stopped, 0 zombie
%Cpu(s): 81.5 us, 17.7 sy, 0.0 ni, 0.7 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
MiB Mem : 128707.5 total, 99500.0 free, 7238.5 used, 21969.1 buff/cache
MiB Swap: 512.0 total, 483.6 free, 28.4 used. 119698.2 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3302589 mysql 20 0 7125124 1.5g 19920 S 557.5 1.2 36:27.83 mysqld
3299443 boutiqu+ 20 0 551096 334856 91744 R 95.3 0.3 15:19.24 php-fpm
3256955 mx2k_ad+ 20 0 457368 275984 143996 R 94.7 0.2 86:34.80 php-fpm
3219667 mx2k_ad+ 20 0 463384 294736 156440 R 93.4 0.2 131:10.60 php-fpm
3288299 boutiqu+ 20 0 559492 335268 100376 S 93.4 0.3 60:14.85 php-fpm
3219478 mx2k_ad+ 20 0 460724 291176 155780 R 90.4 0.2 130:47.54 php-fpm
3299227 boutiqu+ 20 0 567416 343408 100272 R 84.1 0.3 16:38.39 php-fpm
3288679 boutiqu+ 20 0 564968 340928 100220 S 76.1 0.3 58:52.33 php-fpm
3288680 boutiqu+ 20 0 553188 337252 91872 R 73.8 0.3 59:07.43 php-fpm
3303279 bc21770+ 20 0 337488 151504 45868 S 63.1 0.1 0:05.70 php-fpm
3219489 mx2k_ad+ 20 0 478788 309848 156392 S 52.8 0.2 127:21.99 php-fpm
3219678 mx2k_ad+ 20 0 461144 291196 155892 S 47.8 0.2 129:12.88 php-fpm
3288989 boutiqu+ 20 0 550740 334584 91808 R 46.2 0.3 58:39.99 php-fpm
3303268 bc21770+ 20 0 343644 157852 45916 R 43.9 0.1 0:08.24 php-fpm
3299234 boutiqu+ 20 0 540492 323944 91860 S 41.9 0.2 16:19.40 php-fpm
3303237 bc21770+ 20 0 425452 168144 48704 S 21.3 0.1 0:17.56 php-fpm
3299334 boutiqu+ 20 0 513836 297608 91804 R 2.7 0.2 15:48.93 php-fpm
3287569 www-data 20 0 2264280 30220 7236 S 2.3 0.0 1:35.98 apache2
3287550 nginx 20 0 497408 442880 10656 S 2.0 0.3 3:24.15 nginx
3923475 root 20 0 1870572 30484 7644 S 1.7 0.0 680:30.63 fail2ban-ser

And not sure about what my MySQL tell me :

MySQL on localhost (10.3.38) up 0+00:06:59 [12:32:45]
Queries: 650.2k qps: 1589 Slow: 59.0 Se/In/Up/De(%): 137/00/17/00
Sorts: 623302 qps now: 1856 Slow qps: 0.6 Threads: 47 ( 13/ 8) 137/00/17/00
Cache Hits: 364.8k Hits/s: 891.5 Hits now: 1042.5 Ratio: 40.9%
Ratio now: 41.1%
Handler: (R/W/U/D) 1635111/ 6/ 131/ 0 Tmp: R/W/U: 963/12947/ 0
ISAM Key Efficiency: 100.0% Bps in/out: 696.8k/ 8.4M Now in/out: 779.5k/ 8.3M

Id User Host/IP DB Time % Cmd State Query
-- ---- ------- -- ---- - --- ----- ----------
2136 mx2k_q1U localhost mxxx_2023 70 0.0 Sleep
2223 mx2k_q1U localhost mxxx_2023 55 0.0 Sleep
111 admin localhost psa 51 0.0 Sleep
2337 mx2k_q1U localhost mxxx_2023 33 0.0 Sleep
2340 mx2k_q1U localhost mxxx_2023 33 0.0 Sleep
2398 mx2k_q1U localhost mxxx_2023 23 0.0 Sleep
2405 trialmag localhost wp_1 22 0.0 Sleep
2412 trailadv localhost wp_2 19 0.0 Sleep
2457 mx2k_q1U localhost mxxx_2023 12 0.0 Sleep
2132 mx2k_q1U localhost mxxx_2023 7 0.0 Query Creating sort i SELECT l.ID, post_title, post_content, post_name, post_parent, post_author, post_status, post_
2493 mx2k_q1U localhost mxxx_2023 7 0.0 Sleep
2490 mx2k_q1U localhost mxxx_2023 6 0.0 Query Sending data SELECT SQL_CALC_FOUND_ROWS L87gfTN_posts.ID FROM L87gfTN_posts INNER JOIN
2520 boutique localhost woocom 3 0.0 Sleep
2522 mx2k_q1U localhost mxxx_2023 3 0.0 Sleep
2456 mx2k_q1U localhost mxxx_2023 2 0.0 Query Creating sort i SELECT post_date_gmt FROM xxxxxx_posts WHERE post_status = 'publish' AND post_type IN ('post'
2521 mx2k_q1U localhost mxxx_2023 2 0.0 Query Sending data SELECT COUNT(xxxxxx_posts.ID) FROM xxxxx_posts WHERE xxxxx_posts.post_status IN ('publish
2524 mx2k_q1U localhost mxxx_2023 2 0.0 Sleep
2527 mx2k_q1U localhost mxxx_2023 2 0.0 Sleep
2528 boutique localhost woocom 2 0.0 Sleep
2220 mx2k_q1U localhost mxxx_2023 1 0.0 Query Creating sort i SELECT l.ID, post_title, post_content, post_name, post_parent, post_author, post_status, post_
2336 mx2k_q1U localhost mxxx_2023 1 0.0 Query Creating sort i SELECT l.ID, post_title, post_content, post_name, post_parent, post_author, post_status, post_
2397 mx2k_q1U localhost mxxx_2023 1 0.0 Sleep
2532 boutique localhost woocom 1 0.0 Sleep

My.cnf is like this :
I try to removed all customisation and restart MySQL but still same problem

[client-server]

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
[mysqld]
query_cache_size=64M
innodb_buffer_pool_size=1024M
#bind-address = ::
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
local-infile=0
#max_allowed_packet=4196M
#net_buffer_length=1000000
#wait_timeout=31536000
#interactive_timeout=999999

[client]
#wait_timeout=31536000
#max_allowed_packet=4196M

[mysqldump]
#max_allowed_packet=4196M

If anyone have a clue or idea to make it better.

Since I think main of my problem is due to MySQL and SQL request latency.

Thanks for any helps

 

[andyxyz]

Run mysqltuner to get suggestions on what settings to use

 

[Dave W]

Just dont blindly follow what MySQLtuner says without understanding what it is advising you to do.

Also have a look at a slow query log

 

[Teal_cfr]

Run mysqltuner to get suggestions on what settings to use

For now i'm stuck at this point :

ubuntu@modest-hoover:~$ perl mysqltuner.pl --host 127.0.0.1 --user mysql --pass=/etc/psa/.psa.shadow
>> MySQLTuner 2.2.12
* Jean-Marie Renouard <[email protected]>
* Major Hayden <[email protected]>
>> 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
[--] Performing tests on 127.0.0.1:3306
[!!] Attempted to use login credentials, but they were invalid

 

[Peter Debik]

Try username "admin".

 

[Teal_cfr]

From SSH remote connection :
I tried : admin / ubuntu / root / mysql

I try to execute the command from plesk terminal also and got this

 

[Peter Debik]

In your above screenshot you did not provide a password or username for the login.

 

[Teal_cfr]

ubuntu@modest-hoover:~$ perl mysqltuner.pl --host 127.0.0.1 --user admin --pass=`cat /etc/psa/.psa.shadow`
cat: /etc/psa/.psa.shadow: Permission denied
Option pass requires an argument
Name:
MySQLTuner 2.2.12 - MySQL High Performance Tuning Script

Important Usage Guidelines:

ubuntu@modest-hoover:~$ perl mysqltuner.pl --host 127.0.0.1 --user ubuntu --pass=`cat /etc/psa/.psa.shadow`
cat: /etc/psa/.psa.shadow: Permission denied
Option pass requires an argument
Name:
MySQLTuner 2.2.12 - MySQL High Performance Tuning Script

Important Usage Guidelines:

 

[Teal_cfr]

ubuntu@modest-hoover:~$ sudo perl mysqltuner.pl --host 127.0.0.1 --user ubuntu --pass=`sudo cat /etc/psa/.psa.shadow`
>> MySQLTuner 2.2.12
* Jean-Marie Renouard <[email protected]>
* Major Hayden <[email protected]>
>> 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
[--] Performing tests on 127.0.0.1:3306
[!!] Attempted to use login credentials, but they were invalid
ubuntu@modest-hoover:~$ sudo perl mysqltuner.pl --host 127.0.0.1 --user admin --pass=`sudo cat /etc/psa/.psa.shadow`
>> MySQLTuner 2.2.12
* Jean-Marie Renouard <[email protected]>
* Major Hayden <[email protected]>
>> 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
[--] Performing tests on 127.0.0.1:3306
[!!] Attempted to use login credentials, but they were invalid
ubuntu@modest-hoover:~$ sudo perl mysqltuner.pl --host 127.0.0.1 --user root --pass=`sudo cat /etc/psa/.psa.shadow`
>> MySQLTuner 2.2.12
* Jean-Marie Renouard <[email protected]>
* Major Hayden <[email protected]>
>> 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
[--] Performing tests on 127.0.0.1:3306
[!!] Attempted to use login credentials, but they were invalid
ubuntu@modest-hoover:~$ sudo perl mysqltuner.pl --host 127.0.0.1 --user mysql --pass=`sudo cat /etc/psa/.psa.shadow`
>> MySQLTuner 2.2.12
* Jean-Marie Renouard <[email protected]>
* Major Hayden <[email protected]>
>> 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
[--] Performing tests on 127.0.0.1:3306
[!!] Attempted to use login credentials, but they were invalid

 

[Peter Debik]

@Teal_cfr All the login attempts are invalid due to wrong credentials. As an alternative, copy the password from /etc/psa/.psa.shadow in text form and use it directly as the pass=... parameter on the command line. I suspect that the additional "invisible" linefeed characters at the end of .psa.shadow cause a wrong feed to the string.

 

[Dave W]

sudo perl mysqltuner.pl --host 127.0.0.1 --user admin --pass `cat /etc/psa/.psa.shadow`

 

[Teal_cfr]

sudo perl mysqltuner.pl --host 127.0.0.1 --user admin --pass `cat /etc/psa/.psa.shadow`

ubuntu@modest-hoover:~$ sudo perl mysqltuner.pl --host 127.0.0.1 --user admin --pass `sudo cat /etc/psa/.psa.shadow`
>> MySQLTuner 2.2.12
* Jean-Marie Renouard <[email protected]>
* Major Hayden <[email protected]>
>> 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
[--] Performing tests on 127.0.0.1:3306
[!!] Attempted to use login credentials, but they were invalid

 

[Teal_cfr]

@Teal_cfr All the login attempts are invalid due to wrong credentials. As an alternative, copy the password from /etc/psa/.psa.shadow in text form and use it directly as the pass=... parameter on the command line. I suspect that the additional "invisible" linefeed characters at the end of .psa.shadow cause a wrong feed to the string.

I try every users with plaintext pass --pass='md5' or --pass=md5

Nothing work

 

[Peter Debik]

What do you mean by "md5" as a parameter? You need to add the password, and for the "admin" user it is the password that is given in /etc/psa/.psa.shadow. This must absolutely work if you can access Plesk, because Plesk is using the same.

 

[Teal_cfr]

By md5 I mean, the password stocked in the /etc/psa/.psa.shadow, which I guess is encoded in md5

 

[Teal_cfr]

I just tried with admin password of plesk and still have "[!!] Attempted to use login credentials, but they were invalid"

I really have no idea which credential is used for this

 

[sans]

By md5 I mean, the password stocked in the /etc/psa/.psa.shadow, which I guess is encoded in md5

MySQL/MySQL tuner accepts password in this as-is.

Though, can you at least login via phpMyAdmin at "T&S > Database Servers > MariaDB (localhost)" - icon on the right?

 

[Teal_cfr]

MySQL/MySQL tuner accepts password in this as-is.

Though, can you at least login via phpMyAdmin at "T&S > Database Servers > MariaDB (localhost)" - icon on the right?View attachment 24313

yes i can, without asking me loggin

I found out this :

ubuntu@modest-hoover:~$ mysqladmin ping
mysqladmin: unknown variable 'max_allowed_packet=4196M'

After modify my.cnf

ubuntu@modest-hoover:/etc/mysql$ mysqladmin ping
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'ubuntu'@'localhost' (using password: NO)'

 

[Teal_cfr]

Also have this :

ubuntu@modest-hoover:~$ sudo cat /etc/mysql/debian.cnf | grep -i password
password =
password =

 

[Peter Debik]

Please remove username and password settings from the database configuration files. The user name you want to use is also not "ubuntu", but "admin".