maria-developers team mailing list archive

["Jakob Lorberblatt" <jakob@xxxxxxxxxxxxxxxxxxxxxxx>]

This Task is very needed to attain the "transparency" of access method and
functionality specific to user logins still being present.

I ran into this issue in MULTIPLE circumstances with both MySQL 5.1 and
MariaDB 5.1, worst performance metrics (based on smooth, efficient SQL
logic that should not have needed this type of locking as a consequence)
was with the MySQL Cluster product itself; if anything it made the
situation worse.

Jakob Lorberblatt

> -----------------------------------------------------------------------
>                               WORKLOG TASK
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> TASK...........: Reduced table/row locking for SELECT statement with
> read-only stored
> 		function
> CREATION DATE..: Thu, 24 Nov 2011, 10:55
> SUPERVISOR.....: Monty
> IMPLEMENTOR....: Monty
> COPIES TO......:
> CATEGORY.......: Server-RawIdeaBin
> TASK ID........: 244 (http://askmonty.org/worklog/?tid=244)
> VERSION........: Server-5.5
> STATUS.........: Assigned
> PRIORITY.......: 60
> WORKED HOURS...: 0
> ESTIMATE.......: 0 (hours remain)
> ORIG. ESTIMATE.: 0
>
> PROGRESS NOTES:
>
>
>
> DESCRIPTION:
>
> When statement (or mixed) binlogging is used, the server currently is
> overly
> conservative in its use of table/row locks for SELECT statements that call
> stored functions. For example:
>
>   delimiter //
>   CREATE FUNCTION `test_f`(x CHAR(8)) RETURNS INT
>     READS SQL DATA
>   BEGIN
>     DECLARE output INT DEFAULT NULL;
>     SELECT id FROM t1 WHERE a = x INTO output;
>     RETURN output;
>   END //
>   delimiter ;
>
>   SELECT test_f("foo");
>
> In general, it is possible for a SELECT statement that calls a stored
> function
> to modify tables, and thus to need to be binlogged. In statement-based
> binlogging, this requires taking extra read locks to ensure that
> replication
> will apply events from concurrent queries on the master in the correct
> order,
> just like normal UPDATE statements.
>
> However, most uses of stored functions in SELECT do _not_ modify data, as
> in
> the above example. Thus there is no need for extra read locks in
> statement-based binlog mode, as nothing will be logged to the binlog
> anyway
> for the SELECT statement.
>
> However, the server currently takes the pessimistic safe route and
> _always_
> takes extra read locks in statement-based binlogging when stored functions
> are
> used. There is no analysis to detect that stored functions used are
> read-only,
> and hence that no extra locking is needed.
>
> This worklog is about improving this so that in common cases where no
> table
> modification is done in stored functions, no extra locking is done, while
> still preserving correct operation for statement-based replication when
> modification _is_ done.
>
>
> ESTIMATED WORK TIME
>
> ESTIMATED COMPLETION DATE
> -----------------------------------------------------------------------
> WorkLog (v4.0.0)
>
>
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-developers
> Post to     : maria-developers@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-developers
> More help   : https://help.launchpad.net/ListHelp
>