hi!
Ok, somebody asked for it, and I'm posting it to the list.
On Sat, Aug 30, 2008 at 6:59 AM, Michael Galloway <mgx AT ornl DOT gov> wrote:
> On Fri, Aug 29, 2008 at 05:32:54PM +0000, Jose Ildefonso Camargo Tolosa wrote:
>> Ok, as for DB.
>>
>>
>> Tell me a little more about your system, I already know:
>>
>> ~8GB RAM.
>> 4 cores (maybe 2 + HT).
>>
>> Does it do anything beside backup + DB (for backup)?
>
>
> its real cores, opteron. its a file server, 8TB SATA raid (3ware 9660 raid
> card). and
> backup server.
Samba? nfs? , the way the file server is used have impact on how you
choose the value for effective cache size.
Disclaimer: I'm not a DB-expert, and I don't pretend to be. I'm just
sharing what has worked for me, in case that the use of this
information sets fire to your cat, you can't blame me :D .
Ok. I would use around 2700MB shared (for older postgres: 352000
shared buffers).
Off course, you need to modify your system limits (I'm assuming Linux
here), in this case, I set them to 3GB in this example, if you want to
play around with shared buffers, you have to take this into account:
echo 3221225472 > /proc/sys/kernel/shmmax
echo 3221225472 > /proc/sys/kernel/shmall
Off course, after testing, you should modify sysctl.conf accordingly.
Assuming that your DB is *only* used for bacula (ie, not a lot of
connections), you can safely do this:
work_mem = 32M (or 64MB), remember, this could be multiplied by the
number of concurrent connections, this is "general DB optimization", I
don't know if bacula actually uses "complex queries"
maintenance_work_mem = 64M
max_stack_depth = 4500kB (no really sure on this one, but it has
worked for me).
And well, this one, it is used when you run analize, I hasn't actually
verified how it affect performance, somebody told me it was good, so I
just tried it, but when I have some time I'll research it:
default_statistics_target = 100
You should check the Bacground Writer parameters.
Oh, I don't forget to tell your database server about your cache, off
course, you have to think this one really well, because the server is
more than just a DB server:
effective_cache_size = 1536MB (I would set it to this value, and see
:) , this has some impact on what the query planner "thinks" about the
system's disk cache, see references below for more info).
My default checkpiont_timeout value is 5 minutes, I never touched it :) .
>
> thanks!
>
> -- michael
>
>>
>
I hope this helps,
Ildefonso Camargo
References (8.2-centric, I'm using 8.3):
http://www.postgresql.org/docs/8.2/interactive/kernel-resources.html
http://www.postgresql.org/docs/8.2/interactive/runtime-config-resource.html
http://www.postgresql.org/docs/8.2/interactive/runtime-config-query.html
The postgresql.conf file.
-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|