Thread: PostgreSQL and Real Application Testing (RAT)

PostgreSQL and Real Application Testing (RAT)

From

ROS Didier

Date:

27 August 2019, 13:47:24

In my business, one of the things blocking the migration from Oracle to PostgreSQL is not having the equivalent of Oracle Real Application Testing .

This product captures a charge in production and replay it in a test environment.

this allows to know the impacts of a migration to a newer version, the creation of an index..

is there an equivalent in the PostgreSQL community?

if not, do you think it's technically possible to do it ?

who would be interested in this project ?

Thanks in advance

Best Regards

Didier ROS

EDF

Ce message et toutes les pièces jointes (ci-après le 'Message') sont établis à l'intention exclusive des destinataires et les informations qui y figurent sont strictement confidentielles. Toute utilisation de ce Message non conforme à sa destination, toute diffusion ou toute publication totale ou partielle, est interdite sauf autorisation expresse.

Si vous n'êtes pas le destinataire de ce Message, il vous est interdit de le copier, de le faire suivre, de le divulguer ou d'en utiliser tout ou partie. Si vous avez reçu ce Message par erreur, merci de le supprimer de votre système, ainsi que toutes ses copies, et de n'en garder aucune trace sur quelque support que ce soit. Nous vous remercions également d'en avertir immédiatement l'expéditeur par retour du message.

Il est impossible de garantir que les communications par messagerie électronique arrivent en temps utile, sont sécurisées ou dénuées de toute erreur ou virus.
____________________________________________________

This message and any attachments (the 'Message') are intended solely for the addressees. The information contained in this Message is confidential. Any use of information contained in this Message not in accord with its purpose, any dissemination or disclosure, either whole or partial, is prohibited except formal approval.

If you are not the addressee, you may not copy, forward, disclose or use any part of it. If you have received this message in error, please delete it and all copies from your system and notify the sender immediately by return message.

E-mail communication cannot be guaranteed to be timely secure, error or virus-free.

Re: PostgreSQL and Real Application Testing (RAT)

From

Thomas Kellerer

Date:

27 August 2019, 16:49:37

ROS Didier schrieb am 27.08.2019 um 12:47:
> In my business, one of the things blocking the migration from Oracle
> to PostgreSQL is not having the equivalent of Oracle Real Application
> Testing .
>
> This product captures a charge in production and replay it in a test
> environment.
>
> this allows to know the impacts of a migration to a newer version,
> the creation of an index..
>
> is there an equivalent in the PostgreSQL community?
>
> if not, do you think it's technically possible to do it?
>
> who would be interested in this project?
Not sure how up-to-date that is, but you might want to have a look here:

https://wiki.postgresql.org/wiki/Statement_Playback

Re: PostgreSQL and Real Application Testing (RAT)

From

Jaime Casanova

Date:

27 August 2019, 21:42:27

On Tue, 27 Aug 2019 at 05:47, ROS Didier <didier.ros@edf.fr> wrote:

Hi

In my business, one of the things blocking the migration from Oracle to PostgreSQL is not having the equivalent of Oracle Real Application Testing .
This product captures a charge in production and replay it in a test environment.
this allows to know the impacts of a migration to a newer version, the creation of an index..
is there an equivalent in the PostgreSQL community?

I used https://github.com/laurenz/pgreplay recently to re-execute the queries sent to a pg9.1 in a pg11. It was very useful to find queries that are affected but changes in default values of GUCs.

Normally, a query that works in an old version will work in a new one; but this is useful to catch the few that don't if any

Jaime Casanova www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: PostgreSQL and Real Application Testing (RAT)

From

Nikolay Samokhvalov

Date:

28 August 2019, 03:32:44

On Tue, Aug 27, 2019 at 3:47 AM ROS Didier <didier.ros@edf.fr> wrote:

Hi

In my business, one of the things blocking the migration from Oracle to PostgreSQL is not having the equivalent of Oracle Real Application Testing .
This product captures a charge in production and replay it in a test environment.
this allows to know the impacts of a migration to a newer version, the creation of an index..
is there an equivalent in the PostgreSQL community?
if not, do you think it's technically possible to do it ?
who would be interested in this project ?

Replaying workload might or might not apply well to your case.

There are several major difficulties if you want to replay workload:

1) How to "record" workload. You need to write all your queries to the Postgres log. Three problems here:

1a) pgreplay expects log_statements = 'all' while you might prefer dealing with log_min_duration_statement instead. This is a minor issue though, quite easy to solve with preprocessing.

1b) under heavy load, log_min_duration_statement = 0 (or log_statements = 'all') will lead to performance degradation or even downtime. Possible solutions are: write to memory, or don't write at all but send over the network.

1c) ideally, recoding just queries is not enough. To replay workload "as is", we need to replay queries with known plans. There is no easy solution to this problem in the Postgres ecosystem yet.

A couple of additional points regarding item 1b and 1c. In Postgres 12, there is a cool new capability: sampling for query logging, implemented by Adrien Nayrat https://commitfest.postgresql.org/20/1691/ WIth this, it will be possible to fully log, say, 5% of all transactions and use it for replaying. Moreover, with auto_explain, it will be possible to have plans! Open questions are: (a) how to determine, if N% is enough, and (b) how to replay with specified plans. [If anyone is interested in working in this direction – please reach out to me.]

2) Issues with replaying itself. I can highlight at least two problems here:

2a) pgreplay might be not enough for your workload, it doesn't scale well. If interested, look at its analog written in Go, https://github.com/gocardless/pgreplay-go, but this is quite a young project.

2b) Postgres logs have millisecond precision (if you switched from %t to %m in log_line_prefix), this might be not enough. There is a patch to microsecond precision from David Fetter https://www.postgresql.org/message-id/flat/20181023185050.GE6049%40fetter.org, but that conversation hasn't yet led to commit.

Another approach you might be interested in -- workload simulation. This is what we (Postgres.ai) now used in most times when building "lab" environments for our clients. The idea is as follows:

- carefully analyze workload using pg_stat_statements (here, our open-source tool called "postgres-checkup" https://gitlab.com/postgres-ai/postgres-checkup might be helpful, see reports in section K),

- take the most resource-consuming query groups (Top-N ordered by total_time),

- create a set of files with statements with randomly filled parameters (won't work for most cases, I discuss restrictions below),

- use pgbench, feed workload files to it, using multiple -f options, with balancing (-f filename@XX, where XX is to be taked from pg_statements_analysis, but this time, "calls" and their ratio in the whole workload will be needed -- again, postgres-checkup can help here).

- run, analyze, compare behavior.

Restrictions of this approach are obvious:

- doesn't work well if most of your transactions have multiple statements,

- in many cases, randomization is hard (not obvious how to organize; synthetic approach is far from real data distribution in storage and workload; etc),

- the approach requires a significant amount of manual efforts.

However, the "workload simulation" approach is an extremely helpful approach in many cases, helping with change management. It doesn't require anything that might negatively affect your production workload, it utilizes pgbench (or any other tool) which is reliable, has great features and scales well.

You might be interested in looking at our tool that we built to conduct a huge amount of DB experiments, Nancy CLI https://gitlab.com/postgres-ai/nancy. It supports both "workload replay" method (with pgreplay) and "workload simulation" (with pgbench). PM me if you're interested in discussing details.

Thanks,

Nik

Re: PostgreSQL and Real Application Testing (RAT)

From

Jaime Casanova

Date:

28 August 2019, 03:56:27

On Tue, 27 Aug 2019 at 19:33, Nikolay Samokhvalov <samokhvalov@gmail.com> wrote:

On Tue, Aug 27, 2019 at 3:47 AM ROS Didier <didier.ros@edf.fr> wrote:
Hi

In my business, one of the things blocking the migration from Oracle to PostgreSQL is not having the equivalent of Oracle Real Application Testing .
This product captures a charge in production and replay it in a test environment.
this allows to know the impacts of a migration to a newer version, the creation of an index..
is there an equivalent in the PostgreSQL community?
if not, do you think it's technically possible to do it ?
who would be interested in this project ?

Replaying workload might or might not apply well to your case.

There are several major difficulties if you want to replay workload:

1) How to "record" workload. You need to write all your queries to the Postgres log. Three problems here:
1a) pgreplay expects log_statements = 'all' while you might prefer dealing with log_min_duration_statement instead. This is a minor issue though, quite easy to solve with preprocessing.
1b) under heavy load, log_min_duration_statement = 0 (or log_statements = 'all') will lead to performance degradation or even downtime. Possible solutions are: write to memory, or don't write at all but send over the network.
1c) ideally, recoding just queries is not enough. To replay workload "as is", we need to replay queries with known plans. There is no easy solution to this problem in the Postgres ecosystem yet.

why? i prefer queries to take advantage of new plans for example if i'm migrating from 9.5 to 9.6+ i would prefer that, when replaying, the queries use parallel plans so i quickly get if that would somehow be a problem (for example by using more cpu than before)

Jaime Casanova www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services