Обсуждение: Out of memory troubles with version 8.01.01.02
Hi All, I'm trying to fetch a table (simple select * from table) which is 102 columns and some 3.5 million rows. When running this I always end up having a "out of memory". (when I put a where statement or a limit in the SQL, it runs fine). I've tried tweaking the settings for the odbc setting in windows (is windows xp) and upgraded to version 8.01.01.02 (even tried to 103 snapshot), but the problem remains. I've attached both log files created by the odbc driver. I hope someone can give me some hints on way this is happening. I'm accessing the odbc dsn from within SAS 9.1. Many thanks, Filip
Вложения
> I'm trying to fetch a table (simple select * from table) which is 102 > columns and some 3.5 million rows. When running this I always end up > having a "out of memory". (when I put a where statement or a limit in the > SQL, it runs fine). I've tried tweaking the settings for the odbc setting > in windows (is windows xp) and upgraded to version 8.01.01.02 (even tried > to 103 snapshot), but the problem remains. I've attached both log files > created by the odbc driver. I hope someone can give me some hints on way > this is happening. I'm accessing the odbc dsn from within SAS 9.1. Hello, I know about this limitation. There is problem ODBC keep all rows in memory (+ result from libpq = 2 x result). Please try check Use Declare/Fetch option. There is no twice result in memory in psqlODBC. If it doesn't help you can try older releases which mayde do not do this. Please try 8.00.X or even 7.03.209 (if you find it somewhere). I know we're suggesting 8.01.X but few people work with so big datasets. You can buy more memory too (only as quick hack if you need it fast). We have a lot of bug reports (and so few developers) so I don't know when we focus on this problem. Regards, Luf
Ludek Finstrle <luf@pzkagis.cz> writes: > I know about this limitation. There is problem ODBC keep all rows > in memory (+ result from libpq = 2 x result). Please try check > Use Declare/Fetch option. There is no twice result in memory in psqlODBC. > If it doesn't help you can try older releases which mayde do not do this. > Please try 8.00.X or even 7.03.209 (if you find it somewhere). > I know we're suggesting 8.01.X but few people work with so big > datasets. Does it keep everything in memory when using cursors too? Tomas
> > I know about this limitation. There is problem ODBC keep all rows > > in memory (+ result from libpq = 2 x result). Please try check > > Use Declare/Fetch option. There is no twice result in memory in psqlODBC. > > If it doesn't help you can try older releases which mayde do not do this. > > Please try 8.00.X or even 7.03.209 (if you find it somewhere). > > I know we're suggesting 8.01.X but few people work with so big > > datasets. > > Does it keep everything in memory when using cursors too? New releases (8.01.X) keep everything in memory. There is no option to change it. I have an idea how to do it but time ... Older releases (8.00.X or 7.X - not based on libpq) maybe do not keep everything in memory. Regards, Luf
Hi all, Thank you all for your prompt replies. I tried about all the versions I could find (ranging from 7.3.200 to till 8.1.1.03) and they all gave the same trouble. Then I tried the latest snapshot, 8.1.1.04,and youhouuuuu, it worked!!! Me a very happy man. I'm not sure what you did we these dlls, but I just pulled in a +/- 25 milj row table in sas through the odbc (is against a postgresql 8.0.3 amd64 on freebsd 6.0) and it worked perfectly. Memory consumption never went over 45M in windows. The table takes up 8.6GB of harddisk space in windows. Just for future references (in case someone ever has the same error), I used the following settings: PostgreSQL ANSI with the following options: - disable genetic optimizer - KSQO - Recognize Uniq Index - Use Declare/Fetch - Inknown sizes: max - Bools as char - maxvarchar: 254 - cache: 100 - max long varchar: 8190 - systableprefixes: dd_; - show system tables - LF <-> CR/LF - row versioning - int8 as default - protocol >= 6.4 - OID options: show column & fake index Thanks again, Filip Tomas Skäre wrote: > Ludek Finstrle <luf@pzkagis.cz> writes: > > >> I know about this limitation. There is problem ODBC keep all rows >> in memory (+ result from libpq = 2 x result). Please try check >> Use Declare/Fetch option. There is no twice result in memory in psqlODBC. >> If it doesn't help you can try older releases which mayde do not do this. >> Please try 8.00.X or even 7.03.209 (if you find it somewhere). >> I know we're suggesting 8.01.X but few people work with so big >> datasets. >> > > Does it keep everything in memory when using cursors too? > > > Tomas > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
Hello, I write this only for clarify. > Then I tried the latest snapshot, 8.1.1.04,and youhouuuuu, it worked!!! > Me a very happy man. > I'm not sure what you did we these dlls, but I just pulled in a +/- 25 Agh, you don't have problem that the result doesn't fit into memory. Your problem was reported by Dave some days ago. Driver didn't correctly recognize when it end fetching rows. Driver fetch same rows forever (which after some time have to lead to memory problem). This problem was reported as SQLTables doesn't end or something similar. > - Use Declare/Fetch This is the option which lead to the problem with older releases. Regards, Luf