CPU overloaded on 6 cores 6GB RAM Cloud server

[leione]

[Note: remember to include site URL, ZC version, list of plugins, PHP version, etc ... read the Posting Tips shown above for information to include in your post here. And, remove this comment before actually posting!]

Hello there,

We recently experience several 503 errors, due to CPU overloaded. actually our website only have several hundreds visitors a day. For most of time we just use a small percentage of server resource, as it on a 6 CPU cores and 6GB Ram Cloud server.

The server provider give us some information about the slow database queries, I am wondering could anyone tell what's cause the slow query please? we are not sure if it due to bots crawl cause too much traffic or any issues of the plugin we use.

Thanks

PHP Version: 5.6.38 (Zend: 2.6.0) PHP Memory Limit: 512M
Database: MySQL 5.6.36-82.1-log
Plugins:

Dynamic filter
Lightbox
wish list
ajax add to cart
cross sell advanced
quick updates ( never use)
Tabbed products pro
ultimate urls
Numinix product fields

information about slow database queries:

1. Executed 2h 33m 11s ago for 28.721817 sec on Database --> Unknown (NOT FOUND / DELETED)
Date: 2018-09-29 04:28:57 Query_time: 28.721817 Rows_examined: 486735: Rows_sent 0 Lock_time: 0.000209
# Schema: xxx Last_errno: 0 Killed: 0 select distinct p.products_id FROM products p LEFT JOIN manufacturers m USING(manufacturers_id) LEFT JOIN products_description pd on p.products_id = pd.products_id JOIN products_to_categories p2c on p.products_id = p2c.products_id JOIN categories c on p2c.categories_id = c.categories_id LEFT JOIN meta_tags_products_description mtpd ON mtpd.products_id= p2c.products_id AND mtpd.language_id = 1 JOIN products_attributes p2a on p.products_id = p2a.products_id JOIN products_options po on p2a.options_id = po.products_options_id JOIN products_options_values pov on p2a.options_values_id = pov.products_options_values_id WHERE (p.products_status = 1 AND pd.language_id = 1 AND ((pd.products_name LIKE '%171p%' OR p.products_model LIKE '%171p%' OR m.manufacturers_name LIKE '%171p%' OR (mtpd.metatags_keywords LIKE '%171p%' AND mtpd.metatags_keywords !='') OR (mtpd.metatags_description LIKE '%171p%' AND mtpd.metatags_description !='') OR pd.products_description LIKE '%171p%') )) GROUP BY p.products_id HAVING ( FIND_IN_SET("price34.5--50", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name))));
------------------------------------------------------------------------------------------------------
2. Executed 11h 10m 50s ago for 23.808308 sec on Database --> Unknown (NOT FOUND / DELETED)
Date: 2018-09-28 19:51:18 Query_time: 23.808308 Rows_examined: 486196: Rows_sent 3 Lock_time: 0.000359
# Schema:xxx Last_errno: 0 Killed: 0 select distinct p.products_id FROM products p LEFT JOIN manufacturers m USING(manufacturers_id) LEFT JOIN products_description pd on p.products_id = pd.products_id JOIN products_to_categories p2c on p.products_id = p2c.products_id JOIN categories c on p2c.categories_id = c.categories_id LEFT JOIN meta_tags_products_description mtpd ON mtpd.products_id= p2c.products_id AND mtpd.language_id = 1 JOIN products_attributes p2a on p.products_id = p2a.products_id JOIN products_options po on p2a.options_id = po.products_options_id JOIN products_options_values pov on p2a.options_values_id = pov.products_options_values_id WHERE (p.products_status = 1 AND pd.language_id = 1 AND ((pd.products_name LIKE '%305p%' OR p.products_model LIKE '%305p%' OR m.manufacturers_name LIKE '%305p%' OR (mtpd.metatags_keywords LIKE '%305p%' AND mtpd.metatags_keywords !='') OR (mtpd.metatags_description LIKE '%305p%' AND mtpd.metatags_description !='') OR pd.products_description LIKE '%305p%') )) GROUP BY p.products_id HAVING ( FIND_IN_SET("colorsilver", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name))));
------------------------------------------------------------------------------------------------------
3. Executed 5h 53m 16s ago for 19.087285 sec on Database --> Unknown (NOT FOUND / DELETED)
Date: 2018-09-29 01:08:52 Query_time: 19.087285 Rows_examined: 39516: Rows_sent 0 Lock_time: 0.000532
# Schema:xxx Last_errno: 0 Killed: 0 select distinct * FROM products p left join manufacturers m on (p.manufacturers_id = m.manufacturers_id) left join products_to_categories p2c on p.products_id = p2c.products_id left join products_description pd on p.products_id = pd.products_id join products_attributes p2a on p.products_id = p2a.products_id join products_options po on p2a.options_id = po.products_options_id join products_options_values pov on p2a.options_values_id = pov.products_options_values_id WHERE p.products_status = 1 AND pd.language_id = 1 and p.products_date_added >=20180830 GROUP BY p.products_id HAVING ( FIND_IN_SET("category57", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name))) or FIND_IN_SET("category173", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name)))) and ( FIND_IN_SET("colorred", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name))) or FIND_IN_SET("colorkhaki", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name))));
-----------------------------------------------------------------------------------------------------
4. Executed 5h 53m 18s ago for 18.415747 sec on Database --> Unknown (NOT FOUND / DELETED)
Date: 2018-09-29 01:08:50 Query_time: 18.415747 Rows_examined: 39516: Rows_sent 0 Lock_time: 0.000311
# Schema: xxx Last_errno: 0 Killed: 0 select distinct * FROM products p left join manufacturers m on (p.manufacturers_id = m.manufacturers_id) left join products_to_categories p2c on p.products_id = p2c.products_id left join products_description pd on p.products_id = pd.products_id join products_attributes p2a on p.products_id = p2a.products_id join products_options po on p2a.options_id = po.products_options_id join products_options_values pov on p2a.options_values_id = pov.products_options_values_id WHERE p.products_status = 1 AND pd.language_id = 1 and p.products_date_added >=20180830 GROUP BY p.products_id HAVING ( FIND_IN_SET("category153", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name))) or FIND_IN_SET("category173", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name)))) and ( FIND_IN_SET("colorred", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name))) or FIND_IN_SET("colorkhaki", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name)))) and ( FIND_IN_SET("sizes", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name))));
-----------------------------------------------------------------------------------------------------
5. Executed 5h 53m 16s ago for 18.363229 sec on Database --> Unknown (NOT FOUND / DELETED)
Date: 2018-09-29 01:08:52 Query_time: 18.363229 Rows_examined: 39516: Rows_sent 0 Lock_time: 0.000274
# Schema: xxx Last_errno: 0 Killed: 0 select distinct * FROM products p left join manufacturers m on (p.manufacturers_id = m.manufacturers_id) left join products_to_categories p2c on p.products_id = p2c.products_id left join products_description pd on p.products_id = pd.products_id join products_attributes p2a on p.products_id = p2a.products_id join products_options po on p2a.options_id = po.products_options_id join products_options_values pov on p2a.options_values_id = pov.products_options_values_id WHERE p.products_status = 1 AND pd.language_id = 1 and p.products_date_added >=20180830 GROUP BY p.products_id HAVING ( FIND_IN_SET("colorkhaki", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name))) or FIND_IN_SET("colorcamel", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name)))) and ( FIND_IN_SET("price105.01--135", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name)))) and ( FIND_IN_SET("category54", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name))) or FIND_IN_SET("category198", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name))));
-----------------------------------------------------------------------------------------------------
6. Executed 5h 53m 15s ago for 18.066896 sec on Database --> Unknown (NOT FOUND / DELETED)
Date: 2018-09-29 01:08:53 Query_time: 18.066896 Rows_examined: 39516: Rows_sent 0 Lock_time: 0.000433
# Schema: xxxxx Last_errno: 0 Killed: 0 select distinct * FROM products p left join manufacturers m on (p.manufacturers_id = m.manufacturers_id) left join products_to_categories p2c on p.products_id = p2c.products_id left join products_description pd on p.products_id = pd.products_id join products_attributes p2a on p.products_id = p2a.products_id join products_options po on p2a.options_id = po.products_options_id join products_options_values pov on p2a.options_values_id = pov.products_options_values_id WHERE p.products_status = 1 AND pd.language_id = 1 and p.products_date_added >=20180830 GROUP BY p.products_id HAVING ( FIND_IN_SET("category220", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name))) or FIND_IN_SET("category54", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name)))) and ( FIND_IN_SET("colorkhaki", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name))) or FIND_IN_SET("colorcamel", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name)))) and ( FIND_IN_SET("sizexxxl", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name))));
-----------------------------------------------------------------------------------------------------
7. Executed 5h 53m 15s ago for 17.760701 sec on Database --> Unknown (NOT FOUND / DELETED)
Date: 2018-09-29 01:08:53 Query_time: 17.760701 Rows_examined: 39516: Rows_sent 0 Lock_time: 0.000314
# Schema: xxxx Last_errno: 0 Killed: 0 select distinct * FROM products p left join manufacturers m on (p.manufacturers_id = m.manufacturers_id) left join products_to_categories p2c on p.products_id = p2c.products_id left join products_description pd on p.products_id = pd.products_id join products_attributes p2a on p.products_id = p2a.products_id join products_options po on p2a.options_id = po.products_options_id join products_options_values pov on p2a.options_values_id = pov.products_options_values_id WHERE p.products_status = 1 AND pd.language_id = 1 and p.products_date_added >=20180830 GROUP BY p.products_id HAVING ( FIND_IN_SET("category261", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name))) or FIND_IN_SET("category54", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name)))) and ( FIND_IN_SET("price107.01--130", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name)))) and ( FIND_IN_SET("coloryellow", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name))));
-----------------------------------------------------------------------------------------------------
8. Executed 5h 53m 15s ago for 17.640464 sec on Database --> Unknown (NOT FOUND / DELETED)
Date: 2018-09-29 01:08:53 Query_time: 17.640464 Rows_examined: 40786: Rows_sent 0 Lock_time: 0.000307
# Schema: xxx Last_errno: 0 Killed: 0 SELECT p.products_id, p.products_price_sorter, p.master_categories_id, p.manufacturers_id FROM products p left join manufacturers m on (p.manufacturers_id = m.manufacturers_id) left join products_to_categories p2c on p.products_id = p2c.products_id left join products_description pd on p.products_id = pd.products_id join products_attributes p2a on p.products_id = p2a.products_id join products_options po on p2a.options_id = po.products_options_id join products_options_values pov on p2a.options_values_id = pov.products_options_values_id WHERE p.products_status = 1 AND pd.language_id = 1 and p.products_date_added >=20180830 GROUP BY p.products_id HAVING ( FIND_IN_SET("category153", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name))) or FIND_IN_SET("category173", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name)))) and ( FIND_IN_SET("colorsilver", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name))) or FIND_IN_SET("colorkhaki", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name)))) order by p.products_date_added DESC, pd.products_name;
-----------------------------------------------------------------------------------------------------
9. Executed 5h 53m 16s ago for 17.618503 sec on Database --> Unknown (NOT FOUND / DELETED)
Date: 2018-09-29 01:08:52 Query_time: 17.618503 Rows_examined: 39516: Rows_sent 0 Lock_time: 0.000336
# Schema: xxx Last_errno: 0 Killed: 0 select distinct * FROM products p left join manufacturers m on (p.manufacturers_id = m.manufacturers_id) left join products_to_categories p2c on p.products_id = p2c.products_id left join products_description pd on p.products_id = pd.products_id join products_attributes p2a on p.products_id = p2a.products_id join products_options po on p2a.options_id = po.products_options_id join products_options_values pov on p2a.options_values_id = pov.products_options_values_id WHERE p.products_status = 1 AND pd.language_id = 1 and p.products_date_added >=20180830 GROUP BY p.products_id HAVING ( FIND_IN_SET("category78", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name))) or FIND_IN_SET("category54", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name)))) and ( FIND_IN_SET("colorcamel", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name))));
-----------------------------------------------------------------------------------------------------
10. Executed 5h 53m 18s ago for 17.588716 sec on Database --> Unknown (NOT FOUND / DELETED)
Date: 2018-09-29 01:08:50 Query_time: 17.588716 Rows_examined: 39516: Rows_sent 0 Lock_time: 0.000210
# Schema: xxx Last_errno: 0 Killed: 0 select distinct * FROM products p left join manufacturers m on (p.manufacturers_id = m.manufacturers_id) left join products_to_categories p2c on p.products_id = p2c.products_id left join products_description pd on p.products_id = pd.products_id join products_attributes p2a on p.products_id = p2a.products_id join products_options po on p2a.options_id = po.products_options_id join products_options_values pov on p2a.options_values_id = pov.products_options_values_id WHERE p.products_status = 1 AND pd.language_id = 1 and p.products_date_added >=20180830 GROUP BY p.products_id HAVING ( FIND_IN_SET("sizexxxxl", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name)))) and ( FIND_IN_SET("category54", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name))));

the number of input/output operations when 503 errors happened yesterday.

date container cpu io_ops_read io_ops_write procs
2018-09-28 01:24:44.309989-05 c15162 5.67 2453 1561 225
2018-09-28 01:23:44.913046-05 c15162 13.13 226 2125 225
2018-09-28 01:22:45.515004-05 c15162 8.26 341 1542 226
2018-09-28 01:21:46.704627-05 c15162 3.59 57 898 220
2018-09-28 01:20:47.2793-05 c15162 8.22 502 2619 220
2018-09-28 01:13:43.839196-05 c15162 19.16 130 2333 222
2018-09-28 01:12:44.509786-05 c15162 14.73 182 1619 230
2018-09-28 01:11:45.062465-05 c15162 12.08 135 1465 220
2018-09-28 01:10:45.717254-05 c15162 12.51 550 1713 223
2018-09-28 01:09:46.453257-05 c15162 19.79 2428 1673 220
2018-09-28 01:08:46.633489-05 c15162 44.51 29485 1462 221
2018-09-28 01:07:46.990384-05 c15162 74.68 37541 2963 229
2018-09-28 01:06:46.994332-05 c15162 70.04 48300 2074 225
2018-09-28 01:05:46.692667-05 c15162 63.48 43805 1740 224
2018-09-28 01:04:46.813671-05 c15162 64.25 50687 2262 225
2018-09-28 01:03:46.932644-05 c15162 61.38 52930 2444 232
2018-09-28 01:02:47.564054-05 c15162 24.06 24233 2531 228
2018-09-28 01:01:48.253121-05 c15162 18.13 677 2874 224
2018-09-28 01:00:48.708495-05 c15162 24.10 1458 2095 232
2018-09-28 00:59:48.695276-05 c15162 27.99 294 2537 219
2018-09-28 00:58:43.554212-05 c15162 39.82 2606 3503 226
2018-09-28 00:57:44.46312-05 c15162 18.46 730 2881 224

[lat9]

Could you elaborate on the plugins and/or customizations you've got installed?

A standard Zen Cart search query does not include FIND_IN_SET clauses. You might try using your admin's Tools->Developers' Tool Kit to locate all occurrences of that string on the catalog/storefront files.

That modification appears (to me, anyway) as the source of your issue.

Update: Oops, those plugins were listed ...

[leione]

Hello lat9

Thank you very much for your reply.

I just find FIND_IN_SET inside the public_html/includes/modules/garbini/dynamic_filter.php

Actually we have errors logs regarding the dynamic filter after we upgrade to current version, the developer who help us with upgrade try to fixed it several times, but he didn't fix it properly, we still got the error log for dynamic filter, but when the cpu overloaded, there is no such error logged, we didn't consider it cause the issue.

Would you please take a look the code below for dynamic filter?

Thanks

 <?php

// Price Range/Attribute filter
  $manufacturerGroup = str_replace(' ', '', DYNAMIC_FILTER_MANUFACTURER_GROUP);
  $categoryGroup = str_replace(' ', '', DYNAMIC_FILTER_CATEGORY_GROUP);
  $priceGroup = str_replace(' ', '', DYNAMIC_FILTER_PRICE_GROUP);
  $prvKey = '';
  $prvHaving = '';
  $filter = '';
  $having = '';
  $filter_attr = false;

  reset($_GET);
  while(list($key, $value) = each ($_GET))
  {
    if(is_array($value)){
        if(substr($key,0,strlen(DYNAMIC_FILTER_PREFIX)) == DYNAMIC_FILTER_PREFIX && array_filter($value))
        {
          $key = str_replace(DYNAMIC_FILTER_PREFIX, '', $key);
          foreach ($value as $value)
          {

            if($key == $manufacturerGroup || $key == $categoryGroup || $key == $priceGroup)
            {
              if($key != $prvKey)
              {
                if($prvKey != '')
                {
                  $filter .= ') and (';
                } else {
                  $filter .= ' and (';
                }
              }
              else
              {
                $filter .= ' or ';
              }
            }
        // manufacturer
            if($key == $manufacturerGroup)
            {
              $filter .= "m.manufacturers_id = '" . (int)$value . "'";
              $prvKey = $key;
        // category
            }
            else if($key == $categoryGroup)
            {
              $filter .= "p2c.categories_id = '" . (int)$value . "'";
              $prvKey = $key;
        // price range
            }
            else if($key == $priceGroup)
            {
              list($low,$high) = explode("--",$value);
              $filter .= 'p.products_price_sorter>=' . $low . ' and ' . 'p.products_price_sorter<=' . $high;
              $prvKey = $key;
        // attributes
            }
            else
            {
              if($key != $prvHaving)
              {
                if($prvHaving != '')
                {
                  $having .= ') and (';
                } else {
                  $having .= 'HAVING (';
                }
              }
              else
              {
                $having .= ' or ';
              }

    // BOF fix to escape special characters in query by kevin_205 & design75
              $having .= ' FIND_IN_SET("' . $key . addslashes($value) . '", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name)))';
    // EOF fix
              $filter_attr = true;
              $prvHaving = $key;
            }
          }
        }
    }
  }
  if($filter != '') $filter .= ')';
  if($having != '') $having .= ')';
  if($filter_attr == true && defined('TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK')) $filter .= ' AND p2as.quantity > 0 AND FIND_IN_SET(p2a.products_attributes_id, p2as.stock_attributes)';
?>

The must common log we got are

PHP Warning: in_array() expects parameter 2 to be array, string given in /home/xxx/public_html/includes/templates/garbini/sideboxes/tpl_dynamic_filter.php on line 321

PHP Warning: array_filter() expects parameter 1 to be array, string given in /home/xxx/public_html/includes/templates/garbini/sideboxes/tpl_dynamic_filter.php on line 360

PHP Warning: array_filter() expects parameter 1 to be array, string given in /home/xxx/public_html/includes/templates/garbini/sideboxes/tpl_dynamic_filter.php on line 151

PHP Warning: in_array() expects parameter 2 to be array, string given in /home/xxx/public_html/includes/templates/garbini/sideboxes/tpl_dynamic_filter.php on line 156


I am not sure if these php warning related to such issue?

[lat9]

@leione, I'm not familiar with the plugin and suggest you post your question in the Dynamic Filter's support thread (https://www.zen-cart.com/showthread....Support-Thread).

[leione]

Thank you for your time lat9

[chadlly2003]

i had a similar problem. The issue is the dynamic filters. You need to make a few adjustments. I believe I posted a solution in the forum but if u can’t find it let me know and I’ll try and help. What is the url

[leione]

Hello chadlly2003,

Thank you very much for your reply.

Would you please try to find your post and paste here? I am afraid I could not find your post.

I have sent the url via pm.

Best Regards

[chadlly2003]

I responded to your message

[lankeeyankee]

Please post it here for anyone else that may have this issue in the future.

[leione]

Hello Iankeeyankee

Here is the link, perhaps because we have lots of attribute values, the solution not fix our issue.

anyway, thanks for your help chadlly2003

https://www.zen-cart.com/showthread....-Thread/page76