[#MDEV-27795] run sysbench with stored procedure

Comment by Axel Schwenke [ 2022-02-16 ]

I have picked sysbench workloads point-select and OLTP 90:10 for this. The procedures are created by the sysbench prepare stage and look like so.

For MariaDB:

CREATE PROCEDURE point_select_table1(IN x1 INTEGER)

BEGIN

  DECLARE notused CHAR(120);

  SELECT c INTO notused FROM sbtest1 WHERE id=x1;

END

And for PostgreSQL:

CREATE PROCEDURE point_select_table1(IN x1 INTEGER) AS $$

DECLARE notused CHAR(120);

BEGIN

  SELECT c INTO notused FROM sbtest1 WHERE id=x1;

END;

$$ LANGUAGE 'plpgsql'

Rem1: the cludge with SELECT INTO was used to make the CALL statement work with sysbench. As of now only a SELECT statement is expected to return a result set.

Rem2: if multiple tables are used in sysbench, a precedure is created for each of them. This saves us from meddling with dynamic SQL.

With these new workloads, we can compare performance and flame graphs for PG/MariaDB using procedures or not and using prepared statements or not (exception: PostgreSQL cannot prepare CALL statements).

Comment by Axel Schwenke [ 2022-02-17 ]

I uploaded 4 results. Workloads point-select (a single statement in the procedure, like show above) and OLTP 90:10 using this procedure (MariaDB variant only)

CREATE PROCEDURE workload_9010_table1(

  IN x1 INTEGER,   IN x2 INTEGER,   IN x3 INTEGER,

  IN x4 INTEGER,   IN x5 INTEGER,   IN x6 INTEGER,

  IN x7 INTEGER,   IN x8 INTEGER,   IN x9 INTEGER,

  IN y1 CHAR(120),   IN y2 INTEGER)

BEGIN

  DECLARE notused CHAR(120);

  START TRANSACTION;

  SELECT c INTO notused FROM sbtest1 WHERE id=x1;

  SELECT c INTO notused FROM sbtest1 WHERE id=x2;

  SELECT c INTO notused FROM sbtest1 WHERE id=x3;

  SELECT c INTO notused FROM sbtest1 WHERE id=x4;

  SELECT c INTO notused FROM sbtest1 WHERE id=x5;

  SELECT c INTO notused FROM sbtest1 WHERE id=x6;

  SELECT c INTO notused FROM sbtest1 WHERE id=x7;

  SELECT c INTO notused FROM sbtest1 WHERE id=x8;

  SELECT c INTO notused FROM sbtest1 WHERE id=x9;

  UPDATE sbtest1 SET c=y1 WHERE id=y2;

  COMMIT;

END

In both PostgreSQL and MariaDB the one-line procedure doesn't work well. Sending the query alone is faster, especially as prepared statement.

For the more complex 90:10 workload things look different. In MariaDB the peak throughput nearly doubles and for PostgreSQL the troughput even increases by a factor of more than 3 (compared to non-prepared queries). Interestingly calling the procedure with a prepared statement has negative impact on performance (PostgreSQL doesn't even support that).

Numbers again:

           point-select

           SQL                     PROCEDURE

           non-prepared  prepared  non-prepared  prepared

---------------------------------------------------------

PostgreSQL       335632    573608        486780

MariaDB          361412    423020        315552    335113

           90:10

           SQL                     PROCEDURE

           non-prepared  prepared  non-prepared  prepared

---------------------------------------------------------

PostgreSQL        27909     43277         82326

MariaDB           27063     30656         55733     47171

[MDEV-27795] run sysbench with stored procedure Created: 2022-02-10 Updated: 2023-09-27
Status:	Stalled
Project:	MariaDB Server
Component/s:	Stored routines
Fix Version/s:	10.5, 10.6

[MDEV-27795] run sysbench with stored procedure Created: 2022-02-10 Updated: 2023-09-27