search

mauricio / postgresql-async

Async, Netty based, database drivers for PostgreSQL and MySQL written in Scala
Apache License 2.0
1.43k stars 222 forks source link

Simplest query takes 30+ seconds when move from anorm to postgresql-async #212

Open tpraizler opened 7 years ago

tpraizler commented 7 years ago

Hey,

I am not sure if this is the right place for this, but I am hopeless. need your help! I am trying to move my app to postgresql-async from anorm, and encountered a major problem on the first refactor. I changed one of my daos, to be fully async using postgresql-async.

This is a message I get from postgres: [PURPLE] duration: 41353.017 ms execute 1/1: DELETE FROM audit_log WHERE company_id = $1 Detail: parameters: $1 = '30'

This delete is basically doing nothing, I tested it on an empty set. With anorm it work perfectly, but when move to postgresql-async, it takes 30 -40 seconds. This is even when there is nothing to delete.

I configured the connection as follow:

val url = configuration.getString("db.default.url").get
val asyncConf = URLParser.parse(url)
val factory = new PostgreSQLConnectionFactory(asyncConf.copy(ssl = SSLConfiguration(SSLConfiguration.Mode.Require)))
val poolConfiguration = PoolConfiguration.Default.copy(maxObjects = 10, maxQueueSize = 50)
new ConnectionPool(factory, poolConfiguration)

Do have an idea what is missing? Have you encounter such behavior? Do you have any suggestion to how to approch this?

Thank you very much!

mauricio commented 7 years ago

@tpraizler can you provide a self contained example that shows this behavior? without an example database it's really hard to understand what's going on.

tpraizler commented 7 years ago

I am working on it. creating a repo and application on heroku for this. Will update once I have all the details.

Just to make sure. what do you need? The code, and also access to the database?

tpraizler commented 7 years ago

@mauricio So I manage to focus the problem a little (can't believe I just tested it now). Looks like the regular query work perfectly, but the prepared statement is the problematic one. Does this help? any idea what's missing?

mauricio commented 7 years ago

@tpraizler there shouldn't be an issue with prepared statements, were you able to setup a database that fails to execute it? A simple .sql file with code that breaks would be really useful.

tpraizler commented 7 years ago

@mauricio ok. problem solved. but maybe with your help I will understand the logic behind it. It looks like running a ANALYZE VACUUM on this table, fixed the problem. I am not a db expert so it took a while to find that this was the issue.

My question is why prepared statement got stuck while regular query worked perfectly. Is this related to the way you set a prepared statement?

Thanks!

mauricio commented 7 years ago

@tpraizler shouldn't be, there's only one way to setup prepared statements on a table so anything that sets up prepared statements should cause the same issue to happen.

gabfssilva commented 6 years ago

Probably this is caused because of the numeric type mapping. This PR fixes the issue: https://github.com/mauricio/postgresql-async/pull/231.

@mauricio, can you merge this PR?

oshai commented 5 years ago

I fixed/reverted this in jasync-sql(fork) 0.8.30: https://github.com/jasync-sql/jasync-sql/pull/16 . more details in the issue: https://github.com/jasync-sql/jasync-sql/issues/15