1

I have the table below in PostgreSQL (automagically generated with insertions using Marten):

create table mt_doc_order
(
    id               uuid                                                                                          not null
        constraint pk_mt_doc_order
            primary key,
    data             jsonb                                                                                         not null,
    mt_last_modified timestamp with time zone default transaction_timestamp(),
    mt_version       uuid                     default (md5(((random())::text || (clock_timestamp())::text)))::uuid not null,
    mt_dotnet_type   varchar
);

I am inserting a lot of data to see if there is at some point a bottleneck in terms insertion performances.

I recorded the insertion time for every 5000 records inserted and here is below a graph showing the number of records inserted in the table (x-axis), and the insertion time (y-axis):

enter image description here

It seems that around 25,000,000 records the database needs more time for insertion, I supposed that there is something related to the update of structure maintaining the primary key (ie. B-Tree?).

I thought at some point that it was an effect of fragmentation but as far as I know fragmentation is more something that have to do with update / delete in table filled with index(es) so that with Postgre you need to do some vacuum command to ease the b-tree maintaining those indexes.

Plus it looks that even though the auto-vacuum dameon was running, there was no vacuum command after more than 25 millions records inserted:

root@4e862966a7ad:/# ps -axww | grep autovacuum
   63 ?        Ss     0:00 postgres: autovacuum launcher
  139 pts/0    S+     0:00 grep autovacuum
SELECT
    schemaname, relname,
    last_vacuum, last_autovacuum,
    vacuum_count, autovacuum_count  -- not available on 9.0 and earlier
FROM pg_stat_user_tables;

gives:

| schemaname | relname     |last_vacuum| last_autovacuum |vacuum_count| autovacuum_count |
|------------|-------------|-----------|-----------------|------------| -----------------|
| public     | mt_doc_order|   <null>  |    <null>       |      0     |         0        |

So I am wondering:

  • How do you call that effect of there is relatively a "lot" of data that seems to trigger some sort of bottleneck upon insertion performance?

  • Is there anything that can be done to improve the insertion performance once we reach that amount of data in one table?

  • Can you run perf top while the inserts are running and check which function takes more and more CPU time over time? You need the PostgreSQL debugging symbols installed. – Laurenz Albe May 23 at 8:37
  • @LaurenzAlbe My container does not really have perf, is there any alternative with top only? – Ehouarn Perret May 23 at 9:29
  • If it is a Docker container, just do it on the host operating system. A process in a container is just a process on the operating system. – Laurenz Albe May 23 at 10:09
  • @LaurenzAlbe the host operating system being windows I am looking for a decent alternative to perf top – Ehouarn Perret May 23 at 10:24
  • Hm, I don't do windows... Does the problem persist if you manually VACUUM the table while the INSERT`s are going? BTW, it is normal for autovacuum not to run on insert only tables. – Laurenz Albe May 23 at 11:24
2

A performance knee is pretty common when inserting into an indexed table when the indexed values are not inserted in order. That is probably the case here, if your uuid values are generated in a way that they do not order chronologically.

Every insertion dirties a random leaf page in the index. As long as all the leaf pages fit in shared_buffers (and your checkpoints are far enough apart), this is not much of a problem as you can "re-dirty" the same leaf pages over and over, but separate by a substantial amount of time, without much consequence. But once you have enough leaf pages that they exceed shared_buffers, that means every insertion ejects a random dirty page to the kernel, and the kernel then tries to write out random isolated pages to disk, which has horrible performance. In theory you should be able to tune the kernel to maximize the amount of write consolidation, but I've never had good luck with this.

You can put off the performance collapse by making shared_buffers be a large fraction of RAM. But eventually (if you have enough data) that will still be exceeded. The solutions that work beyond this point are to leave the primary key off until the table is populated and then add it afterwards, or to make sure you are inserting the rows in approximately the same order as the index order.

  • thanks this is insightful – Ehouarn Perret May 24 at 19:12
  • the uuid is .NET generated following this stackoverflow.com/questions/1888254/… I have generated them but not sure how PostgreSQL is interpreting them as chronologically or not – Ehouarn Perret May 24 at 19:27
  • @EhouarnPerret Not an expert on this, but it looks like .NET uses type 4 of UUID generation code. They are random, so will not be presented to PostgreSQL in sorted order. Unless you go out of your way to generate a big batch of them, then sort them yourself, then shove them into PostgreSQL in that order. – jjanes May 24 at 19:39
  • that makes sense, tomorrow will try with the library I am using to let Postgre create them for me, so that they should be in order – Ehouarn Perret May 24 at 19:51
  • seems the library is using that jasperfx.github.io/marten/documentation/documents/identity/guid – Ehouarn Perret May 24 at 20:54

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.