Analyze queries using the Percona Toolkit (Adobe Commerce Pro: Cloud architecture only)

If your Adobe Commerce project is deployed on Pro architecture, you can use the Percona Toolkit to analyze queries.

  1. Run the pt-query-digest --type=slowlog command against MySQL slow query logs.

  2. Based on the issues found, take steps to fix the query, so it runs more quickly.

Checking MySQL “process list”

Description

This will help to identify if the MySQL server is alive and that there are no stuck queries.

Steps

  1. Log in to your MySQL command line (Adobe Commerce on-premises/Magento Open Source) or on your cloud server from the command line (Adobe Commerce on cloud infrastructure).

  2. Log in to MySQL using the block of code below. This will automate the process of logging in.

    `export DB_NAME=$(grep [\']db[\'] -A 20 app/etc/env.php | grep dbname | head -n1 | sed "s/.*[=][>][ ]*[']//" | sed "s/['][,]//");    export MYSQL_HOST=$(grep [\']db[\'] -A 20 app/etc/env.php | grep host | head -n1 | sed "s/.*[=][>][ ]*[']//" | sed "s/['][,]//");    export DB_USER=$(grep [\']db[\'] -A 20 app/etc/env.php | grep username | head -n1 | sed "s/.*[=][>][ ]*[']//" | sed "s/['][,]//");    export MYSQL_PWD=$(grep [\']db[\'] -A 20 app/etc/env.php | grep password | head -n1 | sed "s/.*[=][>][ ]*[']//" | sed "s/[']$//" | sed "s/['][,]//");    mysql -h $MYSQL_HOST -u $DB_USER --password=$MYSQL_PWD $DB_NAME -U -A -e 'show processlist;`
    
  3. If you get an error back or it takes more than 30 sec to respond, you should contact Support to check the MySQL server.

  4. Looking at sample output.

  5. Here is some sample output:

    `$ mysql -h $MYSQL_HOST -u $DB_USER --password=$MYSQL_PWD $DB_NAME -U -A -e 'show processlist;'    +-----------+---------------+--------------------+---------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+----------+    | Id        | User          | Host               | db            | Command | Time | State          | Info                                                                                                 | Progress |    +-----------+---------------+--------------------+---------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+----------+    | 123456789 | abcdefghijklm | 192.168.7.10:12345 | abcdefghijklm | Query   |    0 | Writing to net | SELECT `magento_versionscms_hierarchy_node`.*, `page_table`.`title` AS `page_title`, `page_table`.`i |    0.000 |    | 123456788 | abcdefghijklm | 192.168.7.10:12344 | abcdefghijklm | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |    | 123456777 | abcdefghijklm | 192.168.7.10:12333 | abcdefghijklm | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |    | 123456666 | abcdefghijklm | 192.168.5.8:12222  | abcdefghijklm | Sleep   |    0 |                | NULL                                                                                                 |    0.000 |`
    
  6. Check the “Time” column for any time greater than 1800 seconds; that indicates a process that is potentially taking too much time to complete. Note the status of the processes in the “State” column.

  7. Review the queries and possibly kill them if they are found not to be expected to run for that length of time. It is possible that the long running queries may be expected.

Previous pageMultiple rows in database for same entity
Next pageDatabase upload loses connection to MySQL

Commerce


How AI Is Transforming the Commerce Landscape

Online | Session | General Audience

Delve into how AI is revolutionizing the commerce landscape by optimizing operations, enhancing customer experiences, and improving...

Tue, Mar 18, 3:30 PM PDT (10:30 PM UTC)

Register

Put the Customer at the Center and Build Relationships That Last a Lifetime

Online | Strategy Keynote | General Audience

First impressions last a lifetime. Great first impressions feel personal, connected, and relevant right from the start. From the first...

Wed, Mar 19, 2:30 PM PDT (9:30 PM UTC)

Register

Connect with Experience League at Summit!

Get front-row access to top sessions, hands-on activities, and networking—wherever you are!

Learn more