From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: pgsql-performance@lists.postgresql.org
Subject: Re: pgbench results arent accurate
Date: Tue, 18 Dec 2018 16:10:50 +1300
Message-ID: <9cccf498-527f-cd7b-8601-853d10b7990c@catalyst.net.nz> (raw)
In-Reply-To: <CA+t6e1mzeBgT+F3jm0jjChaGSh+G20ZcmG+Bdfi83+NCbeCL7A@mail.gmail.com>
References: <CA+t6e1nofA5=w1HnknWUTvXsmBryqiPEzgdouiqgdYL0tL1Uuw@mail.gmail.com>
<DM6PR12MB27966FF94317A623FF8BB011ECA00@DM6PR12MB2796.namprd12.prod.outlook.com>
<CA+t6e1mqqnLfdM9x0XyLtd6d4xjr9QMQjpB4hWcn_8tr+s8ntg@mail.gmail.com>
<DM6PR12MB2796A3E896A30D9D6AD7ACF9ECA00@DM6PR12MB2796.namprd12.prod.outlook.com>
<CA+t6e1m6ocV7JR4J2zn6Nv5jmF0Wv=zuXAae1qH3i9bJrvfqeA@mail.gmail.com>
<CA+t6e1mzeBgT+F3jm0jjChaGSh+G20ZcmG+Bdfi83+NCbeCL7A@mail.gmail.com>
Hi, I can see two issues making you get variable results:
1/ Number of clients > scale factor
Using -c16 and -s 6 means you are largely benchmarking lock contention
for a row in the branches table (it has 6 rows in your case). So
randomness in *which* rows each client tries to lock will make for
unwanted variation.
2/ Short run times
That 1st run is 5s duration. This will be massively influenced by the
above point about randomness for locking a branches row.
I'd recommend:
- always run at least -T600
- use -s of at least 1.5x your largest -c setting (I usually use -s 100
for testing 1-32 clients).
regards
Mark
On 17/12/18 12:58 AM, Mariel Cherkassky wrote:
> As Greg suggested, update you all that each vm has its own dedicated
> esx. Every esx has it`s own local disks.
> I run it one time on two different servers that has the same hardware
> and same postgresql db (version and conf). The results :
> pgbench -i -s 6 pgbench -p 5432 -U postgres
> pgbench -c 16 -j 4 -T 5 -U postgres pgbench
> MACHINE 1
> starting vacuum...end.
> transaction type: <builtin: TPC-B (sort of)>
> scaling factor: 6
> query mode: simple
> number of clients: 16
> number of threads: 4
> duration: 5 s
> number of transactions actually processed: 669
> latency average = 122.633 ms
> tps = 130.470828 (including connections establishing)
> tps = 130.620286 (excluding connections establishing)
>
> MACHINE 2
>
> pgbench -c 16 -j 4 -T 600 -U postgres -p 5433 pgbench
> starting vacuum...end.
> transaction type: <builtin: TPC-B (sort of)>
> scaling factor: 6
> query mode: simple
> number of clients: 16
> number of threads: 4
> duration: 600 s
> number of transactions actually processed: 2393723
> latency average = 4.011 ms
> tps = 3989.437514 (including connections establishing)
> tps = 3989.473036 (excluding connections establishing)
>
> any idea what can cause such a difference ? Both of the machines have
> 20core and 65GB of ram.
>
> בתאריך יום ה׳, 13 בדצמ׳ 2018 ב-15:54 מאת Mariel Cherkassky
> <mariel.cherkassky@gmail.com <mailto:mariel.cherkassky@gmail.com>>:
>
> Ok, I'll do that. Thanks .
>
> בתאריך יום ה׳, 13 בדצמ׳ 2018 ב-15:54 מאת Greg Clough
> <Greg.Clough@ihsmarkit.com <mailto:Greg.Clough@ihsmarkit.com>>:
>
> Hmmm... sounds like you’ve got most of it covered. It may be
> a good idea to send that last message back to the list, as
> maybe others will have better ideas.
>
> Greg.
>
> *From:* Mariel Cherkassky <mariel.cherkassky@gmail.com
> <mailto:mariel.cherkassky@gmail.com>>
> *Sent:* Thursday, December 13, 2018 1:45 PM
> *To:* Greg Clough <Greg.Clough@ihsmarkit.com
> <mailto:Greg.Clough@ihsmarkit.com>>
> *Subject:* Re: pgbench results arent accurate
>
> Both of the machines are the only vms in a dedicated esx for
> each one. Each esx has local disks.
>
> On Thu, Dec 13, 2018, 3:05 PM Greg Clough
> <Greg.Clough@ihsmarkit.com <mailto:Greg.Clough@ihsmarkit.com>
> wrote:
>
> > I installed a new postgres 9.6 on both of my machines.
>
> Where is your storage? Is it local, or on a SAN? A SAN
> will definitely have a cache, so possibly there is another
> layer of cache that you’re not accounting for.
>
> Greg Clough.
>
> ------------------------------------------------------------------------
>
>
> This e-mail, including accompanying communications and
> attachments, is strictly confidential and only for the
> intended recipient. Any retention, use or disclosure not
> expressly authorised by IHSMarkit is prohibited. This
> email is subject to all waivers and other terms at the
> following link:
> https://ihsmarkit.com/Legal/EmailDisclaimer.html
>
> Please visit www.ihsmarkit.com/about/contact-us.html
> <http://www.ihsmarkit.com/about/contact-us.html; for
> contact information on our offices worldwide.
>
>
> ------------------------------------------------------------------------
>
> This e-mail, including accompanying communications and
> attachments, is strictly confidential and only for the
> intended recipient. Any retention, use or disclosure not
> expressly authorised by IHSMarkit is prohibited. This email is
> subject to all waivers and other terms at the following link:
> https://ihsmarkit.com/Legal/EmailDisclaimer.html
>
> Please visit www.ihsmarkit.com/about/contact-us.html
> <http://www.ihsmarkit.com/about/contact-us.html; for contact
> information on our offices worldwide.
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark.kirkwood@catalyst.net.nz, pgsql-performance@lists.postgresql.org
Subject: Re: pgbench results arent accurate
In-Reply-To: <9cccf498-527f-cd7b-8601-853d10b7990c@catalyst.net.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox