I have a Postgres instance building a GIN index. It's looking at about 200,000 rows and it's so far taken about 9 hours. Who knows how long it will take eventually. The problem is that it's using about 2% of CPU when I'd like it to use more like 90%. Is there any way to force it to speed up?
4
-
Are you sure the performance is CPU bound?jball– jball2010-03-16 21:09:42 +00:00Commented Mar 16, 2010 at 21:09
-
No I'm not sure. I'm just surprised by the amount of CPU it isn't using. A dump of the whole table comes to 130 MB. It's using 2.6% CPU and 2.7% RAM with enough of each spare.Joe– Joe2010-03-16 21:13:15 +00:00Commented Mar 16, 2010 at 21:13
-
1Did you set a proper amount of RAM for maintenance_work_mem ? Edit: There isn't a lock blocking the making of the index?Frank Heikens– Frank Heikens2010-03-16 21:16:43 +00:00Commented Mar 16, 2010 at 21:16
-
@Frank - I set maintenance_work_mem to something more sensible and it's now at 99%. I'm much happier!Joe– Joe2010-03-16 22:17:44 +00:00Commented Mar 16, 2010 at 22:17
Add a comment
|
1 Answer
The main bottleneck is probably disk IO and not CPU.
If you're on a Windows machine, you can check disk IOs using Process Explorer (freeware), if on Unix, use iostat, sar, DTrace (haven't done the latter in a while so not 100% sure of the best tool)
3 Comments
Joey
The Resource Monitor on Windows also shows this and you can usually easily see there whether the machine is currently CPU-limited or by IO.
Joe
Thanks! I'm on unix (Linux & Mac). I'll take a look at the options.
Joe
I think it was due to paging, leading to thrashing. The answer was to increase the working memory by
set maintenance_work_mem = 102400;.