MySQL Bugs: #29341: Performance regression if there are any column-level grants.

Bug #29341	Performance regression if there are any column-level grants.
Submitted:	25 Jun 2007 18:17	Modified:	27 Jul 15:12
Reporter:	Konstantin Osipov
Status:	Won't fix
Category:	Server: Privileges	Severity:	S5 (Performance)
Version:	5.0	OS:	Any
Assigned to:		Target Version:
Tags:	bfsm_2007_11_15, regression
Triage:	Triaged: D2 (Serious)

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.

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.

Note that this regression happened two years ago (December 2004)

When will this be fixed?

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?

The bug is not planned to be fixed in 5.1 or 6.0.

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.