search

sipcapture / homer-ui

HOMER Front-End Application (UI)
http://sipcapture.org
GNU Affero General Public License v3.0
64 stars 62 forks source link

Help with some PSQL queries to try to scope the size/scale of the DB #633

Closed jagnetta closed 6 months ago

jagnetta commented 6 months ago

Good morning,

I hope you're all well. We're getting ready to deploy homer to production, yay! We're very excited. We're trying to scope the size of the disk to host the DB. I'm being told we'd like to store 3 months of call diagnostic data for each site:

  • LOC1 - 305,000 calls in 2 weeks (peak 400 simultaneous)
  • LOC2 - 200,000 calls in 2 weeks (peak 70 simultaneous)

I'm not sure if that alone can answer the question. If not then I'm also wondering about some psql queries to find out:

  1. How many calls are currently in the DB?
  2. What is the date of the oldest call in the DB?
  3. Is there a way to get a count by day or hour?
  4. OPTIONS and NOTIFIES do end up in the DB if they're not otherwise dropped, right?

Thank you again for your support.

lmangani commented 6 months ago

Hey @jagnetta

We're getting ready to deploy homer to production, yay! We're very excited.

We're glad to hear that and we hope this will be a two way street with our project if its used in production 😉

How many calls are currently in the DB?

First clarification: there are no "calls" in HOMER - just SIP messages and other datatypes HOMER assiciates with calls, registrations or out-of-band SIP sessions. Any estimation should take this into account as different types can be rotated faster than others. I doubt anyone would need months and billions of registrations records taking up space one could use for call sessions.

When it comes to data, heplify-server can rotate its own based on its parameters and essentially works by dropping anything older than x - or you can disable that and implement your own rotation/drop logic directly through database queries if that's preferred by mimicking the rotation code. Note this changes greatly in H10 where heplify-server is only a data producer.

What is the date of the oldest call in the DB?

To establish the older record in any give table you would have to do something like SELECT * FROM xxx.hep_proto_1_call ORDER BY create_date ASC LIMIT 1 (fictional) but the other smart guys here might have smarter queries.

Is there a way to get a count by day or hour?

Yes. The stack and heplify-server instances expose a /metric endpoint which can be scraped with all the statistics. I would suggest familiarizing with our example dashboards which already include a good variety of useful sets. For this to work you either need a prometheus service, or our qryn stack which can be used stand-alone or as part of H10 preview.

OPTIONS and NOTIFIES do end up in the DB if they're not otherwise dropped, right?

Nothing gets dropped implicitly - the messages are distributed by HEP type (1,5,100,etc) and SIP Session type (call, registration, other)

Hope this helps,

Lorenzo

jagnetta commented 6 months ago

Hi @lmangani, thanks for taking the time to answer. As usual, my seemingly simple questions open up further questions.

  1. "Two-way street?" I'm not sure I understand...as in "contributions to the repo?" For that matter, what does it mean to "sponsor a feature request" as in my last ticket?
  2. Where is this rotation code? Is that heplify-server.toml?
  3. Where is this "scraping of /metric?" Is this the API? Are you talking about Homer/Prometheus/Grafana? I'm anxious to get into that.

Thanks for your time. Jim

lmangani commented 6 months ago

We're an open source project and just as for any other project, for-profits counting on our software and support in production are always welcome to contribute and sponsor the development and maintenance. The other questions can be answered by looking into the codebase and by running our default demo examples which include statistics and configurations involved.