RE: Drop in insert performance after 20ish seconds

If your server has just 8 GB of RAM and you’re using the default postgresql.conf you are likely indeed running into the RAM limitations with regard to the index size just short of 100M lines, and therefore its time to start partitioning your tables. I thought you only had less than 10,000 lines in the table to start with.

Giving your server more RAM (and increasing the shared buffers) will likely help quite a bit too … but eventually the table will get too large again.

See
https://pgtune.leopard.in.ua/#/

For some basic tuning help.

--Stephen

From: Toni Alfirević <toni(dot)alfirevic(at)gmail(dot)com>
Sent: Tuesday, February 25, 2020 7:52 AM
To: Justin <zzzzz(dot)graf(at)gmail(dot)com>
Cc: Stephen Froehlich <s(dot)froehlich(at)cablelabs(dot)com>; pgsql-novice(at)lists(dot)postgresql(dot)org
Subject: Re: Drop in insert performance after 20ish seconds

Justin,

yeah, I've run some basic system diagnostic and I haven't found anything suspicious.
1 core is being utilized around 50ish %.
RAM utilization is currently at around 6.5 GB / 8 GB utilization, but I'm assuming that's to be expected. Since my table grew to around 80 mil records and indexes are getting bigger.
DISK IO I haven't checked properly, yet. But at this point, I'm guessing my problem is somewhere else.
Not sure if it's relevant but storage is SSD.

Following Stephen's suggestion, I'll try to modify my record inserting app to do EXPLAIN ANALYZE every x records and will get back to you. Maybe that will yield... Something.

On Tue, Feb 25, 2020 at 3:21 PM Justin <zzzzz(dot)graf(at)gmail(dot)com<mailto:zzzzz(dot)graf(at)gmail(dot)com>> wrote:
have you run any system monitor to see what the CPU, RAM and DISK IO is doing, something like TOP, or Glances

On Tue, Feb 25, 2020 at 9:16 AM Stephen Froehlich <s(dot)froehlich(at)cablelabs(dot)com<mailto:s(dot)froehlich(at)cablelabs(dot)com>> wrote:
This is a bit of a head-scratcher …

Usually one sees performance drop off once the index is so large that it can’t fit in RAM anymore, but that’s typically in the hundreds of millions to billions of rows.

It would help a lot if you could run EXPLAIN ANALYSE INSERT and post the results here …

https://www.postgresql.org/docs/current/sql-explain.html

--Stephen

From: Toni Alfirević <toni(dot)alfirevic(at)gmail(dot)com<mailto:toni(dot)alfirevic(at)gmail(dot)com>>
Sent: Tuesday, February 25, 2020 2:50 AM
To: pgsql-novice(at)lists(dot)postgresql(dot)org<mailto:pgsql-novice(at)lists(dot)postgresql(dot)org>
Subject: Drop in insert performance after 20ish seconds

Hi,

I'm trying to gauge how PostgreSQL would perform on a specific VPS and for that purpose I've written a very simple console app that continuously inserts entries into one table.

CREATE TABLE public.pg_test_messages
(
"Id" bigint NOT NULL DEFAULT nextval('"pg_test_messages_Id_seq"'::regclass),
"Message" text COLLATE pg_catalog."default" NOT NULL,
"TimeStamp" timestamp without time zone,
CONSTRAINT "PK_pg_test_messages" PRIMARY KEY ("Id")
)

TABLESPACE pg_default;

-- Index: IX_pg_test_messages_Id

-- DROP INDEX public."IX_pg_test_messages_Id";

CREATE INDEX "IX_pg_test_messages_Id"
ON public.pg_test_messages USING btree
("Id" ASC NULLS LAST)
TABLESPACE pg_default;

And the behaviour I'm experiencing is as follows:

first 20ish seconds of inserts it takes avg. 650-700 ms to insert 1000 entries.
And after that insert performance drops to around 980-1000 ms to insert 1000 entries.

I've run numerous tests and this behaviour is consistent.

Since I'm not very familiar with all of the pgsql internal processes that are running my question is...
Is this something that is normal and expected?
If it is, could you let me know what is this related to?

--
Toni Alfirevic

--
Toni Alfirevic