MySQL Forums
Forum List  »  Performance

Slow "checking permissions"
Posted by: Raphael Thoma
Date: August 06, 2009 06:25AM

Hello

We are running a mysql 5.1.34 on opensolaris with about 1300 databases. For a piece of time we had about 220'000 GRANT-Entries which slowed down the performance of the whole server.

These GRANTs are no longer available on the server, but the performance didn't get any better.

An database with 218 empty tables takes nearly 50 seconds to get dropped.
mysql> drop database zigersch_test;
Query OK, 218 rows affected (49.93 sec)

Profiling seems to be as followed:

mysql> show profile for query 2;
+----------------------+-----------+
| Status | Duration |
+----------------------+-----------+
| starting | 0.000093 |
| checking permissions | 45.418026 |
| Opening table | 4.502463 |
| System lock | 0.000177 |
| Table lock | 0.000125 |
| Opening tables | 0.000053 |
| System lock | 0.000042 |
| Table lock | 0.000070 |
| query end | 0.000044 |
| freeing items | 0.000075 |
| logging slow query | 0.000040 |
| cleaning up | 0.000048 |
+----------------------+-----------+

Most of the time is spent checking permissions. Now there are only 14'000 entries in the mysql.user table and these tables have already been optimized:

mysql> select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
| 14044 |
+----------+

This is the number of users we had before, when the performance was much better. Does someone has an idea how to speed up permission checking?

Kind Regards
Raphael Thoma

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow "checking permissions"
24996
August 06, 2009 06:25AM
11140
August 07, 2009 05:59PM
8048
October 12, 2009 09:28PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.