Bug #29341 Performance regression if there are any column-level grants.
Submitted: 25 Jun 2007 16:17 Modified: 27 Jul 2009 13:12
Reporter: Konstantin Osipov (OCA) Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S5 (Performance)
Version:5.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: bfsm_2007_11_15, regression
Triage: Triaged: D2 (Serious)

[25 Jun 2007 16:17] Konstantin Osipov
Description:
This is a follow-up bug after Bug#24988 "FLUSH PRIVILEGES causes brief unavailability". This performance regression was identified, but not fixed by the patch for Bug#24988:

Even if there are only global grants (no column-level grants), we still lock LOCK_grant lock and search for column-level grants for every statement.

Additionally, if the server was started with --skip-grant-tables, we still
attempt to check the privileges by looking into the grant data on each request.

How to repeat:
This is a scalability problem, not an excessive CPU consumption problem.
To reproduce, use a multi-CPU system. 

1. Start a server with a default installation (no additional grants in the system).

create table test.t1 (a int);

Benchmark SELECT * FROM t1 LIMIT 0 from multiple threads for queries per second ratio: the scalability factor is expected to degrade in 5.0.

2. Start the server with --skip-grant-tables. Re-run the same benchmark and observe comparable performance as with enabled privileges and degraded performance compared to 4.1.

Suggested fix:
Restore semantics of the removed 'grant_option' variable. This variable was originally broken by introduction of Stored Routine privileges, and then completely removed by the patch for Bug#24988.
[25 Jun 2007 16:19] Konstantin Osipov
Trudy, raising priority of this bug to P1. The regression is regarded to be critical (after discussion with Monty). Still left the bug to open to have accurate benchmark information.
[25 Jun 2007 17:07] Sergei Golubchik
Note that this regression happened two years ago (December 2004)
[8 May 2008 22:07] Mark Callaghan
When will this be fixed?
[8 May 2008 22:36] Mark Callaghan
Is grant_options only an optimization that allows code to avoid checks for grants when there are none? My servers always have grants. I want to fix the original bug (24988) without taking the performance hit. Do I get that by never settting grant_options to FALSE?
[9 May 2008 6:16] Konstantin Osipov
The bug is not planned to be fixed in 5.1 or 6.0.
[27 Jul 2009 13:12] Konstantin Osipov
The performance regression was only according to Monty. Was never verified by our benchmarking or reported by a user. Maybe there is no noticeable regression whatsoever. I have no interest in looking at this bug unless there is evidence that the regression is real.