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 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

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.