public inbox for [email protected]help / color / mirror / Atom feed
2 server with same configuration but huge difference in performance 7+ messages / 5 participants [nested] [flat]
* 2 server with same configuration but huge difference in performance @ 2017-08-01 13:41 Sumeet Shukla <[email protected]> 0 siblings, 2 replies; 7+ messages in thread From: Sumeet Shukla @ 2017-08-01 13:41 UTC (permalink / raw) To: pgsql-admin <[email protected]>; pgsql-performance 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 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: 2 server with same configuration but huge difference in performance @ 2017-08-01 14:16 Keith <[email protected]> parent: Sumeet Shukla <[email protected]> 1 sibling, 1 reply; 7+ messages in thread From: Keith @ 2017-08-01 14:16 UTC (permalink / raw) To: Sumeet Shukla <[email protected]>; +Cc: pgsql-admin <[email protected]>; pgsql-performance On Tue, Aug 1, 2017 at 9:41 AM, Sumeet Shukla <[email protected]> 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 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PERFORM] 2 server with same configuration but huge difference in performance @ 2017-08-01 14:21 Anthony Sotolongo <[email protected]> parent: Keith <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Anthony Sotolongo @ 2017-08-01 14:21 UTC (permalink / raw) To: Keith <[email protected]>; Sumeet Shukla <[email protected]>; +Cc: pgsql-admin <[email protected]>; pgsql-performance Hi Sumeet Shukla While script is running check the pg_stat_activity, this view can be util Regards Anthony On 01/08/17 10:16, Keith wrote: > > On Tue, Aug 1, 2017 at 9:41 AM, Sumeet Shukla > <[email protected] <mailto:[email protected]>> 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 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PERFORM] 2 server with same configuration but huge difference in performance @ 2017-08-01 15:35 Scott Marlowe <[email protected]> parent: Sumeet Shukla <[email protected]> 1 sibling, 1 reply; 7+ messages in thread From: Scott Marlowe @ 2017-08-01 15:35 UTC (permalink / raw) To: Sumeet Shukla <[email protected]>; +Cc: pgsql-admin <[email protected]>; pgsql-performance On Tue, Aug 1, 2017 at 6:41 AM, Sumeet Shukla <[email protected]> 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 -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PERFORM] 2 server with same configuration but huge difference in performance @ 2017-08-01 15:45 Sumeet Shukla <[email protected]> parent: Scott Marlowe <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Sumeet Shukla @ 2017-08-01 15:45 UTC (permalink / raw) To: Scott Marlowe <[email protected]>; +Cc: pgsql-admin <[email protected]>; pgsql-performance 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. Thanks & Regards, Sumeet Shukla On Tue, Aug 1, 2017 at 9:05 PM, Scott Marlowe <[email protected]> wrote: > On Tue, Aug 1, 2017 at 6:41 AM, Sumeet Shukla <[email protected]> > 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 > ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: 2 server with same configuration but huge difference in performance @ 2017-08-01 16:13 Scott Marlowe <[email protected]> parent: Sumeet Shukla <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Scott Marlowe @ 2017-08-01 16:13 UTC (permalink / raw) To: Sumeet Shukla <[email protected]>; +Cc: pgsql-admin <[email protected]>; pgsql-performance On Tue, Aug 1, 2017 at 8:45 AM, Sumeet Shukla <[email protected]> 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. -- Sent via pgsql-performance mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PERFORM] 2 server with same configuration but huge difference in performance @ 2017-08-01 22:14 Naveen Kumar <[email protected]> parent: Scott Marlowe <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Naveen Kumar @ 2017-08-01 22:14 UTC (permalink / raw) To: Sumeet Shukla <[email protected]>; +Cc: pgsql-admin <[email protected]>; pgsql-performance; Scott Marlowe <[email protected]> Hello Sumeet, Compare schema on both database to make sure there is no mismatches. And verify LOCKs. if all looks good, do analyze on newly created database before start execution. This will help you. New database doesn't have any stats for generate execution plan. Thanks & Regards, Naveen Kumar .M, Sr. PostgreSQL Database Administrator, Mobile: 7755929449. *My attitude will always be based on how you treat me. * On Tue, Aug 1, 2017 at 9:43 PM, Scott Marlowe <[email protected]> wrote: > On Tue, Aug 1, 2017 at 8:45 AM, Sumeet Shukla <[email protected]> > 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. > > > -- > Sent via pgsql-admin mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2017-08-01 22:14 UTC | newest] Thread overview: 7+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2017-08-01 13:41 2 server with same configuration but huge difference in performance Sumeet Shukla <[email protected]> 2017-08-01 14:16 ` Keith <[email protected]> 2017-08-01 14:21 ` Anthony Sotolongo <[email protected]> 2017-08-01 15:35 ` Scott Marlowe <[email protected]> 2017-08-01 15:45 ` Sumeet Shukla <[email protected]> 2017-08-01 16:13 ` Scott Marlowe <[email protected]> 2017-08-01 22:14 ` Naveen Kumar <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox