sub event_handler {
   my $worker_thread = sub { 
          my $row = shift;
          my $event = SOD::EventCooker->new($row);
          if (exists $p_ids{$row->{'ev_p_id'}}) {
             lock $sessions{$p_ids{$row->{'ev_p_id'}}};
             my $session = thaw sessions{$p_ids{$row->{'ev_p_id'}}};
             $event->evaluate($session->{PLAYER}->{GAMESTATS});
             $sessions{$p_ids{$row->{'ev_p_id'}}} = freeze $session;
          }
          else {
             $event->evaluate;
          }
      };

   my $dist_thread = sub { 
          my @threads;
          my $thread_time = time;
          my $dbh = DBI->connect("DBI:mysql:database=$appdata{DBBASE}"
+, $appdata{DBUSER}, $appdata{DBPASS},
          { RaiseError => 1, AutoCommit => 1 }) || die $dbh->errstr;
          my $res = $dbh->selectall_hashref("SELECT * FROM event WHERE
+ ev_time<=$thread_time", 'ev_time');
          $dbh->do("DELETE FROM event WHERE ev_time<=$thread_time"); 

          map {
              push @threads, threads->new($worker_thread->($res->{$_})
+);
          } sort keys(%$res);
          map {
             $_->detach;
          } @threads;
          $dbh->disconnect;
          undef $dbh;
      };
   
   while (1) {
      sleep 1;
      last if $tflag;
      threads->new($dist_thread)->detach;
   }   
}
[download]

assume that a combination of our two approaches might be sufficient even for time of high traffic & loads.

It really depends upon how you combine them :)

What I can say is that your current code that starts two threads every second with one of them making a new connection to a database each time and the other freezing and thawing a compound structure to and from a shared hash is not going to run quickly.

Perl's threads are quite different from the kind threads found in say Java or Ruby where this kind of "spawn a thread, do a little and throw it away" coding style is common. and practical. Even there, creating new connections to MySql at the rate of one per second and then abandoning them is very likely to consume (leak) resources both in the DBI layers and at the MySQL server end.

I seem to recall that (at v3.xomething), MySQL would not reuse a dropped connection for something like 15 minutes or so? This may have been fixed or be configurable or I may have remembered it wrong, but in any event it is a far from ideal strategy if you are hoping to handle high data rates.

Using a single, long running thread that makes the connection to the DB when it starts and then reuses the handle to issue queries is a much better idea. Without seeing where, how and when your event_handler routine is called; and what SOD::EventCooker is; and what is populating your 'event' table, it is quite hard to advise further.

A high level pseudo-code description of your app might allow us to advise further.

---

Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.

Lingua non convalesco, consenesco et abolesco. -- Rule 1 has a caveat! -- Who broke the cabal?

"Science is about questioning the status quo. Questioning authority".

The "good enough" maybe good enough for the now, and perfection maybe unobtainable, but that should not preclude us from striving for perfection, when time, circumstance or desire allow.

the code snippet i posted is part of a daemon process, which has mainly the purpose of serving and cacheing sessions. as now it is going to become (at least an interface to) the engine of a browser game, the amount of work it has to do has grown immensely. the process consists of mainly 4 threads: the main thread listens to a TCP socket and replies to requests coming from ModPerl::Registry scripts ('CGI'). a second thread observes the validity of sessions and invalidates them, if necessary. the third thread is what we're talking about. fourth there is a logging thread.

the problem i have to face is that perl references may not be shared between threads. that's why session objects are being kept frozen on the shared hashtable. and even worse: that's also why the distributor subthread in the event handler has to explicitly connect each time. it would have been a lot less difficult, if i could have opened a pool of cached connections to the database and pass them around my subthreads. but that's a dream. i even tried freezing the db-handle. :(

the EventCooker is a thing which i have umm... borrowed from ModPerl::RegistryCooker. events will be put into the database at relevant user requests: each event is represented by one table row. in order to keep the engine flexible, it wouldn't be appropriate if i packed all event handling code into a bundle of libraries - as then i would have to restart the daemon each time i modified one line. so now, the code which handles a certain type of event, may now be a perl script, which then will be executed by the EventCooker.

what even worsens the story is, that most probably all of these scripts will have to connect again to the database, as they will need further information. but maybe there'll be a workaround for this problem with a more diligent query from the distributor thread.


language is a virus from outer space.