Re: Suggestions on which poe DBI module to use
On Tue, Apr 28, 2009 at 6:46 AM, Brett Paden wrote: > The app the uses EasyDBI is running in production servers and shuffles > around 2TB of uploaded user data a day; I have ran into 0 problems. Same here. We've been running this in a daemon that processes very large volumes of data for a long time now and we have seen zero problems with this module. Thanks, Phil
Re: Suggestions on which poe DBI module to use
Andrew Feren wrote:
It's been a while since I looked at all of these. I have some code
using EasyDBI and other code using SimpleDBI.
Me too ... I settled on EasyDBI as well, but made that decision two ago
and don't specifically recall my exact reasoning :-). I do know that
EasyDBI met most of my requirements (would return data in arrays of
arrays, placeholder support, clean easy-to-understand interface). The
one place I ran into problems was with queries to show status on tables,
but a quick ping to the author fixed those errors immediately.
The app the uses EasyDBI is running in production servers and shuffles
around 2TB of uploaded user data a day; I have ran into 0 problems.
Initially EasyDBI was the only one I could get to work on both Linux
and Windows. As of version 1.27 SimpleDBI also works on both
platforms. EasyDBI seems to offer a few more specialized ways to get
data back ('single' for example), but you can get the same info with
either module. My very limited benchmarking of the two modules
yielded comparable results.
I haven't looked at LaDBI or DBIAgent in a long while so I can't
really comment other than to say that when I looked at them they
wouldn't run on Windows.
-Andrew
Josh803316 wrote:
I've read the cpan module docs and have looked at the cookbook example (
http://poe.perl.org/?POE_Cookbook/DBI_Helper_Processes). The following
modules all run non-blocking async dbi calls for poe but I'm not sure
which
I should choose. I would love to see a pro/con list for these or get
some
sort of guidance on which would work better in my network testing app.
I'm sure everyone has favourites out there, but is there any reason
to use
one over another in certain scenarios? Are there any drawbacks or
things to
watch out for on specific modules?
POE::Component::LaDBI
POE::Component::EasyDBI
POE::Component::DBIAgent
POE::Component::SimpleDBI
Re: Suggestions on which poe DBI module to use
Last night I remembered (at least partially) the one problem I had with
EasyDBI.
I had some minor problems when connecting to the DB
(http://rt.cpan.org/Ticket/Display.html?id=36693 and I think something
else that I can't find documented). I worked around it with an eval {
... }; if ($@) {... }
-Andrew
Andrew Feren wrote:
It's been a while since I looked at all of these. I have some code
using EasyDBI and other code using SimpleDBI.
Initially EasyDBI was the only one I could get to work on both Linux
and Windows. As of version 1.27 SimpleDBI also works on both
platforms. EasyDBI seems to offer a few more specialized ways to get
data back ('single' for example), but you can get the same info with
either module. My very limited benchmarking of the two modules
yielded comparable results.
I haven't looked at LaDBI or DBIAgent in a long while so I can't
really comment other than to say that when I looked at them they
wouldn't run on Windows.
-Andrew
Josh803316 wrote:
I've read the cpan module docs and have looked at the cookbook example (
http://poe.perl.org/?POE_Cookbook/DBI_Helper_Processes). The following
modules all run non-blocking async dbi calls for poe but I'm not sure
which
I should choose. I would love to see a pro/con list for these or get
some
sort of guidance on which would work better in my network testing app.
I'm sure everyone has favourites out there, but is there any reason
to use
one over another in certain scenarios? Are there any drawbacks or
things to
watch out for on specific modules?
POE::Component::LaDBI
POE::Component::EasyDBI
POE::Component::DBIAgent
POE::Component::SimpleDBI
Re: Suggestions on which poe DBI module to use
I've only read through the code so far, but here are a couple of thoughts.
I thought '' should be specified for unused EasyDBI aliases rather than
'not_used'. Although I like the self documenting nature of 'not_used'.
This seems straight forward enough and looks like what you advertised.
I wonder how much harder it would be to turn this inside out. Have 1..n
EasyDBI::SubProcces rather than 1..n EasyDBI? Seems like it could avoid
an extra trip through the kernel for each request. Maybe not a big
deal. Just thinking out loud.
Only tangentially related to this, but something else I'd like is a DB
response event only on error. At least 98% of my current app is
inserts. For most of those I am happy to assume they succeeded unless I
get an error.
I'll play with this a bit later,
-Andrew
Phil Whelan wrote:
On Mon, Apr 27, 2009 at 1:32 PM, David Davis wrote:
I'd like to take a look at PoCo-EasyDBI-Multiplex.
Btw, I'm the EasyDBI author, so you can direct any questions about it to the
list.
David Davis
Cool. Here it is.
$ cat POE-Component-EasyDBI-Multiplex/lib/POE/Component/EasyDBI/Multiplex.pm
package POE::Component::EasyDBI::Multiplex;
use strict;
use constant DEBUG => $ENV{DEBUG_EASYDBI_POOL};
use constant SQL_QUERY_TIMES_LOGFILE => $ENV{SQL_QUERY_TIMES_LOGFILE};
use constant SQL_QUEUE_SIZE_LOGFILE => $ENV{SQL_QUEUE_SIZE_LOGFILE};
use constant DEFAULT_POOL_SIZE => 1;
use constant GET_QUEUE_SIZE_TIMEOUT => 60;
use POE::Kernel;
use POE::Session;
use POE::Component::EasyDBI;
use Time::HiRes ();
use FileHandle;
my $query_time_log_fh;
my $sql_queue_size_log_fh;
my $previous_sql_req_queue_size = 0;
my $previous_dbi_writers_available = 0;
END {
if ( SQL_QUERY_TIMES_LOGFILE ) {
close $query_time_log_fh
if $query_time_log_fh;
}
if ( SQL_QUEUE_SIZE_LOGFILE ) {
close $sql_queue_size_log_fh
if $sql_queue_size_log_fh;
}
}
sub spawn {
my ($package, %dbi_params) = @_;
# open file here, so the file handle isn't closed when spawning occurs
_open_query_time_log_fh()
if SQL_QUERY_TIMES_LOGFILE;
_open_sql_queue_size_log_fh()
if SQL_QUEUE_SIZE_LOGFILE;
POE::Session->create(
inline_states => {
_start=> \&_start,
queue_sql_req => \&_queue_sql_req,
do=> \&_queue_sql_req,
arrayhash => \&_queue_sql_req,
single=> \&_queue_sql_req,
hash => \&_queue_sql_req,
process_sql_req_queue => \&_process_sql_req_queue,
db_response => \&_db_response,
sql_req_queue_size=> sub { scalar @{
$_[HEAP]->{sql_req_queue} } },
dbi_writers_available => sub { scalar @{
$_[HEAP]->{dbi_writers} } },
timeout => \&_timeout,
shutdown => \&_shutdown,
},
heap => {
dbi_params => \%dbi_params,
poolsize => delete $dbi_params{poolsize} || DEFAULT_POOL_SIZE,
dbi_writers=> [],
sql_req_queue => [],
},
options => {
trace => DEBUG,
},
);
}
sub _open_query_time_log_fh {
if ( SQL_QUERY_TIMES_LOGFILE ) {
$query_time_log_fh = new FileHandle;
open $query_time_log_fh, ">>".SQL_QUERY_TIMES_LOGFILE
or die "Cannot write to sql query time log file :
".SQL_QUERY_TIMES_LOGFILE;
}
}
sub _open_sql_queue_size_log_fh {
if ( SQL_QUEUE_SIZE_LOGFILE ) {
$sql_queue_size_log_fh = new FileHandle;
open $sql_queue_size_log_fh, ">>".SQL_QUEUE_SIZE_LOGFILE
or die "Cannot write to sql queue size log file :
".SQL_QUEUE_SIZE_LOGFILE;
$sql_queue_size_log_fh->autoflush();
}
}
sub _start {
my $heap = $_[HEAP];
# Set an alias for the current session
$_[KERNEL]->alias_set( delete $heap->{dbi_params}->{alias} )
if $heap->{dbi_params}->{alias};
# create a Pool consisting of a configurable number of DBIWriter's
for ( 1 .. $heap->{poolsize} ) {
push @{ $heap->{dbi_writers} },
POE::Component::EasyDBI->spawn(
%{ $heap->{dbi_params} },
alias => 'not_used',
)->ID;
}
if (SQL_QUEUE_SIZE_LOGFILE && $sql_queue_size_log_fh) {
POE::Kernel->delay('timeout', GET_QUEUE_SIZE_TIMEOUT);
}
}
sub _shutdown {
my $heap = $_[HEAP];
while ( my $dbi_writer = pop @{ $heap->{dbi_writers} } ) {
POE::Kernel->call( $dbi_writer, "shutdown" );
}
}
sub _queue_sql_req {
my ($heap, $sql_req) = @_[HEAP,ARG0];
$sql_req->{_return_session_id} = $_[SENDER]->ID;
$sql_req->{_return_event} = delete $sql_req->{event},
$sql_req->{_state} = $_[STATE];
$sql_req->{_sql_start_time}= Time::HiRes::time();
push @{ $_[HEAP]->{sql_req_queue} }, $sql_req;
Re: Suggestions on which poe DBI module to use
On Mon, Apr 27, 2009 at 1:32 PM, David Davis wrote:
> I'd like to take a look at PoCo-EasyDBI-Multiplex.
> Btw, I'm the EasyDBI author, so you can direct any questions about it to the
> list.
>
> David Davis
Cool. Here it is.
$ cat POE-Component-EasyDBI-Multiplex/lib/POE/Component/EasyDBI/Multiplex.pm
package POE::Component::EasyDBI::Multiplex;
use strict;
use constant DEBUG => $ENV{DEBUG_EASYDBI_POOL};
use constant SQL_QUERY_TIMES_LOGFILE => $ENV{SQL_QUERY_TIMES_LOGFILE};
use constant SQL_QUEUE_SIZE_LOGFILE => $ENV{SQL_QUEUE_SIZE_LOGFILE};
use constant DEFAULT_POOL_SIZE => 1;
use constant GET_QUEUE_SIZE_TIMEOUT => 60;
use POE::Kernel;
use POE::Session;
use POE::Component::EasyDBI;
use Time::HiRes ();
use FileHandle;
my $query_time_log_fh;
my $sql_queue_size_log_fh;
my $previous_sql_req_queue_size = 0;
my $previous_dbi_writers_available = 0;
END {
if ( SQL_QUERY_TIMES_LOGFILE ) {
close $query_time_log_fh
if $query_time_log_fh;
}
if ( SQL_QUEUE_SIZE_LOGFILE ) {
close $sql_queue_size_log_fh
if $sql_queue_size_log_fh;
}
}
sub spawn {
my ($package, %dbi_params) = @_;
# open file here, so the file handle isn't closed when spawning occurs
_open_query_time_log_fh()
if SQL_QUERY_TIMES_LOGFILE;
_open_sql_queue_size_log_fh()
if SQL_QUEUE_SIZE_LOGFILE;
POE::Session->create(
inline_states => {
_start=> \&_start,
queue_sql_req => \&_queue_sql_req,
do=> \&_queue_sql_req,
arrayhash => \&_queue_sql_req,
single=> \&_queue_sql_req,
hash => \&_queue_sql_req,
process_sql_req_queue => \&_process_sql_req_queue,
db_response => \&_db_response,
sql_req_queue_size=> sub { scalar @{
$_[HEAP]->{sql_req_queue} } },
dbi_writers_available => sub { scalar @{
$_[HEAP]->{dbi_writers} } },
timeout => \&_timeout,
shutdown => \&_shutdown,
},
heap => {
dbi_params => \%dbi_params,
poolsize => delete $dbi_params{poolsize} || DEFAULT_POOL_SIZE,
dbi_writers=> [],
sql_req_queue => [],
},
options => {
trace => DEBUG,
},
);
}
sub _open_query_time_log_fh {
if ( SQL_QUERY_TIMES_LOGFILE ) {
$query_time_log_fh = new FileHandle;
open $query_time_log_fh, ">>".SQL_QUERY_TIMES_LOGFILE
or die "Cannot write to sql query time log file :
".SQL_QUERY_TIMES_LOGFILE;
}
}
sub _open_sql_queue_size_log_fh {
if ( SQL_QUEUE_SIZE_LOGFILE ) {
$sql_queue_size_log_fh = new FileHandle;
open $sql_queue_size_log_fh, ">>".SQL_QUEUE_SIZE_LOGFILE
or die "Cannot write to sql queue size log file :
".SQL_QUEUE_SIZE_LOGFILE;
$sql_queue_size_log_fh->autoflush();
}
}
sub _start {
my $heap = $_[HEAP];
# Set an alias for the current session
$_[KERNEL]->alias_set( delete $heap->{dbi_params}->{alias} )
if $heap->{dbi_params}->{alias};
# create a Pool consisting of a configurable number of DBIWriter's
for ( 1 .. $heap->{poolsize} ) {
push @{ $heap->{dbi_writers} },
POE::Component::EasyDBI->spawn(
%{ $heap->{dbi_params} },
alias => 'not_used',
)->ID;
}
if (SQL_QUEUE_SIZE_LOGFILE && $sql_queue_size_log_fh) {
POE::Kernel->delay('timeout', GET_QUEUE_SIZE_TIMEOUT);
}
}
sub _shutdown {
my $heap = $_[HEAP];
while ( my $dbi_writer = pop @{ $heap->{dbi_writers} } ) {
POE::Kernel->call( $dbi_writer, "shutdown" );
}
}
sub _queue_sql_req {
my ($heap, $sql_req) = @_[HEAP,ARG0];
$sql_req->{_return_session_id} = $_[SENDER]->ID;
$sql_req->{_return_event} = delete $sql_req->{event},
$sql_req->{_state} = $_[STATE];
$sql_req->{_sql_start_time}= Time::HiRes::time();
push @{ $_[HEAP]->{sql_req_queue} }, $sql_req;
$_[KERNEL]->yield( "process_sql_req_queue" );
}
sub _process_sql_req_queue {
my $heap = $_[HEAP];
# try to get a dbi writer and insert a logline into the db
if ( @{ $heap->{sql_req_queue} }
&& ( my $dbi_session_id = pop @{ $heap->{dbi_writers} } ) ) {
# using LIFO stack rather than FIFO queue
#my $sql_req = shift @{ $heap->{sql_req_queue} };
my $sql_req = pop @{ $heap->{sql_req_queue} };
POE::Kernel->call(
$dbi_session_id,
$sql_req->{_state} => {
%$sql_req,
event=> 'db_response',
},
)
}
}
sub _db_response {
my ($kernel,$heap,$result) = @_[KERNEL,HEAP,ARG0];
# mark the writer as free now the result has been returned
unshift @{ $heap->{dbi_writers} }, $_[SENDER]->ID;
Re: Suggestions on which poe DBI module to use
I'd like to take a look at PoCo-EasyDBI-Multiplex. Btw, I'm the EasyDBI author, so you can direct any questions about it to the list. David Davis ☄ Software Engineer http://xant.us/ http://xantus.tel/ On Mon, Apr 27, 2009 at 12:16, Phil Whelan wrote: > Hi, > > I like POE-Component-EasyDBI > > Actually, I've written a wrapper module > POE-Component-EasyDBI-Multiplex, which I've not yet made public, but > we're using to manage a pool of EasyDBI connections. > POE-Component-EasyDBI-Multiplex uses the same interface as > POE-Component-EasyDBI, but the whole API is not fully support - we > just implemented what we needed. I can send the code to the list if > anyone is interested in using it. Hopefully, will get around to > packaging it up sooner or later. > > Thanks, > Phil > > -- > Twitter: http://www.twitter.com/philwhln > > > On Mon, Apr 27, 2009 at 11:54 AM, Josh803316 wrote: > > I've read the cpan module docs and have looked at the cookbook example ( > > http://poe.perl.org/?POE_Cookbook/DBI_Helper_Processes). The following > > modules all run non-blocking async dbi calls for poe but I'm not sure > which > > I should choose. I would love to see a pro/con list for these or get > some > > sort of guidance on which would work better in my network testing app. > > > > I'm sure everyone has favourites out there, but is there any reason to > use > > one over another in certain scenarios? Are there any drawbacks or things > to > > watch out for on specific modules? > > > > POE::Component::LaDBI > > > > POE::Component::EasyDBI > > > > POE::Component::DBIAgent > > > > POE::Component::SimpleDBI > > >
Re: Suggestions on which poe DBI module to use
Thanks for the suggestions so far!!! I think I will start with EasyDBI specially since your multi-plex code will be available :) On Mon, Apr 27, 2009 at 1:10 PM, Andrew Feren wrote: > I've been starting to implement something similar so I'd love to see this. > > -Andrew > > > Phil Whelan wrote: > >> Hi, >> >> I like POE-Component-EasyDBI >> >> Actually, I've written a wrapper module >> POE-Component-EasyDBI-Multiplex, which I've not yet made public, but >> we're using to manage a pool of EasyDBI connections. >> POE-Component-EasyDBI-Multiplex uses the same interface as >> POE-Component-EasyDBI, but the whole API is not fully support - we >> just implemented what we needed. I can send the code to the list if >> anyone is interested in using it. Hopefully, will get around to >> packaging it up sooner or later. >> >> Thanks, >> Phil >> >> >> >
Re: Suggestions on which poe DBI module to use
I've been starting to implement something similar so I'd love to see this. -Andrew Phil Whelan wrote: Hi, I like POE-Component-EasyDBI Actually, I've written a wrapper module POE-Component-EasyDBI-Multiplex, which I've not yet made public, but we're using to manage a pool of EasyDBI connections. POE-Component-EasyDBI-Multiplex uses the same interface as POE-Component-EasyDBI, but the whole API is not fully support - we just implemented what we needed. I can send the code to the list if anyone is interested in using it. Hopefully, will get around to packaging it up sooner or later. Thanks, Phil
Re: Suggestions on which poe DBI module to use
It's been a while since I looked at all of these. I have some code
using EasyDBI and other code using SimpleDBI.
Initially EasyDBI was the only one I could get to work on both Linux and
Windows. As of version 1.27 SimpleDBI also works on both platforms.
EasyDBI seems to offer a few more specialized ways to get data back
('single' for example), but you can get the same info with either
module. My very limited benchmarking of the two modules yielded
comparable results.
I haven't looked at LaDBI or DBIAgent in a long while so I can't really
comment other than to say that when I looked at them they wouldn't run
on Windows.
-Andrew
Josh803316 wrote:
I've read the cpan module docs and have looked at the cookbook example (
http://poe.perl.org/?POE_Cookbook/DBI_Helper_Processes). The following
modules all run non-blocking async dbi calls for poe but I'm not sure which
I should choose. I would love to see a pro/con list for these or get some
sort of guidance on which would work better in my network testing app.
I'm sure everyone has favourites out there, but is there any reason to use
one over another in certain scenarios? Are there any drawbacks or things to
watch out for on specific modules?
POE::Component::LaDBI
POE::Component::EasyDBI
POE::Component::DBIAgent
POE::Component::SimpleDBI
Re: Suggestions on which poe DBI module to use
Hi, I like POE-Component-EasyDBI Actually, I've written a wrapper module POE-Component-EasyDBI-Multiplex, which I've not yet made public, but we're using to manage a pool of EasyDBI connections. POE-Component-EasyDBI-Multiplex uses the same interface as POE-Component-EasyDBI, but the whole API is not fully support - we just implemented what we needed. I can send the code to the list if anyone is interested in using it. Hopefully, will get around to packaging it up sooner or later. Thanks, Phil -- Twitter: http://www.twitter.com/philwhln On Mon, Apr 27, 2009 at 11:54 AM, Josh803316 wrote: > I've read the cpan module docs and have looked at the cookbook example ( > http://poe.perl.org/?POE_Cookbook/DBI_Helper_Processes). The following > modules all run non-blocking async dbi calls for poe but I'm not sure which > I should choose. I would love to see a pro/con list for these or get some > sort of guidance on which would work better in my network testing app. > > I'm sure everyone has favourites out there, but is there any reason to use > one over another in certain scenarios? Are there any drawbacks or things to > watch out for on specific modules? > > POE::Component::LaDBI > > POE::Component::EasyDBI > > POE::Component::DBIAgent > > POE::Component::SimpleDBI >
