Ecto performance issues

[adr...@releasequeue.com]

Hello,

I'm having some performance issues with Ecto, i have a table which can have a bit more data, up to 64k per row, and it seems queries take a bit too long, like for 7000 rows it takes about 150 seconds, while connecting to the same db and giving the same query in psycopg2(python) takes only about 8-9 seconds. 

I've tried different queries from the simple MyApp.Repo.all to selecting individual columns and also different table layouts, like having the data split in multiple columns or all in one column and it seems the main thing that affects speed, is the overall amount of data retrieved.

Anyone has any advice or tips for speeding queries with Ecto?

Thanks

[José Valim]

Which Ecto version, adapter and adapter version are you using?

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Lead Developer

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/223e929c-9675-4e39-8655-aa605122842b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Adrian]

Hi,

i'm using the latest versions for Ecto: 0.12.0-rc and Postgrex: 0.8.2

[José Valim]

Given your description, it is more likely to be a bug on Ecto or the adapter itself that is being triggered in your particular case. If the data is public and it can be shared, I would love to take a look at it.

If it is not public but you are willing to share it in private, please let me know and we can continue by private e-mail. Thanks for reporting. :)

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Lead Developer

On Wed, Jun 3, 2015 at 9:28 AM, Adrian <adr...@releasequeue.com> wrote:

Hi,

i'm using the latest versions for Ecto: 0.12.0-rc and Postgrex: 0.8.2

--

You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/1d93bb84-45b8-4a8f-87b6-c094c4505297%40googlegroups.com.

[Ringo De Smet]

José,

I'm the founder of ReleaseQueue. Adrian is working for us. This is a DB for our upcoming product so I would rather like to arrange this in private. You can reach both me and Adrian with the email addresses used on this list.

Ringo

[James Fish]

Hi Adrian and Ringo,

I think the issue is how postgrex is receiving the tcp data. The default buffer in a :gen_tcp socket is 1460 bytes. When combined with [active: :once, packet: :raw], the largest amount of data that can be received in one go by the process is only 1460 bytes. Obviously if a row is 64k this is going to be very inefficient and slow.

If that is the issue this patch should help greatly: https://github.com/fishcakez/postgrex/commit/0155fb4791451be5939675917a9e18501f9f059c.

Regards,

James

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/3d866568-d23d-414c-a14e-7ad924da08d9%40googlegroups.com.

[James Fish]

You also may need to increase the `:recbuf` and `:sndbuf` in the `:socket_options` for postgrex. For more information see http://erlang.org/doc/man/inet.html#setopts-2.

[Adrian]

I'll try it out.

Thanks for the suggestion.

[Adrian]

That patch seems to have done it, more than 10x faster with it.

Thanks again

On Wednesday, 3 June 2015 11:43:11 UTC+3, James Fish wrote:

[James Fish]

That's good to hear. It should be possible to get a similar (or better result) without the patch if `socket_options: [recbuf: suitable_value_goes_here]` is added to the configuration for MyApp.Repo in your config.exs file. That will set a suitable buffer at the OS and BEAM level. Given the large amount of data being received it is likely this can be tweaked further. I will send PRs to postgrex and mariaex.

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/54a2d517-8c4f-47d8-b092-7bb1817ffc98%40googlegroups.com.

[Adrian]

Indeed the socket_options config option works also and is more tweakable too.