Quick GPDB Questions

[A Naveen Marshal]

1. Is there way to list out tables which are not frequently used. (hard way is to figure out from physical files last modified date).

2. Is there way to get a memory utilized by a query, during run time ( means any catalog table to fetch for value) i don't want to look for explain analyze.

Thanks,

NM

[Luis Macedo]

1- no unless you parse the query log files/tables.

2- there are views that monitor spill files which will be more important than monitoring memory which will be limited by statement men.

--- Sent from my Google Pixel

--
You received this message because you are subscribed to the Google Groups "Greenplum Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to gpdb-users+unsubscribe@greenplum.org.
To post to this group, send email to gpdb-...@greenplum.org.
Visit this group at https://groups.google.com/a/greenplum.org/group/gpdb-users/.
For more options, visit https://groups.google.com/a/greenplum.org/d/optout.

[Jon Roberts]

Additionally, the data disks should be mounted with "noatime" which prevents the operating system from updating the time on files when read.  This improves I/O performance.  So you won't be able to look at the dates of the files to determine if the tables are in use or not.

http://gpdb.docs.pivotal.io/510/install_guide/prep_os_install_gpdb.html#topic3

Jon Roberts

[akha...@pivotal.io]

Hi NM,

1. Is there way to list out tables which are not frequently used. (hard way is to figure out from physical files last modified date).

I don't know the definite answer, but here is some info I found which may be useful:

https://dba.stackexchange.com/questions/58214/getting-last-modification-date-of-a-postgresql-database-table

https://stackoverflow.com/questions/899203/how-do-i-find-the-last-time-that-a-postgresql-database-has-been-updated

2. Is there way to get a memory utilized by a query, during run time ( means any catalog table to fetch for value) i don't want to look for explain analyze.

Have you seen this?

Viewing Session Memory Usage Information

You can create and use the session_level_memory_consumption view that provides information about the current memory utilization for sessions that are running queries on Greenplum Database. The view contains session information and information such as the database that the session is connected to, the query that the session is currently running, and memory consumed by the session processes.

http://gpdb.docs.pivotal.io/43110/admin_guide/managing/monitor.html#topic_slt_ddv_1q

Also, not sure of your use case, but have you looked into the below GPDB features to manage this?

Resource queues (old) and resource groups (new):

https://gpdb.docs.pivotal.io/latest/admin_guide/workload_mgmt_resgroups.html

https://gpdb.docs.pivotal.io/latest/admin_guide/workload_mgmt.html

Vmem protector:

https://discuss.pivotal.io/hc/en-us/articles/201947018-Pivotal-Greenplum-GPDB-Memory-Configuration

Amil

[Heikki Linnakangas]

On 11/18/2017 03:56 AM, akha...@pivotal.io wrote:
> Hi NM,
>
> 1. Is there way to list out tables which are not frequently used. (hard way
>> is to figure out from physical files last modified date).
>
> I don't know the definite answer, but here is some info I found which may
> be useful:
> https://dba.stackexchange.com/questions/58214/getting-last-modification-date-of-a-postgresql-database-table
> https://stackoverflow.com/questions/899203/how-do-i-find-the-last-time-that-a-postgresql-database-has-been-updated

On PostgreSQL you can indeed use the pg_stat_user_tables view, and look
at the number of heap and index scans. Unfortunately, the view doesn't
work on GPDB as you might expect: it shows the stats on the master node,
which are always 0, because all the data is stored on the segments. So
you'll need to somehow obtain those numbers from the segments, instead.
One option is to connect to each segment in "utility mode", and run
"select * from pg_user_tables" there.

- Heikki

[Luis Macedo]

Heikki,

That is a common request from users, should we think about adding this feature to GPDB on the data dictionary on the master? How hard would it be to implement the collection of this stats from segments into the master?

Rgds,

Luis Macedo | Sr Platform Architect | Pivotal Inc 

Call Me @ +55 11 97616-6438

Take care of the customers and the rest takes care of itself

---

Pivotal POV Blog

Pivotal Mission: Help Developers Do What They Love

Big Data Problems? Go here!

Latency Issues? No more!

---

[Heikki Linnakangas]

On 11/20/2017 06:08 PM, Luis Macedo wrote:
> Heikki,
>
> That is a common request from users, should we think about adding this
> feature to GPDB on the data dictionary on the master?

Yeah, IMHO we really should. The pg_stat views are pretty useless as
they are now.

> How hard would it be to implement the collection of this stats from
> segments into the master?

Shouldn't be hard. With the new EXECUTE ON SEGMENTS/MASTER syntax in
CREATE FUNCTION, in GPDB6, it might be a one-line change, to mark the
functions backing the pg_stat views as EXECUTE ON SEGMENTS.

- Heikki

[Luis Macedo]

Nice! 

BTW did I mention that you guys on engineering are rocking! :)

Luis Macedo | Sr Platform Architect | Pivotal Inc 

Call Me @ +55 11 97616-6438

Take care of the customers and the rest takes care of itself

---

Pivotal POV Blog

Pivotal Mission: Help Developers Do What They Love

Big Data Problems? Go here!

Latency Issues? No more!

---

[A Naveen Marshal]

Hi All,

Thanks for suggestions. 

Thanks to ENG Team.

Regards,

NM