MYSQL optimization for Magento 2

Im running my magento 2 project on 3 core 8gb ram VM. I did some benchmarks and i see that my DB queries take quite some time to execute. Traffic on this site is almost zero. Mysql is running on 5.7.31-34 Percona Server. Database size 1.7gb. Can anyone spot some obvious mistakes and give me some suggestions ?

2947 queries in 1,057.20ms (average time: 0.36ms) - 2788 queries/second 2912 SELECT - 0 INSERT - 0 UPDATE - 0 DELETE - 0 TRANSACTION   2618 queries in 1,132.66ms (average time: 0.43ms) - 2311 queries/second 2593 SELECT - 0 INSERT - 0 UPDATE - 0 DELETE - 0 TRANSACTION  2915 queries in 1,213.47ms (average time: 0.42ms) - 2402 queries/second 2878 SELECT - 0 INSERT - 0 UPDATE - 0 DELETE - 0 TRANSACTION   620 queries in 235.52ms (average time: 0.38ms) - 2633 queries/second 610 SELECT - 0 INSERT - 0 UPDATE - 0 DELETE - 0 TRANSACTION  3214 queries in 1,930.16ms (average time: 0.60ms) - 1665 queries/second 3179 SELECT - 0 INSERT - 0 UPDATE - 0 DELETE - 0 TRANSACTION 

At moment my.cnf is …

[mysqld] user   = mysql pid-file = /var/run/mysqld/mysqld.pid socket   = /var/run/mysqld/mysqld.sock port   = 3306 basedir    = /usr datadir    = /var/lib/mysql tmpdir   = /tmp lc-messages-dir  = /usr/share/mysql explicit_defaults_for_timestamp   ### MyISAM # key_buffer_size = 16M myisam-recover-options = FORCE,BACKUP  ### SAFETY # innodb = force max_allowed_packet = 250M max_connect_errors = 100000 bind-address = 127.0.0.1 skip-name-resolve  ### LANGUAGE # #init_connect='SET collation_connection = utf8_unicode_ci' #init_connect='SET NAMES utf8' #character-set-server=utf8 #collation-server=utf8_unicode_ci #skip-character-set-client-handshake  ### CACHES AND LIMITS # back_log = 20 interactive_timeout = 7200 wait_timeout = 7200 net_read_timeout = 120 net_write_timeout = 300 sort_buffer_size = 8M read_buffer_size = 8M  read_rnd_buffer_size = 2M join_buffer_size = 16M tmp_table_size = 512M  max_heap_table_size = 512M query_cache_type = 1 query_cache_size = 128M max_connections = 60 thread_cache_size = 32 thread_pool_size = 16 open_files_limit = 65535 table_definition_cache = 8000 table_open_cache = 10000  ### INNODB_ # 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_log_file_size = 512M innodb_log_buffer_size = 64M innodb_file_per_table = 1 innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_buffer_pool_instances = 2 innodb_buffer_pool_size = 2000M  

Magento 2 help validate function to count bundle product child items of an order

I’m trying to write a function that takes an order object, and returns an array of sku => qty (key-value) showing how many of each sku was sold/shipped.

The tricky part is, magento formats line items differently on configurable and bundle products.

Example:

Line1: name shirt-X2, type bundle, ordered 2, shipped 2. Line2: name shirt, type simple, ordered 4, shipped 0. // <-- my problem 

I’ve written this code:

/**  * @param \Magento\Sales\Model\Order $  order  * @return array  */ public function getOrderData($  order) {     $  result = [];     foreach ($  order->getAllItems() as $  item) {         if ($  item->getProductType() != 'simple') {             continue;         }         $  sku = $  item->getSku();         $  orderedReal = $  item->getQtyOrdered();         $  orderedB = $  item->getQtyOrdered() - $  item->getQtyRefunded() - $  item->getQtyCanceled();         $  orderedB = max(round($  orderedB, 8), 0);         $  invoicedB = $  item->getQtyOrdered() - $  item->getQtyInvoiced() - $  item->getQtyCanceled();         $  invoicedB = max(round($  invoicedB, 8), 0);         $  shippedReal = 0;         $  isShipped = false;         if ($  item->getParentItem()) {             $  parent = $  item->getParentItem();             if ($  parent->getQtyOrdered() > 0 && $  parent->getQtyOrdered() == $  parent->getQtyShipped()) {                 $  isShipped = true;             }         } else {             if ($  item->getQtyOrdered() > 0 && $  item->getQtyOrdered() == $  item->getQtyShipped()) {                 $  isShipped = true;             }         }         if ($  isShipped) {             $  shippedReal = $  orderedReal;         }         $  result[] = [             'sku' => $  sku,             'invoiced' => $  invoicedB,             'ordered' => $  orderedB,             'shipped' => $  shippedReal         ];     }     return $  result; } 

Is this function valid, or could it be improved ? Any help is appreciated.

Thanks

Magento 2 help validate function to count bundle product child items of an order

I’m trying to write a function that takes an order object, and returns an array of sku => qty (key-value) showing how many of each sku was sold/shipped.

The tricky part is, magento formats line items differently on configurable and bundle products.

Example:

Line1: name shirt-X2, type bundle, ordered 2, shipped 2. Line2: name shirt, type simple, ordered 4, shipped 0. // <-- my problem 

I’ve written this code:

/**  * @param \Magento\Sales\Model\Order $  order  * @return array  */ public function getOrderData($  order) {     $  result = [];     foreach ($  order->getAllItems() as $  item) {         if ($  item->getProductType() != 'simple') {             continue;         }         $  sku = $  item->getSku();         $  orderedReal = $  item->getQtyOrdered();         $  orderedB = $  item->getQtyOrdered() - $  item->getQtyRefunded() - $  item->getQtyCanceled();         $  orderedB = max(round($  orderedB, 8), 0);         $  invoicedB = $  item->getQtyOrdered() - $  item->getQtyInvoiced() - $  item->getQtyCanceled();         $  invoicedB = max(round($  invoicedB, 8), 0);         $  shippedReal = 0;         $  isShipped = false;         if ($  item->getParentItem()) {             $  parent = $  item->getParentItem();             if ($  parent->getQtyOrdered() > 0 && $  parent->getQtyOrdered() == $  parent->getQtyShipped()) {                 $  isShipped = true;             }         } else {             if ($  item->getQtyOrdered() > 0 && $  item->getQtyOrdered() == $  item->getQtyShipped()) {                 $  isShipped = true;             }         }         if ($  isShipped) {             $  shippedReal = $  orderedReal;         }         $  result[] = [             'sku' => $  sku,             'invoiced' => $  invoicedB,             'ordered' => $  orderedB,             'shipped' => $  shippedReal         ];     }     return $  result; } 

Is this function valid, or could it be improved ? Any help is appreciated.

Thanks