Thread: psql vs perl prepared inserts
I've been experimenting with loading a large amount of data into a
fairly simple database using both psql and perl prepared statements.
Unfortunately I'm seeing no appreciable differences between the two
methods, where I was under the impression that prepared statements
should be much faster (in my case, they are slightly slower).
I have included a pseudocode/subset of the perl code I use below. You
can see the prepare statement outside the loop and the execute
statement inside the loop. Alternatively you can see that I write
every INSERT statement to a text file which I then load by using `psql
dbname -f bulksql_load.sql`. Normally I only have either the prepare
or the print-to-file in the loop, but i've included both to make the
pseudocode smaller.
Using a simple `time` command from my system it looks like the
execution time for loading all the data in both scenarios is about 50
seconds. FYI, the infile has 18k lines, and after parsing and the db
enforcing uniqueness there are 15.5k rows in the results table of the
db. This works out to ~300 inserts per second with on pgsql 7.3.2
with fsync turned off. I think that is a decent performance for this
old box, I'm just confused as to why the prepared statements don't
seem to give any speed boost as advertised.
Could the fact that many of my inserts have 15 columns slow down the
prepared statements to the point where they're no longer useful as a
speed enhancement? Or perhaps it's because I'm explicitly inserting
each field/value pair, even if many of them are null (which I think is
generally considered a good habit). Any other suggestions you might
have would be welcome. Please forgive me if I should have posted this
to pgsql-performance or some perl list, but I think the comparison
with psql makes it relevant.
Hopefully this won't be too messy:
#!/usr/bin/perl
open (IN,"scan.nbe");
open (OUT,">bulksql_load.sql");
use DBI;
$dbh = DBI->connect("dbi:Pg:dbname=nessus");
$sth_res = $dbh->prepare("INSERT INTO results
(scan_id,subnet,host,service,plugin_id,type,description,see_also,solution,risk_factor,cve_id,bid_id,port,proto,service_name)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
$sth_tim = $dbh->prepare("INSERT INTO timestamps
(scan_id,hostip_or_nul,start_or_end,scan_time) VALUES (?,?,?,?)");
while (<IN>){
if (/^timestamps/){
parse_ts();
$sth_tim->execute($scan_id,$hostip_or_nul,$start_or_end,$scan_time);
print OUT "INSERT INTO timestamps
(scan_id,hostip_or_nul,start_or_end,scan_time) VALUES
($scan_id,$hostip_or_nul,$start_or_end,$scan_time)\n";
} elsif (/^results/) {
parse_res();
$sth_res->execute($scan_id,$subnet,$host,$service,$plugin_id,$type,$description,$see_also,$solution,$risk_factor,$cve_id,$bid_id,$port,$proto,$service_name);
print OUT "INSERT INTO results
(scan_id,subnet,host,service,plugin_id,type,description,see_also,solution,risk_factor,cve_id,bid_id,port,proto,service_name)
VALUES
($scan_id,$subnet,$host,$service,$plugin_id,$type,$description,$see_also,$solution,$risk_factor,$cve_id,$bid_id,$port,$proto,$service_name)\n";
}
}
Matt Van Mater <matt.vanmater@gmail.com> writes: > I've been experimenting with loading a large amount of data into a > fairly simple database using both psql and perl prepared statements. > Unfortunately I'm seeing no appreciable differences between the two > methods, where I was under the impression that prepared statements > should be much faster (in my case, they are slightly slower). They should be faster ... if the client library is really using server-side prepared statements, and not trying to fake it. Up till 7.4 came out there wasn't any very useful way to use server-side prepared statements in a driver, and I think only the very latest version of DBD::Pg knows anything about it. See this thread for some recent discussion: http://archives.postgresql.org/pgsql-interfaces/2005-04/msg00029.php regards, tom lane
On 4/12/05, Matt Van Mater <matt.vanmater@gmail.com> wrote:
> I've been experimenting with loading a large amount of data into a
> fairly simple database using both psql and perl prepared statements.
> Unfortunately I'm seeing no appreciable differences between the two
> methods, where I was under the impression that prepared statements
> should be much faster (in my case, they are slightly slower).
I've been playing with similar issue and in my case the best solution
for bulk insert was using perl to format data in form suitable for COPY
command.
I believe there may be problems if you have RULEs on table you want
to copy data into (IIRC such RULEs will be ignored).
For a test you might want to try also this approach (both from perl and
from psql):
$dbh->do('PREPARE sth_tim (int,inet,boolean,timestamptz) AS INSERT
INTO timestamps VALUES ($1,$2,$3,$4)');
$sth_tim = $dbh->prepare("EXECUTE sth_tim(?,?,?,?)");
...and later execute it. (and likewise with psql). If you'll see gain in speed
with perl it means your DBD::Pg wasn't using server side prepared
statements. (And there is a quite a chance that your psql will outperform
perl using this syntax).
Regards,
Dawid
PS: I have not tested these statements, yet they should be ok; make
sure the argument list (int,inet,boolean...) is correct for your data.
On Apr 13, 2005, at 4:12 AM, Dawid Kuroczko wrote: > On 4/12/05, Matt Van Mater <matt.vanmater@gmail.com> wrote: >> I've been experimenting with loading a large amount of data into a >> fairly simple database using both psql and perl prepared statements. >> Unfortunately I'm seeing no appreciable differences between the two >> methods, where I was under the impression that prepared statements >> should be much faster (in my case, they are slightly slower). > > I've been playing with similar issue and in my case the best solution > for bulk insert was using perl to format data in form suitable for COPY > command. I second this approach. Generally, getting the data into the database can be done VERY quickly (for the 18k rows you have, it would likely be instantaneous to copy them). I often create a separate "loader" schema into which I load text files. Then, I can use SQL, triggers, or functions to "clean up" the data, enforce referential integrity, etc. within the database. If you have perl code to do this, you can probably modify it just slightly to be used in a pl/perl function to do the same thing as before, but now it is done on the server side and will probably be significantly faster. Sean
Dawid Kuroczko wrote:
> For a test you might want to try also this approach (both from perl and
> from psql):
>
> $dbh->do('PREPARE sth_tim (int,inet,boolean,timestamptz) AS INSERT
> INTO timestamps VALUES ($1,$2,$3,$4)');
> $sth_tim = $dbh->prepare("EXECUTE sth_tim(?,?,?,?)");
>
> ...and later execute it. (and likewise with psql). If you'll see gain in speed
> with perl it means your DBD::Pg wasn't using server side prepared
> statements.
The intent of prepared statements is to reduce the overhead of running
the parser, rewriter and planner multiple times for a statement that is
executed multiple times. For an INSERT query without any sub-selects
that is not rewritten by any rules, the cost to parse, rewrite and plan
the statement is trivial. So I wouldn't expect prepared statements to be
a big win -- you would gain a lot more from batching multiple inserts
into a single transaction, and more still from using COPY.
-Neil
Thanks to all who replied. Thanks for the tip on that last thread Tom, I don't know how I missed it. I have a hunch that it's not applicable to me at this time because I'm running a year and a half old software (included in OpenBSD 3.4), but I will have to check which version of DBD::Pg was installed. > The intent of prepared statements is to reduce the overhead of running > the parser, rewriter and planner multiple times for a statement that is > executed multiple times. For an INSERT query without any sub-selects > that is not rewritten by any rules, the cost to parse, rewrite and plan > the statement is trivial. So I wouldn't expect prepared statements to be > a big win -- you would gain a lot more from batching multiple inserts > into a single transaction, and more still from using COPY. I was thinking something along the same lines, and was considering using the COPY statement as my next step, but as someone mentioned then I have to load it into a temporary database and then do some more internal magic to preserve referential integrity, unique contraints, etc. For that reason I was hoping to keep it in perl, and it's always nice to keep everything in a single neat portable package. Also, I forgot to mention earlier that I tried using transactions to speed things up, but since I expect to see certain inserts fail I would need to rework my code so the whole transaction doesn't fail if one insert goes bad. This is somewhat contrary to the purpose of transactions so I'm not sure how to accomplish this. I saw roughly a 20% speed improvement by turning autocommit off and only committing at the end of parsing each file. I think in the end I need to check the version of my Pg driver and perhaps upgrade to 7.4 or 8.0 in order to take advantage of the server side prepared statements. This is only a development box and I'm doing this mostly as an academic exercise that will someday help me speed up the production side, so upgrading isn't out of the question. Matt
On Wed, Apr 13, 2005 at 09:57:09 -0400, Matt Van Mater <matt.vanmater@gmail.com> wrote: > > Also, I forgot to mention earlier that I tried using transactions to > speed things up, but since I expect to see certain inserts fail I > would need to rework my code so the whole transaction doesn't fail if > one insert goes bad. This is somewhat contrary to the purpose of > transactions so I'm not sure how to accomplish this. I saw roughly a > 20% speed improvement by turning autocommit off and only committing at > the end of parsing each file. You might get a small benefit using savepoints in version 8.
Neil Conway wrote: > For an INSERT query without any sub-selects > that is not rewritten by any rules, the cost to parse, rewrite and plan > the statement is trivial. So I wouldn't expect prepared statements to be > a big win -- you would gain a lot more from batching multiple inserts > into a single transaction, and more still from using COPY. FWIW, when testing pgstream [1] I typically see a 50% increase in execution speed when switching to prepared statements in such a scenario. I'm attaching a small test program that inserts 10000 rows into 5 columns, first without and then with prepared statements, and displays elapsed time. Example of results: elapsed time in loop 0 is 1873 ms (PQexec) elapsed time in loop 1 is 1136 ms (PQexecPrepared) That's with unix domain sockets and a 8.0.1 server. [1] a thin C++ layer on top of libpq (http://manitou-mail.org/pgstream) that happens to have a unified API for prepared/non-prepared statements. -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org #include "pgstream.h" #include <iostream> #include <stdlib.h> #include <stdio.h> #include <sys/time.h> int main(int argc, char** argv) { const int loops=10000; pg_cnx cnx; char buf[]="abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"; try { struct timeval tv1, tv2; if (argc < 2) { std::cerr << "Usage: " << argv[0] << " connection_string\n"; exit(1); } cnx.connect(argv[1]); for (int j=0; j<2; j++) { cnx.set_option("bind_variables", j>0); cnx.set_option("prepare_statements", j>0); { pg_trans trans(cnx); pg_stmt("CREATE TABLE txt1(pk1 int,t1 text,t2 text," "t3 text, t4 text)", cnx); gettimeofday(&tv1,NULL); pg_stream s1("INSERT INTO txt1(pk1,t1,t2,t3,t4) " "VALUES(:i,:p1,:p2,:p3,:p4)", cnx); for (int i=0; i<loops; i++) { s1 << i << buf << buf << buf << buf; } gettimeofday(&tv2,NULL); pg_stmt("DROP TABLE txt1", cnx); trans.commit(); printf("elapsed time in loop %d is %d ms\n", j, (tv2.tv_sec-tv1.tv_sec)*1000+(tv2.tv_usec-tv1.tv_usec)/1000); } } } catch(pg_excpt p) { std::cerr << p.full_error_txt(); } return 0; }
Neil Conway <neilc@samurai.com> writes:
> The intent of prepared statements is to reduce the overhead of running
> the parser, rewriter and planner multiple times for a statement that is
> executed multiple times. For an INSERT query without any sub-selects
> that is not rewritten by any rules, the cost to parse, rewrite and plan
> the statement is trivial.
You'd be surprised ...
I was looking into this just the other day, with a test case that looks
like
create table bench1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,
dummy1 char(30));
create unique index bench1_index_ on bench1 (id,id2);
create index bench1_index_1 on bench1 (id3);
begin;
insert into bench1 values (0,0,0,'ABCDEFGHIJ');
... 300,000 inserts ...
insert into bench1 values (167151,167151,167151,'CDEFGHIJKL');
commit;
According to gprof, the above-1% functions are
% cumulative self self total
time seconds seconds calls s/call s/call name
6.62 53.38 53.38 300007 0.00 0.00 yyparse
5.31 96.19 42.81 11808781 0.00 0.00 hash_search
4.44 132.00 35.81 61268959 0.00 0.00 AllocSetAlloc
4.23 166.13 34.13 902304 0.00 0.00 XLogInsert
3.20 191.99 25.86 13688735 0.00 0.00 _bt_compare
2.94 215.75 23.76 12158347 0.00 0.00 LWLockAcquire
2.67 237.32 21.58 4500066 0.00 0.00 base_yylex
2.56 258.00 20.68 6000510 0.00 0.00 SearchCatCache
1.99 274.07 16.07 12160856 0.00 0.00 LWLockRelease
1.88 289.25 15.18 13008925 0.00 0.00 hash_any
1.49 301.25 12.01 2452386 0.00 0.00 PinBuffer
1.36 312.25 11.00 1201324 0.00 0.00 fmgr_info_cxt_security
1.36 323.24 10.98 300000 0.00 0.00 planner
1.19 332.81 9.57 20700142 0.00 0.00 MemoryContextAllocZeroAligned
I don't trust gprof's tree-structured breakdown entirely, but it puts
a pretty significant fraction of the blame on parse/plan activities:
3.66 767.69 300007/300007 PostgresMain [4]
[5] 95.6 3.66 767.69 300007 exec_simple_query [5]
6.13 283.12 300007/300007 PortalRun [6]
0.48 167.39 300007/300007 pg_analyze_and_rewrite [9]
0.47 122.85 300007/300007 pg_plan_queries [16]
1.56 93.29 300007/300007 pg_parse_query [23]
0.62 34.78 300007/300007 pg_rewrite_queries [52]
0.99 17.39 300007/300007 PortalDrop [79]
0.56 16.26 600014/600014 finish_xact_command [84]
1.19 6.89 300007/300007 CreatePortal [126]
1.08 2.29 600014/600014 start_xact_command [186]
1.88 0.36 300007/300007 PortalStart [218]
0.76 1.44 300007/300007 pq_puttextmessage [220]
1.35 0.00 300007/600017 set_ps_display [210]
1.27 0.00 300007/300007 CreateCommandTag [271]
0.89 0.22 300007/300007 printtup_create_DR [286]
0.74 0.00 300007/300007 CreateDestReceiver [328]
0.61 0.00 300007/600015 pgstat_report_activity [277]
0.10 0.14 300007/13864259 pfree [112]
0.23 0.00 300007/300007 PortalSetResultFormat [449]
0.19 0.00 300007/300007 IsAbortedTransactionBlockState [474]
0.07 0.00 300007/300007 printtup_destroy [564]
0.05 0.00 300007/300007 PortalDefineQuery [580]
0.03 0.00 300007/300007 EndCommand [617]
0.01 0.00 300007/300007 BeginCommand [670]
That adds up to over 50% of the runtime spent in parse/rewrite/plan.
I haven't gotten around to converting the test case into a program that
can use a prepared INSERT command, but it looks plausible to expect
a factor of 2 or so speedup ... of course, using COPY would completely
blow this away, anyway ...
regards, tom lane
On Apr 13, 2005, at 9:57 AM, Matt Van Mater wrote: > Thanks to all who replied. Thanks for the tip on that last thread > Tom, I don't know how I missed it. I have a hunch that it's not > applicable to me at this time because I'm running a year and a half > old software (included in OpenBSD 3.4), but I will have to check which > version of DBD::Pg was installed. > >> The intent of prepared statements is to reduce the overhead of running >> the parser, rewriter and planner multiple times for a statement that >> is >> executed multiple times. For an INSERT query without any sub-selects >> that is not rewritten by any rules, the cost to parse, rewrite and >> plan >> the statement is trivial. So I wouldn't expect prepared statements to >> be >> a big win -- you would gain a lot more from batching multiple inserts >> into a single transaction, and more still from using COPY. > > I was thinking something along the same lines, and was considering > using the COPY statement as my next step, but as someone mentioned > then I have to load it into a temporary database and then do some more > internal magic to preserve referential integrity, unique contraints, > etc. For that reason I was hoping to keep it in perl, and it's always > nice to keep everything in a single neat portable package. You can use pl/perl stored functions to do exactly what you do in perl on the client side, but it happens much faster since the data is already on the server. Then, your neat little portable package IS the database. Sean
On Wed, Apr 13, 2005 at 09:57:09AM -0400, Matt Van Mater wrote: > Also, I forgot to mention earlier that I tried using transactions to > speed things up, but since I expect to see certain inserts fail I > would need to rework my code so the whole transaction doesn't fail if > one insert goes bad. This is somewhat contrary to the purpose of > transactions so I'm not sure how to accomplish this. Try the pgloader project at pgfoundry. It tries to insert all rows using COPY and the rejected ones go to a file, using something akin to binary search. It may be of some use. I haven't tried it so I'm not sure of its maturity. Let us know how it goes if you try it! -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "And as an added bonus, now my computer goes to the toilet for me, leaving me free to spend time on more useful activities! yay slug codefests!" (C. Parker)
On 4/13/05, Matt Van Mater <matt.vanmater@gmail.com> wrote:
> > The intent of prepared statements is to reduce the overhead of running
> > the parser, rewriter and planner multiple times for a statement that is
> > executed multiple times. For an INSERT query without any sub-selects
> > that is not rewritten by any rules, the cost to parse, rewrite and plan
> > the statement is trivial. So I wouldn't expect prepared statements to be
> > a big win -- you would gain a lot more from batching multiple inserts
> > into a single transaction, and more still from using COPY.
> I was thinking something along the same lines, and was considering
> using the COPY statement as my next step, but as someone mentioned
> then I have to load it into a temporary database and then do some more
> internal magic to preserve referential integrity, unique contraints,
> etc. For that reason I was hoping to keep it in perl, and it's always
> nice to keep everything in a single neat portable package.
I believe that COPY handles things like unique constraints and
referer integerity just fine (of the latter I am not sure) -- well,
its hard to imagine PostgreSQL allowing itself to loose integrity
of the data, don't you agree?
A simple test:
qnex=# CREATE TABLE a (a int PRIMARY KEY);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"a_pkey" for table "a"
CREATE TABLEqnex=# COPY a FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1
>> 2
>> 3
>> 1
>> \.
ERROR: duplicate key violates unique constraint "a_pkey"
CONTEXT: COPY a, line 4: "1"
...you may have to rethink your design if there are any RULEs or
TRIGGERs associated with said table. Apart from that, there
should be no problems at the risk that one "bad" row will cancel
whole COPY commands, so if you trust your data, it will be fine. :)
Regards,
Dawid
Just to add some clarity to the Dawid's fine comments: On 4/14/05, Dawid Kuroczko <qnex42@gmail.com> wrote: > On 4/13/05, Matt Van Mater <matt.vanmater@gmail.com> wrote: > I believe that COPY handles things like unique constraints and > referer integerity just fine (of the latter I am not sure) -- well, > its hard to imagine PostgreSQL allowing itself to loose integrity > of the data, don't you agree? > I do, and so does PG. :) Everything that would normally happen with an INSERT, including firing TRIGGERs, checking FKEYs, setting DEFAULTs, running CHECK constraints -- everything -- happens with a COPY import. The *one and only* exception to the above statement is that RULEs to not get used on COPY. The reason for this is that RULEs rewrite queries and after the COPY FROM header there isn't a query to rewrite, it's just a pile of data. This is in the docs, but it's not easy to find. It's actually just one line in the "Notes" section of http://www.postgresql.org/docs/8.0/static/sql-copy.html . Perhaps we should add some more verbiage (he says, non-voluntarily...)? -- Mike Rylander mrylander@gmail.com GPLS -- PINES Development Database Developer http://open-ils.org