Re: Perf decreased although server is better

Stream gives substantially better results with the new server (before
<http://pastebin.com/xrLN9Q6s>/after <http://pastebin.com/fDm9aNDh>)

I've run "bonnie++ -u postgres -d /tmp/ -s 4096M -r 1096" on both machines.
I don't know how to read bonnie++ results (before
<http://pastebin.com/mBi8UstP>/after <http://pastebin.com/hbTrZ8hT>) but it
looks quite the same, sometimes better for the new, sometimes better for
the old.

N.B. tests have been performed while on production on the new server
whereas the old it's not solicited anymore.

I used pg_dump 9.6.1 to dump from Postgres 9.4.5 and restored with pg_restore
9.6.1 with "-j 4" option.

The database has many tables and indexes (50+). I haven't isolated which
queries performs worse, it's just a general effect.

The loss in terms of response time seems now really consistent, look how
the yellow suddenly grew by 100%:
[image: Images intégrées 1]

I haven't tried `pg_repack` yet.

As a user, is it expected that restoring could break index efficiencies
when moving from one version to another?

I feel really bad now because I'm not confident re-switching back to the
old server will restore initial performance :(

2016-11-02 15:55 GMT+01:00 Rick Otten <rottenwindfish(at)gmail(dot)com>:

> How did you migrate from one system to the other?
>
> [ I recently moved a large time series table from 9.5.4 to 9.6.1 using
> dump and restore. Although it put the BRIN index on the time column back
> on, it was borked. Reindexing didn't help. I had to switch it to a
> regular btree index. I think the data wasn't inserted back into the
> database in time order. Therefore, because it was all over the place, the
> efficiency gains from the BRIN index were lost. It was probably because I
> restored it with "-j 8". -- It is possible something you didn't expect
> when moving introduce new inefficiencies. I also found running pg_repack
> after the restore helped performance (and storage size) on my new system
> too. ]
>
>
> Do you have all of the kernel settings configured per best practices?
> Sometimes it is easy to forget them when you get totally focused on just
> moving the data.
> (Things such as your hugepages settings)
>
> With 9.6 you can enable parallel queries. Of course you wouldn't be
> comparing apples-to-apples then, but take advantage of that feature if you
> can.
>
>
> On Wed, Nov 2, 2016 at 9:26 AM, Benjamin Toueg <btoueg(at)gmail(dot)com> wrote:
>
>> Hi everyone,
>>
>> I'm facing a peformance decrease after switching to a more performant VPS
>> : http://serverfault.com/questions/812702/posgres-perf-decreas
>> ed-although-server-is-better
>>
>> My questions are:
>>
>> 1. What benchmark should I perform before switching to a new server?
>> 2. What's your rule of thumb regarding my specific issue? What should
>> be investigated first?
>>
>>
>> Best Regards,
>>
>> Benjamin
>>
>>
>