controlling memory management with regard to a specific query (or groups of connections)
As a temporary fix I need to write some uploaded image files to PostgreSQL until a task server can read/process/delete them.
The problem I've run into (via server load tests that model our production environment), is that these read/writes end up pushing the indexes used by other queries out of memory -- causing them to be re-read from disk. These files can be anywhere from 200k to 5MB.
has anyone dealt with situations like this before and has any suggestions? I could use a dedicated db connection if that would introduce any options.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 11/18/2015 5:10 PM, Jonathan Vanasco wrote:
As a temporary fix I need to write some uploaded image files to PostgreSQL until a task server can read/process/delete them.
The problem I've run into (via server load tests that model our production environment), is that these read/writes end up pushing the indexes used by other queries out of memory -- causing them to be re-read from disk. These files can be anywhere from 200k to 5MB.
has anyone dealt with situations like this before and has any suggestions? I could use a dedicated db connection if that would introduce any options.
We have a system that loads a bunch of files up to be processed - we
queue them for processing behind the scenes. We don't load them into
Postgres before processing. We put them in a temp directory and just
save the location of the file to the database. This configuration does
have limitations. Post-processing can not be load balanced across
servers unless the temp directory is shared.
I'm sure you'll get more DB centric answers from others on the list.
Roxanne
--
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching them the science.
Donald Knuth
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Thanks. Unfortunately, this is in a clustered environment. NFS and other shared drive systems won't scale well. I'd need to run a service that can serve/delete the local files, which is why I'm just stashing it in Postgres for now.
On Nov 19, 2015, at 2:26 AM, Roxanne Reid-Bennett <rox@tara-lu.com> wrote:
We have a system that loads a bunch of files up to be processed - we queue them for processing behind the scenes. We don't load them into Postgres before processing. We put them in a temp directory and just save the location of the file to the database. This configuration does have limitations. Post-processing can not be load balanced across servers unless the temp directory is shared.
I'm sure you'll get more DB centric answers from others on the list.
Roxanne
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, 18 Nov 2015 20:10:00 -0500
Jonathan Vanasco <postgres@2xlp.com> wrote:
As a temporary fix I need to write some uploaded image files to PostgreSQL until a task server can read/process/delete them.
The problem I've run into (via server load tests that model our production environment), is that these read/writes end up pushing the indexes used by other queries out of memory -- causing them to be re-read from disk. These files can be anywhere from 200k to 5MB.
has anyone dealt with situations like this before and has any suggestions? I could use a dedicated db connection if that would introduce any options.
PostgreSQL doesn't have any provisions for preferring one thing
or another for storing in memory.
The easiest thing I can think would be to add memory to the machine
(or configure Postgres to use more) such that those files aren't
pushing enough other pages out of memory to have a problematic
impact.
Another idea would be to put the image database on a different
physical server, or run 2 instances of Postgres on a single
server with the files in one database configured with a low
shared_buffers value, and the rest of the data on the other
database server configured with higher shared_buffers.
I know these probably aren't the kind of answers you're looking
for, but I don't have anything better to suggest; and the rest
of the mailing list seems to be devoid of ideas as well.
--
Bill Moran
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 11/19/2015 12:29 PM, Bill Moran wrote:
On Wed, 18 Nov 2015 20:10:00 -0500
Jonathan Vanasco <postgres@2xlp.com> wrote:As a temporary fix I need to write some uploaded image files to PostgreSQL until a task server can read/process/delete them.
The problem I've run into (via server load tests that model our production environment), is that these read/writes end up pushing the indexes used by other queries out of memory -- causing them to be re-read from disk. These files can be anywhere from 200k to 5MB.
... PostgreSQL doesn't have any provisions for preferring one thing or
another for storing in memory. The easiest thing I can think would be
to add memory to the machine (or configure Postgres to use more) such
that those files aren't pushing enough other pages out of memory to
have a problematic impact.
Perhaps this is just noise - but how is "just a" 5Mb file upload pushing
critical matter out of memory ? Throttle your file uploads ...
Roxanne
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Nov 18, 2015 at 5:10 PM, Jonathan Vanasco <postgres@2xlp.com> wrote:
As a temporary fix I need to write some uploaded image files to PostgreSQL until a task server can read/process/delete them.
The problem I've run into (via server load tests that model our production environment), is that these read/writes end up pushing the indexes used by other queries out of memory -- causing them to be re-read from disk. These files can be anywhere from 200k to 5MB.
Are you storing them as large object, or as bytea? Can you share the
load testing scripts?
Cheers,
Jeff
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general