4

We see in newrelic some peaks if orders are inserted into sales_flat_order_grid. I tried to figure out why, but couldn't find the reason. This peaks appear ~ 1 of 100 orders.

enter image description here

First of all I thought about a database lock, but I couldn't find any hints in the logs.
My second thought was that the database has a high load, but the CPU utilization, connections and write/read latencies are pretty low.

Maybe someone had the same problem and found a solution.

The query looks like this:

INSERT INTO `sales_flat_order_grid` (`entity_id`, `status`, `store_id`, `customer_id`, `base_grand_total`, `base_total_paid`, `grand_total`, `total_paid`, `increment_id`, `base_currency_code`, `order_currency_code`, `store_name`, `created_at`, `updated_at`, `billing_name`, `shipping_name`) 
SELECT 
    `main_table`.`entity_id`, 
    `main_table`.`status`, 
    `main_table`.`store_id`, 
    `main_table`.`customer_id`, 
    `main_table`.`base_grand_total`, 
    `main_table`.`base_total_paid`, 
    `main_table`.`grand_total`, 
    `main_table`.`total_paid`, 
    `main_table`.`increment_id`, 
    `main_table`.`base_currency_code`, 
    `main_table`.`order_currency_code`, 
    `main_table`.`store_name`, 
    `main_table`.`created_at`, 
    `main_table`.`updated_at`,
    CONCAT(IFNULL(table_billing_name.firstname, ?), ?, IFNULL(table_billing_name.middlename, ?), ?, IFNULL(table_billing_name.lastname, ?)) AS `billing_name`, 
    CONCAT(IFNULL(table_shipping_name.firstname, ?), ?, IFNULL(table_shipping_name.middlename, ?), ?, IFNULL(table_shipping_name.lastname, ?)) AS `shipping_name` 
FROM `sales_flat_order` AS `main_table`
 LEFT JOIN `sales_flat_order_payment` AS `table_payment_method` ON `main_table`.`entity_id`=`table_payment_method`.`parent_id`
 LEFT JOIN `sales_flat_order_address` AS `table_billing_name` ON `main_table`.`billing_address_id`=`table_billing_name`.`entity_id`
 LEFT JOIN `sales_flat_order_address` AS `table_shipping_name` ON `main_table`.`shipping_address_id`=`table_shipping_name`.`entity_id`
WHERE (main_table.entity_id IN(?)) ON DUPLICATE KEY UPDATE `entity_id` = VALUES(`entity_id`), 
    `status` = VALUES(`status`), 
    `store_id` = VALUES(`store_id`), 
    `customer_id` = VALUES(`customer_id`), 
    `base_grand_total` = VALUES(`base_grand_total`),
    `base_total_paid` = VALUES(`base_total_paid`), 
    `grand_total` = VALUES(`grand_total`), 
    `total_paid` = VALUES(`total_paid`), 
    `increment_id` = VALUES(`increment_id`), 
    `base_currency_code` = VALUES(`base_currency_code`), 
    `order_currency_code` = VALUES(`order_currency_code`), 
    `store_name` = VALUES(`store_name`), 
    `created_at` = VALUES(`created_at`), 
    `updated_at` = VALUES(`updated_at`), 
    `billing_name` = VALUES(`billing_name`), 
    `shipping_name` = VALUES(`shipping_name`)

PHP Version: 5.6
Mysql: 5.6.27

  • 1
    Did you ever solve this problem? I am also experiencing what I believe is the same issue. Sorry, I couldn't add a comment due to low rep so had to post this as an answer. Thank you – user2314311 Apr 3 '18 at 12:01
  • no, I was never able to solve this – Pawel Apr 5 '18 at 9:13
0

I have perhaps found the source of the problem on our system. I have changed the index management so that it is triggered by cron job once a day (except for price and stock status) . It was previously set to update on save which caused the checkout to cause reindexing to be carried out and slowed the process right down. Fingers crossed this problem now seems to have vanished.

  • which index should cause this issue? catalog_product_flat? I can't really imagine which index affect a order_grid insert query – Pawel Apr 6 '18 at 10:32
  • Please read here for more details : sonassi.com/help/magento-management/index-management – user2314311 Apr 7 '18 at 11:16
  • I'm familiar with the magento index. I know they have sometimes a impact on the frontend performance, but I don't see a correlation between the index and the sales_flat_order tables. – Pawel Apr 8 '18 at 16:14
  • Magento has observers at checkout which can trigger reindexing. Also if you happen to alter any stock etc within magento and save the changes, a reindexing will occur. (unless set to manual) This reindexing causes long query times and can prevent in extreme cases a database lock to occur which is how I happened to find what was the cause. My site takes 300to500 orders per day which meant that on occasions I had an error where the order did not succeed. This could happen between once to 10 times per day, and since altering the indexing to how I mentioned, no more slow down & errors at checkout – user2314311 Apr 9 '18 at 20:06

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.