Re: [PERFORM] Having some problems with concurrent COPY commands
On Mon, Oct 12, 2015 at 1:28 PM, Andres Freund 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. COPY ( SELECT id, id % 100, id % 1000, now() - (id || 's')::INTERVAL FROM generate_series(1, 10) a(id) ) TO '/tmp/loadtest1.csv'; CREATE TABLE test_copy ( idSERIAL 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); 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 & -- Shaun Thomas bonesmo...@gmail.com http://bonesmoses.org/ -- 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] Having some problems with concurrent COPY commands
On Mon, Oct 12, 2015 at 11:17 AM, Shaun Thomas wrote: > Hi guys, > > I've been doing some design investigation and ran into an interesting snag > I didn't expect to find on 9.4 (and earlier). I wrote a quick python script > to fork multiple simultaneous COPY commands to several separate tables and > found that performance apparently degrades based on how many COPY commands > are running. > > For instance, in the logs with one COPY, I see about one second to import > 100k rows. At two processes, it's 2 seconds. At four processes, it's 4 > seconds. This is for each process. Thus loading 400k rows takes 16 seconds > cumulatively. To me, it looked like some kind of locking issue, but > pg_locks showed no waits during the load. In trying to figure this out, I > ran across this discussion: > > > http://www.postgresql.org/message-id/cab7npqqjeasxdr0rt9cjiaf9onfjojstyk18iw+oxi-obo4...@mail.gmail.com > > Which came after this: > > http://forums.enterprisedb.com/posts/list/4048.page > > It would appear I'm running into whatever issue the xloginsert_slots patch > tried to address, but not much discussion exists afterwards. It's like the > patch just kinda vanished into the ether even though it (apparently) > massively improves PG's ability to scale data import. > > I should note that setting wal_level to minimal, or doing the load on > unlogged tables completely resolves this issue. However, those are not > acceptable settings in a production environment. Is there any other way to > get normal parallel COPY performance, or is that just currently impossible? > > I also know 9.5 underwent a lot of locking improvements, so it might not > be relevant. I just haven't gotten a chance to repeat my tests with 9.5 > just yet. > Can you provide the test script? Also, have you tuned your database for high io throughput? What is your storage system like?
Re: [PERFORM] Having some problems with concurrent COPY commands
Hi, On 2015-10-12 13:17:53 -0500, Shaun Thomas wrote: > It would appear I'm running into whatever issue the xloginsert_slots patch > tried to address, but not much discussion exists afterwards. That patch is merged, it's just that the number of slots is hardcoded. You can recompile postgres with different values by changing #define NUM_XLOGINSERT_LOCKS 8 in xlog.c to a different value. A restart is enough afterwards. > Is there any other way to > get normal parallel COPY performance, or is that just currently impossible? > > I also know 9.5 underwent a lot of locking improvements, so it might > not be relevant. I just haven't gotten a chance to repeat my tests > with 9.5 just yet. Hard to say anything substantive without further information. Any chance you could provide profiles of such a run? If yes, I can help you with instructions. I'm just to lazy to write them up if not. Greetings, Andres Freund -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Having some problems with concurrent COPY commands
Hi guys, I've been doing some design investigation and ran into an interesting snag I didn't expect to find on 9.4 (and earlier). I wrote a quick python script to fork multiple simultaneous COPY commands to several separate tables and found that performance apparently degrades based on how many COPY commands are running. For instance, in the logs with one COPY, I see about one second to import 100k rows. At two processes, it's 2 seconds. At four processes, it's 4 seconds. This is for each process. Thus loading 400k rows takes 16 seconds cumulatively. To me, it looked like some kind of locking issue, but pg_locks showed no waits during the load. In trying to figure this out, I ran across this discussion: http://www.postgresql.org/message-id/cab7npqqjeasxdr0rt9cjiaf9onfjojstyk18iw+oxi-obo4...@mail.gmail.com Which came after this: http://forums.enterprisedb.com/posts/list/4048.page It would appear I'm running into whatever issue the xloginsert_slots patch tried to address, but not much discussion exists afterwards. It's like the patch just kinda vanished into the ether even though it (apparently) massively improves PG's ability to scale data import. I should note that setting wal_level to minimal, or doing the load on unlogged tables completely resolves this issue. However, those are not acceptable settings in a production environment. Is there any other way to get normal parallel COPY performance, or is that just currently impossible? I also know 9.5 underwent a lot of locking improvements, so it might not be relevant. I just haven't gotten a chance to repeat my tests with 9.5 just yet. -- Shaun Thomas bonesmo...@gmail.com
