0

I have a webstore with over 400k products that were migrated from m1. Any product that is either saved or deleted takes approximately 25 - 30 seconds per product.

I have done every performance tuning on the server i can with mysql config, php config and the problem appears to be related with magento 2.3.3.

I have also disable any modules that arent in use - so i am at a loss on where to look or how to begin to figure this out.

my.cnf

[mysqld]
sql_mode=""
local-infile=0
max_connections = 600
max_user_connections=1000
key_buffer_size = 2048M
myisam_sort_buffer_size = 64M
read_buffer_size = 1M
table_open_cache = 5000
thread_cache_size = 384
wait_timeout = 120
connect_timeout = 60
tmp_table_size = 256M
max_heap_table_size = 128M
max_allowed_packet = 1024M
net_buffer_length = 16384
max_connect_errors = 100
concurrent_insert = 2
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 256M
query_cache_size = 512M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65535
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
max_write_lock_count = 8
external-locking=FALSE
open_files_limit=5000000
innodb_thread_concurrency = 0
innodb_lock_wait_timeout = 7200
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_log_files_in_group = 2
innodb_file_per_table = 1
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_buffer_pool_instances = 4
innodb_buffer_pool_size = 22G

What are the proper troubleshooting steps to take to pin down the bottle neck?

Any suggestions on Magento settings or tweaks to speed up the save or delete process?

Thanks!

SR

6
  • 1
    What hardware are you using? M1 can be run on 1G RAM, M2 is only possible to run on 6 thread 32GB RAM machine. Aug 14 at 23:44
  • Good point - 48GB RAM - 16 CORES E5-2640 CPU, Intel 400GB PCI SSD- has been independently tweaked by an expert and the problem they believe is M2. I had m1 on the same hardware and could delete or save products in a few seconds.
    – SR_Magento
    Aug 14 at 23:54
  • That should be more than enough. I run 2.3.3 and yes sometimes the save is slow. Are your indexers set to Update on Schedule? Update on Save sometimes makes slow saves. Aug 14 at 23:57
  • 1
    Add your my.cnf to the main post. Sometimes you can overdo an mysql optimization where it causes problems. Aug 14 at 23:59
  • I've tried both update on save and on schedule and havent noticed a difference. I will post my.cnf.
    – SR_Magento
    Aug 15 at 0:26
0
  1. Clean DB logs.
  2. Check on cl tables and triggers.
  3. Set Indexer on schedule.
  4. Make sure that DB table indexes are intact.
  5. Check by login all Mysql queries with PH stack to get idea if some sql query is consuming time.
  6. Use profiler and check

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.