Thread: [PERFORM] 2 server with same configuration but huge difference in performance

Hi Sumeet Shukla

While script is running check the pg_stat_activity, this view can be util

Regards

Anthony

On Tue, Aug 1, 2017 at 9:41 AM, Sumeet Shukla <sumeet.k.shukla@gmail.com> wrote:

Hi,

I have 2 PG servers with same h/w and configuration and they are not in replication.

On server A it takes 20 minutes to execute the script.

On server B it takes more than 20 hours. (Seems to be stuck with create index and and create foreign key steps)

Any guidance to troubleshoot this would be highly appreciated.

Thanks & Regards,

Sumeet Shukla

Check for long running queries on the server that is taking longer. If it's things like CREATE INDEX or ALTER TABLE statements that are being blocked, a transaction running on the table involved will cause those commands to be held until those transactions complete.

If it's normal read/write queries to that are taking longer, ensure the database statistics are up to date by running an analyze.

Keith

On Tue, Aug 1, 2017 at 6:41 AM, Sumeet Shukla <sumeet.k.shukla@gmail.com> wrote:
> Hi,
>
> I have 2 PG servers with same h/w and configuration and they are not in
> replication.
>
> On server A it takes 20 minutes to execute the script.
> On server B it takes more than 20 hours. (Seems to be stuck with create
> index and and create foreign key steps)
>
> Any guidance to troubleshoot this would be highly appreciated.

There's lots of areas where you could be running into problems. I
suggest reading this wiki page on reporting performance problems.
It'll help you gather more evidence of where and what the problem is.

https://wiki.postgresql.org/wiki/Guide_to_reporting_problems

On Tue, Aug 1, 2017 at 8:45 AM, Sumeet Shukla <sumeet.k.shukla@gmail.com> wrote:
> It seems that it is happening because of the way the database is created. On
> an old database it runs perfectly fine or if I use the old DB as template to
> create the new one, it  runs fine. But if I create a new DB with same
> settings and permissions it hangs. I'm now trying to find the difference
> between these 2 databases.

Likely a difference in encoding or collation. What does \l show you
(that's a lower case L btw)

smarlowe=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |
Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | smarlowe | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 smarlowe  | smarlowe | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

UTF8 and en_US are much more expensive than SQL_ASCII and C would be
for text and such. Basically indexes either don't work or work as well
under en_US if you're comparing or sorting text.