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
Thank you.
Yes i did.
i run that command after change common_site_config.json
1 Like
My reply is beeing hold and waiting for approval? why
Hi., @flexy2ky
I ended up upgrading the RAM to 32GB.
ha ha ha
Thank you for the advice.
After that, the server performance is quite good.
I also checked the maridb slow log again, which apparently hasn’t been active -_-. and found several queries there and there were also queries that I thought were unusual.
Here’s the query :
UPDATE `tabSales Invoice`
SET `owner`='sintaishak30@gmail.com', `creation`='2024-03-09 09:41:00.819040', `modified`='2024-03-09 09:41:48.667335', `modified_by`='sintaishak30@gmail.com', `docstatus`='0', `idx`=0, `title`='ASNA HASAN ', `naming_series`='NONE-.MM.YY.', `customer`='7501104406840000', `customer_name`='ASNA HASAN ', `total_nomor_undian`=NULL, `custom_undian_gembira`=NULL, `custom_kode_voucher`=NULL, `last_number`=NULL, `custom_cashback`=0.0e0, `tax_id`=NULL, `company`='PT Sejahterah', `company_tax_id`=NULL, `posting_date`='2024-03-09', `posting_time`='09:41:50.790674', `set_posting_time`=0, `due_date`='2024-03-09', `is_pos`=1, `pos_profile`='GHTM - KASIR 2', `posa_pos_opening_shift`='GHTM - KASIR 2-090324-sintaishak30@gmail.com-005607', `posa_is_printed`=1, `is_consolidated`=0, `is_return`=0, `return_against`=NULL, `update_billed_amount_in_sales_order`=0, `update_billed_amount_in_delivery_note`=1, `is_debit_note`=0, `amended_from`=NULL, `cost_center`='Toko GHTM - PTGGS', `project`=NULL, `currency`='IDR', `conversion_rate`=1.0e0, `selling_price_list`='Langganan Gorontalo', `price_list_currency`='IDR', `plc_conversion_rate`=1.0e0, `ignore_pricing_rule`=0, `scan_barcode`=NULL, `update_stock`=1, `set_warehouse`=NULL, `set_target_warehouse`=NULL, `total_qty`=7.0e0, `total_net_weight`=0.0e0, `base_total`=40500.0e0, `base_net_total`=36486.49e0, `total`=40500.0e0, `net_total`=36486.49e0, `tax_category`='PPN OUT', `taxes_and_charges`='GHTM TAX - PTGGS', `posa_delivery_charges`=NULL, `posa_delivery_charges_rate`=0.0e0, `shipping_rule`=NULL, `incoterm`=NULL, `named_place`=NULL, `base_total_taxes_and_charges`=4013.51e0, `total_taxes_and_charges`=4013.51e0, `base_grand_total`=40500.0e0, `base_rounding_adjustment`=0.0e0, `base_rounded_total`=40500.0e0, `base_in_words`='IDR Forty Thousand, Five Hundred only.', `grand_total`=40500.0e0, `rounding_adjustment`=0.0e0, `use_company_roundoff_cost_center`=0, `rounded_total`=40500.0e0, `in_words`='IDR Forty Thousand, Five Hundred only.', `total_advance`=0.0e0, `outstanding_amount`=0.0e0, `disable_rounded_total`=0, `apply_discount_on`='Grand Total', `base_discount_amount`=0.0e0, `is_cash_or_non_trade_discount`=0, `additional_discount_account`=NULL, `additional_discount_percentage`=0.0e0, `discount_amount`=0.0e0, `other_charges_calculation`='<div class=\"tax-break-up\" style=\"overflow-x: auto;\">\n <table class=\"table table-bordered table-hover\">\n <thead>\n <tr>\n \n \n <th class=\"text-left\">Item</th>\n \n \n \n <th class=\"text-right\">Taxable Amount</th>\n \n \n \n <th class=\"text-right\">PPN OUT - GHTM</th>\n \n \n </tr>\n </thead>\n <tbody>\n \n <tr>\n <td>80230241</td>\n <td class=\"text-right\">\n \n 18.918,92\n \n </td>\n \n \n \n <td class=\"text-right\">\n \n (11.0%)\n \n \n 2.081,08\n \n </td>\n \n \n </tr>\n \n <tr>\n <td>10020264</td>\n <td class=\"text-right\">\n \n 0,00\n \n </td>\n \n \n \n <td class=\"text-right\">\n \n (11.0%)\n \n \n 0,00\n \n </td>\n \n \n </tr>\n \n <tr>\n <td>80230125</td>\n <td class=\"text-right\">\n \n 17.567,57\n \n </td>\n \n \n \n <td class=\"text-right\">\n \n (11.0%)\n \n \n 1.932,43\n \n </td>\n \n \n </tr>\n \n </tbody>\n </table>\n</div>', `total_billing_hours`=0.0e0, `total_billing_amount`=0.0e0, `cash_bank_account`=NULL, `base_paid_amount`=40500.0e0, `paid_amount`=40500.0e0, `base_change_amount`=0.0e0, `change_amount`=0.0e0, `account_for_change_amount`='1111010.1310 - KAS BESAR - GHTM - PTGGS', `allocate_advances_automatically`=0, `only_include_allocated_payments`=0, `write_off_amount`=0.0e0, `base_write_off_amount`=0.0e0, `write_off_outstanding_amount_automatically`=0, `write_off_account`='4120000.1310 - RETUR PENJUALAN - GHTM - PTGGS', `write_off_cost_center`='Toko GHTM - PTGGS', `redeem_loyalty_points`=0, `loyalty_points`=0, `loyalty_amount`=0.0e0, `loyalty_program`=NULL, `loyalty_redemption_account`=NULL, `loyalty_redemption_cost_center`=NULL, `customer_address`=NULL, `address_display`=NULL, `custom_no_ktp`='7501104406840000', `contact_person`='7501104406840000-7501104406840000', `contact_display`='7501104406840000', `contact_mobile`='82189965077', `contact_email`='adminpembelian@gmail.com', `territory`='GHW', `shipping_address_name`=NULL, `shipping_address`=NULL, `dispatch_address_name`=NULL, `dispatch_address`=NULL, `company_address`=NULL, `company_address_display`=NULL, `ignore_default_payment_terms_template`=0, `payment_terms_template`='', `tc_name`='', `terms`=NULL, `po_no`='', `po_date`=NULL, `debit_to`='1131000.1101 - PIUTANG DAGANG - HO - PTGGS', `party_account_currency`='IDR', `is_opening`='No', `unrealized_profit_loss_account`=NULL, `against_income_account`='4110000.1310 - Penjualan Barang Dagang - GHTM - PTGGS', `posa_notes`=NULL, `posa_delivery_date`=NULL, `sales_partner`=NULL, `amount_eligible_for_commission`=36486.49e0, `commission_rate`=0.0e0, `total_commission`=0.0e0, `letter_head`='', `group_same_items`=0, `select_print_heading`='', `language`='en', `from_date`=NULL, `auto_repeat`=NULL, `to_date`=NULL, `status`='Draft', `inter_company_invoice_reference`=NULL, `campaign`=NULL, `represents_company`=NULL, `source`=NULL, `customer_group`='Langganan Gorontalo', `is_internal_customer`=0, `is_discounted`=0, `remarks`='No Remarks', `repost_required`=0 WHERE `name`='INVGHTM-0324-07666';
SELECT `tabStock Ledger Entry`.`item_code`,`tabStock Ledger Entry`.`warehouse`,`tabStock Ledger Entry`.`posting_date`,`tabStock Ledger Entry`.`actual_qty`,`tabStock Ledger Entry`.`valuation_rate`,`tabStock Ledger Entry`.`company`,`tabStock Ledger Entry`.`voucher_type`,`tabStock Ledger Entry`.`qty_after_transaction`,`tabStock Ledger Entry`.`stock_value_difference`,`tabStock Ledger Entry`.`item_code` `name`,`tabStock Ledger Entry`.`voucher_no`,`tabStock Ledger Entry`.`stock_value`,`tabStock Ledger Entry`.`batch_no`,`tabStock Ledger Entry`.`serial_no`,`tabItem`.`item_group`,`tabItem`.`stock_uom`,`tabItem`.`item_name` FROM `tabStock Ledger Entry` JOIN `tabItem` ON `tabStock Ledger Entry`.`item_code`=`tabItem`.`name` WHERE `tabStock Ledger Entry`.`docstatus`<2 AND `tabStock Ledger Entry`.`is_cancelled`=0 AND `tabStock Ledger Entry`.`posting_date`<='2024-03-08' AND `tabStock Ledger Entry`.`company`='PTSejahterah' ORDER BY TIMESTAMP(`tabStock Ledger Entry`.`posting_date`,`tabStock Ledger Entry`.`posting_time`),`tabStock Ledger Entry`.`creation`,`tabStock Ledger Entry`.`actual_qty`;
select
name as gl_entry, posting_date, account, party_type, party,
voucher_type, voucher_no,
cost_center, project,
against_voucher_type, against_voucher, account_currency,
against, is_opening, creation , debit, credit, debit_in_account_currency,
credit_in_account_currency
from `tabGL Entry`
where company='PT Sejahterah' and (posting_date >='2024-01-01' or is_opening = 'Yes') and (posting_date <='2024-03-09' or is_opening = 'Yes') and (finance_book in (NULL, '') OR finance_book IS NULL) and is_cancelled = 0
order by posting_date, creation;
select `tabSales Invoice`.`name`, `tabSales Invoice`.`owner`, `tabSales Invoice`.`creation`, `tabSales Invoice`.`modified`, `tabSales Invoice`.`modified_by`, `tabSales Invoice`.`_user_tags`, `tabSales Invoice`.`_comments`, `tabSales Invoice`.`_assign`, `tabSales Invoice`.`_liked_by`, `tabSales Invoice`.`docstatus`, `tabSales Invoice`.`idx`, `tabSales Invoice`.`title`, `tabSales Invoice`.`total`, `tabSales Invoice`.`net_total`, `tabSales Invoice`.`posa_delivery_charges_rate`, `tabSales Invoice`.`total_taxes_and_charges`, `tabSales Invoice`.`grand_total`, `tabSales Invoice`.`rounding_adjustment`, `tabSales Invoice`.`rounded_total`, `tabSales Invoice`.`total_advance`, `tabSales Invoice`.`outstanding_amount`, `tabSales Invoice`.`discount_amount`, `tabSales Invoice`.`total_billing_amount`, `tabSales Invoice`.`paid_amount`, `tabSales Invoice`.`change_amount`, `tabSales Invoice`.`write_off_amount`, `tabSales Invoice`.`status`, `tabSales Invoice`.`customer`, `tabSales Invoice`.`customer_name`, `tabSales Invoice`.`base_grand_total`, `tabSales Invoice`.`due_date`, `tabSales Invoice`.`company`, `tabSales Invoice`.`currency`, `tabSales Invoice`.`is_return`, `tabSales Invoice`.`_seen`, `tabSales Invoice`.`party_account_currency`
from `tabSales Invoice`
where `tabSales Invoice`.`pos_profile` like '%%gra%%' and `tabSales Invoice`.`name` like '%%08365%%' and `tabSales Invoice`.`title` like '%%lemon tt %%'
group by `tabSales Invoice`.`name`
order by `tabSales Invoice`.`modified` asc
limit 20 offset 0;
When these queries run, it makes the server quite slow