Troubleshooting PXF

A newer version of this documentation is available. Click here to view the most up-to-date release of the Greenplum 5.x documentation.

PXF Errors

The following table describes some errors you may encounter while using PXF:

Error Message Discussion
Protocol “pxf” does not exist Cause: The pxf extension was not registered.
Solution: Create (enable) the PXF extension for the database as described in the PXF Enable Procedure.
Invalid URI pxf://<path-to-data>: missing options section Cause: The LOCATION URI does not include the profile or other required options.
Solution: Provide the profile and required options in the URI.
org.apache.hadoop.mapred.InvalidInputException: Input path does not exist: hdfs://<namenode>:8020/<path-to-file> Cause: The HDFS file that you specified in <path-to-file> does not exist.
Solution: Provide the path to an existing HDFS file.
NoSuchObjectException(message:<schema>.<hivetable> table not found) Cause: The Hive table that you specified with <schema>.<hivetable> does not exist.
Solution: Provide the name of an existing Hive table.
Failed to connect to <segment-host> port 5888: Connection refused (libchurl.c:944) (<segment-id> slice<N> <segment-host>:40000 pid=<process-id>)
Cause: PXF is not running on <segment-host>.
Solution: Restart PXF on <segment-host>.
ERROR: failed to acquire resources on one or more segments
DETAIL: could not connect to server: Connection refused
    Is the server running on host “<segment-host>” and accepting
    TCP/IP connections on port 40000?(seg<N> <segment-host>:40000)
Cause: The Greenplum Database segment host <segment-host> is down.
org.apache.hadoop.security.AccessControlException: Permission denied: user=, access=READ, inode=“”:::-rw——- Cause: The Greenplum Database user that executed the PXF operation does not have permission to access the underlying Hadoop service (HDFS or Hive). See Configuring User Impersonation and Proxying.

PXF Logging

Enabling more verbose logging may aid PXF troubleshooting efforts. PXF provides two categories of message logging: service-level and client-level.

Service-Level Logging

PXF utilizes log4j for service-level logging. PXF-service-related log messages are captured in a log file specified by PXF’s log4j properties file, $PXF_CONF/conf/pxf-log4j.properties. The default PXF logging configuration will write INFO and more severe level logs to $PXF_CONF/logs/pxf-service.log. You can configure the logging level and the log file location.

PXF provides more detailed logging when the DEBUG level is enabled. To configure PXF DEBUG logging and examine the output:

  1. Log in to your Greenplum Database master node:

    $ ssh gpadmin@<gpmaster>
    
  2. Open $PXF_CONF/conf/pxf-log4j.properties in an editor, uncomment the following line, save the file, and exit the editor:

    #log4j.logger.org.greenplum.pxf=DEBUG
    
  3. Use the pxf cluster sync command to copy the updated pxf-log4j.properties file to each segment host. For example:

    gpadmin@gpmaster$ $GPHOME/pxf/bin/pxf cluster sync
    
  4. Restart PXF on each Greenplum Database segment host as described in Restarting PXF.

  5. With DEBUG level logging now enabled, you can perform your PXF operations. Be sure to make note of the time; this will direct you to the relevant log messages in $PXF_CONF/logs/pxf-service.log.

    $ date
    Wed Oct  4 09:30:06 MDT 2017
    $ psql -d <dbname>
    
  6. Create and query an external table. For example:

    dbname=> CREATE EXTERNAL TABLE hdfstest(id int, newid int)
        LOCATION ('pxf://data/dir/hdfsfile?PROFILE=hdfs:text')
        FORMAT 'TEXT' (delimiter='E',');
    dbname=> SELECT * FROM hdfstest;
    <select output>
    
  7. Finally, examine/collect the log messages from pxf-service.log.

Note: DEBUG logging is quite verbose and has a performance impact. Remember to turn off PXF service DEBUG logging after you have collected the desired information.

Client-Level Logging

Database-level client logging may provide insight into internal PXF service operations.

Enable Greenplum Database and PXF debug message logging during operations on PXF external tables by setting the client_min_messages server configuration parameter to DEBUG2 in your psql session.

$ psql -d <dbname>
dbname=# SET client_min_messages=DEBUG2;
dbname=# SELECT * FROM hdfstest;
...
DEBUG2:  churl http header: cell #19: X-GP-URL-HOST: seghost1  (seg0 slice1 127.0.0.1:40000 pid=3981)
CONTEXT:  External table hdfstest
DEBUG2:  churl http header: cell #20: X-GP-URL-PORT: 5888  (seg0 slice1 127.0.0.1:40000 pid=3981)
CONTEXT:  External table hdfstest
DEBUG2:  churl http header: cell #21: X-GP-DATA-DIR: data/dir/hdfsfile  (seg0 slice1 127.0.0.1:40000 pid=3981)
CONTEXT:  External table hdfstest
DEBUG2:  churl http header: cell #22: X-GP-OPTIONS-PROFILE: hdfs:text  (seg0 slice1 127.0.0.1:40000 pid=3981)
CONTEXT:  External table hdfstest
...

Examine/collect the log messages from stdout.

Note: DEBUG2 database session logging has a performance impact. Remember to turn off DEBUG2 logging after you have collected the desired information.

dbname=# SET client_min_messages=NOTICE;

Addressing PXF Memory Issues

Because a single PXF agent (JVM) serves multiple segments on a segment host, the PXF heap size can be a limiting runtime factor. This will be more evident under concurrent workloads and/or queries against large files. You may run into situations where a query will hang or fail due to insufficient memory or the Java garbage collector impacting response times. To avert or remedy these situations, first try increasing the Java maximum heap size or decreasing the Tomcat maximum number of threads, depending upon what works best for your system configuration.

Note: The configuration changes described in this topic require modifying config files on each segment node in your Greenplum Database cluster. After you perform the updates, be sure to synchronize the PXF configuration to each segment host in the cluster.

You will need to re-apply these configuration changes after any PXF version upgrades.

Increasing the JVM Memory for PXF

Each PXF agent running on a segment host is configured with a default maximum Java heap size of 2GB and an initial heap size of 1GB. If the segment hosts in your Greenplum Database cluster have an ample amount of memory, try increasing the maximum heap size to a value between 3-4GB. Set the initial and maximum heap size to the same value if possible.

Perform the following procedure to increase the heap size for the PXF agent running on each segment host in your Greenplum Database cluster.

  1. Log in to your Greenplum Database master node:

    $ ssh gpadmin@<gpmaster>
    
  2. Recall the location of the PXF user configuration directory ($PXF_CONF). Edit the $PXF_CONF/conf/pxf-env.sh file. For example:

    gpadmin@gpmaster$ vi $PXF_CONF/conf/pxf-env.sh
    
  3. Locate the PXF_JVM_OPTS setting in the pxf-env.sh file, and update the -Xmx and/or -Xms options to the desired value. For example:

    PXF_JVM_OPTS="-Xmx3g -Xms3g"
    
  4. Use the pxf cluster sync command to copy the updated pxf-env.sh file to each Greenplum Database segment host. For example:

    gpadmin@gpmaster$ $GPHOME/pxf/bin/pxf cluster sync
    
  5. Restart PXF on each Greenplum Database segment host as described in Restarting PXF.

Another Option for Resource-Constrained PXF Segment Hosts

If increasing the maximum heap size is not suitable for your Greenplum Database deployment, try decreasing the number of concurrent working threads configured for PXF’s underlying Tomcat web application. A decrease in the number of running threads will prevent any PXF node from exhausting its memory, while ensuring that current queries run to completion (albeit a bit slower). Tomcat’s default behavior is to queue requests until a thread is free, or the queue is exhausted.

The Tomcat default maximum number of threads is 300. Decrease the maximum number of threads to a value that works optimally for your Greenplum Database deployment. (If you plan to run large workloads on a large number of files in an external Hive data store, specify an even lower value.)

Perform the following procedure to decrease the maximum number of Tomcat threads for the PXF agent running on each segment host in your Greenplum Database deployment.

  1. Log in to your Greenplum Database master node:

    $ ssh gpadmin@<gpmaster>
    
  2. Edit the $GPHOME/pxf/pxf-service/conf/server.xml file. For example:

    gpadmin@gpmaster$ vi $GPHOME/pxf/pxf-service/conf/server.xml
    
  3. Locate the Catalina Executor block and update the maxThreads setting to the desired value. For example:

    <Executor maxThreads="100"
              minSpareThreads="50"
              name="tomcatThreadPool"
              namePrefix="tomcat-http--"/>
    
  4. Copy the updated server.xml file to each Greenplum Database segment host. For example, if seghostfile contains a list, one-host-per-line, of the segment hosts in your Greenplum Database cluster:

    gpadmin@gpmaster$ gpscp -v -f seghostfile $GPHOME/pxf/pxf-service/conf/server.xml =:/usr/local/greenplum-db/pxf/pxf-service/conf/server.xml
    
  5. Restart PXF on each Greenplum Database segment host as described in Restarting PXF.

Addressing PXF JDBC Connector Time Zone Errors

You use the PXF JDBC connector to access data stored in an external SQL database. Depending upon the JDBC driver, the driver may return an error if there is a mismatch between the default time zone set for the PXF server and the time zone set for the external SQL database.

For example, if you use the PXF JDBC connector to access an Oracle database with a conflicting time zone, PXF logs an error similar to the following:

SEVERE: Servlet.service() for servlet [PXF REST Service] in context with path [/pxf] threw exception
java.io.IOException: ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found

Should you encounter this error, you can set default time zone option(s) for the PXF server in the $PXF_CONF/conf/pxf-env.sh configuration file, PXF_JVM_OPTS property setting. For example, to set the time zone:

export PXF_JVM_OPTS="<current_settings> -Duser.timezone=America/Chicago"

You can use the PXF_JVM_OPTS property to set other Java options as well.

As described in previous sections, you must synchronize the updated PXF configuration to each Greenplum Database segment host and restart the PXF server on each host.