Configuring Database in application.conf causes weird performance impact

[Florian Gutmann]

Hello!

We're using myBatis for database access and would like to use play
evolutions for database migration.
When the db properties are configured in application.conf, so that
evolutions can be used, we encounter significant performance impacts
during normal requests. If the database is not configured everything is ok.

On a simple page request the average response time goes up from 2ms to 27ms.

Some part of play seems to access the database every request with
queries like this:

"SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS
TABLE_NAME, CASE n.nspname ~ '^pg_' OR n.nspname =
'information_schema' WHEN true THEN CASE WHEN n.nspname = 'pg_catalog'
OR n.nspname = 'information_schema' THEN CASE c.relkind WHEN 'r' THEN
'SYSTEM TABLE' WHEN 'v' THEN 'SYSTEM VIEW' WHEN 'i' THEN 'SYSTEM
INDEX' ELSE NULL END WHEN n.nspname = 'pg_toast' THEN CASE
c.relkind WHEN 'r' THEN 'SYSTEM TOAST TABLE' WHEN 'i' THEN 'SYSTEM
TOAST INDEX' ELSE NULL END ELSE CASE c.relkind WHEN 'r' THEN
'TEMPORARY TABLE' WHEN 'i' THEN 'TEMPORARY INDEX' WHEN 'S' THEN
'TEMPORARY SEQUENCE' WHEN 'v' THEN 'TEMPORARY VIEW' ELSE NULL END
END WHEN false THEN CASE c.relkind WHEN 'r' THEN 'TABLE' WHEN 'i'
THEN 'INDEX' WHEN 'S' THEN 'SEQUENCE' WHEN 'v' THEN 'VIEW' WHEN 'c'
THEN 'TYPE' ELSE NULL END ELSE NULL END AS TABLE_TYPE,
d.description AS REMARKS FROM pg_catalog.pg_namespace n,
pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_description d ON (c.oid =
d.objoid AND d.ob"

What could cause these complex, useless queries?
Evolutions? The JPA plugin (there are no JPA entities in the
application)? The DB plugin itslef?

Any help is appreciated!

--
Florian

[Julien Tournay]

Hi,

I think that hibernate is trying to update your schema.

Try to add jpa.ddl=validate to your application.conf.

jto.

--
You received this message because you are subscribed to the Google Groups "play-framework" group.
To post to this group, send email to play-fr...@googlegroups.com.
To unsubscribe from this group, send email to play-framewor...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/play-framework?hl=en.

--
Real Programmers don't need comments-- the code is obvious.

[Florian Gutmann]

No, that's not the case. These statements are executed also in PROD mode, in which jpa.ddl=none by default
Afaik, the JPA module should do nothing as long as no Models are annotated with @Entity.

Btw. we are using Postgresql 9.0 with the latest JDBC 4 driver (9.0-801.jdbc4).

-- 
Florian Gutmann

[Morten Kjetland]

Can you show us how you configure the db in application.conf?

-morten

~~~~~~~~~~~~~~~

Morten Kjetland

~~~~~~~~~~~~~~~

http://kjetland.com/blog/

https://twitter.com/mbknor

[Florian Gutmann]

Hi Morten,

I investigated a bit further and found that the main performance issue is because the Evolutions plugin checks the evolutions state before every invocation, regardless of the application mode.

Evolutions.java around Line 183 (latest 1.2.x branch):

    @Override
    public void beforeInvocation() {
        try {
            checkEvolutionsState();

I think this is not necessary in prod mode. The application doesn't start at all if there are un-applied evolutions. Why should it pick up new or modified evolutions during runtime?

When the checkEvolutionsState(); line is removed from the plugin, performance is back to normal again.

The weird looking SQL statements seem to come from the JDBC driver. There are still some of them when the checkEvolutionsState() is removed. I think they come from the test of idle connections in the database pool because they appear every 10 seconds also without any request.

Maybe it's possible to destroy the pool completely in prod mode after application start, when the JPA plugin is not in use? I don't see any reason why it should still be active after checking for un-applied evolutions.

And finally the answer to you're question:

db.driver=org.postgresql.Driver
%fgutmann-desktop.db.url=jdbc:postgresql:testproject
%fgutmann-desktop.db.user=testproject
%fgutmann-desktop.db.pass=topSecret

Thank you!

[Guillaume Bort]

> I think this is not necessary in prod mode.

Yes you are right, it should be disabled for each request in prod mode.

--
Guillaume Bort, http://guillaume.bort.fr

[Florian Gutmann]

I created a ticket and a pull request for this one.

http://play.lighthouseapp.com/projects/57987-play-framework/tickets/913

The pull request applies on master and contains also the fix for #910,
which is already merged into 1.2.x (but not master).
I hope this is ok.

Thanks,

Florian

--
Florian Gutmann