Should prepare_cached() manage its total size or memory usage?
Hello, I have a database application that selects about 50,000 rows one by one, does some process in Perl, and then executes a SELECT statement for each one, with slight variations in the SQL and parameters. I was using prepare_cached() on this repeatedly called SELECT statement. Recently, as the number of SELECTs crept upwards, the script started to die near the need with out of memory errors, like: Out of memory during request for 8864 bytes, total sbrk() is 415438848 bytes! Oddly, MRTG graphs of the machines memory usage during the run didn't show a significant fluctuation. (The machine has 2 Gigs of RAM, plus swap). Switching to use just a 'prepare()' resolved this issue, after some hours of tracking down the issue. I believe I understand what was happening-- the logic kept caching new unique handles in the cache, until the total memory usage was too much. Would it be reasonable to expect DBI to perform more gracefully in this case? (Or maybe this is more of a driver issue? I use DBD::Pg). My preference would be that I could still use prepare_cached(), but that there would be a maximum cache size, and the oldest unused handles would be auto-expired. A related question: Would the server-side-prepare feature of PostgreSQL 8.0 help with this? Thanks! Mark
adding key to DB object
Can someone explain why I can't do the following:
$ cat uu
#!/usr/bin/perl -l
use strict;
use warnings;
use DBI;
my $dbname = 'foo';
my $host = 'myhost';
my $dbh = DBI-connect(dbi:Pg:dbname=$dbname;host=$host, '', '');
print $dbh;
$dbh-{commitflag} = 1;
print commitflag is $dbh-{commitflag};
__END__
$ ./uu
DBI::db=HASH(0x82f8958)
Use of uninitialized value in concatenation (.) or string at ./uu line 14.
commitflag is
$dbh is nothing but a reference to a hash and I can do the following:
$ cat kk
#!/bin/perl -l
use strict;
use warnings;
my $hr = { 'test' = 1 };
print $hr;
$hr-{flag} = 1;
print $hr-{flag};
__END__
$ ./kk
HASH(0x814cbb8)
1
--
Brandon
Re: adding key to DB object
Brandon Metcalf wrote:
Can someone explain why I can't do the following:
print $dbh;
$dbh is a blessed object, printing it or even using Data::Dumper on it
will get you no joy.
$dbh-{commitflag} = 1;
print commitflag is $dbh-{commitflag};
$dbh attributes must be either a) upper-cased DBI attributes like
RaiseError or b) lower-cased attributes preceded by the standard prefix
for the driver in use (pg_ in this case).
$dbh is nothing but a reference to a hash and I can do the following:
No, that's wrong. It may look like a hash, but when you set or query
attributes, you are actually calling the STORE() and FETCH() methods of
the DBD and of DBI. You can only set attributes that can be handled by
the DBD and by DBI, you can't invent new attributes.
--
Jeff
Re: adding key to DB object
j == [EMAIL PROTECTED] writes:
j No, that's wrong. It may look like a hash, but when you set or query
j attributes, you are actually calling the STORE() and FETCH() methods of
j the DBD and of DBI. You can only set attributes that can be handled by
j the DBD and by DBI, you can't invent new attributes.
Ah, got it. Basically, what I'm trying to do is track whether I've
encountered any errors during a transaction within the object itself,
so I can do something like:
eval { $dbh-execute; };
$dbh-{commitflag} = 0 if $@;
Is there some existing attribute that will do what I need?
Thanks.
--
Brandon
Re: cacheing problem
On 28/03/2005, David Nicol wrote: I am using dbd::mysql on activestate perl 5.8. When I select a row in my program, change the row using the mysql administrator tool, and select the row again in the program, the results are not changed. I can get the new data by selecting a different row, then selecting the changed row again, then I get the new results, but running the same select gives a stale result. Would you care to give some more information, and show the code you use? What mysql administrator tool are you using? I cannot reproduce your problem when I change a record with mysql.exe in between two execute-fetch-finish cycli on the same prepared statement. -- felix
Re: adding key to DB object
On Tue, Mar 29, 2005 at 12:20:09PM -0600, Brandon Metcalf wrote:
j == [EMAIL PROTECTED] writes:
j No, that's wrong. It may look like a hash, but when you set or query
j attributes, you are actually calling the STORE() and FETCH() methods of
j the DBD and of DBI. You can only set attributes that can be handled by
j the DBD and by DBI, you can't invent new attributes.
Unless they start with private_.
If you enable trace you'll see a message like
$h-{commitflag}=1 ignored for invalid driver-specific attribute
Ah, got it. Basically, what I'm trying to do is track whether I've
encountered any errors during a transaction within the object itself,
so I can do something like:
eval { $dbh-execute; };
$dbh-{commitflag} = 0 if $@;
Is there some existing attribute that will do what I need?
$dbh-{private_commitflag} would work.
Tim.
Re: adding key to DB object
T == [EMAIL PROTECTED] writes: T On Tue, Mar 29, 2005 at 12:20:09PM -0600, Brandon Metcalf wrote: T j == [EMAIL PROTECTED] writes: T T j No, that's wrong. It may look like a hash, but when you set or query T j attributes, you are actually calling the STORE() and FETCH() methods of T j the DBD and of DBI. You can only set attributes that can be handled by T j the DBD and by DBI, you can't invent new attributes. T Unless they start with private_. Ah, nice. That's what I need. -- Brandon
Re: adding key to DB object
Hello Brandon:
Why do you see using private attributes like
$dbh-{private_commitflag} as preferable to embedding
a dbh in your own object?
regards,
David
--- Tim Bunce [EMAIL PROTECTED] wrote:
On Tue, Mar 29, 2005 at 12:20:09PM -0600, Brandon
Metcalf wrote:
j == [EMAIL PROTECTED] writes:
j No, that's wrong. It may look like a hash,
but when you set or query
j attributes, you are actually calling the
STORE() and FETCH() methods of
j the DBD and of DBI. You can only set
attributes that can be handled by
j the DBD and by DBI, you can't invent new
attributes.
Unless they start with private_.
If you enable trace you'll see a message like
$h-{commitflag}=1 ignored for invalid
driver-specific attribute
Ah, got it. Basically, what I'm trying to do is
track whether I've
encountered any errors during a transaction within
the object itself,
so I can do something like:
eval { $dbh-execute; };
$dbh-{commitflag} = 0 if $@;
Is there some existing attribute that will do what
I need?
$dbh-{private_commitflag} would work.
Tim.
Re: adding key to DB object
d == [EMAIL PROTECTED] writes:
d Why do you see using private attributes like
d $dbh-{private_commitflag} as preferable to embedding
d a dbh in your own object?
I suppose I could do that. Why would you use the latter method?
--
Brandon
RE: adding key to DB object
For the same reason you wouldn't modify data in a C structure belonging to
an API you're using. It violates the general software engineering
principles of information hiding and encapsulation. There are all kinds of
reasons putting your own data into an API's structure is bad, for instance
if the implementation changes then your trick may no longer work, and your
code will not be portable to new versions of the library.
However this discussion is beyond the scope of this list.
-Will
-Original Message-
From: Brandon Metcalf [mailto:[EMAIL PROTECTED]
Sent: Tuesday 29 March 2005 15:36
To: David Goodman
Cc: dbi-users@perl.org
Subject: Re: adding key to DB object
d == [EMAIL PROTECTED] writes:
d Why do you see using private attributes like
d $dbh-{private_commitflag} as preferable to embedding
d a dbh in your own object?
I suppose I could do that. Why would you use the latter method?
--
Brandon
- - - - - - - Appended by Scientific-Atlanta, Inc. - - - - - - -
This e-mail and any attachments may contain information which is confidential,
proprietary, privileged or otherwise protected by law. The information is
solely intended for the named addressee (or a person responsible for delivering
it to the addressee). If you are not the intended recipient of this message,
you are not authorized to read, print, retain, copy or disseminate this message
or any part of it. If you have received this e-mail in error, please notify the
sender immediately by return e-mail and delete it from your computer.
Re: cacheing problem
It appears that DBI/DBD is caching the results. How can I disable this? the problem was not in DBI. In case anyone else has this non-problem, my PEBKAC was that user-provided data, which was current with the previous fetch, was replacing the data from the newer fetch. Disabling this for the special case of reloading from the database solved the problem.
RE: adding key to DB object
W == [EMAIL PROTECTED] writes: W For the same reason you wouldn't modify data in a C structure belonging W to W an API you're using. It violates the general software engineering W principles of information hiding and encapsulation. There are all kinds W of W reasons putting your own data into an API's structure is bad, for W instance W if the implementation changes then your trick may no longer work, and W your W code will not be portable to new versions of the library. Understood. -- Brandon
embedding DB handle in object (was RE: adding key to DB object)
In trying to take your suggesting and embed a DB handle in my own
object, this is what i've come up with. However, a error is being
thrown when DBI is trying to destory the handle which I understand.
What I don't get is how to do this properly.
This may be inappropriate for this forum.
$ cat jj
#!/usr/bin/perl
use strict;
use warnings;
use ii;
my $dbh = ii-dbconnect_new();
__END__
$ cat ii.pm
package ii;
use strict;
use warnings;
use DBI;
use vars qw{ @EXPORT @ISA};
use Exporter;
@ISA = qw{ Exporter DBI::db };
@EXPORT = qw{ dbconnect_new };
sub dbconnect_new {
my ($pkg, $dbname) = @_;
my $dbh;
$dbh-{dbhandle} = DBI-connect(dbi:Pg:dbname='mydb';host='myhost');
bless $dbh, $pkg;
}
1;
__END__
$ ./jj
SV = RV(0x810eb70) at 0xbfffdb10
REFCNT = 1
FLAGS = (ROK,READONLY)
RV = 0x8108240
(in cleanup) dbih_getcom handle ii=HASH(0x8108240) is not a DBI
handle (has no magic).
--
Brandon
Re: embedding DB handle in object (was RE: adding key to DB object)
On Tue, Mar 29, 2005 at 04:58:53PM -0600, Brandon Metcalf wrote:
In trying to take your suggesting and embed a DB handle in my own
object, this is what i've come up with. However, a error is being
thrown when DBI is trying to destory the handle which I understand.
What I don't get is how to do this properly.
Embedding is simple has-a relationship not an IS-A. So you don't
want to include any DBI classes in your @ISA:
@ISA = qw{ Exporter DBI::db };
Tim.
Re: embedding DB handle in object (was RE: adding key to DB object)
T == [EMAIL PROTECTED] writes:
T On Tue, Mar 29, 2005 at 04:58:53PM -0600, Brandon Metcalf wrote:
T In trying to take your suggesting and embed a DB handle in my own
T object, this is what i've come up with. However, a error is being
T thrown when DBI is trying to destory the handle which I understand.
T What I don't get is how to do this properly.
T Embedding is simple has-a relationship not an IS-A. So you don't
T want to include any DBI classes in your @ISA:
T@ISA = qw{ Exporter DBI::db };
Excellent! Thanks.
--
Brandon
RE: embedding DB handle in object (was RE: adding key to DB objec t)
my $dbh = DBI-connect( );
my $big_honkin_data_structure = { house = semi, car = oldsmobile, dbh
= $dbh,
age = 43, ... };
...
myFavouriteSubroutine( $big_honkin_data_structure-{dbh}, 3 );
...
sub myFavouriteSubroutine
{
my ( $dbh, $num ) = @_;
my $data = $dbh-selectall_arrayref( );
}
This is for simple data structures and you can do a lot of things with this
style. Objects are more complex and not needed for many applications. This
is basic Perl and not properly part of this mailing list.
Books have been suggested recently and are mentioned in perl.org. For
instance, _Programming Perl_ and _Perl Cookbook_.
-Will
-Original Message-
From: Brandon Metcalf [mailto:[EMAIL PROTECTED]
Sent: Tuesday 29 March 2005 17:59
To: Rutherdale, Will
Cc: dbi-users@perl.org
Subject: embedding DB handle in object (was RE: adding key to DB object)
In trying to take your suggesting and embed a DB handle in my own object,
this is what i've come up with. However, a error is being thrown when DBI
is trying to destory the handle which I understand. What I don't get is how
to do this properly.
This may be inappropriate for this forum.
$ cat jj
#!/usr/bin/perl
use strict;
use warnings;
use ii;
my $dbh = ii-dbconnect_new();
__END__
$ cat ii.pm
package ii;
use strict;
use warnings;
use DBI;
use vars qw{ @EXPORT @ISA};
use Exporter;
@ISA = qw{ Exporter DBI::db };
@EXPORT = qw{ dbconnect_new };
sub dbconnect_new {
my ($pkg, $dbname) = @_;
my $dbh;
$dbh-{dbhandle} = DBI-connect(dbi:Pg:dbname='mydb';host='myhost');
bless $dbh, $pkg;
}
1;
__END__
$ ./jj
SV = RV(0x810eb70) at 0xbfffdb10
REFCNT = 1
FLAGS = (ROK,READONLY)
RV = 0x8108240
(in cleanup) dbih_getcom handle ii=HASH(0x8108240) is not a DBI
handle (has no magic).
--
Brandon
- - - - - - - Appended by Scientific-Atlanta, Inc. - - - - - - -
This e-mail and any attachments may contain information which is confidential,
proprietary, privileged or otherwise protected by law. The information is
solely intended for the named addressee (or a person responsible for delivering
it to the addressee). If you are not the intended recipient of this message,
you are not authorized to read, print, retain, copy or disseminate this message
or any part of it. If you have received this e-mail in error, please notify the
sender immediately by return e-mail and delete it from your computer.
RE: adding key to DB object
Rutherdale, Will [mailto:[EMAIL PROTECTED] wrote: For the same reason you wouldn't modify data in a C structure belonging to an API you're using. It violates the general software engineering principles of information hiding and encapsulation. There are all kinds of reasons putting your own data into an API's structure is bad, for instance if the implementation changes then your trick may no longer work, and your code will not be portable to new versions of the library. However, in this case, DBI defines an API that allows you to store your own data in the database handle. Specifically, prepend 'private_' to the name of your attribute. Embedding a DBI handle in another object also has drawbacks. For example, all your code has to work off your object, rather than the standard database handle, which means your code is less useful for people outside your organization. Ronald
RE: embedding DB handle in object (was RE: adding key to DB objec t)
W == [EMAIL PROTECTED] writes:
W my $dbh = DBI-connect( );
W my $big_honkin_data_structure = { house = semi, car = oldsmobile,
W dbh
W = $dbh,
W age = 43, ... };
W ...
W myFavouriteSubroutine( $big_honkin_data_structure-{dbh}, 3 );
W ...
W sub myFavouriteSubroutine
W {
W my ( $dbh, $num ) = @_;
W my $data = $dbh-selectall_arrayref( );
W
W }
This is pretty much what I'm doing. Tim provided the solution which
was to remove DBI::db from @ISA.
--
Brandon
DBI disconnect slow performance
I am having a problem with the DBI and/or DBD Oracle. I have a Perl CGI application. Using the Netscape web server, running on Solaris 9. Using perl 5.6.1, DBI 1.30, and DBD Oracle 1.12 for the CGI scripts. The database is on a separate machine, running Oracle 9.2.0.4.0 on Solaris 9. The application is read only. We open a new connection with each script, and close it at the end of the script. The scripts are all very typical - maybe read some input from the screen, do a database select, format the output, put to screen. For some reason, the disconnect statements are taking a very long time to execute. For example, a script that pulls 100 rows from the database, formats them, and puts them to the screen takes 7 seconds to execute. The disconnect statement itself is taking 6.5+ seconds of that time. I scoured the web looking for any discussion of such a problem, but found none. Am I unaware of some obvious bug with my software versions? Any ideas? Darrell Oresky [EMAIL PROTECTED]
Re: Should prepare_cached() manage its total size or memory usage?
Mark, I was using prepare_cached() on this repeatedly called SELECT statement. Recently, as the number of SELECTs crept upwards, the script started to die near the need with out of memory errors, like: Horrible thing, should not happen. I believe I understand what was happening-- the logic kept caching new unique handles in the cache, until the total memory usage was too much. If you are using bind variables, you should have only quite few distinct SQL statements. If these still generate unique handles, that sounds like a serious bug in the driver. If you are not using bind variables, you should not prepare_cached 50.000 statements. The cache is only useful anyway if you re-use the cached entry. From what you tell, it looks like you have 50.000 distinct handles (each used just once) so you gain nothing from using prepare_cached (only waste memory, and a lot of it apparently). I am not sure if prepare_cached() should manage its total size. It is, after all, an advanced feature, so the DBI user should be forced to think about when to use it. If it had an auto-flush feature, you would not have noticed the problem with your program at all. Thilo
