0

A few days ago, I revised some innodb_stats_* parameters and ran mysqlcheck --analyze --all-databases on some Percona 5.5.38 servers. Now, whenever I execute any simple query in MySQL Workbench, it seems to execute a background query of SHOW INDEX ON t1; Needless to say, this caused some issues when it blocked updates to the table on production servers.

I have ascertained these things:

  1. it happens with any account with sufficient priviledges
  2. it doesn't happen from the command line
  3. it happens for other users
  4. it happens even after I upgrade to 6.2.4 and delete all the settings
  5. it DOESN'T happen against my Percona 5.6 database
  6. SHOW INDEX starts about 5 seconds AFTER my query

I haven't been able to find any explanation for this behavior on the net, so wanted to see if any experienced DBA's have run into this.

Current stats related settings:

  • innodb_stats_auto_update 0
  • innodb_stats_method nulls_equal
  • innodb_stats_on_metadata ON
  • innodb_stats_sample_pages 8
  • innodb_stats_update_need_lock 1
  • innodb_use_sys_stats_table OFF

Thanks for any pointers anyone can provide.

UPDATE After looking through the source code, it turns out that the SHOW INDEX happens for every query on a single table. It checks to see if the selected columns contain a primary key or other unique index, so it can decide whether you can edit the data.

The source code contains this comment:

// XXX this can be slow because of the I_S queries, depending on the server

I went to see why my INFORMATION_SCHEMA was slow, and found a helpful post from Percona

It turns out that innodb_stats_on_metadata had gotten turned on during this process, which causes MySQL to recalculate the statistics every time INFORMATION_SCHEMA is accessed. So I just turned it back off.

What a nightmare.

1

Scanning the source code of MySQL Workbench you can see a few places where SHOW INDEX is sent out. The application needs to run quite a number of queries for its tasks, e.g. the index view or the table editor. Some of them are only active when you open a view from the management section. Others, like the SQL editor need a SHOW INDEX to find info about PK columns which is needed to decide if a resultset returned for a query can be edited.

  • Interesting. This doesn't explain why it only started doing this after the change, or why it does it even when I run the same query 3x in a row. But I'll go root around in the source some. Hoping I can find a way to turn it off since it makes even single row queries take seconds to return. But it does hint at why it throws some results set exceptions and occasionally crashes now (forgot to mention that part since it's always been a little crash). Thanks! – Chimerith Mar 4 '15 at 8:37

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.