In this article we will take a look at some common frequently asked performance related question.
1. My SQL query is running very slow, it was running fine yesterday what should I do?
A. Check that your connection to the Greenplum cluster is still good if you are using a remote client. You can do this by running the SQL locally to the GP cluster.
B. Check that the system tables and user tables involved are not bloated or skewed. Read jetpack or Greenplum toolkit documentation about how to do this.
C. Check with your DBA that the Greenplum interconnect is still performing correctly.
This can be done by checking for dropped packets on the interconnect "netstat -i" and by running gpcheckperf. It is also possible that a segment is experiencing hardware problems, which can be found in the output of dmesg or in /var/log/messages* (Linux) and /var/adm/messages* (Solaris).
2. How to turn on timing, and checking how much time a query takes to execute?
A. You can turn in timing per session before you run your SQL with the \timing command.
B. You can run explain analyze against your SQL statement to get the timing.
3. How to trace child processes on segment server?
When session start in master and segments, all the child processes in segments will be identified with master session_id connection string (con+sess_id).
gpdb=# select * from pg_Stat_activity; datid " datname " procpid " sess_id ".. .. -------+---------+---------+---------+ 16986 " gpdb " 18162 " 76134 " .. ..
In all segments child processes for session 76134:
[gpadmin@stinger2]/export/home/gpadmin/gp40>gpssh -f host_file /usr/ucb/ps -auxww "grep con76134 [stinger2] gpadmin 18162 1.7 6.0386000124480 ? S 09:57:55 0:04 postgres: port 4000, gpadmin gpdb [local] con76134 [local] cmd3 CREATE DATABASE....................................... [stinger2] gpadmin 18625 0.3 2.726056455932 ? S 10:01:56 0:01 postgres: port 40000, gpadmin gpdb 10.5.202.12(18864) con76134 seg0 cmd4 MPPEXEC UTILITY............................... [stinger2] gpadmin 18669 0.0 0.1 3624 752 pts/2 S 10:02:36 0:00 grep con76134 [stinger3] gpadmin 22289 0.8 9.4531860196404 ? S 09:36:20 0:05 postgres: port 40000, gpadmin gpdb 10.5.202.12(18866) con76134 seg1 cmd4 MPPEXEC UTILITY...............................
4. How to check if my session queries are running or waiting on locks?
Check "waiting" column in pg_stat_activity and "granted" column in pg_locks for any object level locks or refer to database locks queries mentioned here.
5. What kind of locks should we focus on MPP system when system is slow /hung?
Locks that are held for a very long time and multiple other queries are waiting for that lock also.
6. How do I monitor user activity history in Greenplum database ?
Use Greenplum Command Center (GPCC), which has GUI to monitor and query performance history.
7. What is Greenplum Command Center and how to install ?
Its a monitoring tool that collects statistics on system and query performance and builds historical data.
Please refer to greenplum command center documentation here for steps on how to install the same.
8. What are orphan process ?
Orphan process are postgres/greenplum process that exists on some segments and there is no information about its gang members (or child process as explained on question 3 above) on any other segments.
9. Will these orphan process cause performance issue ?
Yes they can if the process are holding locks on a relation on that segments and subsequent queries are waiting for this lock to be released.
10. Is there a way to detect if orphan process is running on my database ?
Refer to the guide for some command to detect the orphan process.
11. What are the "Out of Memory / VM Protect Limit" errors ?
Please refer to the guide and sub links mentioned in the document for more details on the same.
12. How to do i understand which process / query is consuming most of the segment memory ?
Execute mem_watcher script available in"Pivotal Support Package" to track the memory consumption of the queries on the segments.
Review the output once an OOM is observed to identify possible queries or processes.
13. How do i determine if my table in a query needs a analyze ?
Check the "gp_stat_check" utility indicated on the article for more information.
14. How to determine the disk / network speed between servers ?
15. What are the different Query tunning parameters in GPDB ?
Please do check the link for the different parameter that are involved in changing/altering the behaviour of the optimizer.
16. What is a table bloat and what are the different options available to remove table bloat?
For more detailed information please refer to the Greenplum Administrator's Guide.