title: Perl Multithreading MySQL Data Fetch
body: <p>I have several very large tables in mysql (Millions of rows) that I need to load into my perl script.</p>
  
  <p>We then do some custom processing of the data, and aggregate it into a hash. Unfortunately, that custom processing can't be implemented in MySQL.</p>
  
  <p>Heres a quick pseudocode.</p>
  
  <pre><code>my @data;
  for my $table_num(@table_numbers){
      my $sth = $dbh-&gt;prepare(...);
      $sth-&gt;execute();
      $sth-&gt;bind_columns(\my($a,$b,$c,...));
      while(($sth-&gt;fetch()){
          $data[$table_num]{black_box($a)}{secret_func($b)}+=$c;
      }
  }
  
  my $x = $#data + 1;
  for my $num (@table_numbers){
      for my $a (keys %{$data[$num]}){
          for my $b (keys %{$data[$num]{$a}){
              $data[$x]{$a}{$b} += $data[$num]{$a}{$b};
          }
       }
  }
  </code></pre>
  
  <p>Now, the first loop can take several minutes per iteration to run, so I am thinking of ways I can run them in parallel. I have looked at using Perl Threads before, but they seem to just run several perl interpreters at once, and my script is already using a lot of memory, and merging the data would seem t be problematic. Also, at this stage, the script is not using a lot of CPU. </p>
  
  <p>I have been looking at possibly using Coro threads, but it seem like there would be a learning curve, plus a fairly complex integration of  my current code. What I would like to know if I am likely to see any gains by going this route. Are there better ways of multithreading code like this. I can not afford to use any more memory then my code already uses. Is there something else I can do here?</p>
  
  <p>Unfortunately doing the aggregation in MySQL is not an option, and rewriting the code in a different language would be too time consuming. I am aware that using arrays instead of hashes is likely to make my code faster/use less memory, but again that would require a major rewrite of a large script.</p>
  
  <p>Edit: The above is pseudo code, the actual logic is a lot more complex. The bucketing is based on several db tables, and many more inputs then just $a and $b. Precomputing them is not practical, as the there are Trillions+ possible combinations. The main goal is how do I make the perl script run faster, not how to fix the SQL Part of things. That requires changes to how the data is stored and indexed in the actual server. Which would affect a lot of other code. There are other people working on doing those optimizations. My current goal is to attempt to make the code faster without changing any sql. </p>