pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to improve insert speed with index on text column
19+ messages / 11 participants
[nested] [flat]
* How to improve insert speed with index on text column
@ 2012-01-30 09:27 Saurabh <saurabh.b85@gmail.com>
0 siblings, 3 replies; 19+ messages in thread
From: Saurabh @ 2012-01-30 09:27 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I am using Postgresql database for our project and doing some
performance testing. We need to insert millions of record with indexed
columns. We have 5 columns in table. I created index on integer only
then performance is good but when I created index on text column as
well then the performance reduced to 1/8th times. My question is how I
can improve performance when inserting data using index on text
column?
Thanks,
Saurabh
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: How to improve insert speed with index on text column
@ 2012-01-30 14:10 Claudio Freire <klaussfreire@gmail.com>
parent: Saurabh <saurabh.b85@gmail.com>
2 siblings, 0 replies; 19+ messages in thread
From: Claudio Freire @ 2012-01-30 14:10 UTC (permalink / raw)
To: Saurabh <saurabh.b85@gmail.com>; +Cc: pgsql-performance
On Mon, Jan 30, 2012 at 6:27 AM, Saurabh <saurabh.b85@gmail.com> wrote:
> Hi all,
>
> I am using Postgresql database for our project and doing some
> performance testing. We need to insert millions of record with indexed
> columns. We have 5 columns in table. I created index on integer only
> then performance is good but when I created index on text column as
> well then the performance reduced to 1/8th times. My question is how I
> can improve performance when inserting data using index on text
> column?
Post all the necessary details. Schema, table and index sizes, some config...
Assuming your text column is a long one (long text), this results in
really big indices.
Assuming you only search by equality, you can make it a lot faster by hashing.
Last time I checked, hash indices were quite limited and performed
badly, but I've heard they improved quite a bit. If hash indices don't
work for you, you can always build them on top of btree indices by
indexing on the expression hash(column) and comparing as hash(value) =
hash(column) and value = column.
On a table indexed by URL I have, this improved things immensely. Both
lookup and insertion times improved.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: How to improve insert speed with index on text column
@ 2012-01-30 15:24 Jeff Janes <jeff.janes@gmail.com>
parent: Saurabh <saurabh.b85@gmail.com>
2 siblings, 1 reply; 19+ messages in thread
From: Jeff Janes @ 2012-01-30 15:24 UTC (permalink / raw)
To: Saurabh <saurabh.b85@gmail.com>; +Cc: pgsql-performance
On Mon, Jan 30, 2012 at 1:27 AM, Saurabh <saurabh.b85@gmail.com> wrote:
> Hi all,
>
> I am using Postgresql database for our project and doing some
> performance testing. We need to insert millions of record with indexed
> columns. We have 5 columns in table. I created index on integer only
> then performance is good but when I created index on text column as
> well then the performance reduced to 1/8th times.
Inserting into a indexed table causes a lot of random access to the
underlying index (unless the data is inserted in an order which
corresponds to the index order of all indexes, which is not likely to
happen with multiple indexes). As soon as your indexes don't fit in
cache, your performance will collapse.
What if you don't have the integer index but just the text? What is
the average length of the data in the text field? Is your system CPU
limited or IO limited during the load?
> My question is how I
> can improve performance when inserting data using index on text
> column?
The only "magic" answer is to drop the index and rebuild after the
insert. If that doesn't work for you, then you have to identify your
bottleneck and fix it. That can't be done with just the information
you provide.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: How to improve insert speed with index on text column
@ 2012-01-30 17:46 Saurabh <saurabh.b85@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 3 replies; 19+ messages in thread
From: Saurabh @ 2012-01-30 17:46 UTC (permalink / raw)
To: pgsql-performance
Thank you for the information.
Schema of table is:
ID bigint
company_name text
data_set text
time timestamp
Date date
Length of company_name is not known so it is of datatype text. I need
to build the index on company_name and ID. And then insert the
records. I can not create the index after insertion because user can
search the data as well while insertion.
Machine is of 8 core, os centos6 and 8 GB of RAM.
Here is my configuration:
max_connections = 100
shared_buffers = 32MB
wal_buffers = 1024KB
checkpoint_segments = 3
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: How to improve insert speed with index on text column
@ 2012-01-30 18:20 Claudio Freire <klaussfreire@gmail.com>
parent: Saurabh <saurabh.b85@gmail.com>
2 siblings, 0 replies; 19+ messages in thread
From: Claudio Freire @ 2012-01-30 18:20 UTC (permalink / raw)
To: Saurabh <saurabh.b85@gmail.com>; +Cc: pgsql-performance
On Mon, Jan 30, 2012 at 2:46 PM, Saurabh <saurabh.b85@gmail.com> wrote:
> max_connections = 100
> shared_buffers = 32MB
> wal_buffers = 1024KB
> checkpoint_segments = 3
That's a default config isn't it?
You'd do well to try and optimize it for your system. The defaults are
really, reeallly conservative.
You should also consider normalizing. I'm assuming company_name could
be company_id ? (ie: each will have many rows). Otherwise I cannot see
how you'd expect to be *constantly* inserting millions of rows. If
it's a one-time initialization thing, just drop the indices and
recreate them as you've been suggested. If you create new records all
the time, I'd bet you'll also have many rows with the same
company_name, so normalizing would be a clear win.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: How to improve insert speed with index on text column
@ 2012-01-30 18:33 Andy Colson <andy@squeakycode.net>
parent: Saurabh <saurabh.b85@gmail.com>
2 siblings, 0 replies; 19+ messages in thread
From: Andy Colson @ 2012-01-30 18:33 UTC (permalink / raw)
To: Saurabh <saurabh.b85@gmail.com>; +Cc: pgsql-performance
On 1/30/2012 3:27 AM, Saurabh wrote:
> Hi all,
>
> I am using Postgresql database for our project and doing some
> performance testing. We need to insert millions of record with indexed
> columns. We have 5 columns in table. I created index on integer only
> then performance is good but when I created index on text column as
> well then the performance reduced to 1/8th times. My question is how I
> can improve performance when inserting data using index on text
> column?
>
> Thanks,
> Saurabh
>
Do it in a single transaction, and use COPY.
-Andy
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: How to improve insert speed with index on text column
@ 2012-01-30 20:32 Jeff Janes <jeff.janes@gmail.com>
parent: Saurabh <saurabh.b85@gmail.com>
2 siblings, 1 reply; 19+ messages in thread
From: Jeff Janes @ 2012-01-30 20:32 UTC (permalink / raw)
To: Saurabh <saurabh.b85@gmail.com>; +Cc: pgsql-performance
On Mon, Jan 30, 2012 at 9:46 AM, Saurabh <saurabh.b85@gmail.com> wrote:
> Thank you for the information.
>
> Schema of table is:
>
> ID bigint
> company_name text
> data_set text
> time timestamp
> Date date
>
> Length of company_name is not known so it is of datatype text. I need
> to build the index on company_name and ID. And then insert the
> records. I can not create the index after insertion because user can
> search the data as well while insertion.
>
> Machine is of 8 core, os centos6 and 8 GB of RAM.
>
> Here is my configuration:
>
> shared_buffers = 32MB
That is very small for your server. I'd use at least 512MB, and maybe 2GB
> wal_buffers = 1024KB
If you are using 9.1, I would have this set to the default of -1 and
let the database decide for itself what to use.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: How to improve insert speed with index on text column
@ 2012-01-31 09:29 Saurabh <saurabh.b85@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Saurabh @ 2012-01-31 09:29 UTC (permalink / raw)
To: pgsql-performance
I changed the configuration in postgresql.conf. Following are the
changed parameters:
shared_buffers = 1GB
maintenance_work_mem = 50MB
checkpoint_segments = 64
wal_buffers = 5MB
autovacuum = off
Insert the records in the database and got a very good performance it
is increased by 6 times.
Can you please tell me the purpose of shared_buffer and
maintenance_work_mem parameter?
Thanks,
Saurabh
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: How to improve insert speed with index on text column
@ 2012-01-31 11:21 Tomas Vondra <tv@fuzzy.cz>
parent: Saurabh <saurabh.b85@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Tomas Vondra @ 2012-01-31 11:21 UTC (permalink / raw)
To: Saurabh <saurabh.b85@gmail.com>; +Cc: pgsql-performance
On 31 Leden 2012, 10:29, Saurabh wrote:
> I changed the configuration in postgresql.conf. Following are the
> changed parameters:
>
> shared_buffers = 1GB
> maintenance_work_mem = 50MB
> checkpoint_segments = 64
> wal_buffers = 5MB
> autovacuum = off
>
> Insert the records in the database and got a very good performance it
> is increased by 6 times.
>
> Can you please tell me the purpose of shared_buffer and
> maintenance_work_mem parameter?
Shared buffers is the cache maintained by PostgreSQL. All all the data
that you read/write need to go through shared buffers.
Maintenance_work_mem specifies how much memory can "maintenance tasks"
(e.g. autovacuum, reindex, etc.) use. This is similar to work_mem for
common queries (sorting, grouping, ...).
Tomas
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: How to improve insert speed with index on text column
@ 2012-01-31 18:46 Josh Berkus <josh@agliodbs.com>
parent: Tomas Vondra <tv@fuzzy.cz>
0 siblings, 2 replies; 19+ messages in thread
From: Josh Berkus @ 2012-01-31 18:46 UTC (permalink / raw)
To: pgsql-performance
> Shared buffers is the cache maintained by PostgreSQL. All all the data
> that you read/write need to go through shared buffers.
While this is technically true, I need to point out that you generally
increase shared_buffers for high concurrency, and for reads, not for
writes, especially for row-at-a-time inserts. There's just not that
much memory required (although more than the out-of-the-box defaults).
I'd suggest increasing wal_buffers to 16MB, which is the maximum useful
amount, rather than 5MB.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: How to improve insert speed with index on text column
@ 2012-01-31 20:20 Merlin Moncure <mmoncure@gmail.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 19+ messages in thread
From: Merlin Moncure @ 2012-01-31 20:20 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
On Tue, Jan 31, 2012 at 12:46 PM, Josh Berkus <josh@agliodbs.com> wrote:
>
>> Shared buffers is the cache maintained by PostgreSQL. All all the data
>> that you read/write need to go through shared buffers.
>
> While this is technically true, I need to point out that you generally
> increase shared_buffers for high concurrency, and for reads, not for
> writes, especially for row-at-a-time inserts. There's just not that
> much memory required (although more than the out-of-the-box defaults).
>
> I'd suggest increasing wal_buffers to 16MB, which is the maximum useful
> amount, rather than 5MB.
yeah -- postgresql.conf settings are not going to play a big role here unless:
*) you defer index build to the end of the load, and do CREATE INDEX
and crank maintenance_work_mem
*) you are doing lots of transactions and relax your sync policy via
synchronous_commit
what's almost certainly happening here is that the text index is
writing out a lot more data. what's the average length of your key?
If I'm inspecting sizes of tables/indexes which start with 'foo', I can do this:
postgres=# select relname,
pg_size_pretty(pg_relation_size(relname::text)) from pg_class where
relname like 'foo%';
relname | pg_size_pretty
-----------+----------------
foo | 40 kB
foo_i_idx | 40 kB
foo_t_idx | 40 kB
We'd like to see the numbers for your table/indexes in question.
merlin
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: How to improve insert speed with index on text column
@ 2012-02-01 02:11 Jeff Janes <jeff.janes@gmail.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 19+ messages in thread
From: Jeff Janes @ 2012-02-01 02:11 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
On Tue, Jan 31, 2012 at 10:46 AM, Josh Berkus <josh@agliodbs.com> wrote:
>
>> Shared buffers is the cache maintained by PostgreSQL. All all the data
>> that you read/write need to go through shared buffers.
>
> While this is technically true, I need to point out that you generally
> increase shared_buffers for high concurrency, and for reads, not for
> writes, especially for row-at-a-time inserts. There's just not that
> much memory required (although more than the out-of-the-box defaults).
When inserting rows in bulk (even just with inserts in a tight loop)
into indexed tables, I often see the performance collapse soon after
the active index size exceeds shared_buffers. Or at least,
shared_buffers + however much dirty data the kernel is willing to
tolerate. But that later value is hard to predict. Increasing the
shared_buffers can really help a lot here. I'm sure this behavior
depends greatly on your IO subsystem.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: How to improve insert speed with index on text column
@ 2012-02-01 03:29 Rosser Schwarz <rosser.schwarz@gmail.com>
parent: Saurabh <saurabh.b85@gmail.com>
2 siblings, 1 reply; 19+ messages in thread
From: Rosser Schwarz @ 2012-02-01 03:29 UTC (permalink / raw)
To: Saurabh <saurabh.b85@gmail.com>; +Cc: pgsql-performance
On Mon, Jan 30, 2012 at 9:46 AM, Saurabh <saurabh.b85@gmail.com> wrote:
> I can not create the index after insertion because user can
> search the data as well while insertion.
Remember, DDL is transactional in PostgreSQL. In principle, you
should be able to drop the index, do your inserts, and re-create the
index without affecting concurrent users, if you do all of that inside
an explicit transaction. Doing the inserts inside a transaction may
speed them up, as well.
rls
--
:wq
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: How to improve insert speed with index on text column
@ 2012-02-01 03:49 Claudio Freire <klaussfreire@gmail.com>
parent: Rosser Schwarz <rosser.schwarz@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Claudio Freire @ 2012-02-01 03:49 UTC (permalink / raw)
To: Rosser Schwarz <rosser.schwarz@gmail.com>; +Cc: Saurabh <saurabh.b85@gmail.com>; pgsql-performance
On Wed, Feb 1, 2012 at 12:29 AM, Rosser Schwarz
<rosser.schwarz@gmail.com> wrote:
> Remember, DDL is transactional in PostgreSQL. In principle, you
> should be able to drop the index, do your inserts, and re-create the
> index without affecting concurrent users, if you do all of that inside
> an explicit transaction. Doing the inserts inside a transaction may
> speed them up, as well.
Creating an index requires an update lock on the table, and an
exclusive lock on the system catalog.
Even though with "CONCURRENTLY" it's only for a short while.
So it does affect concurrent users.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: How to improve insert speed with index on text column
@ 2012-02-01 03:51 Claudio Freire <klaussfreire@gmail.com>
parent: Claudio Freire <klaussfreire@gmail.com>
0 siblings, 0 replies; 19+ messages in thread
From: Claudio Freire @ 2012-02-01 03:51 UTC (permalink / raw)
To: Rosser Schwarz <rosser.schwarz@gmail.com>; +Cc: Saurabh <saurabh.b85@gmail.com>; pgsql-performance
On Wed, Feb 1, 2012 at 12:49 AM, Claudio Freire <klaussfreire@gmail.com> wrote:
> On Wed, Feb 1, 2012 at 12:29 AM, Rosser Schwarz
> <rosser.schwarz@gmail.com> wrote:
>> Remember, DDL is transactional in PostgreSQL. In principle, you
>> should be able to drop the index, do your inserts, and re-create the
>> index without affecting concurrent users, if you do all of that inside
>> an explicit transaction. Doing the inserts inside a transaction may
>> speed them up, as well.
>
> Creating an index requires an update lock on the table, and an
> exclusive lock on the system catalog.
> Even though with "CONCURRENTLY" it's only for a short while.
> So it does affect concurrent users.
Forgot to mention that if you don't commit the drop, you see no
performance increase.
So:
begin
drop
insert
create
commit
Does not work to improve performance. At all.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: How to improve insert speed with index on text column
@ 2012-02-03 19:03 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 19+ messages in thread
From: Kevin Grittner @ 2012-02-03 19:03 UTC (permalink / raw)
To: saurabh.b85@gmail.com; pgsql-performance
Saurabh wrote:
> wal_buffers = 5MB
As as already been suggested, use 16MB (or if the version you're
using supports it, the default of -1);
> autovacuum = off
If the only activity while this is off is a bulk load, that might be
OK, but be sure *not* to leave this off. You will almost certainly
regret very much later. Your tables will tend to bloat and slowly
get very slow and very big. At that point it will be much more
painful to do aggressive maintenance to clean things up. If you
think you have some particular reason to turn it off, please discuss
it here -- you might have better options.
-Kevin
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: How to improve insert speed with index on text column
@ 2012-02-03 20:54 Scott Marlowe <scott.marlowe@gmail.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 0 replies; 19+ messages in thread
From: Scott Marlowe @ 2012-02-03 20:54 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
On Tue, Jan 31, 2012 at 1:20 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> yeah -- postgresql.conf settings are not going to play a big role here unless:
> *) you defer index build to the end of the load, and do CREATE INDEX
> and crank maintenance_work_mem
> *) you are doing lots of transactions and relax your sync policy via
> synchronous_commit
checkpoint segments sometimes helps for loading large amounts of data.
> what's almost certainly happening here is that the text index is
> writing out a lot more data. what's the average length of your key?
Yeah, the OP really needs to switch to hashes for the indexes. And
like another poster mentioned, it's often faster to use a btree of
hashes than to use the built in hash index type.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: How to improve insert speed with index on text column
@ 2012-02-05 17:29 Saurabh <saurabh.b85@gmail.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 19+ messages in thread
From: Saurabh @ 2012-02-05 17:29 UTC (permalink / raw)
To: pgsql-performance
Kelvin,
My intention to keep autovacuum as off is bulk loading only. I was
thinking after bullk load I will change it.
I changed wal_buffer from 5MB to 16MB but I got same performance that
I got with 5MB (even less).
Thanks,
Saurabh
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: How to improve insert speed with index on text column
@ 2012-02-29 18:35 Robert Haas <robertmhaas@gmail.com>
parent: Saurabh <saurabh.b85@gmail.com>
0 siblings, 0 replies; 19+ messages in thread
From: Robert Haas @ 2012-02-29 18:35 UTC (permalink / raw)
To: Saurabh <saurabh.b85@gmail.com>; +Cc: pgsql-performance
On Sun, Feb 5, 2012 at 12:29 PM, Saurabh <saurabh.b85@gmail.com> wrote:
> My intention to keep autovacuum as off is bulk loading only. I was
> thinking after bullk load I will change it.
>
> I changed wal_buffer from 5MB to 16MB but I got same performance that
> I got with 5MB (even less).
Does it help if you create the index using COLLATE "C"? Assuming
you're on 9.1.x...
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2012-02-29 18:35 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-01-30 09:27 How to improve insert speed with index on text column Saurabh <saurabh.b85@gmail.com>
2012-01-30 14:10 ` Claudio Freire <klaussfreire@gmail.com>
2012-01-30 15:24 ` Jeff Janes <jeff.janes@gmail.com>
2012-01-30 17:46 ` Saurabh <saurabh.b85@gmail.com>
2012-01-30 18:20 ` Claudio Freire <klaussfreire@gmail.com>
2012-01-30 20:32 ` Jeff Janes <jeff.janes@gmail.com>
2012-01-31 09:29 ` Saurabh <saurabh.b85@gmail.com>
2012-01-31 11:21 ` Tomas Vondra <tv@fuzzy.cz>
2012-01-31 18:46 ` Josh Berkus <josh@agliodbs.com>
2012-01-31 20:20 ` Merlin Moncure <mmoncure@gmail.com>
2012-02-03 20:54 ` Scott Marlowe <scott.marlowe@gmail.com>
2012-02-01 02:11 ` Jeff Janes <jeff.janes@gmail.com>
2012-02-01 03:29 ` Rosser Schwarz <rosser.schwarz@gmail.com>
2012-02-01 03:49 ` Claudio Freire <klaussfreire@gmail.com>
2012-02-01 03:51 ` Claudio Freire <klaussfreire@gmail.com>
2012-01-30 18:33 ` Andy Colson <andy@squeakycode.net>
2012-02-03 19:03 Re: How to improve insert speed with index on text column Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-02-05 17:29 ` Saurabh <saurabh.b85@gmail.com>
2012-02-29 18:35 ` Robert Haas <robertmhaas@gmail.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