We recently migrated hive metastore database from embedded postgresql(coming with cloudera hadoop) to external postgresql(standalone in vm server). After that we notified that there is increase of 5 to 10 seconds in query execution. May i know why it happens?. Postgresql config setting almost same except archive_mode which is on external postgresql, while in embedded postgresql it is off.
3
-
1wiki.postgresql.org/wiki/SlowQueryQuestionsuser1822– user18222017-08-28 11:58:09 +00:00Commented Aug 28, 2017 at 11:58
-
Also: why are you using an outdated and unsupported Postgres version?user1822– user18222017-08-28 11:58:41 +00:00Commented Aug 28, 2017 at 11:58
-
1"embedded" PostgreSQL?Craig Ringer– Craig Ringer2017-08-28 12:20:25 +00:00Commented Aug 28, 2017 at 12:20
Add a comment
|
1 Answer
For me it looks like an issue with the VM and/or network and not Postgres, if it is true that nothing else has changed, but only the "dedication" to another machine.
We had a bad VM configuration - my VM was on the same rack as our Scientists run their stuff. So each time they did anything - all resources went there.
Look at your VM, how much RAM and Processing power does it really have? If you can afford it - give your your postgres more RAM and config it to cache most of the DB (or all). And if you can afford it - buy a dedicated server for Postgres. You'll have one for below $1k (with couple of SSDs and 32GB RAM).
For the rest: listen to a_horse_with_no_name, and give us additional info (and hell, if you use pg91 - upgrade to pg96, if you use anything below pg96 - upgrade!)
-- should be a comment, but my reputation is not enough for it.
