pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedArray interface
7+ messages / 3 participants
[nested] [flat]
* Array interface
@ 2010-11-02 19:46 Mladen Gogala <mladen.gogala@vmsinfo.com>
0 siblings, 1 reply; 7+ messages in thread
From: Mladen Gogala @ 2010-11-02 19:46 UTC (permalink / raw)
To: pgsql-performance
I wrote a little Perl script, intended to test the difference that array
insert makes with PostgreSQL. Imagine my surprise when a single record
insert into a local database was faster than batches of 100 records.
Here are the two respective routines:
sub do_ssql
{
my $exec_cnt = 0;
while (<FL>)
{
chomp;
my @row = split /$sep/;
$sth->execute(@row);
$exec_cnt++;
}
$dbh->commit();
print "Insert executed $exec_cnt times.\n";
}
sub do_msql
{
my $bsz = shift;
die("Batch size must be >0!\n") unless $bsz > 0;
my $exec_cnt = 0;
my @tstat;
my (@col1, @col2, @col3);
while (<FL>)
{
chomp;
my @row = split /$sep/;
push @col1, $row[0];
push @col2, $row[1];
push @col3, $row[2];
if ($. % $bsz == 0)
{
my $tuples = $sth->execute_array({ArrayTupleStatus => \@tstat},
\@col1, \@col2, \@col3);
die("Multiple insert failed!\n") if (!$tuples);
@col1 = ();
@col2 = ();
@col3 = ();
$exec_cnt++;
}
}
if ($#col1 >= 0)
{
my $tuples = $sth->execute_array({ArrayTupleStatus => \@tstat},
\@col1, \@col2, \@col3);
die("Multiple insert failed!\n") if (!$tuples);
$exec_cnt++;
}
$dbh->commit();
print "Insert executed $exec_cnt times.\n";
}
The variable "$sth" is a prepared statement handle.
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions
^ permalink raw reply [nested|flat] 7+ messages in thread
* Array interface
@ 2010-11-02 21:18 Mladen Gogala <mladen.gogala@vmsinfo.com>
0 siblings, 0 replies; 7+ messages in thread
From: Mladen Gogala @ 2010-11-02 21:18 UTC (permalink / raw)
To: pgsql-performance
I wrote a little Perl script, intended to test the difference that array
insert makes with PostgreSQL. Imagine my surprise when a single record
insert into a local database was faster than batches of 100 records.
Here are the two respective routines:
sub do_ssql
{
my $exec_cnt = 0;
while (<FL>)
{
chomp;
my @row = split /$sep/;
$sth->execute(@row);
$exec_cnt++;
}
$dbh->commit();
print "Insert executed $exec_cnt times.\n";
}
sub do_msql
{
my $bsz = shift;
die("Batch size must be >0!\n") unless $bsz > 0;
my $exec_cnt = 0;
my @tstat;
my (@col1, @col2, @col3);
while (<FL>)
{
chomp;
my @row = split /$sep/;
push @col1, $row[0];
push @col2, $row[1];
push @col3, $row[2];
if ($. % $bsz == 0)
{
my $tuples = $sth->execute_array({ArrayTupleStatus => \@tstat},
\@col1, \@col2, \@col3);
die("Multiple insert failed!\n") if (!$tuples);
@col1 = ();
@col2 = ();
@col3 = ();
$exec_cnt++;
}
}
if ($#col1 >= 0)
{
my $tuples = $sth->execute_array({ArrayTupleStatus => \@tstat},
\@col1, \@col2, \@col3);
die("Multiple insert failed!\n") if (!$tuples);
$exec_cnt++;
}
$dbh->commit();
print "Insert executed $exec_cnt times.\n";
}
The variable "$sth" is a prepared statement handle for the insert statement.
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions
^ permalink raw reply [nested|flat] 7+ messages in thread
* Array interface
@ 2010-11-02 21:32 Mladen Gogala <mladen.gogala@vmsinfo.com>
0 siblings, 1 reply; 7+ messages in thread
From: Mladen Gogala @ 2010-11-02 21:32 UTC (permalink / raw)
To: pgsql-performance
I sent 2 emails, both containing a Perl code sample, but none of them
went through. Essentially, I was testing Perl array bind & execute.
Everything went well, except for the fact that array execute is no
faster than the row-by-row way of executing things. I was surprised
because I expected array bind to produce better results over the network
than the row-by-row operations, yet it didn't. Can anybody elaborate a
bit? It seems that some kind of email scanner has quarantined my emails
containing Perl code samples as dangerous, so I can't really show the
code sample.
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Array interface
@ 2010-11-02 22:40 Conor Walsh <ctw@adverb.ly>
parent: Mladen Gogala <mladen.gogala@vmsinfo.com>
0 siblings, 1 reply; 7+ messages in thread
From: Conor Walsh @ 2010-11-02 22:40 UTC (permalink / raw)
To: Mladen Gogala <mladen.gogala@vmsinfo.com>; +Cc: pgsql-performance
On Tue, Nov 2, 2010 at 2:32 PM, Mladen Gogala <mladen.gogala@vmsinfo.com> wrote:
> I was surprised because I expected array bind to produce better
> results over the network than the row-by-row operations, yet it
> didn't. Can anybody elaborate a bit?
While all of the bulk-execute functions are likely to have
implementations, they are not necessarily likely to actually be
efficient implementations.
I ran into this with DBD::ODBC a while back because DBD::ODBC
implements execute_array() as "execute($_) foreach(@_)". DBD::Pg
doesn't appear to implement execute_array() at all, so perhaps it's
falling back on a similar default implementation in the superclass.
I generally suspect this is a Perl problem rather than a Postgres
problem, but can't say more without code. Maybe try pastebin if
you're having email censorship issues.
-Conor
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Array interface
@ 2010-11-03 14:56 Mladen Gogala <mladen.gogala@vmsinfo.com>
parent: Conor Walsh <ctw@adverb.ly>
0 siblings, 0 replies; 7+ messages in thread
From: Mladen Gogala @ 2010-11-03 14:56 UTC (permalink / raw)
To: Conor Walsh <ctw@adverb.ly>; +Cc: pgsql-performance
Conor Walsh wrote:
>
> I generally suspect this is a Perl problem rather than a Postgres
> problem,
So do I. I had the same situation with Oracle, until John Scoles had the
DBD::Oracle driver fixed and started utilizing the Oracle array interface.
> but can't say more without code. Maybe try pastebin if
> you're having email censorship issues.
>
> -Conor
>
>
I posted it to comp.databases.postgresql.
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Array interface
@ 2010-11-10 09:10 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
parent: Mladen Gogala <mladen.gogala@vmsinfo.com>
0 siblings, 1 reply; 7+ messages in thread
From: Mark Kirkwood @ 2010-11-10 09:10 UTC (permalink / raw)
To: pgsql-performance
On 03/11/10 08:46, Mladen Gogala wrote:
> I wrote a little Perl script, intended to test the difference that
> array insert makes with PostgreSQL. Imagine my surprise when a single
> record insert into a local database was faster than batches of 100
> records. Here are the two respective routines:
Interesting - I'm seeing a modest but repeatable improvement with bigger
array sizes (using attached program to insert pgbench_accounts) on an
older dual core AMD box with a single SATA drive running Ubuntu 10.04 i686.
rows arraysize elapsed(s)
1000000 1 161
1000000 10 115
1000000 100 110
1000000 1000 109
This is *despite* the fact that tracing the executed sql (setting
log_min_duration_statement = 0) shows that there is *no* difference (i.e
1000000 INSERT executions are performed) for each run. I'm guessing that
some perl driver overhead is being saved here.
I'd be interested to see if you can reproduce the same or similar effect.
What might also be interesting is doing each INSERT with an array-load
of bind variables appended to the VALUES clause - as this will only do 1
insert call per "array" of values.
Cheers
Mark
Attachments:
execinsert.pl (application/x-perl, 1.3K) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Array interface
@ 2010-11-10 09:42 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
parent: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 0 replies; 7+ messages in thread
From: Mark Kirkwood @ 2010-11-10 09:42 UTC (permalink / raw)
To: pgsql-performance
On 10/11/10 22:10, Mark Kirkwood wrote:
>
> What might also be interesting is doing each INSERT with an array-load
> of bind variables appended to the VALUES clause - as this will only do
> 1 insert call per "array" of values.
This is probably more like what you were expecting:
rows num values tuples(i.e array size) elapsed
1000000 1 106
1000000 10 14
1000000 100 13
1000000 1000 14
I didn't try to use PREPARE + EXECUTE here, just did "do" with the
INSERT + array size number of VALUES tuples (execute could well be
faster). The obvious difference here is we only do rows/(array size)
number of insert calls.
Cheers
Mark
Attachments:
execinsert-values.pl (application/x-perl, 1.2K) download
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2010-11-10 09:42 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-02 19:46 Array interface Mladen Gogala <mladen.gogala@vmsinfo.com>
2010-11-10 09:10 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-11-10 09:42 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-11-02 21:18 Array interface Mladen Gogala <mladen.gogala@vmsinfo.com>
2010-11-02 21:32 Array interface Mladen Gogala <mladen.gogala@vmsinfo.com>
2010-11-02 22:40 ` Conor Walsh <ctw@adverb.ly>
2010-11-03 14:56 ` Mladen Gogala <mladen.gogala@vmsinfo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox