I need to fetch all the records from databse through perl. Currently, i am getting a out of memory issue. Any other best alternative way to read all the records.
Query to fetch all records from the table.
select cpu_id, system, generation, vendor, item, week_first_moved, week_last_moved from us_item_tbl
Rows :5812750
Fetching all records through below code and getting out of memory:
sub sane {
my $self = shift;
my %lookup;
my $dbh = DBI->connect ('dbi:Oracle:usbmfs', 'US', 'states', {AutoCommit => 0, RaiseError => 1});
my $sth = $dbh->prepare (qq{ select cpu_id, system, generation, vendor, item, week_first_moved, week_last_moved from us_item_tbl});
$sth->execute();
my $rows = @{$dbh->selectall_arrayref('select upc_id, system, generation, vendor, item, week_first_moved, eek_last_moved from uk_item_tbl')};
foreach my $row (@$rows) {
my($cpu, sys, $gen, $vend, $item, $wad, $wlm) =@$rows;
my $ean = sprintf "%02s%05s%05s", $sys, $vend, $item;
$cpu = sprintf "%014s", $cpu;
$lookup{$nae}{$cpu} = [$wad, $wlm];
}
}
(it looks very much like it is ), and assuming that is the case , I would appreciate if you took the time to respond to the questions/suggestions made there first.
""....I have tried the below command and getting the out of memory issues. I am not sure how to clear the cache to get rid of the out of the memory issue...""
I asked you supply details of that which you failed to do and now this post ...
I recommend you merge this into that request and we can all move forwards.
Sorry for the late response. Due to some issues not able to respond to your response. I apologize for it.
Yes we can merge the request. I have tried to change fetchrow_* and getting error. Please find my output details.
Currently, i am getting out of memory issues in the below line.
$lookup{$ean}{$cpu} = [$wad, $wlm];
Do we have any replacement for the above lookup line.
Normally, when I run across these issues, which is rare because I usually buy a lot of RAM for my machines, since RAM is so cheap these days, I count the size of the array or the hash and print the count.
Then, I simply do a quick experiment, and /or change the query (debug) and count the results of the SELECT statement (without actually) doing the full SELECT; to get an idea on the size of the hash or array. See reference below.
Finally, you are posting about "out of memory" but you have failed to post any details about your system memory and swap space.
This would be one of the first things I would post @nmkl2021, if I was asking others to help me with an "out of memory" issue. Details matter.
For example:
"Hey guys, I'm getting OOM on a DB query using PERL. The total RAM in my machine is 64GB and there is 2GB of swap configured. It's Ubuntu. I added the PERL Memory:Process class and checked the memory before the query and then before the line where the error occurs, and the total used system memory when from 48GB to .... "
Operating system : HP UNIX
swapinfo
Kb Kb Kb PCT START/ Kb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 16777216 373700 16403516 2% 0 - 1
reserve - 6528164 -6528164
memory 7985408 2809984 5175424 35%
Operating systems have version numbers. HP UNIX could be anything since HP has been selling unix systems for as long as I can remember.
I strongly suggest to you @nmkl2021, that you should answer all moderator questions with full details and not pick-and-choose which questions to reply to and nor should you reply with partial details.
Not sure how long you have been in the IT business, but details matter when you are seeking support.