mysqldump waiting for table fush for long time

Question

MySQL version: MySQL Commercial 8.0.16 OS: RHEL 7.6

Following is my mysqldump command

mysqldump --defaults-extra-file=/mysql_data/config/extra_options.cnf --single-transaction --flush-logs --master-data=2 --dump-date --max-allowed-packet=64M  --events --routines --all-databases >  all-databases_`date +%F`.sql

When I issued show full processlist;, I found out that the mysqldump is waiting for table flush

There is one job that keeps running on the server which keeps two tables open.

SELECT CUSTOMER_ID , CARD_NUMBER , RRN_NUMBER , GROSS_TENURE , ADVANCE_EMI , POS_ENTRY_MODE , NO_OF_ADVANCE_EMI , MODE_OF_TRANSACTION , SOURCE_OF_TRANSACTION , RESPONSE_CODE , DECLINE_REASON_DESCRIPTION , FAILED_RULE_ID , NOW_AVAILABLE_LIMIT , BEFORE_TXN_AVAILABLE_LIMIT , OPEN_ECS_AVAILABLE_EMI_LIMIT_C , OPEN_ECS_VALID_DATE_C , CIBIL_TRIGGERED_ID , PRICING_CALCULATED_FEE , NO_LIVE_LOANS_DIGI , NO_LIVE_LOANS_REMI , NO_LIVE_LOANS_ECOM , NO_LIVE_LOANS_NONDIGI , NO_LIVE_LOANS_REP , NO_LIVE_LOANS_EPL , NO_LIVE_LOANS_SPL , NO_LIVE_LOANS_ERL , NO_LIVE_LOANS_WLT , NO_LIVE_LOANS_RWT , NO_LOANS_EVER_DIGI , NO_LOANS_EVER_REMI , NO_LOANS_EVER_ECOM , NO_LOANS_EVER_NONDIGI , DEALER_CODE_ETAILER , DEALER_FORMAT , EMI_AMT , TOTAL_EMI_LIVE_LOANS_ALL , TOTAL_EMI_LIVE_LOANS_ECF , TOTAL_EMI_LIVE_LOANS_ERF , TOTAL_EMI_LIVE_LOANS_REMI , TOTAL_EMI_LIVE_LOANS_DIGI , TOTAL_EMI_LIVE_LOANS_NONDIGI , CARD_UTILIZATION_ACCOUNT , CARD_UTILIZATION_CUSTOMER , HIGHEST_UTILIZATION_L6M , NO_OF_NON_TECH_BOUNCE_12M_ALL_LOANS , NO_OF_NON_TECH_BOUNCE_6M_ALL_LOANS , NO_NONTECH_BNC_L3M , NO_BNC_L12M , DAYS_DIFF_BASELOAN , HAS_HL_BUREAU , HAS_CC_BUREAU , BUREAU_TIMESERIES_SEG , MBK_DNLD_DT_STATUS , DEALER_RANK , DEALER_CITY_ID , DEALER_PINCODE , DEALER_STATE , CUSTOMER_PINCODE , CUSTOMER_STATE , INTERCITY_TRXN , MBL_CHANGE_DATE , DAYS_DIFF_MBLCHNG , CIBILSCORE , CARD_TYPE , CUSTOMER_TYPE , CARD_SOURCE_IDENTIFIER , ASSET_CATEGORY , DAYS_FROM_LAST_CIBIL , ASSET_CATEGORY_RISK_HIGH_MEDIUM_LOW , PRODUCT , LTV , DAYS_FROM_CARD_VALID_DATE , DAYS_FROM_LAST_LOAN , B_SCORE , DAYS_FROM_LAST_TRANSACTION , NET_LOAN_AMOUNT , VALIDFROM_C_CUSTOMER_LEVEL , LAST_CIBIL_DATE_C , NO_OF_ACTIVE_LOANS_C , PROCESS_DEVIATION_FLAG , SMT_FLAG , HML_TAG , LAST_MODIFIED_DATE_RESIDENCE_EMAIL , LAST_MODIFIED_DATE_OFFICE_MOBILE_NO , LAST_MODIFIED_DATE_RESIDENCE_ADDRESS , LAST_MODIFIED_DATE_OFFICE_ADDRESS , ORIGINAL_AUTH_AMOUNT , PERTNER_EDC_CHANNEL , FRAUD_FLAG , CIBIL_THICK_THIN , LINE_MULTIPLIER , COMPUTED_LIMIT , DEALER_TYPE , BLOCK_CODE_SYSTEM , CARD_DESIGN , DEALER_SUPER_GROUP , PRICING_OVL_LINE_AMOUNT , PRICING_OVL_LINE_CONSUMPTION , PRICING_OVL_PCNT_CONSUMPTION , AUTH_REQUEST_DATE_TIME , CHAMPAIGN_CHALLENGER_NAME , CUSTOMER_CITY_ID , CARD_LIMIT , TOTAL_NET_ACTIVE_LOAN , TRANSACTION_STATUS , VOID_DATE , CONVENIENCE_FEE , CONVENIENCE_FEE_CHARGE_ID , CHARGE_FEE , CHARGE_ID , PRICING_FEE_CHARGE_ID , SHADOW_LIMIT , SHADOW_LIMIT_CONSUMPTION , DELIVERY_PIN_CODE , NO_OF_APPR_TXNS_DONE_ON_CARD_SAME_DAY , NO_OF_REJ_TXNS_DONE_ON_CARD_SAME_DAY , NO_OF_TXNS_DONE_ON_CARD_SAME_DAY , DAYS_DIFF_MBLCHNG , CARDS_CUSTOMER_IDS_MARKED_RISKY_IN_ATOS , PRIOR_FAILED_TRANSACTION_ON_THE_SAME_DAY_DUE_TO_OTP_EXPIRY , MULT_TXNS_ON_CARD_ON_SAME_DAY_TOT_AMT_GREATER_THAN_30000 , OTHER_TXN_TYPE_ON_THE_SAME_DAY , TRANSACTION_DONE_ON_INTERSTATE_DEALER , DELIVERY_ADDRESS_CHANGE_FLAG , NO_OF_CARDS_LINKED_TO_MOBILE , ADDON_CARD_FLAG , ASSET_TYPE , ORDER_NUMBER_ECOM_PARTNER , EMAIL_ID , LAST_APPROVED_TRANSACTION_DATE , DOB , RESIDENCE_MOBILE_NUMBER_C , OFFICE_MOBILE_NUMBER_C , DELER_NAME_C , CUSTOMER_RESIDENCE_CITY_C , CUSTOMER_STATE_C , DEALER_CITY_C , DEALER_STATE_C , OLD_MOBILENUMBER , FOS_MOBILE FROM db1.transaction_details WHERE RESPONSE_CODE = '00' AND RRN_NUMBER NOT IN ( SELECT RRN FROM db1.JOB_PROCESSING ) LIMIT 1000

This query runs for almost 12 seconds and runs every minute.

root@localhost > show open tables where in_use > 0;
+----------+--------------------------+--------+-------------+
| Database | Table                    | In_use | Name_locked |
+----------+--------------------------+--------+-------------+
| db1      | job_processing           |      1 |           0 |
| db1      | transaction_details      |      1 |           0 |
+----------+--------------------------+--------+-------------+
3 rows in set (0.01 sec)

The tables are not very big in size.

root@localhost> select table_name, (data_length+index_length)/1024/1024 from information_schema.tables where table_name in('transaction_details','job_processing');
+--------------------------+--------------------------------------+
| TABLE_NAME               | (data_length+index_length)/1024/1024 |
+--------------------------+--------------------------------------+
| job_processing           |                           5.04687500 |
| transaction_details      |                          38.14062500 |
+--------------------------+--------------------------------------+

The row count is as follows

root@localhost> select count(*) from job_processing;
    +----------+
    | count(*) |
    +----------+
    |    13525 |
    +----------+
    1 row in set (0.01 sec)

root@localhost> select count(*) from transaction_details;

    +----------+
    | count(*) |
    +----------+
    |    15369 |
    +----------+
    1 row in set (0.01 sec)

Is this happening because the tables are open? The mysqldump option --single-transaction performs a flush tables with read lock and is it waiting for the queries to close the tables? State "Waiting for table flush" in processlist

MySQL Docs says "How MySQL Opens and Closes Tables"

MySQL is multi-threaded, so there may be many clients issuing queries for a given table simultaneously. To minimize the problem with multiple client sessions having different states on the same table, the table is opened independently by each concurrent session. This uses additional memory but normally increases performance. With MyISAM tables, one extra file descriptor is required for the data file for each client that has the table open. (By contrast, the index file descriptor is shared between all sessions.)

What should I do in this case? Please help.

I keep physical backups as well using Enterprise Backup, but the company policy requires me to maintain both the backups and once I see 'waiting for table flush', all the subsequent queries get stuck with 'waiting for table flush'. Thus, sometimes the backup fails.

UNLOCK TABLES does not help. One thing I do is I kill the query, the above mentioned job and after sometime things are fine.

Answer 1

Slow query? Let's speed it up;

Instead of

    FROM  db1.transaction_details
    WHERE  RESPONSE_CODE = '00'
      AND  RRN_NUMBER NOT IN (
        SELECT  RRN
            FROM  db1.JOB_PROCESSING 
                          )
    LIMIT  1000

say

    FROM  db1.transaction_details AS td
    LEFT JOIN db1.JOB_PROCESSING AS jp
             ON td.RRN_NUMBER = jp.RRN
    WHERE  RESPONSE_CODE = '00'
      AND  jp.RRN  IS NULL
    LIMIT  1000

or

    FROM  db1.transaction_details AS td
    WHERE  RESPONSE_CODE = '00'
      AND  NOT EXISTS ( SELECT 1
                        FROM  db1.JOB_PROCESSING 
                        WHERE RRN = td.RRN_NUMBER )
    LIMIT  1000

(Different versions of MySQL and MariaDB optimize these 3 variants differently; I can't predict which is fastest.)

transaction_details needs an index starting with RESPONSE_CODE.

JOB_PROCESSING needs an index starting with RRN.

If those things don't speed it up enough, we can work on pushing the LIMIT into a derived table that delivers only the PK of td.

Push LIMIT down

How fast does this run by itself?

SELECT id    -- just the PRIMARY KEY
    FROM  db1.transaction_details
    WHERE  RESPONSE_CODE = '00'
      AND  RRN_NUMBER NOT IN (
        SELECT  RRN
            FROM  db1.JOB_PROCESSING 
                             )
    LIMIT  1000;

If reasonably fast, then see how the following works:

SELECT lots-of-stuff
    FROM ( the-above-query ) AS x
    JOIN db1.transaction_details AS td
        USING(id);   -- again the PRIMARY KEY