From: | Justin <zzzzz(dot)graf(at)gmail(dot)com> |
---|---|
To: | Stephen Froehlich <s(dot)froehlich(at)cablelabs(dot)com> |
Cc: | Toni Alfirević <toni(dot)alfirevic(at)gmail(dot)com>, "pgsql-novice(at)lists(dot)postgresql(dot)org" <pgsql-novice(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Drop in insert performance after 20ish seconds |
Date: | 2020-02-25 14:21:28 |
Message-ID: | CALL-XeM2RpgLeekLNrNPDEfG2u6AQe3NbjNhHaHuVgR1vrz3Kg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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>
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>
> *Sent:* Tuesday, February 25, 2020 2:50 AM
> *To:* 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Toni Alfirević | 2020-02-25 14:51:52 | Re: Drop in insert performance after 20ish seconds |
Previous Message | Stephen Froehlich | 2020-02-25 14:15:58 | RE: Drop in insert performance after 20ish seconds |