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.
