Thread: Postgresql INSERT speed (how to improve performance)?
Hi, I've googled and yahooed and most of the performance tweaks suggested cover SELECT speed, some cover COPY speed with things like turning fsync off and such. But I still have not found how to improve regular INSERT speed on Postgresql. I have a table in MySQL with three compound indexes. I have only three columns from this table also in PostgreSQL, which serves as a cache of sorts for fast queries, and this table has only ONE main index on the primary key! INSERTing into MySQL takes 0.0001 seconds per insert query. INSERTing into PgSQL takes 0.871 seconds per (much smaller) insert query. What can I do to improve this performance? What could be going wrong to elicit such poor insertion performance from Postgresql? Thanks.
Lim Berger wrote: > > INSERTing into MySQL takes 0.0001 seconds per insert query. > INSERTing into PgSQL takes 0.871 seconds per (much smaller) insert query. > > What can I do to improve this performance? What could be going wrong > to elicit such poor insertion performance from Postgresql? > > Thanks. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > > The first thing I would ask is what type of DB engine are you using while doing the inserts on MySQL? The reason MySQL is doing the inserts faster is it does not have transaction support if you are using anything other than InnoDB. With that said you can increase your insert performance by simply using a transaction and committing every 1000 rows or so. If you do this you will see a huge performance increase. hope that helps. Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of Lightning Admin for PostgreSQL and MySQL
On 8/14/07, Lim Berger <straightfwd007@gmail.com> wrote: > INSERTing into MySQL takes 0.0001 seconds per insert query. > INSERTing into PgSQL takes 0.871 seconds per (much smaller) insert query. > > What can I do to improve this performance? What could be going wrong > to elicit such poor insertion performance from Postgresql? MySQL might not be writing the data straight out to disk ... just a guess. > Thanks. Cheers, Andrej
On 8/14/07, Andrej Ricnik-Bay <andrej.groups@gmail.com> wrote: > On 8/14/07, Lim Berger <straightfwd007@gmail.com> wrote: > > > INSERTing into MySQL takes 0.0001 seconds per insert query. > > INSERTing into PgSQL takes 0.871 seconds per (much smaller) insert query. > > > > What can I do to improve this performance? What could be going wrong > > to elicit such poor insertion performance from Postgresql? > MySQL might not be writing the data straight out > to disk ... just a guess. > The MYSQL table is MYISAM, yes, so no transaction support. I would like PgSQL to do the same. These are not a batch of queries so I cannot bundle them inside a transaction. These are individual submissions from the web. To make PG behave in the above manner, I have the following in my conf: commit_delay = 0 fsync = on wal_buffers=64 checkpoint_segments=64 checkpoint_timeout=900 Am I missing something? (I may well be). Would explicitly issuing a "COMMIT" command help at all? Should I do the following: BEGIN TRANSACTION; INSERT INTO...; COMMIT; Would this be faster?
"Lim Berger" <straightfwd007@gmail.com> writes:
> I have a table in MySQL with three compound indexes. I have only three
> columns from this table also in PostgreSQL, which serves as a cache of
> sorts for fast queries, and this table has only ONE main index on the
> primary key!
> INSERTing into MySQL takes 0.0001 seconds per insert query.
> INSERTing into PgSQL takes 0.871 seconds per (much smaller) insert query.
You had better provide some details, because that's completely out of
line, assuming that by "insert query" you mean insert one row. For a
comparison point, I get this on a rather old and slow machine:
regression=# create table t1 (f1 int, f2 int, f3 int,
regression(# unique(f1,f2),
regression(# unique(f2,f3),
regression(# unique(f1,f3));
NOTICE: CREATE TABLE / UNIQUE will create implicit index "t1_f1_key" for table "t1"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "t1_f2_key" for table "t1"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "t1_f1_key1" for table "t1"
CREATE TABLE
regression=# \timing
Timing is on.
regression=# insert into t1 values(1,2,3);
INSERT 0 1
Time: 9.048 ms
regression=# insert into t1 values(1,7,4);
INSERT 0 1
Time: 4.357 ms
regression=# insert into t1 values(11,7,5);
INSERT 0 1
Time: 3.998 ms
regression=#
regards, tom lane
Lim Berger wrote: > On 8/14/07, Andrej Ricnik-Bay <andrej.groups@gmail.com> wrote: > >> On 8/14/07, Lim Berger <straightfwd007@gmail.com> wrote: >> >> >>> INSERTing into MySQL takes 0.0001 seconds per insert query. >>> INSERTing into PgSQL takes 0.871 seconds per (much smaller) insert query. >>> >>> What can I do to improve this performance? What could be going wrong >>> to elicit such poor insertion performance from Postgresql? >>> Lim, Are you sure you are not confusing Seconds VS Milliseconds from one query tool to the other? What tools are you using to show the query times? As far as I know the psql command line tool shows milliseconds not seconds. You should probably post some more info like the actual insert query used and the table. I have never seen a insert take that long even a big one, unless you have some network latency to the PGSQL host and it's causing the time to be messed up on the client. Might also help to let us know exactly how you are timing this stuff. Are you connecting remotely via PSQL or are you connecting via SSH and running psql or mysql that way? Later, Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for PostgreSQL and MySQL
On 8/14/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Lim Berger" <straightfwd007@gmail.com> writes:
> > I have a table in MySQL with three compound indexes. I have only three
> > columns from this table also in PostgreSQL, which serves as a cache of
> > sorts for fast queries, and this table has only ONE main index on the
> > primary key!
>
> > INSERTing into MySQL takes 0.0001 seconds per insert query.
> > INSERTing into PgSQL takes 0.871 seconds per (much smaller) insert query.
>
> You had better provide some details, because that's completely out of
> line, assuming that by "insert query" you mean insert one row. For a
> comparison point, I get this on a rather old and slow machine:
>
> regression=# create table t1 (f1 int, f2 int, f3 int,
> regression(# unique(f1,f2),
> regression(# unique(f2,f3),
> regression(# unique(f1,f3));
> NOTICE: CREATE TABLE / UNIQUE will create implicit index "t1_f1_key" for table "t1"
> NOTICE: CREATE TABLE / UNIQUE will create implicit index "t1_f2_key" for table "t1"
> NOTICE: CREATE TABLE / UNIQUE will create implicit index "t1_f1_key1" for table "t1"
> CREATE TABLE
> regression=# \timing
> Timing is on.
> regression=# insert into t1 values(1,2,3);
> INSERT 0 1
> Time: 9.048 ms
> regression=# insert into t1 values(1,7,4);
> INSERT 0 1
> Time: 4.357 ms
> regression=# insert into t1 values(11,7,5);
> INSERT 0 1
> Time: 3.998 ms
> regression=#
Thanks Tom. But on a newly minted table, sure, the performance would
be great. My table now has about 3 million rows (both in MySQL and
PG).
Here's the table definition:
Table "public.cachedstats"
Column | Type | Modifiers
-----------------------+-----------------------+------------------------------
id | bigint | not null
prof_name | character varying(20) | not null
notes | text | not null
inform_prof_on_change | character(1) | not null default 'N'::bpchar
Indexes:
"cachedstats_pkey" PRIMARY KEY, btree (id)
"idx_cachedstats_unique_prof_name" UNIQUE, btree (alias)
How can I test the bottleneck?
On 8/14/07, Lim Berger <straightfwd007@gmail.com> wrote:
> On 8/14/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > "Lim Berger" <straightfwd007@gmail.com> writes:
> > > I have a table in MySQL with three compound indexes. I have only three
> > > columns from this table also in PostgreSQL, which serves as a cache of
> > > sorts for fast queries, and this table has only ONE main index on the
> > > primary key!
> >
> > > INSERTing into MySQL takes 0.0001 seconds per insert query.
> > > INSERTing into PgSQL takes 0.871 seconds per (much smaller) insert query.
> >
> > You had better provide some details, because that's completely out of
> > line, assuming that by "insert query" you mean insert one row. For a
> > comparison point, I get this on a rather old and slow machine:
> >
> > regression=# create table t1 (f1 int, f2 int, f3 int,
> > regression(# unique(f1,f2),
> > regression(# unique(f2,f3),
> > regression(# unique(f1,f3));
> > NOTICE: CREATE TABLE / UNIQUE will create implicit index "t1_f1_key" for table "t1"
> > NOTICE: CREATE TABLE / UNIQUE will create implicit index "t1_f2_key" for table "t1"
> > NOTICE: CREATE TABLE / UNIQUE will create implicit index "t1_f1_key1" for table "t1"
> > CREATE TABLE
> > regression=# \timing
> > Timing is on.
> > regression=# insert into t1 values(1,2,3);
> > INSERT 0 1
> > Time: 9.048 ms
> > regression=# insert into t1 values(1,7,4);
> > INSERT 0 1
> > Time: 4.357 ms
> > regression=# insert into t1 values(11,7,5);
> > INSERT 0 1
> > Time: 3.998 ms
> > regression=#
>
>
>
> Thanks Tom. But on a newly minted table, sure, the performance would
> be great. My table now has about 3 million rows (both in MySQL and
> PG).
>
> Here's the table definition:
>
>
> Table "public.cachedstats"
> Column | Type | Modifiers
> -----------------------+-----------------------+------------------------------
> id | bigint | not null
> prof_name | character varying(20) | not null
> notes | text | not null
> inform_prof_on_change | character(1) | not null default 'N'::bpchar
>
> Indexes:
> "cachedstats_pkey" PRIMARY KEY, btree (id)
> "idx_cachedstats_unique_prof_name" UNIQUE, btree (alias)
>
>
> How can I test the bottleneck?
>
Btw, the query is as simple as:
INSERT INTO cachedstats
(id, prof_name, notes, inform_prof_on_change)
VALUES
(3190087, 'Lim Berger', '.....text of about 1000 chars', 'Y');
I am testing through PHP microtime function. The query is administered
through pg_query() function of PHP. I know there could be some latency
coming in from the PHP's PG functions' overhead, but not such an order
of magnitude different from "mysqli_query"! (I hope -- because this
is quite a common real-world situation I would think).
"Lim Berger" <straightfwd007@gmail.com> writes:
>> Thanks Tom. But on a newly minted table, sure, the performance would
>> be great. My table now has about 3 million rows (both in MySQL and
>> PG).
Well, INSERT speed is really not very dependent on table size (else I'd
have inserted a few zillion rows before timing it). Personally I rather
like Tony's theory that you've gotten confused about what the timing
units are.
> Btw, the query is as simple as:
> INSERT INTO cachedstats
> (id, prof_name, notes, inform_prof_on_change)
> VALUES
> (3190087, 'Lim Berger', '.....text of about 1000 chars', 'Y');
These are the sorts of details that you can't leave out if you want
an intelligent response.
> I am testing through PHP microtime function.
Well, that brings up a whole host of questions, starting with whether
the PHP DB adapter you're using is any good. Can you replicate similar
timings if you feed the same query into plain psql?
regards, tom lane
"Lim Berger" <straightfwd007@gmail.com> writes: > Here's the table definition: > > Table "public.cachedstats" > Column | Type | Modifiers > -----------------------+-----------------------+------------------------------ > id | bigint | not null > prof_name | character varying(20) | not null > notes | text | not null > inform_prof_on_change | character(1) | not null default 'N'::bpchar > > Indexes: > "cachedstats_pkey" PRIMARY KEY, btree (id) > "idx_cachedstats_unique_prof_name" UNIQUE, btree (alias) What's "alias"? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
On Aug 13, 2007, at 11:54 PM, Lim Berger wrote: > I am testing through PHP microtime function. The query is administered > through pg_query() function of PHP. I know there could be some latency > coming in from the PHP's PG functions' overhead, but not such an order > of magnitude different from "mysqli_query"! (I hope -- because this > is quite a common real-world situation I would think). You might also try using prepared statements for your inserts (see PHP's pg_prepare and pg_execute functions). This will also save time you are likely using to escape the inserted strings. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL