From: | Dave Johansen <davejohansen(at)gmail(dot)com> |
---|---|
To: | Shaun Thomas <sthomas(at)optionshouse(dot)com> |
Cc: | pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Unexpected pgbench result |
Date: | 2013-12-19 22:06:18 |
Message-ID: | CAAcYxUd7sm6L6p3=bWiFPRm8qpvEu59zHuwtv_ni-HAgHO0PQw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Thu, Dec 19, 2013 at 10:44 AM, Shaun Thomas <sthomas(at)optionshouse(dot)com>wrote:
> On 12/19/2013 11:00 AM, Dave Johansen wrote:
>
> When I run pgbench in "SELECT only" after doing "-i -s 2000" I get what
>> appears to be good performance (60k-70k tps) but if I initialize a new
>> database with "-i -s 4000" the tps drops to 4k-7k. Is this order of
>> magnitude drop expected? Or is there something wrong with my hardware or
>> database configuration that is causing this issue?
>>
>
> When you increase the size of the initialized pgbench tables, you increase
> the size on disk. My guess is that you doubled it so that the data no
> longer fits in memory. You can verify this yourself:
>
> SELECT pg_size_pretty(sum(pg_database_size(oid))::bigint)
> from pg_database;
>
> Any amount of memory you have that is smaller than that, will affect
> select performance. I can guarantee you will not get 60k-70k tps from
> anything short of an array of SSD devices or a PCIe NVRAM solution. Your
> '-s 2000' test was probably running mostly from memory, while the '-s 4000'
> did not.
>
> What you're seeing is the speed your records are being supplied from disk,
> plus whatever cache effects are there when records are read before they are
> flushed in favor of more recent data.
>
Yep, that was exactly it and that definitely makes sense now that you point
it out.
Right now, we're running a RAID 1 for pg_clog, pg_log and pg_xlog and then
a RAID 1+0 with 12 disks for the data. Would there be any benefit to
running a separate RAID 1+0 with a tablespace for the indexes? Or is
reading the indexes and data a serial process where separating them like
that won't have any big benefit?
Thanks,
Dave
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Crawford | 2013-12-19 23:40:25 | Re: Regarding Hardware Tuning |
Previous Message | Sergey Konoplev | 2013-12-19 21:24:26 | Re: slow query - will CLUSTER help? |