Re: Memory full
Hi,
Here is an example of a program and a perl module that parses a .xls file
and eats the whole memory.
I have tried it under Linux and Windows and it has the same problem under
both OSs, so it has big bugs.
I have tried using that destructor class in the perl module but I think what
I have done is not correct.
Thank you for telling me how to solve this issue.
The program executes a method from the module but it doesn't need any result
of that method, because it just parse a .xls file, get some results and
inserts them into MySQL, then the program does the same with the next file,
and so on.
The program:
use lib .;
use strict;
use Parse;
my $f = Parse-new;
opendir(DIR, .);
my @files = readdir DIR;
closedir DIR;
foreach my $file(@files) {
next unless $file =~ /\.xls$/i;
$f-parse($file);
}
The module:
package Parse;
use strict;
use Spreadsheet::ParseExcel;
use DBI;
my $dbh = DBI-connect(DBI:mysql:database=bvb;host=127.0.0.1, root,
undef, {RaiseError=1, PrintError=0});
sub new {
my $init = shift;
my $class = ref($init) || $init;
my $self = { @_ };
return bless($self, $class);
}
sub parse {
my $self = shift;
my $file = shift;
my $book = Spreadsheet::ParseExcel::Workbook-Parse($file);
my $sheet = ${$book-{Worksheet}}[0];
my %f;
for(my $i = 0; $i = 1000; $i++) {
my ($cod, $name);
my $market = $sheet-{Cells}[$i][3]-{Val};
if ($market =~ /total|regular|deal|odd lot/i and
$sheet-{Cells}[$i][6]-{Val} =~ /^[\d,.]+$/) {
if ($sheet-{Cells}[$i][1]-{Val} =~ /^\w{3,4}[\*\+]?$/) {
$cod = $sheet-{Cells}[$i][1]-{Val};
$cod =~ s/[\*\+]$//;
$name = $sheet-{Cells}[$i][2]-{Val};
}
elsif ($sheet-{Cells}[$i-1][1]-{Val} =~ /^\w{3,4}[\*\+]?$/) {
$cod = $sheet-{Cells}[$i-1][1]-{Val};
$cod =~ s/[\*\+]$//;
$name = $sheet-{Cells}[$i-1][2]-{Val};
}
elsif ($sheet-{Cells}[$i-2][1]-{Val} =~ /^\w{3,4}[\*\+]?$/) {
$cod = $sheet-{Cells}[$i-2][1]-{Val};
$cod =~ s/[\*\+]$//;
$name = $sheet-{Cells}[$i-2][2]-{Val};
}
elsif ($sheet-{Cells}[$i-3][1]-{Val} =~ /^\w{3,4}[\*\+]?$/) {
$cod = $sheet-{Cells}[$i-3][1]-{Val};
$cod =~ s/[\*\+]$//;
$name = $sheet-{Cells}[$i-3][2]-{Val};
}
$f{$cod}{$market}{name} = $name;
$f{$cod}{$market}{vn} = $sheet-{Cells}[$i][4]-{Val};
$f{$cod}{$market}{per} = $sheet-{Cells}[$i][5]-{Val};
$f{$cod}{$market}{close} = $sheet-{Cells}[$i][6]-{Val};
$f{$cod}{$market}{change} = $sheet-{Cells}[$i][7]-{Val};
$f{$cod}{$market}{open} = $sheet-{Cells}[$i][8]-{Val};
$f{$cod}{$market}{max} = $sheet-{Cells}[$i][9]-{Val};
$f{$cod}{$market}{min} = $sheet-{Cells}[$i][10]-{Val};
$f{$cod}{$market}{average} = $sheet-{Cells}[$i][11]-{Val};
$f{$cod}{$market}{volume} = $sheet-{Cells}[$i][12]-{Val};
$f{$cod}{$market}{value} = $sheet-{Cells}[$i][13]-{Val};
$f{$cod}{$market}{trades} = $sheet-{Cells}[$i][14]-{Val};
$f{$cod}{$market}{max52} = $sheet-{Cells}[$i][15]-{Val};
$f{$cod}{$market}{min52} = $sheet-{Cells}[$i][16]-{Val};
$f{$cod}{$market}{van} = $sheet-{Cells}[$i][17]-{Val};
}
}
my ($year, $mon, $mday) = $file =~ /trades(\d\d\d\d)(\d\d)(\d\d)\.xls$/gi;
my $date = $mon/$mday/$year;
#Create the table if it is not there
my $rapoarte_t = $dbh-do(create table if not exists temp_rapoarte(
symbol varchar(10) not null,
name varchar(255) not null,
market varchar(10) not null,
date date not null,
open varchar(20) not null,
high varchar(20),
low varchar(20),
close varchar(20) not null,
volume varchar(20) not null,
vn varchar(20),
per varchar(20),
change_ varchar(20),
average_ varchar(20),
value_ varchar(30) not null,
trades varchar(5),
min52 varchar(20),
max52 varchar(20),
van varchar(20),
primary key(symbol, date, market)
));
#Insert into database
my $rapoarte_i = $dbh-prepare(insert ignore into temp_rapoarte values(?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?));
foreach my $cod(sort keys %f) {
foreach my $market(keys %{$f{$cod}}) {
$rapoarte_i-execute($cod, $f{$cod}{$market}{name}, $market,
$year-$mon-$mday, $f{$cod}{$market}{open}, $f{$cod}{$market}{max},
$f{$cod}{$market}{min}, $f{$cod}{$market}{close}, $f{$cod}{$market}{volume},
$f{$cod}{$market}{vn}, $f{$cod}{$market}{per}, $f{$cod}{$market}{change},
$f{$cod}{$market}{average}, $f{$cod}{$market}{value},
$f{$cod}{$market}{trades}, $f{$cod}{$market}{min52},
$f{$cod}{$market}{max52}, $f{$cod}{$market}{van});
}
}
}
sub DESTROY {
my $self = shift;
undef $self;
#I don't know if this sub helps, or if it is correctly written
}
1;
I can send you a .xls file if you think it will help, but I guess the errors
of my program or module are obvious.
Thank you.
Teddy
- Original Message -
From: Jay [EMAIL PROTECTED]
To: Perl Beginners List beginners@perl.org
Sent: Wednesday, January 12, 2005 12:22 AM
Subject: Re: Memory full
On Mon, 10 Jan 2005 12:53:50 +0200, Octavian Rasnita [EMAIL PROTECTED]
wrote:
Hi,
I have made a perl module object oriented which is something like this:
Then I access the parse() method in another program that feeds the
module
with more Excel files (around 500 files).
After more files parsed (around 300), the memory
Re: Memory full
Here is an example of a program and a perl module that parses a .xls file
and eats the whole memory.
I have tried it under Linux and Windows and it has the same problem under
both OSs, so it has big bugs.
[snip]
#Insert into database
my $rapoarte_i = $dbh-prepare(insert ignore into temp_rapoarte values(?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?));
foreach my $cod(sort keys %f) {
foreach my $market(keys %{$f{$cod}}) {
$rapoarte_i-execute(...);
}
}
You should call $rapoarte_i-finish() here. I know if you're using
Oracle, not finishing will keep cursors open and that could
potentially be hogging memory like you're experiencing. Not sure about
other databases. What database are you using? Let us know if this
solves your problem.
sub DESTROY {
my $self = shift;
undef $self;
#I don't know if this sub helps, or if it is correctly written
}
This is correctly written, but it's kind of redundant... because if
something is getting garbage collected, it's going to get undef'd
anyway. Unless you have circular references or some other oddity (see
Programming Perl v3, chapter 12). Which it doesn't look like is the
case, at first glance.
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/ http://learn.perl.org/first-response
Re: Memory full
On Wed, 12 Jan 2005 12:24:00 +0200, Octavian Rasnita [EMAIL PROTECTED] wrote:
The program:
use lib .;
use strict;
use Parse;
my $f = Parse-new;
opendir(DIR, .);
my @files = readdir DIR;
closedir DIR;
foreach my $file(@files) {
next unless $file =~ /\.xls$/i;
$f-parse($file);
}
sub DESTROY {
my $self = shift;
undef $self;
#I don't know if this sub helps, or if it is correctly written
}
Teddy,
There are a couple of things going on here. The last one is the
destructor. Perl should Garbage collect automatically. The only time
you need to give it a push, is if you end up creating references to
references. the Garabge collector does actually get trid of things,
it just lowers their refcount by one. Now that youwe've seen you
code, this probably isn't your problem, and messing with $self is
dangerous if you don't need to do it.
You're real issue here is scoping. Once you call new, that object
stays in scope until it passes out of scope. You call new at the
beginning of the program, and then go into a forecah loop, and keep
adding refferences to Workbook-Parse() objects, and then garbage
collection isn't done until the end. This is what allows you to only
open the database connection once, but it causes things to pile up in
memory.
If you don't mind incurring the extra overhead on the database server, move
$f = Parse-new ;
Inside the foreach loop. This will ensure that that the Parse object
is destroyed at the end of each iteration:
foreach my $file(@files) {
next unless $file =~ /\.xls$/i;
$f = Parse-new ;
$f-parse($file);
}
If you want to keep the database connection open, you need to make
sure that the reference counts of $f and $book fall to zero each time
sub Parse exits. Because they're references, they don't go out of
scope when the subroutine exits, so they and the Workbook-Parse
objects keep piling up. when you're debugging, you can use
Devel::Peek to keep track of the references and make sure the counts
go down. Also take a look at Chapter 11 and particularly recipe 11.5
in the _Perl Cookbook_.
HTH,
--jay
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/ http://learn.perl.org/first-response
Re: Memory full
On Mon, 10 Jan 2005 12:53:50 +0200, Octavian Rasnita [EMAIL PROTECTED] wrote:
Hi,
I have made a perl module object oriented which is something like this:
Then I access the parse() method in another program that feeds the module
with more Excel files (around 500 files).
After more files parsed (around 300), the memory is full.
Please tell me what can I do to free the memory after each file parsed by
the module.
I run this program manually or by a cron job, but I would like to know
better how to free the memory because I might want to use it under mod_perl.
Thank you very much for any tip.
Teddy
Teddy,
This is one of the tricky parts of objects and pass-by-reference
applications: a refence never goes out of scope, so it's memory is
never reclaimed. Think about what would happen, for example, if you
created 500 hundred references to DBD::mysql handles, or similar, and
never called $handle-disconnect() on any of them.
What you need to do is write a method that, when called, will make
sure that all that everything associated with the object it's passed
is dereferenced. In the main program, you can call it explicitly when
you're done with each object when you're done with it.
When you call it, the code should look something like this:
foreach (@files) {
my $obj = yourmod-parse($_) ;
# do something to $obj
$obj-destroy() or warn can't dereference ;
}
HTH
--jay
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/ http://learn.perl.org/first-response
Memory full
Hi,
I have made a perl module object oriented which is something like this:
use Spreadsheet::ParseExcel;
use DBI;
...
sub new {
...
}
sub parse {
my $dbh = ...;
#here some Excel files parses and database updates...
}
Then I access the parse() method in another program that feeds the module
with more Excel files (around 500 files).
After more files parsed (around 300), the memory is full.
Please tell me what can I do to free the memory after each file parsed by
the module.
I run this program manually or by a cron job, but I would like to know
better how to free the memory because I might want to use it under mod_perl.
Thank you very much for any tip.
Teddy
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/ http://learn.perl.org/first-response
Re: Memory full
From: zentara [EMAIL PROTECTED] On Mon, 10 Jan 2005 12:53:50 +0200, [EMAIL PROTECTED] (Octavian Rasnita) wrote: Hi, I have made a perl module object oriented which is something like this: use Spreadsheet::ParseExcel; use DBI; Then I access the parse() method in another program that feeds the module with more Excel files (around 500 files). After more files parsed (around 300), the memory is full. Please tell me what can I do to free the memory after each file parsed by the module. I run this program manually or by a cron job, but I would like to know better how to free the memory because I might want to use it under mod_perl. Thank you very much for any tip. Maybe you can get something out of http://perlmonks.org/index.pl?replies=1;displaytype=print;node_id=3797 43 Reducing the memory usage of Spreadsheet::ParseExcel Good to know, but this'd require rather big changes in the script. Besides it doesn't seem the problem is in parsing a single file. It seems there is a memory leak either in the script or in the module that causes the memory not to be freed when done with a file. Octavian, could you try to create a minimal example that displays this behaviour? Or, if not too long, show us the actual code? Jenda = [EMAIL PROTECTED] === http://Jenda.Krynicky.cz = When it comes to wine, women and song, wizards are allowed to get drunk and croon as much as they like. -- Terry Pratchett in Sourcery -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] http://learn.perl.org/ http://learn.perl.org/first-response
