out of memory for query result
out of memory for query result
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.
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.
-
- Enthusiast
- Posts: 271
- Joined: Thu Dec 17, 2009 12:00 pm
- Location: Llangadog, Wales, UK
- Contact:
Re: out of memory for query result
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.
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.
Re: out of memory for query result
ThanQ for reply,
for now I just select selectively from 1st element to half and then remaining.
But would be great if better solution.
for now I just select selectively from 1st element to half and then remaining.
But would be great if better solution.
Re: out of memory for query result
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.
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.
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
Re: out of memory for query result
This could be a 32-bit\64-bit problem. 32-bit has a memory limitation.
Sveinung
Sveinung
Re: out of memory for query result
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.
All records should be select and processed.
Re: out of memory for query result
If you must use 32-bit, you will have problems with objects >1Gb. What lib is the one that is 32-bit?
Sveinung
Sveinung
Re: out of memory for query result
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
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
Re: out of memory for query result
What type of data is the blob(like a pictrue or something)? Can you export it to a file, using lo_export?
Sveinung
Sveinung
Re: out of memory for query result
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.
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.
- RichAlgeni
- Addict
- Posts: 914
- Joined: Wed Sep 22, 2010 1:50 am
- Location: Bradenton, FL
Re: out of memory for query result
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!
The 'explain analyze' process if your friend!
Re: out of memory for query result
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.
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.
- RichAlgeni
- Addict
- Posts: 914
- Joined: Wed Sep 22, 2010 1:50 am
- Location: Bradenton, FL
Re: out of memory for query result
Very good! Always learn!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.