Hi,
May we know why the max_allowed_packet is set to 1073741824? 268435456 is what I’m seeing as default in my Mariadb .
1073741824 is for 1GB max_allowed_packet
268435456 is for 256MB
If the max_allowed_packet was increased to deal with the error, and has not helped, then increasing wait_timeout could be tried.
Did the error start showing up after a change to the configuration or is this happening to a new installation? It may help to show the operating environment.
Its happening to a new installation
Review the installation instructions that were followed, that they are current, all steps were completed and the system requirements were met. Does the operating platform have adequate resources available?
@NCP
So sorry to have to tag you.
I often follow your advice in other posts.
Can you help provide a solution to this problem?
Hi @Gembira_IT_Tech
Try reinstalling MySQL maybe solve the problem 
sudo apt install mariadb-server mariadb-client
sudo mysql_secure_installation
"""
Enter current password for root: (Enter your SSH root user password)
Switch to unix_socket authentication [Y/n]: Y
Change the root password? [Y/n]: Y
It will ask you to set new MySQL root password at this step. This can be different from the SSH root user password.
Remove anonymous users? [Y/n] Y
Disallow root login remotely? [Y/n]: N
This is set as N because we might want to access the database from a remote server for using business analytics software like Metabase / PowerBI / Tableau, etc.
Remove test database and access to it? [Y/n]: Y
Reload privilege tables now? [Y/n]: Y
"""
sudo nano /etc/mysql/my.cnf
ADD
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
[mysql]
default-character-set = utf8mb4
sudo service mysql restart
Thank You!
Its on production server bro…
No other suggestion?
Remove the custom parameters set in my.cnf and use only the default
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
[mysql]
default-character-set = utf8mb4
and then restart
sudo service mysql restart
I find that sometimes these custom parameters break mariadb.
Thanks for suggestion,
i’ll try to comment # all custom parameters latter and set your suggestion on my.cnf.
latter after all the shop are closed.
Thanks
I just tried this.
I’m very sorry but this is slower than usual which is already slow.
Then turn on slow query log to check what processes could be slowing down the system.
I found something strange
common_site_config.json
bench doctor
htop
The number of workers in bench doctor with different common_site_config.json
is this normal?
Or maybe the number of workers causes a bottleneck?
already done this. but no log is created
As far as I remember, I once tried running the show processlist command when ERPnext was very slow.
there are lots of queries like this:
select *, timestamp(posting_date, posting_time) as “timestamp”
from tabStock Ledger Entry
where item_code = ‘0102010010009’
and warehouse = ‘Open End Blow Room Floor - IHL’
and is_cancelled = 0
and (
posting_date < ‘2023-12-31’ or
(
posting_date = ‘2023-12-31’ and
time_format(posting_time, ‘%H:%i:%s’) < time_format(‘20:41:23.753190’, ‘%H:%i:%s’)
)
)
order by timestamp(posting_date, posting_time) desc, creation desc
limit 1
ps: this is not the actual query data, but the model is exactly the same as what happens on my server
The number of background workers set is proportional to the number of cpu cores you have active from what i understand. However, this can negatively impact multiple background workers are working simultaneously but not enough RAM to handle the load… this is where i believe mariaDB suffers. So you have two options here:
1: Reduce the number of workers to maybe 4 or 5 to limit the number of concurrent tasks being run.
2: Increase your RAM to compensate for the workload mariaDB is forced to handle synchroneously.
These are suggested actions so they may not ultimately solve the problem. But reducing the workload by reducing the amount of active workers might help, although in a high volume production environment, this also has some negative impact so you may want to opt for increasing RAM, or both.
Okay, thank you.
So what should be reduced are Gunicorn Workers or Background workers?
Both should be reduced to 5 each. You can scale up gradually and add one each after reviewing performance on current number.
Done
I’ve taken both of them down.
The number of workers visible on htop also seems to be correct.
Hopefully this can help ease mariadb’s work
Thank you very much for your help.
Can I tell you again about what happened after this update?
Yes you can. I hope you ran the necessary commands to apply the change?
bench setup supervisor
sudo supervisorctl update
sudo supervisorctl reread
sudo supervisorctl reload