right. locking doesn't help in this case. ;-)

however - isn't there a possibility to
a) generate something like "a socket for each local connection attempt" (ResourcePool::Factory ??) within the deamon.pl, which collects the different application.pl's data and
b) pump the data from the different socket connections to a "general/shared queue" in deamon.pl

like this pseudo-code deamon.pl:


$queue = generate_global_ressource();
create_socket_pool(max_sockets = 200);
while(1=1){
    if($input=connection_attempt) {
        if($input ne 'finish') {
            push($fetched_data_from_socket, queue);
        } else {
            close_socket();
            create_new_socket_in_socket_pool();
        }
    }
[download]

(where generate_global_ressource() is something like ResourcePool::Command::DBI::Execute)

because all i need that deamon for are INSERT statements, the applications don't need to wait for return values. they should only "dump" the sql statements to a socket without caring what happens. (also there is a quite generous error-tolerance here: i don't care for lost data, as long as it doesn't exceed 5-10%...)

i still don't get:
a) how to keep a number of opened sockets in stock for the different application.pl which all use THE SAME port; and
b) how to use deamon.pl's general $queue AS a general queue for the several connection instances...



jdporter fixed code formatting and CPAN links

may be sth like this will work?!

#!/usr/bin/perl

use IO::Select;
use IO::Socket::INET;

use ResourcePool;
use ResourcePool::Factory::DBI;
use ResourcePool::Command::DBI::Execute;


# Initialize parameter ------------------------------------------------

$debug= 1;
$main = new IO::Socket::INET (
      LocalHost => '127.0.0.1',
      LocalPort => 6000,
      Listen => 5,
      Proto => 'tcp',
      Reuse => 1 ) || die $!;
$zero = chr(0);
$/ = $zero;
$\ = $zero;
$| = 1;
my $dsn = "DBI:mysql:DBNAME;your.server#2;3306";
my $username = "xxx";
my $passwd = "zzz";

# Initialize Ressource ------------------------------------------------

my $factory = ResourcePool::Factory::DBI->new(
      $dsn,
      $username,
      $passwd);
my $pool = ResourcePool->new($factory, MaxTry => 3);


# Initialize IO::Select ------------------------------------------------

$handles = new IO::Select();
$handles->add($main);

print "Starting listening cycle\n" if($debug > 0);

LISTEN: while (1)
   {
      ($pending) = IO::Select->select($handles, undef, undef, 60);
      foreach $sock (@$pending)
      {
         if ($sock == $main)
         {
            $num++;             print "Got new connection: $num from ".$sock->sockhost()."\n" if($debug > 0);
            my $newsock = $sock->accept();
            $newsock->autoflush();
            $number{$newsock} = $num;
            $handles->add($newsock);
         } else {
            my $buf = <$sock>;
            if ($buf)
            {
               print "Existing socket $number{$sock} is pending: " if($debug > 0);
               chomp $buf;
               print "$buf\n" if($debug > 0);
               my $cmd = ResourcePool::Command::DBI::Execute->new();
               $pool->execute($cmd, $buf);
            } else {
               print "Socket $number{$sock} is gone.\n" if($debug > 0);
               $handles->remove($sock);
            }
         }
      }
   }

i tried this with

my $port = 6000;
my $host = 'localhost';
my $proto= getprotobyname('tcp');
my $iaddr = inet_aton($host);
my $paddr = sockaddr_in($port, $iaddr);

my $code = 'INSERT INTO test (r1, r2, r3) VALUES (1,2,3);\n';

socket(SOCK, PF_INET, SOCK_STREAM, $proto) || die "socket: $!";
connect(SOCK, $paddr) || die "connect: $!";
print SOCK $code;
close (SOCK) || die "close: $!";


but after one connection attempt succeeded, the second one kills the deamon.pl:

linux:/srv/www/system # ./deamon.pl &
1 7336
linux:/srv/www/system # ./client.pl
linux:/srv/www/system # ./client.pl
1+ Stopped ./deamon.pl
linux:/srv/www/system #


...strange... :-( ...i use a logfile for the data the deamon.pl receives, and the first connections data was logged; but not the second ones....

it does, indeed.

in fact - the INSERTS i use are DELAYED ones anyway...

On the other hand: Is DBI the right tool for the job? Mass inserts (as you seem to be doing) are best left to the LOAD DATA command. Maybe you ftp the files over and have the daemon insert them locally.

hmm.. problem is - the next stage will be a bidirectional communication, because i need the lastinsertid.

i already switched back from INSERT DELAYED to plain INSERT. like i just posted, it works in a way :-)

if i can handle the local connections in a multithreaded style, a "usual" db_connect should also be possible... i think...

if you
- just send 1 statement at a time and
- then disconnect and reconnect for the next one,
- each transmission terminate by $EOL
you could try this deamon, which seems to work for several hundred parallel client attempts. (i brute forced it up to 700..800x8kB - took 1.5 min, but the first 500 went through within 20..30sec or so - but with just 100 at a time youre done in about 10 sec. so depending on your average and peek load you have to monitor the runtime of the clients. just do several scenarios for your use and see if there is still a bottleneck...) :


#!/usr/bin/perl -Tw

use strict;
use warnings;
use IO::Socket;
use Net::hostent;
use ResourcePool;
use ResourcePool::Factory::DBI;
use ResourcePool::Command::DBI::Execute;

my $EOL = "\015\012";
sub spawn;
my $port = 6000;
my $proto = getprotobyname('tcp');
my $dsn = "DBI:mysql:DB;HOST;3306";
my $username = "xxx";
my $passwd = "yyy";

my $factory = ResourcePool::Factory::DBI->new($dsn,$username,$passwd);
my $pool = ResourcePool->new($factory, MaxTry => 3);

socket(Server, PF_INET, SOCK_STREAM, $proto) || die "socket: $!";
setsockopt(Server, SOL_SOCKET, SO_REUSEADDR, pack("l", 1)) || die "setsockopt: $!";
bind(Server, sockaddr_in($port, INADDR_ANY)) || die "bind: $!";
listen(Server,SOMAXCONN) || die "listen: $!";

my $waitedpid = 0;
my $paddr;
$SIG{CHLD} = 'IGNORE';

for ($waitedpid = 0; ($paddr = accept(CLIENT,Server)) || $waitedpid; $waitedpid = 0, close CLIENT)
{
   next if $waitedpid and not $paddr;
   my($port,$iaddr) = sockaddr_in($paddr);
   my $name = gethostbyaddr($iaddr,AF_INET);

   spawn sub {
      $|=1;
      my $line; my $clientdata = '';
      for (;;)
      {
         undef $!;
         unless (defined($line= <> ))
         {
            die $! if $!;
            last; # reached EOF
         }
         $clientdata .= $line;
         last if eof;
      }
      my $cmd = ResourcePool::Command::DBI::Execute->new();
   $pool->execute($cmd, $clientdata);
   # and here may be a possibility to send data back to the client (f.i. lastinsertid)
   # <> = "place return data here $EOL";
   };
}

sub spawn
{
   my $coderef = shift;
   unless (@_ == 0 && $coderef && ref($coderef) eq 'CODE')
   {confess "usage: spawn CODEREF";}
   my $pid;
   if (!defined($pid = fork))
   {return;} elsif ($pid) {return; # I'm the parent}
   # else I'm the child -- go spawn
   open(STDIN, "<&CLIENT") || die "can't dup client to stdin";
   open(STDOUT, ">&CLIENT") || die "can't dup client to stdout";
   exit &$coderef();
}

cool! thanx a lot!!

now i go and try to implement the bidirectional communication. seems to work so far... *smile*