Perl and Postgres

From: Eduardo Vázquez Rodríguez <evazquez(at)insys-corp(dot)com(dot)mx>
To: Postgresql Mailing list <pgsql-novice(at)postgresql(dot)org>
Subject: Perl and Postgres
Date: 2004-09-30 00:00:17
Message-ID: 415B4C91.6010809@insys-corp.com.mx
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hello everybody out there using postgres and perl

I am writing Perl scripts with the purpouse of parsing text and
inserting the text parsed into a database, which I choose Postgresql.

My scripts are many, exactly 7, if I ran each one of them sequencially,
my processing speed is about 200 lines per second, but if I ran
concurrently the 7 scripts my processing speed downs to 1 line per
second, which is not acceptable.

My strategy of connecting to the database is the following

$mbd = DBI->connect("dbi:Pg:dbname=$database", "$username", "$password")
or die "Error opening database: $DBI::errstr \n";

$sth1 = $mbd->prepare
("
INSERT INTO public.table ( date, time, time_taken, c_ip,
sc_status,
s_action, sc_bytes, cs_bytes,
cs_method,
cs_uri_scheme, cs_host,
cs_uri_stem,
cs_username, s_hierarchy,
s_supplier_name,
cs_content_type, cs_user_agent,
sc_filter_result,
sc_filter_category, xvirus_id,
s_ip, s_sitename)

VALUES (?::varchar(10), ?::varchar(8) , ?::float4 ,
?::varchar(15),
?::varchar(5) , ?::varchar(30), ?::float4, ?::float4,
?::varchar(10), ?::varchar(10), ?::varchar(50),
?::varchar(800),
?::varchar(30), ?::varchar(20), ?::varchar(30),
?::varchar(30),
?::varchar(200), ?::varchar(30), ?::varchar(30),
?::varchar(30),
?::varchar(15), ?::varchar(30))
");

And when I insert

$sth1->execute ($normal[0], $normal[1], $normal[2], $normal[3],
$normal[4],
$normal[5], $normal[6], $normal[7],
$normal[8], $normal[9],
$normal[10], $normal[11], $normal[12],
$normal[13], $normal[14],
$normal[15], $normal[16], $normal[17],
$normal[18], $normal[19],
$normal[20], $normal[21])

or die "Can't insert $DBI::errstr\n"

Every script use 3 database handlers of the "same size", I mean using
the same numbers of fields.

My question is

How can I improve the database performance?

Is there anything wrong on my database handlers that degrade the
performance, from 200 lines per second to 1 line per second?

Thanks in advanced.

Browse pgsql-novice by date

  From Date Subject
Next Message Ramon Orticio 2004-09-30 01:56:03 can't connect to postmaster or postgresql server
Previous Message Devrim GUNDUZ 2004-09-29 16:57:19 Re: DELETE EMPTY ROWS