run a set of scripts in parallel

February 22, 2013 — Leave a comment

when loading lots and lots of data to the database it is often better for performance to create the indexes and constraints after the load ( given the loaded data is consistent ). if there are some cpus available doing this in parallel would be a nice option so I started to look what perl can do for me in this case. there is a little module available on cpan called Parallel::ForkManager. below is a simple perl script which executes four scripts in parallel using this module. this example uses postgresql but you may replace the psql call with sqlplus and you’re done if you want to use it with oracle.

#!/usr/bin/perl
use Parallel::ForkManager;

my ( @sqlFiles     # the set of sql-scripts to execute
   , $pm           
   );
push ( @sqlFiles, 'scripts/01.sql' );
push ( @sqlFiles, 'scripts/02.sql' );
push ( @sqlFiles, 'scripts/03.sql' );
push ( @sqlFiles, 'scripts/04.sql' );
my $arraysize = @sqlFiles;
# set the amount of forks to the count of the sql script array
# do not set this greater than the number of CPUs available
$pm = Parallel::ForkManager->new($arraysize);
foreach my $file ( @sqlFiles ) {
  $pm->start and next;     # do the fork
  my $result = `psql -f $file`;
  print "result: $result \n";
  $pm->finish;             # do the exit in the child process
}
1;

the sql scripts in this case just create an index on each column of a test table:

$ cat scripts/01.sql 
select pg_backend_pid();
create index i1 on t1(a);
$ cat scripts/02.sql 
select pg_backend_pid();
create index i2 on t1(b);
$ cat scripts/03.sql 
select pg_backend_pid();
create index i3 on t1(c);
$ cat scripts/04.sql 
select pg_backend_pid();
create index i4 on t1(d);

of course it can be anything else but just creating indexes.

Advertisements

No Comments

Be the first to start the conversation!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s