pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
many instances or many databases or many users? 2+ messages / 2 participants [nested] [flat]
* many instances or many databases or many users? @ 2007-02-13 08:55 ismo.tuononen@solenovo.fi 2007-02-13 10:07 ` Re: many instances or many databases or many users? Heikki Linnakangas <heikki@enterprisedb.com> 0 siblings, 1 reply; 2+ messages in thread From: ismo.tuononen@solenovo.fi @ 2007-02-13 08:55 UTC (permalink / raw) To: pgsql-performance Hi, I have used postgresql some years now, but only small databases and only one database per instance and one user per database. Now we have a server reserved only for postgresql, and I'm wondering if it is better to set up: - only one instance and many databases or - many instances and only one database/instance or - one instance, one database and many users server will have 8G memory and 2 processors. Earlier we have had problems with difficult queries, some query can take 100% cpu and all other processes have slowed down. I have used oracle many years and in oracle it's better have one instance and many users, but oracle can handle many difficult queries in same time. no process (=query) can slow other queries as much as in postgesql. there is no need think safety, maintenance, ... only pure performance! is one instance capable to use that 8G of memory? and share it with different databases/users as needed? or will one big and difficult query take all memory and slow down whole server? if there is 2 instances one query can't take all memory, but downside is that if instance1 is inactive and instance2 is active, there will be much unused memory (reverved for instance1) and that can produce disk io when instance2 reads lots of data and sorts it. how you have set up your postgresql server? Ismo ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: many instances or many databases or many users? 2007-02-13 08:55 many instances or many databases or many users? ismo.tuononen@solenovo.fi @ 2007-02-13 10:07 ` Heikki Linnakangas <heikki@enterprisedb.com> 0 siblings, 0 replies; 2+ messages in thread From: Heikki Linnakangas @ 2007-02-13 10:07 UTC (permalink / raw) To: ismo.tuononen@solenovo.fi; +Cc: pgsql-performance ismo.tuononen@solenovo.fi wrote: > Now we have a server reserved only for postgresql, and I'm wondering if it > is better to set up: > - only one instance and many databases or > - many instances and only one database/instance or > - one instance, one database and many users It depends. One instance should give you best overall throughput, because the OS can maximize the use of resources across all users. There shouldn't be any difference between having one instance with many databases and one database and many users. > server will have 8G memory and 2 processors. > > Earlier we have had problems with difficult queries, some query can take > 100% cpu and all other processes have slowed down. How much data do you have? If it all fits in memory, it's not going to make much difference if you have one or more instances. If not, you might be better off with many instances dividing the memory between them, giving some level of fairness in the memory allocation. Unfortunately there's no way to stop one query from using 100% CPU (though on a 2 CPU server, it's only going to saturate 1 CPU). If you have difficult queries like that, I'd suggest that you take a look at the access plans to check if they could benefit from adding indexes or rewritten in a more efficient way. > is one instance capable to use that 8G of memory? and share it with > different databases/users as needed? > or will one big and difficult query take all memory and slow down whole > server? One instance can use all of the 8G of memory. You should set your shared_buffers to maybe 1-2G. People have different opinions on what exactly is the best value; I'd suggest that you try with different values to see what gives you the best performance in your application. > if there is 2 instances one query can't take all memory, but downside is > that if instance1 is inactive and instance2 is active, there will be much > unused memory (reverved for instance1) and that can produce disk io when > instance2 reads lots of data and sorts it. Yep. > how you have set up your postgresql server? I'd say it's more a question of isolation and administration than performance. For example, do you want to be able to do filesystem-level backups and restores one database at a time? Do you need to shut down one database while keeping the rest of them running? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-02-13 10:07 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2007-02-13 08:55 many instances or many databases or many users? ismo.tuononen@solenovo.fi 2007-02-13 10:07 ` Heikki Linnakangas <heikki@enterprisedb.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox