Re: Perf decreased although server is better
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/after) 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%:
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@gmail.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@gmail.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:
- What benchmark should I perform before switching to a new server?
- What's your rule of thumb regarding my specific issue? What should be investigated first?
Best Regards,Benjamin
Вложения
В списке pgsql-performance по дате отправления: