Обсуждение: multiple databases vs multiple clusters on the same host
Hello! I would like to find out what is the difference in hardware resources consuming between two solutions: 1. Several databases in the same postgresql cluster 2. Several clusters (one per each database) on the same host Currently I have about 10 databases in the same cluster. For some reasons I'm going to switch to using separate clusterson the same machine. I suspect that this could affect the performance. Any ideas on how much more hardware resources will be consumed? Thanks.
On 28 Září 2013, 20:12, Eugene Ostrovsky wrote: > Hello! > > I would like to find out what is the difference in hardware resources > consuming between two solutions: > 1. Several databases in the same postgresql cluster > 2. Several clusters (one per each database) on the same host > > Currently I have about 10 databases in the same cluster. For some reasons > I'm going to switch to using separate clusters on the same machine. I > suspect that this could affect the performance. > > Any ideas on how much more hardware resources will be consumed? Well, that's hard to say because we don't know (a) the version of PostgreSQL you're using, (2) how you use it and (c) what hardware you use. There are probably some corner cases where this might improve the performance, but in most cases it's going to be worse. Why are you switching to multiple clusters? For example consider that you'll probably have to use much smaller shared buffers (which might cause issues on the active database, while the other databases don't use their portion of memory), you'll have to either use much smaller max_connections or decrease work_mem (you can't just keep the values because then you might run into OOM much more frequently) etc. Tomas
On 9/28/2013 11:29 AM, Tomas Vondra wrote: > There are probably some corner cases where this might improve the > performance, but in most cases it's going to be worse. Why are you > switching to multiple clusters? > > For example consider that you'll probably have to use much smaller shared > buffers (which might cause issues on the active database, while the other > databases don't use their portion of memory), you'll have to either use > much smaller max_connections or decrease work_mem (you can't just keep the > values because then you might run into OOM much more frequently) etc. indeed, 10 separate checkpoint processes going off asynchronously, meh. -- john r pierce 37N 122W somewhere on the middle of the left coast
Thanks for the answer! About you questions: 1. Postgres 9.3 2. There are about 30-50 user connections. Actually Only 2 of databases are used intensively, others only in rare cases. 3. Hardware is AMD Phenom II X4 965, 8 Gb RAM, 2 SATA2 HDD in software mirror raid 4. The reason to switch to multiple clusters is that my software uses roles (login users and groups) for a single database.There are some problems with it in case of several databases because in postgres roles are shared between all thedatabases in the same cluster. 28.09.2013, 22:29, "Tomas Vondra" <tv@fuzzy.cz>: > On 28 Září 2013, 20:12, Eugene Ostrovsky wrote: > >> Hello! >> >> I would like to find out what is the difference in hardware resources >> consuming between two solutions: >> 1. Several databases in the same postgresql cluster >> 2. Several clusters (one per each database) on the same host >> >> Currently I have about 10 databases in the same cluster. For some reasons >> I'm going to switch to using separate clusters on the same machine. I >> suspect that this could affect the performance. >> >> Any ideas on how much more hardware resources will be consumed? > > Well, that's hard to say because we don't know (a) the version of > PostgreSQL you're using, (2) how you use it and (c) what hardware you use. > > There are probably some corner cases where this might improve the > performance, but in most cases it's going to be worse. Why are you > switching to multiple clusters? > > For example consider that you'll probably have to use much smaller shared > buffers (which might cause issues on the active database, while the other > databases don't use their portion of memory), you'll have to either use > much smaller max_connections or decrease work_mem (you can't just keep the > values because then you might run into OOM much more frequently) etc. > > Tomas
On 28 Září 2013, 21:30, Eugene Ostrovsky wrote: > Thanks for the answer! > > About you questions: > 1. Postgres 9.3 > 2. There are about 30-50 user connections. Actually Only 2 of databases > are used intensively, others only in rare cases. > 3. Hardware is AMD Phenom II X4 965, 8 Gb RAM, 2 SATA2 HDD in software > mirror raid > 4. The reason to switch to multiple clusters is that my software uses > roles (login users and groups) for a single database. There are some > problems with it in case of several databases because in postgres roles > are shared between all the databases in the same cluster. Wouldn't it be easier just setup unique roles for each database? It's much better solution than deploying 10 separate clusters (which you'll learn soon, if you go in this direction). And what are those "some problems" that you mentioned? Seems to me this thread started from the wrong end - setting up multiple clusters instead of tackling the actual problem first. Tomas
29.09.2013, 00:38, "Tomas Vondra" <tv@fuzzy.cz>: > On 28 Září 2013, 21:30, Eugene Ostrovsky wrote: > >> Thanks for the answer! >> >> About you questions: >> 1. Postgres 9.3 >> 2. There are about 30-50 user connections. Actually Only 2 of databases >> are used intensively, others only in rare cases. >> 3. Hardware is AMD Phenom II X4 965, 8 Gb RAM, 2 SATA2 HDD in software >> mirror raid >> 4. The reason to switch to multiple clusters is that my software uses >> roles (login users and groups) for a single database. There are some >> problems with it in case of several databases because in postgres roles >> are shared between all the databases in the same cluster. > > Wouldn't it be easier just setup unique roles for each database? It's much > better solution than deploying 10 separate clusters (which you'll learn > soon, if you go in this direction). > > And what are those "some problems" that you mentioned? Seems to me this > thread started from the wrong end - setting up multiple clusters instead > of tackling the actual problem first. > > Tomas Well, I’ll try to describe the main issue about the roles. There are a set of permissions defining what users can and cannot do. There are a set of groups such as “Administrator”, “Manager”, etc. Any subset of permissions can be granted to any group. There are login users which belong to one or more groups. Each user gets all the permissions of all the groups it belongsto. Thus a combination of permissions can be granted to a user by including the user to a group. A permission can be given orrevoked to/from a number of users by granting/revoking the permission to/from corresponding group. All the three entities (permissions, groups and users) are implemented as sql roles. User belongs to a group If correspondinguser login role is a member of the group role. Group has some permission if the group role is a member of thepermission role. Permission checking is done by either of two ways: 1. If the permission can be expressed in terms of sql privileges those privileges are granted to the permission role.In this case permission checks are performed by postgres itself. 2. In other cases application (e.g. in trigger function) explicitly checks if current session user is a member of therequested permission role. This works fine for a single database. But for different databases different relations between permissions-groups-users arerequested. E.g. different set of permissions for “Manager” group or different group membership for a given user. Thisdoesn’t work for several databases in a single cluster as the roles and their relations a common for the whole cluster.
On 9/29/2013 8:21 AM, Eugene Ostrovsky wrote: > This works fine for a single database. But for different databases different relations between permissions-groups-usersare requested. E.g. different set of permissions for “Manager” group or different group membershipfor a given user. This doesn’t work for several databases in a single cluster as the roles and their relationsa common for the whole cluster. then don't use the same group names for each application -- john r pierce 37N 122W somewhere on the middle of the left coast