0

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.

  • What did the Time column of PROCESSLIST say when it was "waiting"? – Rick James Nov 27 '19 at 5:31
  • Run SHOW OPEN TABLES; – Rick James Nov 27 '19 at 5:32
  • The TIME was almost 500 or something like that. SHOW OPEN TABLES output is in the question. – Avinash Pawar Nov 27 '19 at 5:38
  • The only thing I could do was kill the running job, and re-initiate the dump. – Avinash Pawar Nov 27 '19 at 5:38
0

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
|improve this answer|||||
  • Thanks Mr James, the LEFT JOIN optimisation took almost 46 minutes, and the second optimisation took almost 8 while the original query was executed in 6 minutes. And yes, the said indexes are already there on the table. – Avinash Pawar Nov 27 '19 at 8:28
  • @AvinashPawar - Ouch. – Rick James Nov 27 '19 at 20:25
  • @AvinashPawar - Eh? The Question mentions "12 seconds"; your comment says "6 minutes"?? – Rick James Nov 27 '19 at 20:26
  • @AvinashPawar - and see if the new formulation helps any. – Rick James Nov 27 '19 at 20:31
  • When the job was executed during the initial setup there was not much data in the table and then it took 12 seconds (or may be I missed something), now there is a lot data in the transaction_details table. I will try the suggestions and post the results. – Avinash Pawar Nov 28 '19 at 5:13

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.