2

Restoring a single table on the server is very slow

From Fri Jul 22 13:54:48 CEST 2016 to Fri Jul 22 14:17:41 CEST 2016
Server
2016-07-22 13:55:11 CEST [88719-1] tester@test LOG:  duration: 22988.122 ms  statement: COPY testtable (id, updated, lang, q, results) FROM stdin;
2016-07-22 14:06:09 CEST [88719-2] tester@test LOG:  duration: 658189.147 ms  statement: ALTER TABLE ONLY testtable ADD CONSTRAINT testtable_lang_q_key UNIQUE (lang, q);
2016-07-22 14:06:10 CEST [88719-3] tester@test LOG:  duration: 1546.800 ms  statement: ALTER TABLE ONLY testtable ADD CONSTRAINT testtable_pkey PRIMARY KEY (id);
2016-07-22 14:06:13 CEST [88719-4] tester@test LOG:  duration: 2305.070 ms  statement: CREATE INDEX testtable_lang ON testtable USING btree (lang);
2016-07-22 14:06:15 CEST [88719-5] tester@test LOG:  duration: 2374.251 ms  statement: CREATE INDEX testtable_lang_like ON testtable USING btree (lang varchar_pattern_ops);
2016-07-22 14:17:38 CEST [88719-6] tester@test LOG:  duration: 683316.708 ms  statement: CREATE INDEX testtable_q ON testtable USING btree (q);
2016-07-22 14:17:41 CEST [88719-7] tester@test LOG:  duration: 2839.739 ms  statement: CREATE INDEX testtable_q_like ON testtable USING btree (q varchar_pattern_ops);

Local machine

From Fr 22. Jul 14:38:54 CEST 2016 to Fr 22. Jul 14:42:05 CEST 2016
Lokal
2016-07-22 14:39:32 CEST [23005-1] tester@test LOG:  duration: 37444.240 ms  statement: COPY testtable (id, updated, lang, q, results) FROM stdin;
2016-07-22 14:40:44 CEST [23005-2] tester@test LOG:  duration: 71899.607 ms  statement: ALTER TABLE ONLY testtable ADD CONSTRAINT testtable_lang_q_key UNIQUE (lang, q);
2016-07-22 14:40:46 CEST [23005-3] tester@test LOG:  duration: 2027.237 ms  statement: ALTER TABLE ONLY testtable ADD CONSTRAINT testtable_pkey PRIMARY KEY (id);
2016-07-22 14:40:49 CEST [23005-4] tester@test LOG:  duration: 3781.663 ms  statement: CREATE INDEX testtable_lang ON testtable USING btree (lang);
2016-07-22 14:40:52 CEST [23005-5] tester@test LOG:  duration: 2731.623 ms  statement: CREATE INDEX testtable_lang_like ON testtable USING btree (lang varchar_pattern_ops);
2016-07-22 14:42:02 CEST [23005-6] tester@test LOG:  duration: 69512.264 ms  statement: CREATE INDEX testtable_q ON testtable USING btree (q);
2016-07-22 14:42:05 CEST [23005-7] tester@test LOG:  duration: 3177.827 ms  statement: CREATE INDEX testtable_q_like ON testtable USING btree (q varchar_pattern_ops);

testtable

                                  Table "public.testtable"
 Column  |           Type           |                              Modifiers                              
---------+--------------------------+---------------------------------------------------------------------
 id      | integer                  | not null default nextval('testtable_id_seq'::regclass)
 updated | timestamp with time zone | not null
 lang    | character varying(2)     | not null
 q       | character varying(100)   | not null
 results | text                     | not null
Indexes:
    "testtable_pkey" PRIMARY KEY, btree (id)
    "testtable_lang_q_key" UNIQUE CONSTRAINT, btree (lang, q)
    "testtable_lang" btree (lang)
    "testtable_lang_like" btree (lang varchar_pattern_ops)
    "testtable_q" btree (q)
    "testtable_q_like" btree (q varchar_pattern_ops)

Tested with PG 9.4 and 9.5 on both machines with the same configuration. Restoring the dump takes even much longer on the server if auto vacuum is enabled...

What could be the problem? What's going wrong here? Any ideas how to debug?

Update: U 16.04 is running on server, U 14.04 LTS on local machine. Number of rows: 1574775, Size of plain SQL dump 1.8 GB.

  • Any ideas how to debug without downgrading the server to U 14.04? This would be my next step... – Barmi Jul 22 '16 at 18:00
0

Looking at your server times: 23 seconds to restore a table with 1,6 million rows from a 1.8 GB dump file. That does not seem too bad. Might be faster, but doesn't seem very slow either.

To speed up index creation (as well as UNIQUE and PK constraints) set maintenance_work_mem high enough temporarily (for the local session or transaction only). The manual:

maintenance_work_mem (integer)

... Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. It defaults to 64 megabytes (64MB).

Set it just high enough for Postgres to process everything in RAM (If you have enough RAM to spare (temporarily). For instance:

BEGIN;
SET LOCAL maintenance_work_mem = 1GB;  -- adapt to your needs
ALTER TABLE testtable ADD CONSTRAINT testtable_lang_q_key UNIQUE (lang, q);
COMMIT;

The manual warns:

Note that when autovacuum runs, up to autovacuum_max_workers times this memory may be allocated, so be careful not to set the default value too high.

Setting it high for your transaction only avoids this problem.

Related:

  • Thanks for your answer, but have look at the times: 23 minutes, not seconds!!! I tried different configurations with different PG versions on U14.04 and U16.04 but it's always the same result: Total time with U14.04: ca. 3 minutes, with U16.04 almost 23 minutes. I also tried installing PG on U16.04 with docker and this could be a solution: The total restore time is ca. 3 minutes. It also shows, that this is not a hardware problem. – Barmi Jul 25 '16 at 8:18
  • @Barmi: 22988.122 ms are 23 seconds, not minutes. – Erwin Brandstetter Jul 25 '16 at 17:07
  • Sorry for misunderstanding. I thought restoring includes also Alter Table and Create Index: From Fri Jul 22 13:54:48 CEST 2016 to Fri Jul 22 14:17:41 CEST 2016 -> 23 minutes. Especially each of this operation took over 600000 ms – Barmi Jul 25 '16 at 23:15
  • Yesterday I was experimenting with docker. The standard debian PG containers (PG 9.4 and PG 9.5) based on debian were running like a charme - on both machines. – Barmi Jul 25 '16 at 23:19
  • Today I dowgnraded the server to U14.04 and now everything works fine too (no hardware problems). Maintenance_work_mem was set to 1GB on the server (with U16.04 and U14.04) as well as on the lokal machine (I used on both machines always the same configuration). But still wondering what the problem with U16.04 was/is? – Barmi Jul 25 '16 at 23:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.