Message-ID: <561CCFB6.6040405@iki.fi>
Date: Tue, 13 Oct 2015 12:32:38 +0300
From: Heikki Linnakangas <hlinnaka@iki.fi>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Icedove/31.8.0
MIME-Version: 1.0
To: Shaun Thomas <bonesmoses@gmail.com>,
 Andres Freund <andres@anarazel.de>
CC: pgsql-performance@postgresql.org
Subject: Re: Having some problems with concurrent COPY commands
References: 
 <CAB78C+C9uw4hS+_k2_sR1zgjb1DyL5UonqjmW+WNno3osx=_8g@mail.gmail.com>
	<20151012182808.GB10323@awork2.anarazel.de>
 <CAB78C+D=4CyZ-33kLvvZMenQWtkNEsmvkMKRPSV3AG2edchgbA@mail.gmail.com>
In-Reply-To: 
 <CAB78C+D=4CyZ-33kLvvZMenQWtkNEsmvkMKRPSV3AG2edchgbA@mail.gmail.com>
Content-Type: multipart/mixed; boundary="------------050406030208090702050809"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

This is a multi-part message in MIME format.
--------------050406030208090702050809
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

On 10/12/2015 11:14 PM, Shaun Thomas wrote:
> On Mon, Oct 12, 2015 at 1:28 PM, Andres Freund <andres@anarazel.de> wrote:
>
>> Any chance
>> you could provide profiles of such a run?
>
> This is as simple as I could make it reliably. With one copy running,
> the thread finishes in about 1 second. With 2, it's 1.5s each, and
> with all 4, it's a little over 3s for each according to the logs. I
> have log_min_duration_statement set to 1000, so it's pretty obvious.
> The scary part is that it's not even scaling linearly; performance is
> actually getting *worse* with each subsequent thread.
>
> Regarding performance, all of this fits in memory. The tables are only
> 100k rows with the COPY statement. The machine itself is 8 CPUs with
> 32GB of RAM, so it's not an issue of hardware. So far as I can tell,
> it happens on every version I've tested on, from 9.2 to 9.4. I also
> take back what I said about wal_level. Setting it to minimal does
> nothing. Disabling archive_mode and setting max_wal_senders to 0 also
> does nothing. With 4 concurrent processes, each takes 3 seconds, for a
> total of 12 seconds to import 400k rows when it would take 4 seconds
> to do sequentially. Sketchy.

I was not able reproduce that behaviour on my laptop. I bumped the 
number of rows in your script 100000, to make it run a bit longer. 
Attached is the script I used. The total wallclock time the COPYs takes 
on 9.4 is about 8 seconds for a single COPY, and 12 seconds for 4 
concurrent COPYs. So it's not scaling as well as you might hope, but 
it's certainly not worse-than-serial either, as you you're seeing.

If you're seeing this on 9.2 and 9.4 alike, this can't be related to the 
XLogInsert scaling patch, although you might've found a case where that 
patch didn't help where it should've. I ran "perf" to profile the test 
case, and it looks like about 80% of the CPU time is spent in the b-tree 
comparison function. That doesn't leave much scope for XLogInsert 
scalability to matter one way or another.

I have no explanation for what you're seeing though. A bad spinlock 
implementation perhaps? Anything special about the hardware at all? Can 
you profile it on your system? Which collation?

- Heikki

--------------050406030208090702050809
Content-Type: application/x-sh;
 name="launch4.sh"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="launch4.sh"

psql <<EOF
DROP TABLE test_copy;
DROP TABLE test_copy2;
DROP TABLE test_copy3;
DROP TABLE test_copy4;
COPY (
  SELECT id, id % 100, id % 1000, now() - (id || 's')::INTERVAL
    FROM generate_series(1, 1000000) a(id)
) TO '/tmp/loadtest1.csv';

CREATE TABLE test_copy (
  id            SERIAL PRIMARY KEY,
  location      VARCHAR NOT NULL,
  reading       BIGINT NOT NULL,
  reading_date  TIMESTAMP NOT NULL
);

CREATE INDEX idx_test_copy_location ON test_copy (location);
CREATE INDEX idx_test_copy_date ON test_copy (reading_date);

CREATE TABLE test_copy2 (LIKE test_copy INCLUDING INDEXES);
CREATE TABLE test_copy3 (LIKE test_copy INCLUDING INDEXES);
CREATE TABLE test_copy4 (LIKE test_copy INCLUDING INDEXES);
EOF

psql -c "COPY test_copy FROM '/tmp/loadtest1.csv'" &>/dev/null &
#psql -c "COPY test_copy2 FROM '/tmp/loadtest1.csv'" &>/dev/null &
#psql -c "COPY test_copy3 FROM '/tmp/loadtest1.csv'" &>/dev/null &
#psql -c "COPY test_copy4 FROM '/tmp/loadtest1.csv'" &>/dev/null &
date
wait
date


--------------050406030208090702050809
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


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

--------------050406030208090702050809--