pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to improve the performance of creating index
5+ messages / 4 participants
[nested] [flat]
* how to improve the performance of creating index
@ 2007-10-19 11:57 Yinan Li <yinanli@cse.ust.hk>
0 siblings, 3 replies; 5+ messages in thread
From: Yinan Li @ 2007-10-19 11:57 UTC (permalink / raw)
To: pgsql-performance
Hi, all
I am trying to improve the performance of creating index.
I've set shared_buffers = 1024MB
Effective_cache_size = 1024MB
Work_mem = 1GB
Maintenance_work_mem=512MB
(I'm sure that the server process has received the SIGHUP signal)
However, when create index, I found that the memory used by Postgres is only
50MB. And it is very slow. How to make it faster?
All helps are appreciated.
Thanks.
Yinan
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: how to improve the performance of creating index
@ 2007-10-19 12:03 Heikki Linnakangas <heikki@enterprisedb.com>
parent: Yinan Li <yinanli@cse.ust.hk>
2 siblings, 1 reply; 5+ messages in thread
From: Heikki Linnakangas @ 2007-10-19 12:03 UTC (permalink / raw)
To: Yinan Li <yinanli@cse.ust.hk>; +Cc: pgsql-performance
Yinan Li wrote:
> I am trying to improve the performance of creating index.
>
> I've set shared_buffers = 1024MB
>
> Effective_cache_size = 1024MB
>
> Work_mem = 1GB
>
> Maintenance_work_mem=512MB
>
> (I'm sure that the server process has received the SIGHUP signal)
>
> However, when create index, I found that the memory used by Postgres is only
> 50MB. And it is very slow. How to make it faster?
What version of Postgres are you using? How much RAM does the box have?
How big is the table? How long does the index build take? What kind of
an I/O system do you have?
maintenance_work_mem is the one that controls how much memory is used
for the sort.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: how to improve the performance of creating index
@ 2007-10-19 12:52 Chris Browne <cbbrowne@acm.org>
parent: Yinan Li <yinanli@cse.ust.hk>
2 siblings, 0 replies; 5+ messages in thread
From: Chris Browne @ 2007-10-19 12:52 UTC (permalink / raw)
To: pgsql-performance
yinanli@cse.ust.hk ("Yinan Li") writes:
> I am trying to improve the performance of creating index.:p>
>
> I've set shared_buffers = 1024MB:p>
>
> Effective_cache_size = 1024MB:p>
>
> Work_mem = 1GB:p>
>
> Maintenance_work_mem=512MB:p>
>
> (I'm sure that the server process has received the SIGHUP signal):p>
>
> However, when create index, I found that the memory used by Postgres is only 50MB. And it is very
> slow. How to make it faster?:p>
>
> All helps are appreciated.:p>
Those values seem rather large, with the exception of the effective
cache size, which I would expect to be somewhat bigger, based on the
other values.
Note that the values for work_mem and maintenance_work_mem get used
each time something is sorted or maintained. So if those values get
set high, this can pretty easily lead to scary amounts of swapping,
which would tend to lead to things getting "very slow."
You may want to do a census as to how much resources you have on the
server. Knowing that would help people make more rational evaluations
of whether your parameters are sensible or not.
--
(reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc"))
http://linuxdatabases.info/info/languages.html
All syllogisms have three parts, therefore this is not a syllogism.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: how to improve the performance of creating index
@ 2007-10-19 14:50 Heikki Linnakangas <heikki@enterprisedb.com>
parent: Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 0 replies; 5+ messages in thread
From: Heikki Linnakangas @ 2007-10-19 14:50 UTC (permalink / raw)
To: Yinan Li <yinanli@cse.ust.hk>; +Cc: pgsql-performance
Please keep the list cc'd so that others can help.
Yinan Li wrote:
> What version of Postgres are you using?
> 8.2.4
> How much RAM does the box have?
> 2G
> How big is the table?
> 256M tuples, each tuple contains 2 integers.
> How long does the index build take?
> About 2 hours
> What kind of an I/O system do you have?
> A SATA disk (7200 rpm)
2 hours does sound like a very long time. With a table like that, we're
talking about ~7-9 GB worth of data if I did the math right.
You could try lowering shared_buffers to something like 50 MB while you
build the index, to leave more RAM available for OS caching.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: how to improve the performance of creating index
@ 2007-10-19 18:21 Scott Marlowe <scott.marlowe@gmail.com>
parent: Yinan Li <yinanli@cse.ust.hk>
2 siblings, 0 replies; 5+ messages in thread
From: Scott Marlowe @ 2007-10-19 18:21 UTC (permalink / raw)
To: Yinan Li <yinanli@cse.ust.hk>; +Cc: pgsql-performance
On 10/19/07, Yinan Li <yinanli@cse.ust.hk> wrote:
>
> Hi, all
> I am trying to improve the performance of creating index.
> I've set shared_buffers = 1024MB
> Effective_cache_size = 1024MB
> Work_mem = 1GB
> Maintenance_work_mem=512MB
> (I'm sure that the server process has received the SIGHUP signal)
>
> However, when create index, I found that the memory used by Postgres is only
> 50MB. And it is very slow. How to make it faster?
What, exactly is the create index statement? I assume that if there's
only two columns then at worst it's a two part index (i.e. column1,
column2) which can get rather large.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-10-19 18:21 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-19 11:57 how to improve the performance of creating index Yinan Li <yinanli@cse.ust.hk>
2007-10-19 12:03 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-10-19 14:50 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-10-19 12:52 ` Chris Browne <cbbrowne@acm.org>
2007-10-19 18:21 ` Scott Marlowe <scott.marlowe@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