MySQL / MariaDB Problems after WHM & MariaDB update's ( post is edit for cPanelLauren request)

[nadav123]

Hey Guys how are you?
First, i want to say thanks to all the people here help me in the last time. ( a lot of knowledge here in this forum )

So... again C-Panel and his updates ( Specially MariaDB / MySql ).
i move my server from California to Virginia with the same configuration before (don't was any problem like this before).

The first problem start when you start do commend line to restart MySql in the WHM>TERMINAL
command line: service mysql restart
and this what hepend:

SCREENSHOT:

AND THIS CREATE ERROR IN THE LOGS AS WELL AND SOMETIME
HAVE A NOTE TO USE "NATIVE AIO" ( i will put my.cnf configuration at the end of the explanation for more info ):

SCREENSHOT:



NOW WHEN I RESTART THE MySql:
WHM > Restart Services > SQL Server
and restart the server from the interface everything is fine and i don't get any errors... is very wired.

So i start hacking deeper to the problem and i download a tuner... the tuner show overload on the memory on 1GB Pool and my server is 4GB Physical...
something didn't hepend in the old server... ( The old server showed 40% and the potential was 84% with the same configuration)

SCREENSHOT:



BUT AGAIN! SO WIRED! WHEN YOU GO TO:
WHM > SERVICE STATUS
EVERYTHING LOOK FINE! LIKE TO THE SERVER DONT HAVE ANY OVERLOAD....!

SCREENSHOT:




_____________________________________________________________________


AFTER THIS I START TO LOSE IT, I CALLED TO MY HOSTING PROVIDER TRY TO SOLVE THE PROBLEM...
we try to update MariaDB to see what hepend:

SCREENSHOT:


AND WE GOT ONE ERROR, BUT MARIADB SHOW EVERYTHING IS FINE AFTER ( BUT IT IS NOT OK AT ALL ):

SCREENSHOT:


We check MariaDB Create config:

SCREENSHOT:


BUT AGAIN! I NOTE IN THE SCREENSHOT LIKE YOU SEE GUYS! WE HAVE DUPLICATE CONFIG! WHEN YOU LOOK "NANO" YOU SEE MARIADB COPY MY.CNF!
SOMETHING MARIADB DIDNT DID BEFORE IN ANY UPDATE... ( WIRED!?? )


So again... chacking, even permissions of the folder:
SCREENSHOT:


like you see i mark everything and mysql:mysql
like should be is ok...

and still... server is in overload when you run "commend tuner" to analyze the database and every 4 - 3 days the database
shut down... like today, and this the 2 errors he shows me in the log after the shut down:

SCREENSHOT:



And i got this from the c-panel notification in my email after the Database server shutdown:

The cPanel & WHM update process failed for the following reason:

Maintenance ended; however, it did not exit cleanly (256). The following events were logged: “scripts/rpmup”. Review the update logs to determine why the update failed. Update log preview:​ ... ... [2020-08-18 22:06:50 -0400] E [/usr/local/cpanel/scripts/rpmup] The “/usr/local/cpanel/scripts/rpmup” command (process 25277) reported error number 1 when it ended. ... [2020-08-18 22:07:38 -0400] - Finished command `/usr/local/cpanel/scripts/check_mysql` in 0.209 seconds [2020-08-18 22:07:38 -0400] Processing: Checking CloudLinux installation [2020-08-18 22:07:38 -0400] - Processing command `/usr/local/cpanel/bin/cloudlinux_update` [2020-08-18 22:07:38 -0400] - Finished command `/usr/local/cpanel/bin/cloudlinux_update` in 0.061 seconds [2020-08-18 22:07:38 -0400] Processing: Updating plugins data cache [2020-08-18 22:07:38 -0400] - Processing command `/usr/local/cpanel/bin/refresh_plugin_cache` [2020-08-18 22:07:38 -0400] - Finished command `/usr/local/cpanel/bin/refresh_plugin_cache` in 0.030 seconds [2020-08-18 22:07:38 -0400] Processing: Ensuring SSL certificate information for CCS is up to date. [2020-08-18 22:07:38 -0400] - Processing command `/usr/local/cpanel/scripts/ccs-check --run --ssl` [2020-08-18 22:07:38 -0400] - Finished command `/usr/local/cpanel/scripts/ccs-check --run --ssl` in 0.009 seconds [2020-08-18 22:07:38 -0400] Processing: Ensure cpanel-plugins yum repo exists [2020-08-18 22:07:38 -0400] - Finished in 0.311 seconds [2020-08-18 22:07:38 -0400] Processing: Checking Addon Licenses [2020-08-18 22:07:38 -0400] - Finished in 0.020 seconds [2020-08-18 22:07:38 -0400] Processing: Checking End Of Life for current version. [2020-08-18 22:07:39 -0400] - Finished in 1.073 seconds [2020-08-18 22:07:39 -0400] Processing: [2020-08-18 22:07:39 -0400] Maintenance complete. => Log closed Tue Aug 18 22:07:39 2020 ---------------------------------------------------------------------------------------------------- => Log opened from cPanel Update (upcp) - Slave (25252) at Tue Aug 18 22:07:39 2020

Copyright© 2020 cPanel, L.L.C.​

WHAT TO DO GUYS??!? PLEASE I STUCK WITH THAT OVER A MONTH AND MY SERVER PROVIDER DONT KNOW WHAT THE PROBLEM TO AND HOW TO FIX IT
BECAUSE IS RELATED TO THE NEW UPDATES FROM C-PANEL... PLEASE HELP ME GUYS, BY THE WAY, THIS IS THE CONFIGURATION OF MY.CNF:

(WANT TO NOTIFIED BEFORE YOU LOOK ON THAT! I EVEN TRIED THE DEFAULT CONFIGURATION FROM C-PANEL AND THE PROBLEM IS STILL THE SAME)
___________________________________________________________________________________________________________________________________________________________________

[mysql]
port = 3306
#socket = /var/lib/mysql/mysql.sock

[mysqld]
# Addons (must be in the top)
performance_schema = ON

# Required Settings
basedir = /usr
bind_address = 127.0.0.1 # Change to 0.0.0.0 to allow remote connections
datadir = /var/lib/mysql
max_allowed_packet = 256M
max_connect_errors = 100000
#pid_file = /var/run/mysqld/mysqld.pid
port = 3306

# SKIP
skip_external_locking
#skip-federated
#skip-pbxt
#skip-pbxt_statistics
#skip-archive
#skip_name_resolve
#old_passwords

# MYSQL SOCKET MAIN SOCKET (ALL OUR THE SAME)
#socket = /var/lib/mysql/mysql.sock


# Enable for b/c with databases created in older MySQL/MariaDB versions (e.g. when using null dates)
#sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES

tmpdir = /tmp
user = mysql

# InnoDB Settings
default_storage_engine = InnoDB
innodb_use_native_aio = 0
innodb_buffer_pool_instances = 1 # Use 1 instance per 1GB of InnoDB pool size
innodb_buffer_pool_size = 1G
#innodb-buffer-pool-chunk-size = 256M

# Use up to 70-80% of RAM
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M
innodb_log_file_size = 256M
innodb_log_files_in_group = 1
innodb_stats_on_metadata = 0
#innodb_temp_data_file_path = ibtmp1:64M:autoextend:max:20G # Control the maximum size for the ibtmp1 file
#innodb_thread_concurrency = 4 # Optional: Set to the number of CPUs on your system (minus 1 or 2) to better
# contain CPU usage. E.g. if your system has 8 CPUs, try 6 or 7 and check
# the overall load produced by MySQL/MariaDB.
innodb_read_io_threads = 32
innodb_write_io_threads = 32

# MyISAM Settings
#query_cache_limit = 1M # UPD - Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x
query_cache_size = 0 # UPD - Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x
query_cache_type = 0 # Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x

key_buffer_size = 32M # UPD
myisam_sort_buffer_size = 32M

low_priority_updates = 1
concurrent_insert = 2

# Connection Settings
max_connections = 100 # UPD
max_user_connections = 75
back_log = 256

thread_cache_size = 100
thread_stack = 192K

interactive_timeout = 180
wait_timeout = 180

# For MySQL 5.7+ only (disabled by default)
#max_execution_time = 30000 # Set a timeout limit for SELECT statements (value in milliseconds).
# This option may be useful to address aggressive crawling on large sites,


# For MariaDB 10.1.1+ only (disabled by default)
#max_statement_time = 30 # The equivalent of "max_execution_time" in MySQL 5.7+ (set above)
# The variable is of type double, thus you can use subsecond timeout.


# Buffer Settings
join_buffer_size = 2M # UPD
read_buffer_size = 1M # UPD
read_rnd_buffer_size = 2M # UPD
sort_buffer_size = 2M # UPD
#max_length_for_sort_data = 2M # Centminmod
net_buffer_length = 16K # Centminmod

# Table Settings
table_definition_cache = 20000 # UPD
#table_open_cache = 20000 # UPD
open_files_limit = 40000 # UPD - This can be 2x to 3x the table_open_cache value or match the system's
# open files limit usually set in /etc/sysctl.conf or /etc/security/limits.conf
# In systemd managed systems this limit must also be set in:
# /etc/systemd/system/mysqld.service.d/override.conf (for MySQL 5.7+) and
# /etc/systemd/system/mariadb.service.d/override.conf (for MariaDB)

max_heap_table_size = 128M
tmp_table_size = 128M

# Search Settings
ft_min_word_len = 3 # Minimum length of words to be indexed for search results

# Logging
log_error = /var/lib/mysql/mysql_error.log
log_queries_not_using_indexes = 1
long_query_time = 5
slow_query_log = 0 # Disabled for production
slow_query_log_file = /var/lib/mysql/mysql_slow.log

[mysqldump]
# Variable reference
quick
quote_names
max_allowed_packet = 64M

___________________________________________________________________________________________________________________________________________________________________


Please, guys, help... i know is long but is detailed with a screenshot to see what i actually see throw my eyes... please help is
DAMAGE SEO AND UPTIME OF ALL MY WEBSITES

It is an emergency, my friends, i will apricate any good advice you have.

 

[cPanelLauren]

I am not sure where/how you correlate this to updates, I'm not seeing any evidence that an update caused this issue. The maintenance update email you received is a result of daily maintenance.

• The note:

  [Note] InnoDB: Buffer pool(s) load completed at 200813 23:27:06

  is a result of the Buffer pool reloading and has absolutely nothing to do with server load. MySQL discusses this here: MySQL :: MySQL 5.7 Reference Manual :: 14.8.3.6 Saving and Restoring the Buffer Pool State
• MariaDB and MySQL use the same config file, /etc/my.cnf
• You're running CentOS 7.8 you should not be using service mysql start the most preferrable command to run is /scripts/restartsrv_mysql which has a number of flags that can be seen by running

  /scripts/restartsrv_mysql --help

• I don't understand the correlation you're making between the ownership of mysql and the load? Can you clarify how those would be related? Nothing looks to be incorrect about the ownership or permissions from the screenshot.
• The error you received when updating to MariaDB does not look like it caused a failure to install MariaDB
• Why is the Max MySQL memory set to 27G when you have only 4G on the server? I'd assume this is related to the issue at hand.
• Looking at the following in your my.cnf file alone : key_buffer_size = 32M # UPD innodb_buffer_pool_size = 1G innodb_log_buffer_size = 16M = 1.5G and that's not totalling in all the buffers you're allocating memory for.
• What is the output of the following when run from the mysql command line:

  SHOW VARIABLES LIKE '%buffer%';

• Besides the failure to restart that initial time what are the exact issues you're experiencing?

 

[nadav123]

Hey! thank you!

OK, So.. i only want to say about the 1.5G you point here...
Still... 1.5G is very little to 4G Physical.

And you have a place to way more.... (MySQL recommended 80%)

but! this is incorrect info the tuner create.... what is mean... i think the tuner read 2 databases with the same configuration and because of that
server shut down when the tuner get to 600%
(THIS THE REASON WHY YOU GO TO WHM>SERVICE STATUS AND THE MEMORY LOOK FINE...)

now like i said in the old server is was before 6 months ago this not was like that... was the same configuration and the tuner was
84% max potential...

the main problem is... every 3 days the database fall... and then i get the error as you see in the last screenshot i send in the post.
when is hepend all the DATA from the database is gone... (refreshing and restarting)... i lose ranking in google and more problems show up with uptime...
errors in the aria_control_log_file...
and control problem in the look like:
2020-08-20 15:00:46 259 [Warning] IP address '173.231.218.122' could not be resolved: Name or service not known
2020-08-20 15:25:34 416 [Warning] IP address '85.93.20.148' could not be resolved: Name or service not known
2020-08-20 16:03:38 534 [Warning] IP address '185.234.216.38' could not be resolved: Name or service not known
or error in the log about aria log file like in the screenshot in post...

when the server restart because the overload, it comes back to 23% and 680 potential.. (not logic as well..)


ABOUT COMMAND LINE: service MySQL restart

this is never and ever field to me... i restarted MySQL like that when i was working on the server... before version 88 of c-panel i never
see this commend line failed to restart MariaDB...


now i try to change the configuration.... when we talk.... and even a pool of 512M is still with 689% something like that...
the % change only when i change:
max_allowed_packet = 268435456 (DEFAULT FROM C-PANEL)

max_allowed_packet = 32M


only then i get 90% potential in the tuner... but is not make sense! come on 32M max allowed packet....
is cant be...

by the way, c-panel change it every restart to that:
max_allowed_packet = 268435456 (DEFAULT FROM C-PANEL)

this is mean C-PANEL read only 1 database and the database possible to do it... i have something here not let it hepend...
i don't know why...

and the command line you give me not working.
or i do something wrong?

 

[cPanelLauren]

OK, So.. i only want to say about the 1.5G you point here...
Still... 1.5G is very little to 4G Physical.

It's really not though, with just those buffers alone you're reserving 37.5% of the memory on the server. Add in what you've selected for the other customizations and I'd wager you far outweigh the amount of memory you have presently.

You need to run the command I provided from the MySQL command line not straight from SSH. For example:

[root@server ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 46002
Server version: 10.3.24-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)]> show variables like 'buffer%';
Empty set (0.011 sec)

MariaDB [(none)]> show variables like '%buffer%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| aria_pagecache_buffer_size          | 134217728      |
| aria_sort_buffer_size               | 268434432      |
| bulk_insert_buffer_size             | 8388608        |
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
| innodb_change_buffer_max_size       | 25             |
| innodb_change_buffering             | all            |
| innodb_log_buffer_size              | 16777216       |
| innodb_sort_buffer_size             | 1048576        |
| join_buffer_size                    | 262144         |
| join_buffer_space_limit             | 2097152        |
| key_buffer_size                     | 134217728      |
| mrr_buffer_size                     | 262144         |
| myisam_sort_buffer_size             | 134216704      |
| net_buffer_length                   | 16384          |
| preload_buffer_size                 | 32768          |
| read_buffer_size                    | 131072         |
| read_rnd_buffer_size                | 262144         |
| sort_buffer_size                    | 2097152        |
| sql_buffer_result                   | OFF            |
+-------------------------------------+----------------+
28 rows in set (0.005 sec)

MariaDB [(none)]>

Generally speaking MySQL does do some optimization of the my.cnf file based on your usage if you have this enabled (it is enabled by default) in WHM>>Server Configuration>>Tweak Settings

Allow cPanel & WHM to determine the best value for your MySQL max_allowed_packet configuration?
cPanel & WHM will adjust the max_allowed_packet value during each MySQL restart, if a change is necessary.

 

[nadav123]

OK First thanks again
secound i only want to tell you with the default configuration from c-panel i have the same overload.... and currently now WHM>TWAEK SETTING > MYSQL
all the option of c-panel was on all this time... and still overload...
with my configuration and with c-panel default configuration...
is not make sense...
even if only the buffer like you said is 37% memory you have more 50% (2G)
is a lot a lot a lot....

this is what you told me to do:
root@server [~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 7811
Server version: 10.3.24-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)]> show variables like 'buffer%';
Empty set (0.001 sec)

MariaDB [(none)]> show variables like '%buffer%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| aria_pagecache_buffer_size | 134217728 |
| aria_sort_buffer_size | 268434432 |
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 536870912 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 524288 |
| join_buffer_space_limit | 2097152 |
| key_buffer_size | 33554432 |
| mrr_buffer_size | 262144 |
| myisam_sort_buffer_size | 16777216 |
| net_buffer_length | 8192 |
| preload_buffer_size | 32768 |
| read_buffer_size | 262144 |
| read_rnd_buffer_size | 524288 |
| sort_buffer_size | 524288 |
| sql_buffer_result | OFF |
+-------------------------------------+----------------+
28 rows in set (0.001 sec)

MariaDB [(none)]>

____________________________________________________________________________________________________________________

Again the Tweak setting on default (everything is ON) from the minute the server is created...
so again... is impossible to get overload when c-panel managing everything... and still you get overload...
we have a problem here 100%

even if you look on your configuration they are not so different then main... is because in the TWAEK PANEL > SQL everything is on...

look almost the same configuration you and me.

and still... my server falling every 3 days and i am sure yours not...
think about that

BY THE WAY...
i did couple of tests and the only way bring down the potential memory from 688% something...
to 90%

is to max_allowed_packet = 64 like i told you before...
if you have some command line we can see some data about that i will send you as well.

Thank you again for your help! i appreciate this.

 

[nadav123]

Hey, whatsup? someone?

So... i try to do a test with you guys... i take a screenshot of the tuner right now...
the tuner is 400% 600% in the WHM > SERVICE STATUS is 32%

so i take a screenshot of the tuner... let's say the tuner data is not true...
so we will wait to see if we get shot down or error.... the database only uptime of 5h and already 400% so i belive tomorrow we have a shutdown.

then we have another indication or speculation what is can be... like i said when i changed the
max_allowed_packet = 64 as i told you before... the potential memory go down in 500%...
is not ok when one option control all the memory... 500% for max allowed packet? from 256 mg (c-panel tweak default) to 64 mg?
is impossible.....!
please try to get it... max allowed is a very important value, and he has a heavy factor on the database...
but 500% different? on 200mg?
is not make sane cPanelLauran
if you have some command line we can see some data about that i will send you as well.

Please help me here,
Regards
Nadav Levi Yahel