Which One of these Nodes Doesn’t Belong?

[Deleted User] · June 2018

Jim Knicely wrote this tip.

We strongly recommend that you use a homogeneous hardware configuration for your Vertica cluster: Each node of the cluster should be similar in CPU, clock speed, number of cores, memory, and operating system version. We learned in the last Vertica Quick Tip that the V_CATALOG.HOST_RESOURCES system table provides a snapshot of the nodes in a Vertica cluster. We can use the information in that table to find “odd ball” node(s) in our cluster that are different from the majority of nodes!

Example:

dbadmin=> SELECT host_name, processor_count, processor_core_count, processor_description, ROUND(total_memory_bytes / 1024^3, 2) total_memory_gbytes FROM V_MONITOR.HOST_RESOURCES;
   host_name   | processor_count | processor_core_count |          processor_description           | total_memory_gbytes
---------------+-----------------+----------------------+------------------------------------------+---------------------
 192.168.2.200 |               1 |                    1 | Intel(R) Core(TM) i5-4300U CPU @ 1.90GHz |                2.78
 192.168.2.201 |               1 |                    1 | Intel(R) Core(TM) i5-4300U CPU @ 1.90GHz |                2.78
 192.168.2.202 |               1 |                    2 | Intel(R) Core(TM) i5-4300U CPU @ 1.90GHz |                2.78
(3 rows)

dbadmin=> SELECT a.host_name, a.processor_count, a.processor_core_count, a.processor_description, ROUND(a.total_memory_bytes / 1024^3, 2) total_memory_gbytes
dbadmin->   FROM V_MONITOR.HOST_RESOURCES a
dbadmin->   JOIN (SELECT processor_count, processor_core_count, processor_description, total_memory_bytes
dbadmin(>   FROM V_MONITOR.HOST_RESOURCES
dbadmin(>          MINUS
dbadmin(>         SELECT processor_count, processor_core_count, processor_description, total_memory_bytes
dbadmin(>           FROM V_MONITOR.HOST_RESOURCES
dbadmin(>          GROUP BY 1, 2, 3, 4
dbadmin(>         HAVING COUNT(*) > 1) b
dbadmin->          USING (processor_count, processor_core_count, processor_description, total_memory_bytes);
   host_name   | processor_count | processor_core_count |          processor_description           | total_memory_gbytes
---------------+-----------------+----------------------+------------------------------------------+---------------------
 192.168.2.202 |               1 |                    2 | Intel(R) Core(TM) i5-4300U CPU @ 1.90GHz |                2.78
(1 row)

Have Fun!