On Mon, May 8, 2017 at 4:24 PM, Vincent Veyron <vv.lists@wanadoo.fr> wrote:
> On Mon, 8 May 2017 12:48:29 -0600
> Scott Marlowe <scott.marlowe@gmail.com> wrote:
>
> Hi Scott,
>
> Thank you for your input.
>
>>
>> The most likely cause of the difference would be that one server IS
>> honoring fsync requests from the db and the other one isn't.
>>
>> If you run pgbench on both (something simple like pgbench -c 1 -T 60,
>> aka one thread for 60 seconds) on a machine running on a 7200RPM hard
>> drive, you should get approximately 120 transactions per second
>
> Here are the results :
>
> #Kimsufi
> pgbench -c 1 -T 60 test
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 1
> query mode: simple
> number of clients: 1
> number of threads: 1
> duration: 60 s
> number of transactions actually processed: 6618
> latency average: 9.069 ms
> tps = 110.270771 (including connections establishing)
> tps = 110.283733 (excluding connections establishing)
Just under 120, looks like fsync is working.
>
> #Online
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 1
> query mode: simple
> number of clients: 1
> number of threads: 1
> duration: 60 s
> number of transactions actually processed: 1150
> latency average: 52.317 ms
> tps = 19.114403 (including connections establishing)
> tps = 19.115739 (excluding connections establishing)
OK that's horrendous. My mobile phone is likely faster. We need to
figure out why it's so slow. If it's in a RAID-1 set it might be
syncing.
>> > -Why are regular queries much faster on this same server?
>>
>> That's a whole nother subject. Most likely the faster machine can fit
>> the whole db in memory, or has much faster memory, or the whole
>> dataset is cached etc etc.
>>
>
> The dataset is small (35 MB) and both servers have 4GB memory. It appears to be faster on the Online server.
Yeah it fits in memory. Select queries will only hit disk at bootup.
First machine
SNIP
> Speed: 1066 MHz
SNIP
> Configured Clock Speed: 1066 MHz
Second machine
> Speed: 1600 MHz
SNIP
> Configured Clock Speed: 1333 MHz
Yeah the second machine likely has a noticeably faster CPU than the
first as well. It's about two years younger so yeah it's probably just
cpu/mem that's making it fast.