3

We have about 20 databases with about 8000 tables each. Modifying the software is not an option (it is bought), and it seems most of the cpu is consumed by "checking permissions". While the s/w accesses the DB the DB cpus get all to 100%, while processlist report 99% of queries stuck in "checking permissions". Any idea on how to improve this, or how to disable permissions in mysql altogether?

Tables are innodb file_per_table, table_open_cache=60000, table_definition_cache=50000 The database has only 3 users. Nearly all of the queries are like the one below. DB runs on Centos 6.5

mysql> SHOW FULL TABLES FROM `XXXX` LIKE 'HISTORY_CONFIG';
+---------------------------------------+------------+
| Tables_in_XXXX (HISTORY_CONFIG) | Table_type |
+---------------------------------------+------------+
| HISTORY_CONFIG                        | BASE TABLE |
+---------------------------------------+------------+
1 row in set (0.12 sec)

mysql> SHOW PROFILES;
+----------+------------+----------------------------------------------------------+
| Query_ID | Duration   | Query                                                    |
+----------+------------+----------------------------------------------------------+
|        1 | 0.16267675 | SHOW FULL TABLES FROM `XXXX` LIKE 'HISTORY_CONFIG' |
+----------+------------+----------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show profile context switches;
+----------------------+----------+-------------------+---------------------+
| Status               | Duration | Context_voluntary | Context_involuntary |
+----------------------+----------+-------------------+---------------------+
| starting             | 0.000077 |                 0 |                   0 |
| checking permissions | 0.000014 |                 0 |                   0 |
| checking permissions | 0.000013 |                 0 |                   0 |
| Opening tables       | 0.000038 |                 0 |                   0 |
| init                 | 0.000017 |                 0 |                   0 |
| System lock          | 0.000015 |                 0 |                   0 |
| optimizing           | 0.000013 |                 0 |                   0 |
| statistics           | 0.000017 |                 0 |                   0 |
| preparing            | 0.000016 |                 0 |                   0 |
| executing            | 0.000015 |                 0 |                   0 |
| checking permissions | 0.111432 |               360 |                 189 |
| Sending data         | 0.000042 |                 0 |                   0 |
| end                  | 0.000017 |                 0 |                   0 |
| query end            | 0.000013 |                 0 |                   0 |
| closing tables       | 0.000012 |                 0 |                   0 |
| removing tmp table   | 0.000016 |                 0 |                   0 |
| closing tables       | 0.000013 |                 0 |                   0 |
| freeing items        | 0.050857 |               129 |                  59 |
| cleaning up          | 0.000043 |                 0 |                   0 |
+----------------------+----------+-------------------+---------------------+
19 rows in set, 1 warning (0.00 sec)

UPDATE Solved by creating a mysql-proxy lua script which smartly caches successfull table hits and answers instantly. This was an application-specific fix though.

7
  • How many user accounts do you have? select count(*) from mysql.user; Commented Jun 2, 2014 at 16:12
  • As I state in the description, we only have 3 accounts. I tried adding 10 accounts but this didn't raise times, so it must be something else.
    – sivann
    Commented Jun 6, 2014 at 6:51
  • 3 or 13 accounts shouldn't matter. How many GRANTs do you have? What is the app doing (dropping tables, perhaps?)? Commented Jun 10, 2014 at 21:21
  • the app has lots of alter table requests, and some create/drop tables.
    – sivann
    Commented Jun 18, 2014 at 13:43
  • Perhaps this lock is the culprit? mysqlperformanceblog.com/2009/06/16/slow-drop-table Commented Jun 19, 2014 at 17:28

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.