Re: [PERFORM] One long transaction or multiple short transactions?

[Carlo]

>> How many cores do you have on that machine?

Test if limiting number of simultaneous feeds, like bringing their number
down to half of your normal connections has the same positive effect.

<< 

 

I am told 32 cores on a LINUX VM. The operators have tried limiting the
number of threads. They feel that the number of connections is optimal.
However, under the same conditions they noticed a sizable boost in
performance if the same import was split into two successive imports which
had shorter transactions.

 

I am just looking to see if there is any reason to think that lock
contention (or anything else) over longer vs. shorter single-row-write
transactions under the same conditions might explain this. 

 

Carlo

 

From: Igor Neyman [mailto:iney...@perceptron.com] 
Sent: October 6, 2015 9:10 AM
To: Carlo; pgsql-performance@postgresql.org
Subject: RE: [PERFORM] One long transaction or multiple short transactions?

 

 

 

From: pgsql-performance-ow...@postgresql.org
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Carlo
Sent: Monday, October 05, 2015 11:11 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] One long transaction or multiple short transactions?

 

We have a system which is constantly importing flat file data feeds into
normalized tables in a DB warehouse over 10-20 connections. Each data feed
row results in a single transaction of multiple single row writes to
multiple normalized tables.

 

The more columns in the feed row, the more write operations, longer the
transaction.

 

Operators are noticing that splitting a single feed of say – 100 columns –
into two consecutive feeds of 50 columns improves performance dramatically.
I am wondering whether the multi-threaded and very busy import environment
causes non-linear performance degradation for longer transactions. Would the
operators be advised to rewrite the feeds to result in more smaller
transactions rather than fewer, longer ones?

 

Carlo

 

 

Ø  over 10-20 connections

 

How many cores do you have on that machine?

Test if limiting number of simultaneous feeds, like bringing their number
down to half of your normal connections has the same positive effect.

 

Regards,

Igor Neyman

Re: [PERFORM] Re: Multi processor server overloads occationally with system process while running postgresql-9.4

[Scott Marlowe]

On Tue, Oct 6, 2015 at 11:08 PM, ajaykbs  wrote:
> I have checked the transparent huge pages and zone reclaim mode and those are
> already disabled.
>
> As a trial and error method, I have reduced the shared buffer size from
> 8500MB to 3000MB.
> The CPU i/o wait is icreased a little. But the periodical over load has not
> occurred afterwards. (3 days passed without such situation). I shall report
> further developments.

Reduce max connections to something more reasonable like < 100 and get
a connection pooler in place (pgbouncer is simple to setup and use)


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] shared-buffers set to 24GB but the RAM only use 4-5 GB average

[FattahRozzaq]

Response from you all are very precious.

@Merlin,
I'm misunderstood the question.
Yes, I didn't measure it. I only monitor RAM and CPU using htop (I also use
nmon for disk IO, iftop for the network utilization).
Did 1 connection need 1 core dedicatedly?
(I was having 40-80 connections in stable condition. And when the problem
happened the connections would be more than 150)

@Scott,
Just before the problem happened and when the problem happened, my server
didn't running out of IO/RAM/CPU.
The SSD IO total usage was 25-50% (I use nmon to monitor the disk IO)
The RAM total usage was 4-5GB of total 128GB (I monitor it using htop)
The CPU was 100% in 2 cores, 70% in 3 cores, the other 19 cores were under
5% (I monitor it using htop)
The network interface utilization was only 300-400 Mbps of total 1Gbps (I
monitor it using iftop)
So, maybe the 128GB RAM will never all be fully use by PostgreSQL?

I will test PostgreSQL with pg_bouncer in an identical logical (OS,
softwares, etc) condition and physical resource condition.


Respect,
FattahRozzaq

Re: [PERFORM] shared-buffers set to 24GB but the RAM only use 4-5 GB average

[Merlin Moncure]

On Wed, Oct 7, 2015 at 5:29 AM, FattahRozzaq  wrote:
> Response from you all are very precious.
>
> @Merlin,
> I'm misunderstood the question.
> Yes, I didn't measure it. I only monitor RAM and CPU using htop (I also use

Can you be a little more specific.  What values did you look at and
how did you sum them up?  Assuming your measurement was correct, you
might be looking at simple prewarm issue in terms of getting shared
buffers stuffed up.  There are some tactics to warm up shared buffers
(like pg_prewarm), but it's not clear that would be useful in your
case.

One cause (especially with older kernels) of low memory utilization is
misconfigured NUMA.  Note this would only affect the backing o/s
cache, not pg's shared buffers.

Very first thing you need to figure out is if your measured issues are
coming from storage or not.  iowait % above single digits suggests
this. With fast SSD it's pretty difficult to max out storage,
especially when reading data, but it's always the first thing to look
at.   Context switch issues (as Scott notes) as another major
potential cause of performance variability, as is server internal
contention.   But rule out storage first.

merlin


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] shared-buffers set to 24GB but the RAM only use 4-5 GB average

[Scott Marlowe]

On Wed, Oct 7, 2015 at 4:29 AM, FattahRozzaq  wrote:
> Response from you all are very precious.
>
> @Merlin,
> I'm misunderstood the question.
> Yes, I didn't measure it. I only monitor RAM and CPU using htop (I also use
> nmon for disk IO, iftop for the network utilization).
> Did 1 connection need 1 core dedicatedly?
> (I was having 40-80 connections in stable condition. And when the problem
> happened the connections would be more than 150)
>
> @Scott,
> Just before the problem happened and when the problem happened, my server
> didn't running out of IO/RAM/CPU.
> The SSD IO total usage was 25-50% (I use nmon to monitor the disk IO)
> The RAM total usage was 4-5GB of total 128GB (I monitor it using htop)
> The CPU was 100% in 2 cores, 70% in 3 cores, the other 19 cores were under
> 5% (I monitor it using htop)
> The network interface utilization was only 300-400 Mbps of total 1Gbps (I
> monitor it using iftop)
> So, maybe the 128GB RAM will never all be fully use by PostgreSQL?

Check what vmstat 10 has to say, specifically the in and cs columns,
which is interrupts and context switches per second. What you'll
likely see is it ranging from 10k to 20k normally and spiking to 10 or
100 times when this is happening. That's the typical symptom that your
OS is spending all its time trying to switch between 1,000 processes
instead of servicing a handful at a time.

You should also see a huge uptick in pg processes that are active at
once, either in top or via pg_stat_activity.

Don't worry about making PostgreSQL use all your RAM, the OS will do
that for you, worry about getting PostgreSQL to process as many
queries per second as it can. And you do that by using a connection
pooler. I have machines with 350G dbs on machines with > 512GB RAM,
and eventually the whole db is in kernel cache and the only IO is when
blocks get written to disk. But the kernel only caches the parts of
the db that get read. If your db isn't reading more than a few dozen
gigabytes then that's how much memory will be used to cache the db.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance