out of memory for query result

Windows specific forum
LiK137
Enthusiast
Enthusiast
Posts: 279
Joined: Wed Jun 23, 2010 5:13 pm

out of memory for query result

Post by LiK137 »

Hi,
I am processing all blob data in huge PostgreSQL table of 200K records.
so I select * from thisTable and process record up to last in table.
I receive "out of memory for query result" in DatabaseError()
I have no idea how to fix this problem and many thanx for replys.
deeproot
Enthusiast
Enthusiast
Posts: 269
Joined: Thu Dec 17, 2009 12:00 pm
Location: Llangadog, Wales, UK
Contact:

Re: out of memory for query result

Post by deeproot »

Only a suggestion based on what works for me - others may have better ideas -

I'm using SQLite rather than PostreSQL but I'd imagine any system could get a memory problem if pulling a large number of BLOB rows.

Firstly - I split out the large BLOB data into a separate DB file and ATTACH it to the "main" DB which has other types of data tables. I don't know the technical reasons, but did some tests many years ago and found this gave better overall query performance for all tables.

In the BLOB DB file there are 2 tables - the BLOB data in a table of it's own with just a numeric key (the rowid) and the BLOB column itself. A 2nd table is maintained with exactly the same rowid key. This table also has any text or number data columns that relate to the BLOBs, but the rows are quite small. All processing is done on this smaller table and then when needed a single record query using the rowid handles the individual BLOB, one at a time.
LiK137
Enthusiast
Enthusiast
Posts: 279
Joined: Wed Jun 23, 2010 5:13 pm

Re: out of memory for query result

Post by LiK137 »

ThanQ for reply,
for now I just select selectively from 1st element to half and then remaining.
But would be great if better solution.
User avatar
Shield
Addict
Addict
Posts: 1021
Joined: Fri Jan 21, 2011 8:25 am
Location: 'stralia!
Contact:

Re: out of memory for query result

Post by Shield »

This question is impossible to answer without more details on what the tables look like,
what data we're talking about (size) and how the database is configured.

Anyway:
- You do SELECT *...do you really need all rows? SELECT * is almost always a waste of resources.
- If you get a memory error on the server you may be able to fix this by throwing more at it.
Maybe your server isn't configured appropriately and you may just need to give it more memory to work with.
Image
Blog: Why Does It Suck? (http://whydoesitsuck.com/)
"You can disagree with me as much as you want, but during this talk, by definition, anybody who disagrees is stupid and ugly."
- Linus Torvalds
User avatar
Sveinung
Enthusiast
Enthusiast
Posts: 142
Joined: Tue Oct 07, 2003 11:03 am
Location: Bergen, Norway

Re: out of memory for query result

Post by Sveinung »

This could be a 32-bit\64-bit problem. 32-bit has a memory limitation.

Sveinung
LiK137
Enthusiast
Enthusiast
Posts: 279
Joined: Wed Jun 23, 2010 5:13 pm

Re: out of memory for query result

Post by LiK137 »

Because of library is 32bit I cannot move to 64bit so I am oliged to stay in 32bit and need 32bit solution.
All records should be select and processed.
User avatar
Sveinung
Enthusiast
Enthusiast
Posts: 142
Joined: Tue Oct 07, 2003 11:03 am
Location: Bergen, Norway

Re: out of memory for query result

Post by Sveinung »

If you must use 32-bit, you will have problems with objects >1Gb. What lib is the one that is 32-bit?

Sveinung
LiK137
Enthusiast
Enthusiast
Posts: 279
Joined: Wed Jun 23, 2010 5:13 pm

Re: out of memory for query result

Post by LiK137 »

Not only library but os running application also 32bit and device attached to it only has 32bit drivers.
Manufacturer isn't going to make 64 bit. Anyway it has to be 32bit.
The bottleneck is manufacturer drivers.
PureBasic is 5.44LTS 32 bit with DroopyHmod

2nd problem I have is that I couldn't find 64 bit droopy for Purebasic 5.44 LTS
User avatar
Sveinung
Enthusiast
Enthusiast
Posts: 142
Joined: Tue Oct 07, 2003 11:03 am
Location: Bergen, Norway

Re: out of memory for query result

Post by Sveinung »

What type of data is the blob(like a pictrue or something)? Can you export it to a file, using lo_export?

Sveinung
LiK137
Enthusiast
Enthusiast
Posts: 279
Joined: Wed Jun 23, 2010 5:13 pm

Re: out of memory for query result

Post by LiK137 »

its binary file of 15kb
Again, on 64bit (not considering driver usage) everything OK.
On 32 bit processing with portioning again OK.
But may be there is a better way to handle all data in database faster than portioning.

I each time open connection, select portion, process, close connection, repeat until last record.
User avatar
RichAlgeni
Addict
Addict
Posts: 914
Joined: Wed Sep 22, 2010 1:50 am
Location: Bradenton, FL

Re: out of memory for query result

Post by RichAlgeni »

This may be the problem, however it is always best (from experience) to create an index for as many of your queries as possible!

The 'explain analyze' process if your friend!
LiK137
Enthusiast
Enthusiast
Posts: 279
Joined: Wed Jun 23, 2010 5:13 pm

Re: out of memory for query result

Post by LiK137 »

Hi RichAlgeni,
ThanQ for reply.
The problem is solved by optimizing variables.
Somewhere inside code long needed and somewhere integer.
That was the problem causing buffer overflaw and memory being unusable.
User avatar
RichAlgeni
Addict
Addict
Posts: 914
Joined: Wed Sep 22, 2010 1:50 am
Location: Bradenton, FL

Re: out of memory for query result

Post by RichAlgeni »

LiK137 wrote:Hi RichAlgeni,
ThanQ for reply.
The problem is solved by optimizing variables.
Somewhere inside code long needed and somewhere integer.
That was the problem causing buffer overflaw and memory being unusable.
Very good! Always learn!
Post Reply