From: | "Andrus" <kobruleht2(at)hot(dot)ee> |
---|---|
To: | "Tomas Vondra" <tv(at)fuzzy(dot)cz> |
Cc: | "Scott Carey" <scott(at)richrelevance(dot)com>, "PFC" <lists(at)peufeu(dot)com>, <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Hash join on int takes 8..114 seconds |
Date: | 2008-11-24 12:35:25 |
Message-ID: | 7395E52430A9420E8248D54726DC634B@andrusnotebook |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Tomas,
> Let's suppose you set a reasonable value (say 8096) instead of 2GB. That
> gives about 160MB.
> Anyway this depends - if you have a lot of slow queries caused by on-disk
> sorts / hash tables, use a higher value. Otherwise leave it as it is.
Probably product orders table is frequently joined which product table.
currently there was work_memory = 512 in conf file.
I changed it to work_memory = 8096
>>> If it is all cached in memory, you may want to ensure that your
>>> shared_buffers is a reasonalbe size so that there is less shuffling of
>>> data
>>> from the kernel to postgres and back. Generally, shared_buffers works
>>> best
>>> between 5% and 25% of system memory.
>>
>> currently shared_buffers = 15000
>
> That's 120MB, i.e. about 6% of the memory. Might be a little bit higher,
> but seems reasonable.
I changed it to 20000
> Given the fact that the performance issues are caused by bloated tables
> and / or slow I/O subsystem, moving to a similar system won't help I
> guess.
I have ran VACUUM FULL ANALYZE VERBOSE
and set MAX_FSM_PAGES = 150000
So there is no any bloat except pg_shdepend indexes which should not affect
to query speed.
Andrus.
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Clark | 2008-11-24 13:23:54 | Re: Perc 3 DC |
Previous Message | Richard Huxton | 2008-11-24 09:36:17 | Re: Increasing pattern index query speed |