hive + too many connection to PostgreSQL DB

Question

We have ambari cluster with Hadoop version – 2.6.4

When we run query in order to verify connections in PostgreSQL DB , we found many hive connection – in our case around 90

This cause other application problems

So we suspect that hiveserver2 is not cleaning up connections like it should and files are not being released.

Any suggestion why hive is with a lot connection? to postgresql ? , And what is the solution for that?

One option that we found is to update the file /etc/security/limits.conf ( according to the answer in the link

https://community.hortonworks.com/questions/48351/hiveserver2-hive-users-nofile-ulimit-above-64000.html )

other interesting post is from - https://community.hortonworks.com/questions/48351/hiveserver2-hive-users-nofile-ulimit-above-64000.html

But we are not sure that this is the solution

su postgres
bash-4.2$ psql
psql (9.2.13)
Type "help" for help.

postgres=#
postgres=# select pid as process_id,
postgres-#        usename as username,
postgres-#        datname as database_name,
postgres-#        client_addr as client_address,
postgres-#        application_name,
postgres-#        backend_start,
postgres-#        state,
postgres-#        state_change
postgres-# from pg_stat_activity;
 process_id | username | database_name | client_address | application_name |         backend_start         |        state        |         state_change
------------+----------+---------------+----------------+------------------+-------------------------------+---------------------+-------------------------------
      24182 | ambari   | ambari        | 127.0.0.1      |                  | 2019-05-29 08:20:12.136194+00 | idle                | 2019-05-29 13:36:53.660075+00
      24184 | ambari   | ambari        | 127.0.0.1      |                  | 2019-05-29 08:20:12.330268+00 | idle                | 2019-05-29 14:06:24.794683+00
      24185 | ambari   | ambari        | 127.0.0.1      |                  | 2019-05-29 08:20:12.346594+00 | idle                | 2019-05-29 14:06:57.014266+00
      24186 | ambari   | ambari        | 127.0.0.1      |                  | 2019-05-29 08:20:12.352127+00 | idle                | 2019-05-29 14:07:01.373678+00
      24192 | ambari   | ambari        | 127.0.0.1      |                  | 2019-05-29 08:20:14.736059+00 | idle                | 2019-05-29 14:06:37.701989+00
      24499 | ambari   | ambari        | 127.0.0.1      |                  | 2019-05-29 08:20:47.801281+00 | idle                | 2019-05-29 14:06:37.701784+00
      24500 | ambari   | ambari        | 127.0.0.1      |                  | 2019-05-29 08:20:47.801537+00 | idle                | 2019-05-29 14:06:37.701784+00
      24501 | ambari   | ambari        | 127.0.0.1      |                  | 2019-05-29 08:20:47.801818+00 | idle                | 2019-05-29 14:06:37.701956+00
      24502 | ambari   | ambari        | 127.0.0.1      |                  | 2019-05-29 08:20:47.934395+00 | idle                | 2019-05-29 14:06:54.125167+00
      24628 | hive     | hive          | 34.22.1.91   |                  | 2019-05-29 08:21:12.959034+00 | idle                | 2019-05-29 14:06:11.004364+00
      24620 | hive     | hive          | 34.22.1.91   |                  | 2019-05-29 08:21:10.674515+00 | idle                | 2019-05-29 14:06:10.697886+00
      24621 | hive     | hive          | 34.22.1.91   |                  | 2019-05-29 08:21:10.866393+00 | idle                | 2019-05-29 14:06:10.900711+00
      24624 | hive     | hive          | 34.22.1.91   |                  | 2019-05-29 08:21:12.11398+00  | idle                | 2019-05-29 14:05:46.928607+00
      24625 | hive     | hive          | 34.22.1.91   |                  | 2019-05-29 08:21:12.139117+00 | idle                | 2019-05-29 14:06:14.719214+00
      24629 | hive     | hive          | 34.22.1.91   |                  | 2019-05-29 08:21:12.980466+00 | idle                | 2019-05-29 14:06:11.005997+00
      24750 | hive     | hive          | 34.22.1.91   |                  | 2019-05-29 08:21:17.681669+00 | idle                | 2019-05-29 14:06:14.713394+00
      24751 | hive     | hive          | 34.22.1.91   |                  | 2019-05-29 08:21:17.68909+00  | idle                | 2019-05-29 14:06:14.618542+00
      25223 | hive     | hive          | 34.22.1.93   |                  | 2019-05-29 08:21:27.016383+00 | idle                | 2019-05-29 14:06:22.918908+00
      25293 | hive     | hive          | 34.22.1.93   |                  | 2019-05-29 08:21:27.394022+00 | idle                | 2019-05-29 14:06:22.933555+00
      24818 | hive     | hive          | 34.22.1.93   |                  | 2019-05-29 08:21:23.344101+00 | idle                | 2019-05-29 13:54:22.786162+00
      24819 | hive     | hive          | 34.22.1.93   |                  | 2019-05-29 08:21:23.364094+00 | idle                | 2019-05-29 13:54:22.937269+00
      24820 | hive     | hive          | 34.22.1.93   |                  | 2019-05-29 08:21:23.377741+00 | idle                | 2019-05-29 13:57:22.797683+00
      24821 | hive     | hive          | 34.22.1.93   |                  | 2019-05-29 08:21:23.389561+00 | idle                | 2019-05-29 13:57:22.928034+00
      24822 | hive     | hive          | 34.22.1.93   |                  | 2019-05-29 08:21:23.397512+00 | idle                | 2019-05-29 14:00:22.808355+00
      24823 | hive     | hive          | 34.22.1.93   |                  | 2019-05-29 08:21:23.411604+00 | idle                | 2019-05-29 14:00:22.930902+00
      24824 | hive     | hive          | 34.22.1.93   |                  | 2019-05-29 08:21:23.420796+00 | idle                | 2019-05-29 14:03:22.872217+00
      24825 | hive     | hive          | 34.22.1.93   |                  | 2019-05-29 08:21:23.42768+00  | idle                | 2019-05-29 14:03:23.033186+00
      25196 | hive     | hive          | 34.22.1.93   |                  | 2019-05-29 08:21:25.651445+00 | idle                | 2019-05-29 14:06:22.79415+00
      24881 | hive     | hive          | 34.22.1.93   |                  | 2019-05-29 08:21:24.131849+00 | idle                | 2019-05-29 14:06:26.752754+00
      24884 | hive     | hive          | 34.22.1.93   |                  | 2019-05-29 08:21:24.16027+00  | idle                | 2019-05-29 14:06:26.754117+00

tk421 · Accepted Answer · 2019-05-29T17:38:47.437

1

Most all Hadoop services tend to have a lot of open files. It's not uncommon for a production environment to have a 100K-200K open file descriptor limit.

The easiest solution is to raise the file descriptor limit.

Reference:

https://unix.stackexchange.com/questions/8945/how-can-i-increase-open-files-limit-for-all-processes

edited May 29 '19 at 17:38

answered May 29 '19 at 15:53

tk421

5,775
6
23
34

sorry , not understand the suggestion ? – Judy May 29 '19 at 16:32
do you suggest to update the /etc/security/limits.conf according to the link https://community.hortonworks.com/questions/48351/hiveserver2-hive-users-nofile-ulimit-above-64000.html ?- ( do you think is solve the issue about too many connection to postgres? – Judy May 29 '19 at 16:39
ok lets say I will do what was in the post , so do you think the hive connection will be much less ? – Judy May 29 '19 at 17:49
in our machine , the current configuration is * soft nofile 20000 * hard nofile 22000 – Judy May 29 '19 at 18:00
I don't think 90 connections is unreasonable. If you had several thousand, I'd be more concerned. Also if you look at your connections, there are no idle connections several days old. So the `hive.server2.idle.session.timeout` looks like it's working. – tk421 May 29 '19 at 19:44
so do you recommended to add the follwing parameters to purge the connection - hive.server2.session.check.interval=6h hive.server2.idle.operation.timeout=5d hive.server2.idle.session.check.operation=true hive.server2.idle.session.timeout=7d – Judy May 29 '19 at 20:24
this parameters are from the post - https://community.hortonworks.com/articles/202485/hiveserver2-configurations-deep-dive.html – Judy May 29 '19 at 20:24
I don't think you have to change anything other than raise your open file descriptor limit. – tk421 May 29 '19 at 21:28
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/194136/discussion-between-judy-and-tk421). – Judy May 29 '19 at 21:33
do you mean to increase the - fs.file-max = 500000 to higher value ? ( in /etc/sysctl.conf ) – Judy May 29 '19 at 21:37
or to set higher value for hive.conf as hive - nofile 100000 – Judy May 29 '19 at 21:40
You have to raise the system limit since changing the config without raising the system limit will generate exceptions. – tk421 May 29 '19 at 21:46
so I need to raise both with the same value ? – Judy May 29 '19 at 21:49
if yes can you explain please how increasing both values affected on the hive connection to postgres? – Judy May 29 '19 at 21:54
Sorry if I was not clear. I don't think your 90 connections is a problem. My recommendation is that most production clusters raise their open file descriptors limits to 100K-200K because most Hadoop/Hive components open a LOT of files. – tk421 May 29 '19 at 22:06
I see other solution from other post about to increase postgres connection - what you think about - https://stackoverflow.com/questions/30778015/how-to-increase-the-max-connections-in-postgres ??? – Judy May 29 '19 at 22:23

hive + too many connection to PostgreSQL DB

1 Answers1