search

ibarwick / firebird_fdw

A PostgreSQL foreign data wrapper (FDW) for Firebird - latest version 1.4.0 (2024-05-11)
https://sql-info.de/postgresql/firebird-fdw/index.html
Other
36 stars 9 forks source link

limit rows returned #44

Open ablomeke opened 4 months ago

ablomeke commented 4 months ago

We use firebird_fdw to connect our postgres instance to a fairly large-sized firebird instance(s). When one of our users makes a query that calls a significantly-large dataset, the query will spin and eventually cause a memory leak that will cause oomkiller to terminate the process. Doing that results in the database crashing and going into recovery mode. We are investigating ways to mitigate this, including communicating with users to appropriately limit their queries to something that will return, but try as we might, people write queries they shouldn't. Doing so shouldn't result in a crash of the database.

We have similar queries every now and again in our postgres fdws, but they seem to handle themselves much better. I am curious if part of the reason why is that postgres_fdw has the fetch_size parameter that allows you to limit the number of rows that are pulled in a single query. Doing that seems to slow things down enough for us to be able to catch the issue and deal with it properly before things spiral out of control affecting other users, or allows postgres to manage it more appropriately in memory. Either way, I'm wondering if that feature can be implemented in firebird_fdw, or if some other means of managing memory usage can be created.

Let me know what, if any, log files or error messages I can provide.

ibarwick commented 4 months ago

Thanks for the report. With this kind of issue, the first step to finding the right fix is being able to reproduce it, so what would be very helpful is some indication of the size of "a fairly large-sized firebird instance", or more specifically the approximate size and definition of the tables being queried (field names etc. anonymized if appropriate), and a sample query which triggers the issue.

Other information which would be helpful:

Feel free to send me anything you don't want to attach here to: barwick [a] gmail.com.

ablomeke commented 4 months ago

I will work on getting the version details to you tomorrow. The queries and tables vary, but the important thing is that the results returned be more than the total memory capacity of the server(plus swap space. Once it hits that limit oomkiller terminates the thread and brings the whole DB down.

On Wed, Feb 7, 2024, 16:37 Ian Barwick @.***> wrote:

Thanks for the report. With this kind of issue, the first step to finding the right fix is being able to reproduce it, so what would be very helpful is some indication of the size of "a fairly large-sized firebird instance", or more specifically the approximate size and definition of the tables being queried (field names etc. anonymized if appropriate), and a sample query which triggers the issue.

Other information which would be helpful:

  • firebird_fdw version(s) being used (and how installed, e.g. from package or source)
  • PostgreSQL version(s) firebird_fdw is running ion
  • Firebird version(s) being queried
  • sample PostgreSQL log file(s) output from around the time the issue occurs (redacted/anonymized if appropriate).

Feel free to send me anything you don't want to attach here to: barwick [a] gmail.com.

— Reply to this email directly, view it on GitHub https://github.com/ibarwick/firebird_fdw/issues/44#issuecomment-1933174304, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABNVHZGTT5SHDNAH5BFTSD3YSQM37AVCNFSM6AAAAABC6NJSW2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSMZTGE3TIMZQGQ . You are receiving this because you authored the thread.Message ID: @.***>

surfcode commented 3 months ago

the important thing is that the results returned be more than the total memory capacity of the server(plus swap space. Once it hits that limit oomkiller terminates the thread and brings the whole DB down.

I experienced the exact same problem. Today I had the need to query for an additional year of data which naturally results in more rows getting returned from firebird to postgres. This resulted in a crash of postgres and indeed, the database entered recovery mode.

Increasing the server memory (from 1GB to 2GB on a DO Droplet in this case) solved the problem so I'm not bothered too much but if I can help in making this awesome wrapper more resilient, this was the info I gathered:

dmesg | grep -A2 Kill Out of memory: Killed process 370357 (postgres) total-vm:2821968kB, anon-rss:690616kB, file-rss:324kB, shmem-rss:104kB, UID:113 pgtables:5008kB oom_score_adj:0