Обсуждение: optimal memory
Is there a way to tell what the optimal memory is for a specific postgresql instance? I am configuring Xen virtual machines and I don't want to give it more then it needs. Would looking at the swap be an indication? As soon as it starts to use swap, that means I need more, but until that point, I have enough? Thanks Sim
In response to Sim Zacks : > Is there a way to tell what the optimal memory is for a specific > postgresql instance? > > I am configuring Xen virtual machines and I don't want to give it more > then it needs. > > Would looking at the swap be an indication? As soon as it starts to use > swap, that means I need more, but until that point, I have enough? You can't have enough ;-) Fits your DB into the RAM? If you don't have enough, for instance, work_mem, sort-operations performed on disk and not in the ram. That's much slower. So, as i said, you can't have enough ram ;-) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
In response to Sim Zacks : > > > On 03-Aug-2010 11:18 AM, A. Kretschmer wrote: > > In response to Sim Zacks : > > > >> Is there a way to tell what the optimal memory is for a specific > >> postgresql instance? > >> > >> I am configuring Xen virtual machines and I don't want to give it more > >> then it needs. > >> > >> Would looking at the swap be an indication? As soon as it starts to use > >> swap, that means I need more, but until that point, I have enough? > >> > > You can't have enough ;-) > > > > Fits your DB into the RAM? > > > > If you don't have enough, for instance, work_mem, sort-operations > > performed on disk and not in the ram. That's much slower. So, as i said, > > you can't have enough ram ;-) > > > In theory that's a great answer. ;-) > If my database is 400MB (du on the base directory) and there are 10 > active users running functions and queries, that for the most part take > less then 1 sec each. > I would assume that 10GB of RAM is overkill. Maybe. > Is 2 GB RAM also overkill? Is there a way to know when you have reached > the overkill level? I think, you should try it. Set your virtual machine to 2 GByte, set shared_buffers to 512 MByte, effective_cache_size to 1.5 gbyte and work_mem to, for instance, 20 mbyte. Monitor the machine, watch the logfile (set log_min_duration_statement properly). Reduce all parameters to 50% and compare the results. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
2010/8/3 Sim Zacks <sim@compulab.co.il>: > Is there a way to tell what the optimal memory is for a specific > postgresql instance? > > I am configuring Xen virtual machines and I don't want to give it more > then it needs. > > Would looking at the swap be an indication? As soon as it starts to use > swap, that means I need more, but until that point, I have enough? Once you start using swap space it's WAY too small. If your db is say 1GB, and you have 10 or so connections running at once, and each might need 20MB of work_mem, then it would be good to at least 2Gig or so so that the db can be cached by the cache and you've got enough memory left over for the db to allocate enough shared_buffers to hold a decent chunk of it (say 256 to 512Meg). OTOH, if your database is 1TB in size, then you can't really have too much memory, as every bit you throw at the machine will help. So, about how big is your db? How many users are likely to be running queries at once? How big of a chunk of data are those users likely to each need for sorts etc?
> So, about how big is your db? How many users are likely to be running > queries at once? How big of a chunk of data are those users likely to > each need for sorts etc? > The database is 400MB (using du on the base folder), I have 10 active users who run queries and functions that generally take less then 1 second each to run.
On Tue, Aug 3, 2010 at 6:27 AM, Sim Zacks <sim@compulab.co.il> wrote: > >> So, about how big is your db? How many users are likely to be running >> queries at once? How big of a chunk of data are those users likely to >> each need for sorts etc? >> > The database is 400MB (using du on the base folder), I have 10 active > users who run queries and functions that generally take less then 1 > second each to run. Then a couple gigabytes should be enough to run it smoothly, assuming that those queries aren't doing things that need 100s of megabytes for sorting for each query. I'd start with a 2 or 3Gig VM, give 400 or so to shared_buffers, and set work_mem to something like 8 or 16Meg and see how it runs.