Обсуждение: memory tuning

I have 2 GB of RAM on this machine and a table with 2.9Million rows.

The most I'm getting reported on memory usage for postgres (7.4 can't upgrade
just yet) is 2.6%.

I have set the following parameters:

shared_buffers = 3000           # min 16, at least max_connections*2, 8KB each
sort_mem = 262144               # min 64, size in KB
vacuum_mem = 262144             # min 1024, size in KB


But I'm not utilizing much memory and things are running frighteningly slow.
I did run a VACUUM ANALYZE today with no incidents so I wouldn't expect that to
be a problem.  I don't think there have been more than 10^5 transactions since
then (assuming that's not too many?)

tips?

On 6/20/06 9:33 PM, "Tom Allison" <tallison@tacocat.net> wrote:


> But I'm not utilizing much memory and things are running frighteningly slow.
> I did run a VACUUM ANALYZE today with no incidents so I wouldn't expect that
> to
> be a problem.  I don't think there have been more than 10^5 transactions since
> then (assuming that's not too many?)
>
> tips?

What is slow about the database?  Do you have particular queries that are
slow?  Have you looked at EXPLAIN output?  What is the table structure?

Yes, memory settings can speed up a database (I'm no expert in the settings
arena, so I won't answer that directly), but there are often other issues
that impact performance that are at least as important.

Sean

On 6/21/06 10:55 AM, "Tom Allison" <tallison@tacocat.net> wrote:

>
> Well, I did find a number of problems with the database that took all
> night to fix.
>
> I had to REINDEX and then VACUUM FULL ANALYZE again just to make sure
> that there indexes were valid.
>
> But when all this is going on, there is very little memory use (<4%).  I
> assumed that using more memory was proportional to getting things done
> faster.

It can be, but we don't know what tasks you are trying to get done.  Also,
we don't know how you are using your database.  If you are the only user,
then your memory settings can be different than if you are expecting 100
simultaneous connections.  All that said, try this site (or any one of
several google hits) or the postgres documentation on performance tuning:

http://revsys.com/writings/postgresql-performance.html

>>
>>
>>
>> On 6/20/06 9:33 PM, "Tom Allison" <tallison@tacocat.net> wrote:
>>
>>
>>> But I'm not utilizing much memory and things are running frighteningly slow.
>>> I did run a VACUUM ANALYZE today with no incidents so I wouldn't expect that
>>> to
>>> be a problem.  I don't think there have been more than 10^5 transactions
>>> since
>>> then (assuming that's not too many?)
>>>
>>> tips?
>>
>> What is slow about the database?  Do you have particular queries that are
>> slow?  Have you looked at EXPLAIN output?  What is the table structure?
>>
>> Yes, memory settings can speed up a database (I'm no expert in the settings
>> arena, so I won't answer that directly), but there are often other issues
>> that impact performance that are at least as important.
>>
>> Sean
>>