AE-2115: Install TimeScale DB and populating APV data
Review Request #1038 — Created Aug. 29, 2025 and submitted — Latest diff uploaded
| Information | |
|---|---|
| apoorva.sn | |
| AMP | |
| amp_4_0 | |
| AE-2107 | |
| Reviewers | |
| ngurunathan, pmurugaiyan, pradeep, shuinvy | |
Problem
adding scripts to install Timescale DB and create Hypertables for populate the DB with APV data.
Solution
Since TimeScaleDB has a gentler learning curve, provides a clear migration path, and may suit our needs without requiring a hardware refresh.
Going with moving to TimeScaleDB for Time Series DB.Have come up with the script for TimeScaleDB installation and also .sql file to create hypertables for the data to get inserted successfully
Data is successfully getting inserted in all tables
amp_ts=> select * from apv_virtual_stats limit 10;
time | agent_host | serverid | addr | port | protocol | url_hits | hostname_hits | perstnt_cookie_hits | qos_cookie_hits | default_hits | per
stnt_url_hits | static_hits | qos_network_hits | qos_url_hits | backup_hits | cache_hits | regex_hits | rcookie_hits | icookie_hits | conn_cnt | qos_client_port_hits | qos_body_hits |
header_hits | hash_url_hits | redirect_hits | conn_per_sec | in_byte_per_sec | out_byte_per_sec | in_packet_per_sec | out_packet_per_sec | health_status | host
---------------------------------+-----------------+----------+----------------+------+----------+----------+---------------+---------------------+-----------------+--------------+----
--------------+-------------+------------------+--------------+-------------+------------+------------+--------------+--------------+----------+----------------------+---------------+-
------------+---------------+---------------+--------------+-----------------+------------------+-------------------+--------------------+---------------+-----------------------
2025-08-26 00:27:45.64191+05:30 | 192.168.162.166 | vs1 | 192.168.162.92 | 443 | 5 | | | | | |
| | | | | | | | | | | |
| | | | | | | | |
2025-08-29 17:25:20+05:30 | 192.168.162.166 | vs1 | 192.168.162.92 | 443 | 5 | 0 | 0 | 0 | 0 | 0 |
0 | 30 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | localhost.localdomain
2025-08-29 17:25:30+05:30 | 192.168.162.166 | vs1 | 192.168.162.92 | 443 | 5 | 0 | 0 | 0 | 0 | 0 |
0 | 30 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | localhost.localdomain
2025-08-29 17:25:40+05:30 | 192.168.162.166 | vs1 | 192.168.162.92 | 443 | 5 | 0 | 0 | 0 | 0 | 0 |
0 | 30 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | localhost.localdomain
2025-08-29 17:25:50+05:30 | 192.168.162.166 | vs1 | 192.168.162.92 | 443 | 5 | 0 | 0 | 0 | 0 | 0 |
0 | 30 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | localhost.localdomain
2025-08-29 17:26:00+05:30 | 192.168.162.166 | vs1 | 192.168.162.92 | 443 | 5 | 0 | 0 | 0 | 0 | 0 |
0 | 30 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | localhost.localdomain
2025-08-29 17:26:10+05:30 | 192.168.162.166 | vs1 | 192.168.162.92 | 443 | 5 | 0 | 0 | 0 | 0 | 0 |
0 | 30 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | localhost.localdomain
2025-08-29 17:26:20+05:30 | 192.168.162.166 | vs1 | 192.168.162.92 | 443 | 5 | 0 | 0 | 0 | 0 | 0 |
0 | 30 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | localhost.localdomain
2025-08-29 17:26:30+05:30 | 192.168.162.166 | vs1 | 192.168.162.92 | 443 | 5 | 0 | 0 | 0 | 0 | 0 |
0 | 30 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | localhost.localdomain
2025-08-29 17:26:40+05:30 | 192.168.162.166 | vs1 | 192.168.162.92 | 443 | 5 | 0 | 0 | 0 | 0 | 0 |
0 | 30 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | localhost.localdomain
(10 rows)amp_ts=> select * from apv_llb_stats limit 10;
time | agent_host | link_index | link_name | link_gateway | link_status | link_resp_time | link_up_time | link_down_time | link_down_count | link_down_event
| link_bandwid_in | link_bandwid_out | link_thresh | link_hits | link_conn | link_usage | host
---------------------------+-----------------+------------+-----------+---------------+-------------+----------------+--------------+----------------+-----------------+----------------
-+-----------------+------------------+-------------+-----------+-----------+------------+-----------------------
2025-08-29 17:25:20+05:30 | 192.168.162.166 | 1 | VLAN-162 | 192.168.162.1 | Up | 0.679ms | 10+19:05:54 | 00:00:00 | 0 | Link is Up
| | | | | | | localhost.localdomain
2025-08-29 17:25:20+05:30 | 192.168.162.166 | 2 | VLAN-163 | 192.168.163.1 | Up | 0.705ms | 10+18:19:17 | 00:00:00 | 0 | Link is Up
| | | | | | | localhost.localdomain
2025-08-29 17:25:30+05:30 | 192.168.162.166 | 1 | VLAN-162 | 192.168.162.1 | Up | 0.679ms | 10+19:06:04 | 00:00:00 | 0 | Link is Up
| | | | | | | localhost.localdomain
2025-08-29 17:25:30+05:30 | 192.168.162.166 | 2 | VLAN-163 | 192.168.163.1 | Up | 0.705ms | 10+18:19:27 | 00:00:00 | 0 | Link is Up
| | | | | | | localhost.localdomain
2025-08-29 17:25:40+05:30 | 192.168.162.166 | 1 | VLAN-162 | 192.168.162.1 | Up | 0.679ms | 10+19:06:14 | 00:00:00 | 0 | Link is Up
| | | | | | | localhost.localdomain
2025-08-29 17:25:40+05:30 | 192.168.162.166 | 2 | VLAN-163 | 192.168.163.1 | Up | 0.705ms | 10+18:19:37 | 00:00:00 | 0 | Link is Up
| | | | | | | localhost.localdomain
2025-08-29 17:25:50+05:30 | 192.168.162.166 | 1 | VLAN-162 | 192.168.162.1 | Up | 0.679ms | 10+19:06:24 | 00:00:00 | 0 | Link is Up
| | | | | | | localhost.localdomain
2025-08-29 17:25:50+05:30 | 192.168.162.166 | 2 | VLAN-163 | 192.168.163.1 | Up | 0.711ms | 10+18:19:47 | 00:00:00 | 0 | Link is Up
| | | | | | | localhost.localdomain
2025-08-29 17:26:00+05:30 | 192.168.162.166 | 1 | VLAN-162 | 192.168.162.1 | Up | 0.689ms | 10+19:06:34 | 00:00:00 | 0 | Link is Up
| | | | | | | localhost.localdomain
2025-08-29 17:26:00+05:30 | 192.168.162.166 | 2 | VLAN-163 | 192.168.163.1 | Up | 0.718ms | 10+18:19:57 | 00:00:00 | 0 | Link is Up
| | | | | | | localhost.localdomain
(10 rows)amp_ts=>
amp_ts=>
amp_ts=> select * from an_device_storage limit 10;
time | agent_host | prefix | size | used | alloc_unit | host
---------------------------+-----------------+-----------------+---------+---------+------------+-----------------------
2025-08-29 17:25:20+05:30 | 192.168.162.166 | /run/user/0 | 96988 | 0 | 4096 | localhost.localdomain
2025-08-29 17:25:20+05:30 | 192.168.162.166 | /run | 484937 | 3257 | 4096 | localhost.localdomain
2025-08-29 17:25:20+05:30 | 192.168.162.166 | /var | 5710363 | 852873 | 4096 | localhost.localdomain
2025-08-29 17:25:20+05:30 | 192.168.162.166 | /boot | 194235 | 80519 | 1024 | localhost.localdomain
2025-08-29 17:25:20+05:30 | 192.168.162.166 | Physical memory | 3879500 | 3701520 | 1024 | localhost.localdomain
2025-08-29 17:25:20+05:30 | 192.168.162.166 | Memory buffers | 3879500 | 68 | 1024 | localhost.localdomain
2025-08-29 17:25:20+05:30 | 192.168.162.166 | Swap space | 2097148 | 317924 | 1024 | localhost.localdomain
2025-08-29 17:25:20+05:30 | 192.168.162.166 | /dev/shm | 484937 | 1 | 4096 | localhost.localdomain
2025-08-29 17:25:20+05:30 | 192.168.162.166 | Shared memory | 484304 | 484304 | 1024 | localhost.localdomain
2025-08-29 17:25:20+05:30 | 192.168.162.166 | /sys/fs/cgroup | 484937 | 0 | 4096 | localhost.localdomain
(10 rows)amp_ts=> select * from an_device_metrics limit 10;
time | agent_host | cpu_usage | mem_usage | net_mem_usage | total_openssl_conns | connections | requests | total_in | total_out | host
---------------------------+-----------------+-----------+-----------+---------------+---------------------+-------------+----------+-------------+-------------+-----------------------
2025-08-29 17:25:20+05:30 | 192.168.162.166 | 9 | 57 | 3 | 0 | 0 | 0 | 27518994722 | 22368147330 | localhost.localdomain
2025-08-29 17:25:30+05:30 | 192.168.162.166 | 12 | 57 | 3 | 0 | 0 | 0 | 27519054465 | 22368233917 | localhost.localdomain
2025-08-29 17:25:40+05:30 | 192.168.162.166 | 11 | 57 | 3 | 0 | 0 | 0 | 27519102079 | 22368320329 | localhost.localdomain
2025-08-29 17:25:50+05:30 | 192.168.162.166 | 10 | 57 | 3 | 0 | 0 | 0 | 27519151292 | 22368414634 | localhost.localdomain
2025-08-29 17:26:00+05:30 | 192.168.162.166 | 11 | 57 | 3 | 0 | 3 | 0 | 27519251537 | 22368529182 | localhost.localdomain
2025-08-29 17:26:10+05:30 | 192.168.162.166 | 11 | 57 | 3 | 0 | 0 | 0 | 27519313087 | 22368618256 | localhost.localdomain
2025-08-29 17:26:20+05:30 | 192.168.162.166 | 10 | 57 | 3 | 0 | 0 | 0 | 27519375302 | 22368704540 | localhost.localdomain
2025-08-29 17:26:30+05:30 | 192.168.162.166 | 12 | 57 | 3 | 0 | 0 | 0 | 27519438610 | 22368790897 | localhost.localdomain
2025-08-29 17:26:40+05:30 | 192.168.162.166 | 10 | 57 | 3 | 0 | 0 | 0 | 27519489012 | 22368877054 | localhost.localdomain
2025-08-29 17:26:50+05:30 | 192.168.162.166 | 9 | 57 | 3 | 0 | 0 | 0 | 27519552185 | 22368983295 | localhost.localdomain
(10 rows)