From: | Rajesh Madiwale <rajeshmadiwale65(at)gmail(dot)com> |
---|---|
To: | Morgan Ramsay <morgan(dot)ramsay(at)gmail(dot)com> |
Cc: | Rosser Schwarz <rosser(dot)schwarz(at)gmail(dot)com>, PgSQL ADMIN <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Out of memory running 560 MB query |
Date: | 2014-09-15 04:47:53 |
Message-ID: | CALDEMcQvfAMJgo3tvK78xqaHnu=rVgN_7xMnU86_rcbNn8bN+Q@mail.gmail.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Mon, Sep 15, 2014 at 6:23 AM, Morgan Ramsay <morgan(dot)ramsay(at)gmail(dot)com>
wrote:
> Sorry, I was running the query on what appeared to be a cached copy of the
> old table—or something.
>
>
>
> After a reboot, everything seems to be working. The admin client wasn’t
> responding but now all of my data is there.
>
>
>
> With regard to the list, I wasn’t sure whether there was a .conf setting
> that limited the size of queries or whether there was a query size limit.
>
>
Hi,
I suspect this issue occured due to no proper configuration of parameter(
shared_buffer and maintenance_work_mem) in postgresql.conf file.
For greater insert/copy operation shared_buffer and maintenance_work_mem
parameters should be configured to resolve the issue.
Regards,
Rajesh Madiwale,
Database Administrator,
Shreeyansh Technologies.
>
>
> —Morgan
>
>
>
>
>
> *From:* Rosser Schwarz [mailto:rosser(dot)schwarz(at)gmail(dot)com]
> *Sent:* Sunday, September 14, 2014 5:28 PM
> *To:* Morgan Ramsay
> *Cc:* PgSQL ADMIN
> *Subject:* Re: [ADMIN] Out of memory running 560 MB query
>
>
>
> On Sun, Sep 14, 2014 at 5:18 PM, Morgan Ramsay <morgan(dot)ramsay(at)gmail(dot)com>
> wrote:
>
> Any idea how to successfully run a query that large? What’s the size limit
> of a query?
>
>
>
> Without seeing your actual query (or a syntactically similar one, if there
> are private details you can't disclose), it's difficult to say. In
> general, though, a couple of thoughts come to mind:
>
>
>
> Can you run it as a single COPY statement, instead of however many
> individual INSERTS? Failing that, can you use the multi-row VALUES syntax
> for your INSERT?
>
>
>
> As an aside, this type of question is probably more suited for the
> pgsql-sql list, rather than -admin.
>
>
>
> rls
>
>
>
> --
> :wq
>
From | Date | Subject | |
---|---|---|---|
Next Message | Guillaume Lelarge | 2014-09-15 15:33:32 | Re: Standby is not removing restored WAL segments |
Previous Message | Morgan Ramsay | 2014-09-15 00:53:39 | Re: Out of memory running 560 MB query |