Обсуждение: Out of memory running 560 MB query

Поиск
Список
Период
Сортировка

Out of memory running 560 MB query

От
"Morgan Ramsay"
Дата:

I have 560 MB worth of INSERT statements in a .sql file. I’m trying to run that query but I’m getting an out of memory error.

 

Any idea how to successfully run a query that large? What’s the size limit of a query?

 

—Morgan

Re: Out of memory running 560 MB query

От
Rosser Schwarz
Дата:
On Sun, Sep 14, 2014 at 5:18 PM, Morgan Ramsay <morgan.ramsay@gmail.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

Re: Out of memory running 560 MB query

От
"Morgan Ramsay"
Дата:

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.

 

—Morgan

 

 

From: Rosser Schwarz [mailto:rosser.schwarz@gmail.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.ramsay@gmail.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

Re: Out of memory running 560 MB query

От
Rajesh Madiwale
Дата:
On Mon, Sep 15, 2014 at 6:23 AM, Morgan Ramsay <morgan.ramsay@gmail.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.schwarz@gmail.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.ramsay@gmail.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