Re: Read write performance check - Mailing list pgsql-general
| From | veem v |
|---|---|
| Subject | Re: Read write performance check |
| Date | |
| Msg-id | CAB+=1TXHvvVfzOLibSQ9J9SazMvPxttVW__GsfT5kjajAD1iWQ@mail.gmail.com Whole thread Raw |
| In response to | Re: Read write performance check (Ilya Kosmodemiansky <ik@dataegret.com>) |
| Responses |
Re: Read write performance check
Re: Read write performance check |
| List | pgsql-general |
Thank you for the confirmation.
So at first, we need to populate the base tables with the necessary data (say 100million rows) with required skewness using random functions to generate the variation in the values of different data types. Then in case of row by row write/read test , we can traverse in a cursor loop. and in case of batch write/insert , we need to traverse in a bulk collect loop. Something like below and then this code can be wrapped into a procedure and passed to the pgbench and executed from there. Please correct me if I'm wrong.
Also can you please guide how the batch(say batch size of ~1000) Insert can be written ?
-- Row by row write
FOR i IN 1..total_rows LOOP
data_row := (SELECT
md5(random()::TEXT),
floor(random() * 100)::INT,
random() * 1000::NUMERIC,
NOW()::TIMESTAMP
);
INSERT INTO BASE_TABLE(column1, column2, column3, column4)
VALUES (data_row.column1, data_row.column2, data_row.column3, data_row.column4);
END LOOP;
--Row by row read
BEGIN
FOR i IN 1..total_rows LOOP
-- Row by row read
SELECT * INTO data_row FROM BASE_TABLE WHERE limit 1;
END LOOP;
END;
-- Batch read
BEGIN
-- Batch read
OPEN data_set FOR SELECT * FROM BASE_TABLE LIMIT total_rows;
CLOSE data_set;
END;
FOR i IN 1..total_rows LOOP
data_row := (SELECT
md5(random()::TEXT),
floor(random() * 100)::INT,
random() * 1000::NUMERIC,
NOW()::TIMESTAMP
);
INSERT INTO BASE_TABLE(column1, column2, column3, column4)
VALUES (data_row.column1, data_row.column2, data_row.column3, data_row.column4);
END LOOP;
--Row by row read
BEGIN
FOR i IN 1..total_rows LOOP
-- Row by row read
SELECT * INTO data_row FROM BASE_TABLE WHERE limit 1;
END LOOP;
END;
-- Batch read
BEGIN
-- Batch read
OPEN data_set FOR SELECT * FROM BASE_TABLE LIMIT total_rows;
CLOSE data_set;
END;
On Tue, 19 Dec 2023 at 12:58, Ilya Kosmodemiansky <ik@dataegret.com> wrote:
Hi Veem,
On Tue, Dec 19, 2023 at 7:36 AM veem v <veema0000@gmail.com> wrote:
> 1)For write performance , the rows needs to be inserted from multiple sessions at same time, with required random values as per the data types i.e. Character, Number, date columns. And this needs to be tested for row by row insert and batched insert.
>
> 2)For the read performance test , the table first has to be populated with those ~100million rows. Then querying will happen on that table row by row and batched way from the concurrent session.
>
> I am new to postgresql but mostly worked with Oracle, so I wanted to understand if the above can be achieved by creating a simple procedure or will a major effort be needed? And I saw a few blogs , pgbench to be used for concurrency tests.
Yes, you are right, pgbench with customized script is what you are looking for
>I want to know if this will still work on Aurora postgresql from intellij client worksheet.
pgbench would work with aurora as with normal postgres, it is
basically a shell script which connects to the database. Not sure if
idea worksheet would help you in such case however, you can run it
just from any machine with pgbench installed
Best regards,
Ilya
--
Ilya Kosmodemiansky
CEO, Founder
Data Egret GmbH
Your remote PostgreSQL DBA team
T.: +49 6821 919 3297
ik@dataegret.com
pgsql-general by date: