Purge PS tables and metadata_lock

Follow

lale

• March 30, 2018 09:07

Hi all,

we recently setup a galera cluster (3 nodes mysql 5.7) and we keep it monitored using cluster control. It happens that when cmon execute the task 'Purge PS tables' on all the nodes, we have a "Waiting for table metadata lock" for the statement "TRUNCATE TABLE performance_schema.events_statements_summary_by_digest".

I think that the query that keep the table locked is

"SELECT SUM_TIMER_WAIT, MAX_TIMER_WAIT, MIN_TIMER_WAIT, AVG_TIMER_WAIT, SUM_LOCK_TIME, DIGEST_TEXT, SCHEMA_NAME, SUM_ROWS_AFFECTED, SUM_ROWS_SENT, SUM_ROWS_EXAMINED,SUM_CREATED_TMP_DISK_TABLES, SUM_CREATED_TMP_TABLES, SUM_SELECT_FULL_JOIN, SUM_SELECT_FULL_RANGE_JOIN, SUM_SELECT_RANGE,SUM_SELECT_RANGE_CHECK, SUM_SELECT_SCAN, SUM_SORT_MERGE_PASSES, SUM_SORT_RANGE,SUM_SORT_ROWS, SUM_SORT_SCAN,SUM_NO_INDEX_USED,SUM_NO_GOOD_INDEX_USED, COUNT_STAR, UNIX_TIMESTAMP(FIRST_SEEN), UNIX_TIMESTAMP(LAST_SEEN), DIGEST FROM performance_schema.events_statements_summary_by_digest WHERE SCHEMA_NAME NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') AND SCHEMA_NAME <> 'NULL' AND  LAST_SEEN > DATE_SUB(NOW(), INTERVAL 1 SECOND)"

I noticed that this happens if i keep opened on my browser the page http://clustercontromachine/clustercontrol/#/cluster:5,g:overview

Does anyone experienced the same issue ? Any idea on how i can solve the issue ?

Thanks!

0

• 

  Ashraf Sharif

  • April 01, 2018 07:28

  Hi,

  Can you run the following statement on one of the DB node?

  SELECT COUNT(schema_name) AS Rows FROM performance_schema.events_statements_summary_by_digest WHERE SCHEMA_NAME NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') AND SCHEMA_NAME <> 'NULL' AND LAST_SEEN > DATE_SUB(NOW(), INTERVAL 1 SECOND);

  The above shall return the number of rows to tell how big the resultset is for your workload. This will give us ideas on how to reproduce the issue and provide fix if required.

  Regards,
  Ashraf

  0

  Comment actions Permalink
• 

  lale

  • April 03, 2018 07:26

  Hi Ashraf and thanks for answering.

  The result of

  SELECT COUNT(schema_name) AS Rows FROM performance_schema.events_statements_summary_by_digest WHERE SCHEMA_NAME NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') AND SCHEMA_NAME <> 'NULL' AND LAST_SEEN > DATE_SUB(NOW(), INTERVAL 1 SECOND);

  is 0.

  BTW: if I restart cmon avery 30 minutes, the issue doesn't appear

  Thanks

  0

  Comment actions Permalink
• 

  Ashraf Sharif

  • April 04, 2018 06:00

  Hi,

  We haven't had any luck to reproduce this problem with Overview page but we got the idea on why the metadata lock happens when truncating the PS table. Purging PS will be optional via a purge button in the next release (1.6).

  Regards,
  Ashraf

  0

  Comment actions Permalink
• 

  lale

  • April 04, 2018 06:54

  Thanks!

  we will wait the next release then.

   

  Regards,

  Lale

  0

  Comment actions Permalink

Please sign in to leave a comment.