Sometimes but not always truncate table statements get stuck in checking permissions state for several minutes (up to 7-8 minutes).

I'm running MySQL 8.0.12 (for Linux on x86_64 (MySQL Community Server - GPL)) My tables are partitioned with around 365 partitions in each.

There are only 5 users (excluding root and mysql.). The 5 users are not tied to a specific host. Sometimes the same users is used from multiple hosts at same time.

Is this a known problem? What are the possible causes or solutions?

Any help is much appreciated!

  • 1
    In common the checking permissions is a result of too many user accounts. Remove unnesessary (outdated, unused, etc.) MySQL logins.
    – Akina
    Sep 12 '18 at 10:02
  • @Akina Thanks for the suggestions. We only have 5 users. I've added more detail in the question.
    – Wessi
    Sep 12 '18 at 10:23
  • 1
    possibly related, but no solution: dba.stackexchange.com/q/218269/1396 Sep 21 '18 at 17:51

Hence my advice not to have more than about 50 partitions in a table. Or to abandon partitioning altogether.

What may be happening: table_open_cache (or one of the other caches) may be too small, and, hence, thrashing.

To verify that theory, I need to check several things. Please provide SHOW GLOBAL STATUS;, SHOW VARIABLES; and how much RAM you have. More details.

To critique the partitioning, please provide SHOW CREATE TABLE for at least one of the tables with a lot of partitions.

And you are TRUNCATEing a table with a lot of partitions?

If possible, look in the filesystem and list some of the files for that table.

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.