Thread: how to improve the performance of creating index
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
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
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.
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
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. From what you said in your reply to Heikki, these settings are WAY too high. You shouldn't try to allocate more memory than your machine has to the database. with shared buffers at 1G, work mem at 1G and maint workmem at 0.5 gig you could use all your memory plus 0.5G on a single query. Set them at something more sane. shared_buffers at 128M to 512M, work_mem at 64M, and maintenance_work_mem at 128M to 512M (max) What do top, vmstat, and iostat have to say about your machine while the create query is going on? If you want it to go faster, it depends on what's holding you back. If you're CPUs are maxed out, then you might need more CPU. If your I/O is maxed, then you might need more I/O bandwidth, and if neither seems maxed out, but you've got a lot of time spend waiting / idle, then you might need faster / more memory. Until we / you know what's slow, we don't know what to change to make your create index run faster. On my reporting server at work, where we have 2 Gigs ram, and 70Million or so rows using about 45Gigs (tables and indexes) I can create a new index in about 10-15 minutes. So your machine sounds kinda slow. The primary difference between my machine and yours is that I have a 4 disk RAID-10 software array. Even if that made my machine twice as fast as yours, that doesn't explain the really slow performance of yours.