[Engine-devel] Opimizing Postgres Stored Procedures

Eli Mesika emesika at redhat.com
Sun Sep 1 08:35:43 UTC 2013



----- Original Message -----
> From: "Laszlo Hornyak" <lhornyak at redhat.com>
> To: "Eli Mesika" <emesika at redhat.com>
> Cc: "engine-devel" <engine-devel at ovirt.org>
> Sent: Friday, August 30, 2013 7:17:32 PM
> Subject: Re: [Engine-devel] Opimizing Postgres Stored Procedures
> 
> Hi Eli,
> 
> I wrote a quick benchmark to see if there is any difference when using STABLE
> modifier on functions running queries the way the engine does it (calling it
> from JDBC, one function in a single statement)
> 
> with a stable function:
> create function getKakukk(_id int) returns VARCHAR STABLE as 'select val from
> kakukk where id = $1' language sql;
> and one not marked as stable
> create function getKakukk_(_id int) returns VARCHAR as 'select val from
> kakukk where id = $1' language sql;
> the table is this simple:
> create table kakukk(id int primary key, val varchar);
> and the only content is:
> insert into kakukk (id, val) values (1, 'bla bla bla');
> 
> Now the benchmark code:
> 
> package com.foobar;
> 
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> 
> import org.junit.After;
> import org.junit.Before;
> import org.junit.Test;
> 
> public class SpeedTest {
> 
>     Connection connection;
> 
>     @Before
>     public void connect() throws SQLException {
>         connection =
>         DriverManager.getConnection("jdbc:postgresql://localhost/stabletest",
>         "engine", "engine");
>     }
> 
>     @After
>     public void disconnect() throws SQLException {
>         connection.close();
>     }
> 
>     private long measure(Runnable runnable, int times) {
>         final long start = System.currentTimeMillis();
>         for (int i = 0; i < times; i++) {
>             runnable.run();
>         }
>         final long end = System.currentTimeMillis();
>         return end - start;
>     }
> 
>     public static class Select implements Runnable {
> 
>         public Select(PreparedStatement preparedStatement) {
>             super();
>             this.preparedStatement = preparedStatement;
>         }
> 
>         final PreparedStatement preparedStatement;
> 
>         public void run() {
>             try (
>                     ResultSet resultSet = preparedStatement.executeQuery();)
>                     {
>                 while (resultSet.next()) {
>                     // nothing, just next
>                 }
>             } catch (SQLException e) {
>                 // TODO Auto-generated catch block
>                 e.printStackTrace();
>             }
>         }
>     }
> 
>     @Test
>     public void performTest() throws SQLException {
>         for (int i = 0; i < 10; i++) {
>             try (
>                     PreparedStatement stable =
>                     connection.prepareStatement("select getKakukk(1)");
>                     PreparedStatement notStable =
>                     connection.prepareStatement("select getKakukk_(1)");) {
>                 System.out.println("STABLE: " + measure(new Select(stable),
>                 100000));
>                 System.out.println("not STABLE: " + measure(new
>                 Select(notStable), 100000));
>                 System.out.println("---");
> 
>             }
>         }
>     }
> }
> 
> 
> --
> 
> The results are very similar, seemingly no difference at all.
> Therefore, it seems we do not need those STABLE markers for performance
> reasons.

Please refer to http://www.postgresql.org/docs/8.3/static/xfunc-volatility.html
It says :

"For best optimization results, you should label your functions with the strictest volatility category that is valid for them."

So , using STABLE , IMMUTABLE , STRICT is mandatory from now on.

Thanks  
Eli


> 
> Thank you,
> Laszlo
> 
> ----- Original Message -----
> > From: "Laszlo Hornyak" <lhornyak at redhat.com>
> > To: "Eli Mesika" <emesika at redhat.com>
> > Cc: "engine-devel" <engine-devel at ovirt.org>
> > Sent: Wednesday, August 28, 2013 1:02:18 PM
> > Subject: Re: [Engine-devel] Opimizing Postgres Stored Procedures
> > 
> > 
> > ----- Original Message -----
> > > From: "Eli Mesika" <emesika at redhat.com>
> > > To: "Laszlo Hornyak" <lhornyak at redhat.com>
> > > Cc: "engine-devel" <engine-devel at ovirt.org>
> > > Sent: Wednesday, August 28, 2013 11:45:14 AM
> > > Subject: Re: [Engine-devel] Opimizing Postgres Stored Procedures
> > > 
> > > 
> > > 
> > > ----- Original Message -----
> > > > From: "Laszlo Hornyak" <lhornyak at redhat.com>
> > > > To: "Eli Mesika" <emesika at redhat.com>
> > > > Cc: "engine-devel" <engine-devel at ovirt.org>
> > > > Sent: Tuesday, August 27, 2013 11:40:27 AM
> > > > Subject: Re: [Engine-devel] Opimizing Postgres Stored Procedures
> > > > 
> > > > Hi Eli,
> > > > 
> > > > Most of the functions that we have in the DB are doing very simple jobs
> > > > like
> > > > run a query, insert/update and I see that now you have all QUERY
> > > > functions
> > > > as STABLE.
> > > > My questions:
> > > > Is this required for new functions from now on?
> > > Yes and a email asking that was posted to engine_devel
> > > 
> > > > Is this done in order to improve performance?
> > > Yes
> > 
> > Do you have any documents/benchmarks on how and why does this improve
> > performance?
> > STABLE functions should improve performance if they return the same result
> > for the same parameters in the same statement.
> > E.g. if you have a stable function like "select foo(x) from y" then the
> > function can be invoked only once to evaluate each distinct value of y.x -
> > this is kind of useful
> > Functions running queries for the ovirt engine are typically invoked from
> > client side, therefore they are only ivoked once from the parameters list
> > and therefore will be only executed once for that single statement.
> > 
> > > 
> > > > 
> > > > Thank you,
> > > > Laszlo
> > > > 
> > > > ----- Original Message -----
> > > > > From: "Eli Mesika" <emesika at redhat.com>
> > > > > To: "engine-devel" <engine-devel at ovirt.org>
> > > > > Sent: Monday, August 26, 2013 11:22:20 AM
> > > > > Subject: [Engine-devel] Opimizing Postgres Stored Procedures
> > > > > 
> > > > > Hi
> > > > > 
> > > > > I had merged the following patch
> > > > > http://gerrit.ovirt.org/#/c/17962/
> > > > > 
> > > > > This patch introduce usage of the IMMUTABLE, STABLE and STRICT
> > > > > keywords
> > > > > in
> > > > > order to boost performance of the Postgres SPs.
> > > > > 
> > > > > Please make sure that your current/and future DB scripts applied
> > > > > that.
> > > > > 
> > > > > 
> > > > > Volatility
> > > > > ----------
> > > > > * A function should be marked as IMMUTABLE if it doesn't change the
> > > > > database,
> > > > > and if it doesn't perform any lookups (even for database
> > > > > configuration
> > > > > values) during its operation.
> > > > > * A function should be marked STABLE if it doesn't change the
> > > > > database,
> > > > > but
> > > > > might perform lookups (IMMUTABLE is preferable if function meets the
> > > > > requirements).
> > > > > * A function doesn't need to be marked VOLATILE, because that's the
> > > > > default.
> > > > > 
> > > > > STRICTNESS
> > > > > ----------
> > > > > A function should be marked STRICT if it should return NULL when it
> > > > > is
> > > > > passed
> > > > > a NULL argument, and then the function won't even be called if it is
> > > > > indeed
> > > > > passed a NULL argument.
> > > > > 
> > > > > 
> > > > > I am available for any questions.
> > > > > 
> > > > > Thanks
> > > > > 
> > > > > Eli
> > > > > _______________________________________________
> > > > > Engine-devel mailing list
> > > > > Engine-devel at ovirt.org
> > > > > http://lists.ovirt.org/mailman/listinfo/engine-devel
> > > > > 
> > > > 
> > > 
> > _______________________________________________
> > Engine-devel mailing list
> > Engine-devel at ovirt.org
> > http://lists.ovirt.org/mailman/listinfo/engine-devel
> > 
> 



More information about the Engine-devel mailing list