Re: Sudden insert performance degradation

[Sebastian Dressler <sebastian@xxxxxxxxxxx>]

Hi Henrique,

On 13. Jul 2020, at 16:23, Henrique Montenegro <typoon@xxxxxxxxx> wrote:

[...]

* Insert the data from the `users` table into the `users_no_dups` table

```
insert into users_no_dups (
    created_ts,
    user_id,
    name,
    url
) (
    select
        created_ts,
        user_id,
        name,
        url
    from
        users
) on conflict do nothing
```

How do you check contraints here? Is this enforced with UK/PK?

Running the above loop worked fine for about 12 hours. Each file was taking
about 30 seconds to be processed. About 4 seconds to create the `users` table
and have the CSV data loaded into it and anything between 20 and 30 seconds to
insert the data from `users` into `users_no_dups`.

Do you see anything suspicious in the logs, i.e. something in the realms of running out of transaction IDs?

[...]

Recreating the table now isn't really providing any improvements. I tried
recreating it with a `fillfactor` of `10`, but it was taking too long and too
much space (the table had 300GB with the fillfactor set to 30; with it set to
10 it went up to almost 1TB).

To me it sounds like the UK/PK is getting too much to write. A possible solution could be to start partitioning the table.

[...]
```
ssl = off
shared_buffers = 8GB
work_mem = 12GB
maintenance_work_mem = 12GB
max_stack_depth = 4MB
synchronous_commit = off
wal_writer_flush_after = 128MB
max_wal_size = 32GB
min_wal_size = 80MB
effective_cache_size = 96GB
```

Another suggestion would be to increase the min_wal_size here, but since you use UNLOGGED tables it does not matter much.

Information about the machine:

```
Processor: 2x Intel(R) Xeon(R) CPU E5-2630L v2 @ 2.40GHz (6 cores each, 12 threads)
RAM: 256GB


Disk1: 2TB SSD SATA-3 Samsung Evo 860
Disk2: 6TB HDD SATA-3 Seagate Exos Enterprise 7200RPM
Disk3: 8TB HDD SATA-3 Seagate Exos Enterprise 7200RPM

Disk1 and Disk2 are configured as a single logical volume.

Just curious: does that mean you mix up SSD + HDD?

Cheers,

Sebastian

--

Sebastian Dressler, Solution Architect 
+49 30 994 0496 72 | sebastian@xxxxxxxxxxx 

Swarm64 AS
Parkveien 41 B | 0258 Oslo | Norway
Registered at Brønnøysundregistrene in Norway under Org.-Number 911 662 787
CEO/Geschäftsführer (Daglig Leder): Thomas Richter; Chairman/Vorsitzender (Styrets Leder): Dr. Sverre Munck 

Swarm64 AS Zweigstelle Hive
Ullsteinstr. 120 | 12109 Berlin | Germany
Registered at Amtsgericht Charlottenburg - HRB 154382 B