2 small or 1 big server?

Hi Mats,

We host around 1,000 unique sites across 7 servers, each with 16 GB of RAM and 8 logical cores.

You can easily raise the number of concurrent connections in the PostgreSQL config. 100 clients is not a hard limit. Some other limits might need to be tuned, e.g. shared buffers, but you’ll need to play with your workload and client load to solve any major performance issues.

In a multi-tenent setup, pooling won’t be much help unless you’re partitioning a single database. We individual databases for each client, so pooling doesn’t help. Some multi-tenant solutions partition by schema or identifiers on each record, so pooling is possible if you build your application right.

Our users generally log into their software at the start of their business day, access the data in fits and spurts, then logout at the end of the day.

We have solid reconnect code built into our software. Seriously–if you’re working with a network connection expect it to degrade and fail.

We solved this problem by killing connections on the backend when they’ve been idle after 10 minutes. This means a client who logs in, works for 30 minutes, then comes back 4 hours later isn’t using a backed connection for most of that time.

Solid reconnect code is the key to this. If the client can’t handle a terminated connection on the server gracefully, you’ll have a bad time.

Of course, all this is wonderfully solved by stateless data transfer, e.g. RESTful APIs, but that brings its own set of challenges.

Alex

> On Aug 29, 2017, at 07:58, ADJob <mats@adjob.se> wrote:
>
> Hi,
>
> I have a an fat Omnis client connecting to Postgresql. There are several databases at the same server (multi tenant). It is very fast and reliable.
>
> Postgresql are limited to 100 concurrent sessions by “factory settings”
>
> The server is no slowly reaching this limit by peaks. The goal is to add more tenants. Adding more sessions = more memory needed
>
> My qualified guess is that adding pooling possibilities will not do this alone – (pgBouncer). Or?
>
> I have two main options that for me are equal (= same cost and configuring time):
>
> 1. Expand the existing server by adding more RAM and CPU.
> 2. Add another small server for new tenants.
>
> I know there is many of you that already have been there and done that. Hopefully you can share some tip or advice.
>
> Which option is the best one? Regarding speed etc?
>
> TIA for any input.
>
> /Mats
> _____________________________________________________________
> Manage your list subscriptions at lists.omnis-dev.com

_____________________________________________________________
Manage your list subscriptions at lists.omnis-dev.com