pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp bad results with pgbench
4+ messages / 2 participants
[nested] [flat]
* Help bad results with pgbench
@ 2017-04-10 15:09 Lazaro Garcia <lazaro3487@gmail.com>
2017-04-10 15:48 ` Re: Help bad results with pgbench Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Lazaro Garcia @ 2017-04-10 15:09 UTC (permalink / raw)
To: pgsql-admin
Good morning everyone.
I'm having the following problem with pgbench and test again:
I create a database with a scale of 200 and execute a test (read only) with
40 users and this is the result:
pgbench -U postgres -p 5433 -j 10 -c 40 -T 30 -h 127.0.0.1 -S pgbench
scaling factor: 200
query mode: simple
number of clients: 40
number of threads: 10
duration: 30 s
number of transactions actually processed: 2682306
latency average = 0.446 ms
latency stddev = 0.303 ms
tps = 89329.960851 (including connections establishing)
tps = 89348.642359 (excluding connections establishing)
Then restart the server and when I run the test again the result is really
very bad and the IO starts to climb (here the weird thing is that I'm not
writing just reading)
scaling factor: 200
query mode: simple
number of clients: 40
number of threads: 10
duration: 30 s
number of transactions actually processed: 6933
latency average = 174.032 ms
tps = 229.842173 (including connections establishing)
tps = 229.870409 (excluding connections establishing)
Can someone give me some suggestions so I can determine what's going on?
PostgreSQL 9.6.2.
Thank you very much for your time.
Regards.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Help bad results with pgbench
2017-04-10 15:09 Help bad results with pgbench Lazaro Garcia <lazaro3487@gmail.com>
@ 2017-04-10 15:48 ` Scott Marlowe <scott.marlowe@gmail.com>
2017-04-10 15:56 ` Re: Help bad results with pgbench Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Scott Marlowe @ 2017-04-10 15:48 UTC (permalink / raw)
To: Lazaro Garcia <lazaro3487@gmail.com>; +Cc: pgsql-admin
On Mon, Apr 10, 2017 at 9:09 AM, Lazaro Garcia <lazaro3487@gmail.com> wrote:
> Good morning everyone.
>
> I'm having the following problem with pgbench and test again:
>
> I create a database with a scale of 200 and execute a test (read only) with
> 40 users and this is the result:
>
> pgbench -U postgres -p 5433 -j 10 -c 40 -T 30 -h 127.0.0.1 -S pgbench
> tps = 89329.960851 (including connections establishing)
> tps = 89348.642359 (excluding connections establishing)
>
> Then restart the server and when I run the test again the result is really
> very bad and the IO starts to climb (here the weird thing is that I'm not
> writing just reading)
>
> tps = 229.842173 (including connections establishing)
> tps = 229.870409 (excluding connections establishing)
>
> Can someone give me some suggestions so I can determine what's going on?
>
> PostgreSQL 9.6.2.
>
> Thank you very much for your time.
Yeah this is the file system cache warm vs cold. In the first instance
everything fits in RAM (or most of it) and is there because it's been
accessed. When you reboot the machine is "warming up" so to speak the
file system cache. Often just running select * from table is all you
need to warm them up yourself.
--
To understand recursion, one must first understand recursion.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Help bad results with pgbench
2017-04-10 15:09 Help bad results with pgbench Lazaro Garcia <lazaro3487@gmail.com>
2017-04-10 15:48 ` Re: Help bad results with pgbench Scott Marlowe <scott.marlowe@gmail.com>
@ 2017-04-10 15:56 ` Scott Marlowe <scott.marlowe@gmail.com>
2017-04-10 16:24 ` Re: Help bad results with pgbench Lazaro Garcia <lazaro3487@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Scott Marlowe @ 2017-04-10 15:56 UTC (permalink / raw)
To: Lazaro Garcia <lazaro3487@gmail.com>; +Cc: pgsql-admin
On Mon, Apr 10, 2017 at 9:48 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Mon, Apr 10, 2017 at 9:09 AM, Lazaro Garcia <lazaro3487@gmail.com> wrote:
>> Good morning everyone.
>>
>> I'm having the following problem with pgbench and test again:
>
> Yeah this is the file system cache warm vs cold. In the first instance
> everything fits in RAM (or most of it) and is there because it's been
> accessed. When you reboot the machine is "warming up" so to speak the
> file system cache. Often just running select * from table is all you
> need to warm them up yourself.
So -i 200 is only a couple of gigs. You should fill up the file system
cache pretty fast.
Just keep running pgbench and the numbers should climb over time.
Or you can look into the pg_prewarm module.
https://www.postgresql.org/docs/9.4/static/pgprewarm.html
--
To understand recursion, one must first understand recursion.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Help bad results with pgbench
2017-04-10 15:09 Help bad results with pgbench Lazaro Garcia <lazaro3487@gmail.com>
2017-04-10 15:48 ` Re: Help bad results with pgbench Scott Marlowe <scott.marlowe@gmail.com>
2017-04-10 15:56 ` Re: Help bad results with pgbench Scott Marlowe <scott.marlowe@gmail.com>
@ 2017-04-10 16:24 ` Lazaro Garcia <lazaro3487@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Lazaro Garcia @ 2017-04-10 16:24 UTC (permalink / raw)
To: 'Scott Marlowe' <scott.marlowe@gmail.com>; +Cc: pgsql-admin
Thank you very much for the answer, your righ testing it again durin 30 min and the tps was increasing over the time.
Regards and thank you for the help.
-----Mensaje original-----
De: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Enviado el: lunes, 10 de abril de 2017 11:56 a. m.
Para: Lazaro Garcia
CC: pgsql-admin@postgresql.org
Asunto: Re: [ADMIN] Help bad results with pgbench
On Mon, Apr 10, 2017 at 9:48 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Mon, Apr 10, 2017 at 9:09 AM, Lazaro Garcia <lazaro3487@gmail.com> wrote:
>> Good morning everyone.
>>
>> I'm having the following problem with pgbench and test again:
>
> Yeah this is the file system cache warm vs cold. In the first instance
> everything fits in RAM (or most of it) and is there because it's been
> accessed. When you reboot the machine is "warming up" so to speak the
> file system cache. Often just running select * from table is all you
> need to warm them up yourself.
So -i 200 is only a couple of gigs. You should fill up the file system cache pretty fast.
Just keep running pgbench and the numbers should climb over time.
Or you can look into the pg_prewarm module.
https://www.postgresql.org/docs/9.4/static/pgprewarm.html
--
To understand recursion, one must first understand recursion.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2017-04-10 16:24 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-04-10 15:09 Help bad results with pgbench Lazaro Garcia <lazaro3487@gmail.com>
2017-04-10 15:48 ` Scott Marlowe <scott.marlowe@gmail.com>
2017-04-10 15:56 ` Scott Marlowe <scott.marlowe@gmail.com>
2017-04-10 16:24 ` Lazaro Garcia <lazaro3487@gmail.com>
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