Monitoring

Use TIMESTAMPDIFF to identify long running cronjobs in Magento2

Improving performance in cron jobs can be very time consuming. Even just figuring out where to start can take hours.

I came up with an idea that really helped me to locate potential performance killer just by checking the execution time by using TIMESTAMPDIFF. For example:

SELECT schedule_id, job_code, executed_at, finished_at, TIMESTAMPDIFF(SECOND, executed_at, finished_at) AS runtime
FROM cron_schedule
ORDER BY runtime desc
LIMIT 10;

SELECT schedule_id, job_code, executed_at, finished_at, TIMESTAMPDIFF(SECOND, executed_at, finished_at) AS runtime

FROM cron_schedule

ORDER BY runtime desc

LIMIT 10;

Output example:

+-------------+------------------------------------+---------------------+---------------------+---------+
| schedule_id | job_code                           | executed_at         | finished_at         | runtime |
+-------------+------------------------------------+---------------------+---------------------+---------+
|    25432321 | custom_customer_sync               | 2022-09-13 18:50:07 | 2022-09-13 18:57:28 |     456 |
|    25433099 | indexer_update_all_views           | 2022-09-13 19:05:07 | 2022-09-13 19:05:13 |       6 |
|    25432315 | indexer_update_all_views           | 2022-09-13 18:35:07 | 2022-09-13 18:35:11 |       4 |
|    25432645 | indexer_update_all_views           | 2022-09-13 18:43:06 | 2022-09-13 18:43:09 |       3 |
|    25433048 | indexer_update_all_views           | 2022-09-13 18:59:06 | 2022-09-13 18:59:09 |       3 |
...
+-------------+------------------------------------+---------------------+---------------------+---------+
20 rows in set (0.01 sec)

+-------------+------------------------------------+---------------------+---------------------+---------+

+-------------+------------------------------------+---------------------+---------------------+---------+

| 25432321 | custom_customer_sync | 2022-09-13 18:50:07 | 2022-09-13 18:57:28 | 456 |

| 25433099 | indexer_update_all_views | 2022-09-13 19:05:07 | 2022-09-13 19:05:13 | 6 |

| 25432315 | indexer_update_all_views | 2022-09-13 18:35:07 | 2022-09-13 18:35:11 | 4 |

| 25432645 | indexer_update_all_views | 2022-09-13 18:43:06 | 2022-09-13 18:43:09 | 3 |

| 25433048 | indexer_update_all_views | 2022-09-13 18:59:06 | 2022-09-13 18:59:09 | 3 |

...

+-------------+------------------------------------+---------------------+---------------------+---------+

20 rows in set (0.01 sec)

The runtime can be a sign of high memory or CPU usage, therefore, you should probably look at those cron jobs first.

Find Malware in Image Files

It’s a nightmare. Your production environment was compromised and you actually don’t know how and how much data was stolen. For sure there are different ways to be compromised. In this post I just want to explain, how hackers can get full access to your production environment by using image files.

As explained on Snapfast and Sucuri it is pretty easy to store any kind of PHP code in EXIF headers. Often times it only needs a simple script to create an administrator account. If you are using third-party extensions with an image upload function or if you are late with the last security update, there is a high risk to be compromised.

You can use commands like svn status or git status to find changes, but this is no guarantee to find malicious code, because oftentimes your /media/ or similar folders are not version controlled.

Here is one simple command how you can find infected image files.

$ cd /var/www/{{{MAGENTO}}}/
$ find . -iregex '.*\.\(jpg\|gif\)' -exec grep -o -P 'base64_decode' {} \;

1 2	$ cd /var/www/{{{MAGENTO}}}/ $ find . -iregex '.*\.$jpg\\|gif$' -exec grep -o -P 'base64_decode' {} \;

In case if any file is infected, you will see the following search result.

Binary file ./media/test.jpg matches

1	Binary file ./media/test.jpg matches

Query to get size of database tables in MB

If your hosting environment has no database monitoring, you should be able to monitor it manually once in a while or with your own shell script to prevent rapidly growing MySQL tables, because the result of large MySQL tables ( I am talking about 2 – 10 GB with millions of rows ) are often random performance issues, especially if you are using community modules with not well written MySQL queries.

SELECT TABLE_NAME, table_rows, data_length, index_length,
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = "{{{YOUR_DATABASE}}}"
ORDER BY (data_length + index_length) DESC;

SELECT TABLE_NAME, table_rows, data_length, index_length,

round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"

FROM information_schema.TABLES WHERE table_schema = "{{{YOUR_DATABASE}}}"

ORDER BY (data_length + index_length) DESC;

This is the output of the query.

| enterprise_logging_event                      |     122389 |    15939572 |      3270656 |      18.32 |
| catalog_product_index_eav                     |     324014 |     4212182 |     18053120 |      21.23 |
| log_visitor_info                              |     182838 |    19706548 |      3599360 |      22.23 |
| catalog_product_index_eav_idx                 |     324014 |     4522245 |     21139456 |      24.47 |
| catalog_product_entity_varchar                |     255303 |    10852180 |     15244288 |      24.89 |
| log_url                                       |     228886 |    10966704 |     15636480 |      25.37 |
| log_url_info                                  |     228886 |    80258352 |      7542784 |      83.73 |
| enterprise_logging_event_changes              |      69184 |   160619752 |      1499136 |     154.61 |
+-----------------------------------------------+------------+-------------+--------------+------------+
586 rows in set (0.05 sec)

| enterprise_logging_event | 122389 | 15939572 | 3270656 | 18.32 |

| catalog_product_index_eav | 324014 | 4212182 | 18053120 | 21.23 |

| log_visitor_info | 182838 | 19706548 | 3599360 | 22.23 |

| catalog_product_index_eav_idx | 324014 | 4522245 | 21139456 | 24.47 |

| catalog_product_entity_varchar | 255303 | 10852180 | 15244288 | 24.89 |

| log_url | 228886 | 10966704 | 15636480 | 25.37 |

| log_url_info | 228886 | 80258352 | 7542784 | 83.73 |

| enterprise_logging_event_changes | 69184 | 160619752 | 1499136 | 154.61 |

+-----------------------------------------------+------------+-------------+--------------+------------+

586 rows in set (0.05 sec)