Thread: Memory usage / concept
Hi, I'm a novice in PostgreSql. I'm used to work with Oracle. I'm testing PostgreSql (7.2) on linux RedHat 7.1 and Solaris 8. I have some problems with large queries : for example a "select * from my_table ..." and my_table with a size of several Gb and several Mrows. If I set the ulimit to "unlimited", I overflow my memory and my swap; if I limit it, my query crashes. In fact I don't know how PG works with the memory use : with Oracle I have to manage its memory space (the SGA); is there anything like that with PG ? Thanks, Valerie. ******************************************************************** * Les points de vue exprimes sont strictement personnels et * * n'engagent pas la responsabilite de METEO-FRANCE. * ******************************************************************** * Valerie SCHNEIDER Tel : +33 (0)5 61 07 81 91 * * METEO-FRANCE / DSI/DEV Fax : +33 (0)5 61 07 81 09 * * 42, avenue G. Coriolis Email : Valerie.Schneider@meteo.fr * * 31057 TOULOUSE Cedex - FRANCE http://www.meteo.fr * ********************************************************************
hi, i think it's your client (psql) what's actually crashing. it's attempting to load all your rows into memory before displaying them. i'd recommend using select * from foo order by something limit x offset y or using cursor. i think there is no use for you to issue query like this... see: http://www.postgresql.org/idocs/index.php?queries-limit.html http://www.postgresql.org/idocs/index.php?sql-declare.html http://www.postgresql.org/idocs/index.php?sql-fetch.html hope that helps, kuba On Mon, 5 Aug 2002, Valerie Schneider DSI/DEV wrote: > Hi, I'm a novice in PostgreSql. I'm used to work with Oracle. I'm testing > PostgreSql (7.2) on linux RedHat 7.1 and Solaris 8. I have some problems > with large queries : for example a "select * from my_table ..." and my_table > with a size of several Gb and several Mrows. If I set the ulimit to > "unlimited", I overflow my memory and my swap; if I limit it, my query > crashes. In fact I don't know how PG works with the memory use : > with Oracle I have to manage its memory space (the SGA); is there anything > like that with PG ? > Thanks, Valerie. > > > > ******************************************************************** > * Les points de vue exprimes sont strictement personnels et * > * n'engagent pas la responsabilite de METEO-FRANCE. * > ******************************************************************** > * Valerie SCHNEIDER Tel : +33 (0)5 61 07 81 91 * > * METEO-FRANCE / DSI/DEV Fax : +33 (0)5 61 07 81 09 * > * 42, avenue G. Coriolis Email : Valerie.Schneider@meteo.fr * > * 31057 TOULOUSE Cedex - FRANCE http://www.meteo.fr * > ******************************************************************** > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
> >i think it's your client (psql) what's actually crashing. it's attempting > > Yes it is. But without using a cursor (and even if this kind of query isn't > recommended) is there any solution to limit the use of the memory by PG itself > (as for Oracle) ? if you want to limit memory usage of psql (the client program), use standard *nix tools - there isn't anything i know about in config etc... if you want to limit memory usage of postgresql server itself, use configuration parametrs shared_buffers and sort_mem. see: http://www.postgresql.org/idocs/index.php?runtime-config.html kuba
On Mon, 5 Aug 2002, Jakub Ouhrabka wrote: > > > >i think it's your client (psql) what's actually crashing. it's attempting > > > > Yes it is. But without using a cursor (and even if this kind of query isn't > > recommended) is there any solution to limit the use of the memory by PG itself > > (as for Oracle) ? > What sort of error does Oracle report when you try to retrieve several GB of data? Is this in it's 'command line' interface? Basically what I'm asking about is what the Client/Server protocol does. Presuming that all clients don't crash but abort the query then presumably the client library is doing the abort when it fails to allocate a chunk of storage during the recieve. I must admit to not knowing what libpq does but it certainly doesn't trap this situation and return an error to the caller. Why not? Other than the caller should be implementing sufficient intelligence in a real application to avoid the situation. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants