Re: High SYS CPU - need advise - Mailing list pgsql-general

Gavin Flower wrote:

We found that the real-world production performance of a web
application servicing millions of we hits per day with thousands
of concurrent users improved when we reconfigured our database
connection pool to be about 35 instead of 55, on a 16 core box
with a 40 drive RAID.

Curious, what is your RAID configuration?

We have the OS on RAID 1 on one RAID controller, WAL on another RAID
1 array on its own controller, and the PostgreSQL data directory on
RAID 5 on a pair of controllers which each has a path to every drive.
(Don't ask me how that works, I just trust the hardware guys to get
that part right.) The active portion of the database is cached, the
read-to-write ratio is about 10 to 1, and the controllers all have
BBU cache with write-back. It is actually one pool of about 30 for
the read-only web app with so many hits, and a separate pool of 6
database connections for logical replication from about 80 source
databases handling an OLTP load of 3000 directly connected users.

We were seeing some performance degradation at peak loads, and
improved both user response time and overall throughput by reducing
the database connections used by the connection pool. Peak load was
handled much better that way.

I cringe every time I see someone say they need a large number of
database connections in order to support that many users. Having one
database connection for each user is, in my experience, guaranteed to
make things fall apart under load. 

-Kevin

Thanks for a very comprehensive answer! (almost simply asked what 'type' of RAID did you have, I am glad I rephrased that!  I learnt far more than I expected, which is good - more than merely satisfied my curiosity!.

I use RAID-6; but in development, reliability (for me, I've had 5 hard disk crashes in 20 years) is orders of magnitude more important than performance, with rare exceptions!

I can understand the mentality of wanting one connection per user, though I fully know why it is the wrong approach - I would also want to use connection pooling in your situation.

Backing the 1980's I was a Mainframe COBOL analyst/programmer and just starting to use databases. Initially I thought of a transaction in terms of the user wanting to ensure their display and update were in the same transaction. Took me a while to fully appreciate and accept that transactions should be short to protect the database while not blocking other users too much (multi-user access was also a whole new concept), and that keeping a transaction open for user actions was not the 'done thing'! It requires a whole new change of mindset – something a lot of people find difficult, as it conflicts with part of their implicit belief system (just try discussing even the most obvious flaws in Creationism with a Believer!).

Now I'm quite comfortable with the idea of multi-user and am much more database centric while still appreciating the needs of users. Now my weapons of choice are Java & Linux, with Postgres being my preferred database.


Cheers,
Gavin