Thread: More shared_buffers instead of effective_cache_size?

More shared_buffers instead of effective_cache_size?

From
Ulrich
Date:
Hi,
I have a virtual server with 256 MB of RAM. I am using it as a
webserver, mailserver and for postgres. So there is something like 150MB
left for postgres.

Here are my configs (I haven't benchmarked...)
max_connections = 12 (I think, I will not have more parallel
connections, because I only have 10 PHP worker threads)
shared_buffers = 24MB
work_mem = 1MB
maintenance_work_mem = 16MB

(effective_cache_size = 80MB)

Normally, the file-cache is part of the free ram. But on my virtual
server, it looks like if there is one big file cache for the whole
hardware node and I do not have my own reserved cached, so it is not
easy to find a good value for effective_cache_size.

I've also benchmarked the file-cache using dd (100MB file)

1. Read from HDD:
104857600 bytes (105 MB) copied, 8.38522 seconds, 12.5 MB/s
2. Read from Cache:
104857600 bytes (105 MB) copied, 3.48694 seconds, 30.1 MB/s

That is really really slow (10 times slower than on my other machine).

What would you do now?  Increasing shared_buffers to 100MB and setting
effective_cache_size to 0MB? Or increasing effective_cache_size, too?

Thanks for help.

Regards,
-Ulrich

Re: More shared_buffers instead of effective_cache_size?

From
"Scott Marlowe"
Date:
On Thu, Sep 4, 2008 at 1:24 PM, Ulrich <ulrich.mierendorff@gmx.net> wrote:
> Hi,
> I have a virtual server with 256 MB of RAM. I am using it as a webserver,
> mailserver and for postgres. So there is something like 150MB left for
> postgres.
>
> Here are my configs (I haven't benchmarked...)
> max_connections = 12 (I think, I will not have more parallel connections,
> because I only have 10 PHP worker threads)
> shared_buffers = 24MB
> work_mem = 1MB
> maintenance_work_mem = 16MB
>
> (effective_cache_size = 80MB)
>
> Normally, the file-cache is part of the free ram. But on my virtual server,
> it looks like if there is one big file cache for the whole hardware node and
> I do not have my own reserved cached, so it is not easy to find a good value
> for effective_cache_size.
>
> I've also benchmarked the file-cache using dd (100MB file)
>
> 1. Read from HDD:
> 104857600 bytes (105 MB) copied, 8.38522 seconds, 12.5 MB/s
> 2. Read from Cache:
> 104857600 bytes (105 MB) copied, 3.48694 seconds, 30.1 MB/s
>
> That is really really slow (10 times slower than on my other machine).
>
> What would you do now?  Increasing shared_buffers to 100MB and setting
> effective_cache_size to 0MB? Or increasing effective_cache_size, too?

Stop using a virtual server?  I wouldn't set shared_buffers that high
just because things like vacuum and sorts need memory too.

>
> Thanks for help.
>
> Regards,
> -Ulrich
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

Re: More shared_buffers instead of effective_cache_size?

From
Ulrich
Date:
Scott Marlowe wrote:
> Stop using a virtual server?
That is not possible...
> I wouldn't set shared_buffers that high
> just because things like vacuum and sorts need memory too
Okay, I understand that vacuum uses memory, but I thought sorts are done
in work_mem? I am only sorting the result of one query which will never
return more than 500 rows.

-Ulrich

Re: More shared_buffers instead of effective_cache_size?

From
"Scott Marlowe"
Date:
On Thu, Sep 4, 2008 at 1:39 PM, Ulrich <ulrich.mierendorff@gmx.net> wrote:
> Scott Marlowe wrote:
>>
>> Stop using a virtual server?
>
> That is not possible...

Sorry shoulda had a smiley face at the end of that. :)  <-- there

>> I wouldn't set shared_buffers that high
>> just because things like vacuum and sorts need memory too
>
> Okay, I understand that vacuum uses memory, but I thought sorts are done in
> work_mem? I am only sorting the result of one query which will never return
> more than 500 rows.

You can probably play with larger shared memory, but I'm betting that
the fact that you're running under a VM is gonna weigh eveything down
a great deal, to the point that you're tuning is going to have minimal
effect.

Re: More shared_buffers instead of effective_cache_size?

From
Ulrich
Date:
Scott Marlowe wrote:
> On Thu, Sep 4, 2008 at 1:39 PM, Ulrich <ulrich.mierendorff@gmx.net> wrote:
>
>>> I wouldn't set shared_buffers that high
>>> just because things like vacuum and sorts need memory too
>>>
>> Okay, I understand that vacuum uses memory, but I thought sorts are done in
>> work_mem? I am only sorting the result of one query which will never return
>> more than 500 rows.
>>
>
> You can probably play with larger shared memory, but I'm betting that
> the fact that you're running under a VM is gonna weigh eveything down
> a great deal, to the point that you're tuning is going to have minimal
> effect.
>
Hmm... Why do you think so? Is there a reason for it or do other people
have problems with virtual servers and databases?
I have reserved cpu power and reserved ram (okay, not much, but it is
reserved ;-) ), the only thing I dont have is reserved file-cache.

-Ulrich

Re: More shared_buffers instead of effective_cache_size?

From
"Scott Marlowe"
Date:
On Thu, Sep 4, 2008 at 2:01 PM, Ulrich <ulrich.mierendorff@gmx.net> wrote:
> Scott Marlowe wrote:
>>
>> On Thu, Sep 4, 2008 at 1:39 PM, Ulrich <ulrich.mierendorff@gmx.net> wrote:
>>
>>>>
>>>> I wouldn't set shared_buffers that high
>>>> just because things like vacuum and sorts need memory too
>>>>
>>>
>>> Okay, I understand that vacuum uses memory, but I thought sorts are done
>>> in
>>> work_mem? I am only sorting the result of one query which will never
>>> return
>>> more than 500 rows.
>>>
>>
>> You can probably play with larger shared memory, but I'm betting that
>> the fact that you're running under a VM is gonna weigh eveything down
>> a great deal, to the point that you're tuning is going to have minimal
>> effect.
>>
>
> Hmm... Why do you think so? Is there a reason for it or do other people have
> problems with virtual servers and databases?
> I have reserved cpu power and reserved ram (okay, not much, but it is
> reserved ;-) ), the only thing I dont have is reserved file-cache.

Well, Databases tend to be IO bound, and VMs don't tend to focus on IO
performance as much as CPU/Memory performance.  Also, things like
shared memory likely don't get as much attention in a VM either.  Just
guessing, I haven't tested a lot of VMs.