Observing a common error in Freeradius with Postgres but not justification why it happens
Hello all, We have implemented freeradius 3.2.3 with Postgresql 12.15 to store accounting packets i.e start, interim-update, stop for reporting purposes. We customized the queries files to store fewer columns than the original schema since this is what we need. The following columns are customized in the accounting queries and same columns in postgres radacct table: radacctid, acctsessionid, acctuniqueid, acctstarttime, acctudpatetime, acctstoptime, callingstationid, framedipaddress, imei, 3gpplocation. Also the following indexes are in radacct table: 1. radacct_active_session_idx (this is the original from FR schema of postgresql) 2. framedipaddress, starttime, stoptime, updatetime (This one we added to use upon need in our read queries on report generation) Currently, from postgres dashboard we observe around 300-400 write/update transactions. The issue we noticed a couple of days ago is there were a lot of messages in radius log stating "Error: xxx requests have been waiting in the processing queue for x seconds. Check that all databases are running properly!" And sometimes we see another warning stating "Warning: Please check the configuration file. The value for 'max_requests' is probably set too low. " Based on the above observed messages, we tuned two matters: 1. the postgresql db by changing the default shared_buffer from 128MB to 8GB since we have plenty of RAM in the server, and this clearly reflected in the performance and removed around 98% of the above error messages. However we sometimes see the same error message showing up in peak hours and KPI of radius is impacted per the NAS report. After tuning the Postgres, we used pgbench and the following is what we get: [postgres@postgresql-syslog01 ~]$ pgbench -c 10 -j 2 -t 1000 my_benchmark_test_db starting vacuum...end. transaction type: <builtin: TPC-B (sort of)> scaling factor: 1 query mode: simple number of clients: 10 number of threads: 2 number of transactions per client: 1000 number of transactions actually processed: 10000/10000 latency average = 10.931 ms tps = 914.869517 (including connections establishing) tps = 915.175572 (excluding connections establishing) 2. In radiusd.conf we have the following, and we tuned max request to 32000, start_servers = 5 max_servers = 32 min_spare_servers = 3 max_spare_servers = 10 max_requests_per_server = 0 cleanup_delay = 5 max_request_time = 30 max_requests = 32000 Radius server specs: We have 4 sockets 1.87Ghz and RAM 8GB In Linux top command, load average is almost always : 0.17, 0.14, 0.13 Postgres server specs: 8 sockets 2.5Ghz and RAM 16GB In Linux top command, load average is around most of the time : 0.59, 0.50, 0.45 The questions to summarize are : 1. Is ~900 TPS and 10ms acceptable DB performance for accounting? 2. In the max_requests setting, what does the clients mean here, does it mean NAS client or SQL connections based on max requests? how to size it? 3. What could be the culprit given the fact all the servers are relaxed? Really appreciate your guidance here Best,
On 2/09/2023 at 9:10:10 PM, Ibrahim Al Mahfooz <ibrahim.nezar@sevennet.net> wrote:
Hello all,
<stuff>
The questions to summarize are : 1. Is ~900 TPS and 10ms acceptable DB performance for accounting?
Depends what your queries are and your table sizes and so on - you say you have modified them but have not provided the changes or the logs or any postgres table metrics so no way to tell. It will also depend heavily on the storage - postgres by default does not complete a commit until the data is written to disk. If you have SSD vs. spinning rust it will be very different. I don’t think your pgbench data is very useful, it is using a different number of clients, and it is doing completely different queries to FreeRADIUS on completely different schema. Pgbench is useful for comparing 2 systems, it is not useful for evaluating one system in isolation. 2. In the max_requests setting, what does the clients mean here, does it
mean NAS client or SQL connections based on max requests? how to size it?
The documentation describes what this parameter is where you configure it, and says: "This should be 256 multiplied by the number of clients. e.g. With 4 clients, this number should be 1024." https://github.com/FreeRADIUS/freeradius-server/blob/db3d1924d9a2e8d37c43872... 3. What could be the culprit given the fact all the servers are relaxed?
Really appreciate your guidance here
This is almost certainly going to be a Postgres tuning problem rather than a FreeRADIUS problem. How big are your tables (rows and GB) and how big are your indexes (GB)? Are you deleting old data from the tables and vacuuming them regularly? You probably should be doing that, unless you know to how scale postgres properly. -- Nathan Ward
Thanks for your responses and patience and apology for the lack of details, let me add more, Depends what your queries are and your table sizes and so on - you say you
have modified them but have not provided the changes or the logs or any postgres table metrics so no way to tell.
Postgres Dashboard constantly showing these metrics, from pgadmin: - Transaction Per Second: ~ 1500 every ~ 5 seconds) - Tuples In: ~ 100 Inserts, ~ 1400 Updates every 5 seconds, 0 deletes - Tuples Out: Fetched ~ 1500, Returned ~ 25k every 5 seconds - Block I/O: ~ 15-20 Reads, ~ 40k Hits - Database Sessions: Total 20-30 sessions, Idle 20-30 sessions, Active 1-2 sessions - Note that we have no reads from the DB, since this is not the main use case, we just store and read rarely. Here is the accounting part of the queries.conf file column_list = "\ AcctSessionId, \ AcctUniqueId, \ AcctStartTime, \ AcctUpdateTime, \ AcctStopTime, \ CallingStationId, \ FramedIpAddress, \ imei, \ location" start { query = "\ INSERT INTO ${....acct_table1} \ (${...column_list}) \ VALUES(\ '%{Acct-Session-Id}', \ '%{Acct-Unique-Session-Id}', \ ${....event_timestamp}, \ ${....event_timestamp}, \ NULL, \ '%{Calling-Station-Id}', \ NULLIF('%{Framed-IP-Address}', '')::inet, \ NULLIF('%{3GPP-IMEISV}',''), \ NULLIF('%{3GPP-User-Location-Info}',''))\ ON CONFLICT (AcctUniqueId) \ DO UPDATE \ SET \ AcctStartTime = ${....event_timestamp}, \ AcctUpdateTime = ${....event_timestamp} \ WHERE ${....acct_table1}.AcctUniqueId = '%{Acct-Unique-Session-Id}' \ AND ${....acct_table1}.AcctStopTime IS NULL" .... interim-update { query = "\ UPDATE ${....acct_table1} \ SET \ FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \ AcctUpdateTime = ${....event_timestamp}, \ location = NULLIF('%{3GPP-User-Location-Info}','') \ WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' \ AND AcctStopTime IS NULL" query = "\ INSERT INTO ${....acct_table1} \ (${...column_list}) \ VALUES(\ '%{Acct-Session-Id}', \ '%{Acct-Unique-Session-Id}', \ TO_TIMESTAMP(${....event_timestamp_epoch} - %{%{Acct-Session-Time}:-0}), \ ${....event_timestamp}, \ NULL, \ '%{Calling-Station-Id}', \ NULLIF('%{Framed-IP-Address}', '')::inet, \ NULLIF('%{3GPP-IMEISV}', ''), \ NULLIF('%{3GPP-User-Location-Info}','')) \ ON CONFLICT (AcctUniqueId) \ DO NOTHING" .... stop { query = "\ UPDATE ${....acct_table2} \ SET \ AcctStopTime = ${....event_timestamp}, \ AcctUpdateTime = ${....event_timestamp}, \ FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \ location = NULLIF('%{3GPP-User-Location-Info}','') \ WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' \ AND AcctStopTime IS NULL" query = "\ INSERT INTO ${....acct_table1} \ (${...column_list}) \ VALUES(\ '%{Acct-Session-Id}', \ '%{Acct-Unique-Session-Id}', \ TO_TIMESTAMP(${....event_timestamp_epoch} - %{%{Acct-Session-Time}:-0}), \ ${....event_timestamp}, \ ${....event_timestamp}, \ '%{Calling-Station-Id}', \ NULLIF('%{Framed-IP-Address}', '')::inet, \ NULLIF('%{3GPP-IMEISV}', ''), \ NULLIF('%{3GPP-User-Location-Info}','')) \ ON CONFLICT (AcctUniqueId) \ DO NOTHING" .... Here is a raddebug from sample user performing start session: (1745106) Sat Sep 2 13:44:04 2023: Debug: Received Accounting-Request Id 222 from 10.26.3.10:45939 to 172.16.17.14:1813 length 399 (1745106) Sat Sep 2 13:44:04 2023: Debug: Acct-Status-Type = Start (1745106) Sat Sep 2 13:44:04 2023: Debug: Acct-Link-Count = 1 (1745106) Sat Sep 2 13:44:04 2023: Debug: Event-Timestamp = "Sep 2 2023 13:44:04 +03" (1745106) Sat Sep 2 13:44:04 2023: Debug: Acct-Authentic = RADIUS (1745106) Sat Sep 2 13:44:04 2023: Debug: Acct-Delay-Time = 0 (1745106) Sat Sep 2 13:44:04 2023: Debug: Acct-Multi-Session-Id = "9755fbbc" (1745106) Sat Sep 2 13:44:04 2023: Debug: Calling-Station-Id = "1112223334445" (1745106) Sat Sep 2 13:44:04 2023: Debug: Framed-IP-Address = 10.25.241.108 (1745106) Sat Sep 2 13:44:04 2023: Debug: Acct-Session-Id = "0a1a03169755fbbc" (1745106) Sat Sep 2 13:44:04 2023: Debug: Framed-Protocol = GPRS-PDP-Context (1745106) Sat Sep 2 13:44:04 2023: Debug: Called-Station-Id = "gnet" (1745106) Sat Sep 2 13:44:04 2023: Debug: User-Name = "gnet" (1745106) Sat Sep 2 13:44:04 2023: Debug: Service-Type = Framed-User (1745106) Sat Sep 2 13:44:04 2023: Debug: NAS-Port-Type = Virtual (1745106) Sat Sep 2 13:44:04 2023: Debug: 3GPP-IMSI = "55557011234567" (1745106) Sat Sep 2 13:44:04 2023: Debug: 3GPP-Charging-ID = 2538994620 (1745106) Sat Sep 2 13:44:04 2023: Debug: 3GPP-PDP-Type = 0 (1745106) Sat Sep 2 13:44:04 2023: Debug: 3GPP-Charging-Gateway-Address = 10.26.3.106 (1745106) Sat Sep 2 13:44:04 2023: Debug: 3GPP-GPRS-Negotiated-QoS-profile = "08-4406001e84800004baf0" (1745106) Sat Sep 2 13:44:04 2023: Debug: 3GPP-SGSN-Address = 10.26.3.22 (1745106) Sat Sep 2 13:44:04 2023: Debug: 3GPP-GGSN-Address = 10.26.3.22 (1745106) Sat Sep 2 13:44:04 2023: Debug: 3GPP-IMSI-MCC-MNC = "41877" (1745106) Sat Sep 2 13:44:04 2023: Debug: 3GPP-GGSN-MCC-MNC = "41877" (1745106) Sat Sep 2 13:44:04 2023: Debug: 3GPP-NSAPI = "6" (1745106) Sat Sep 2 13:44:04 2023: Debug: 3GPP-Selection-Mode = "0" (1745106) Sat Sep 2 13:44:04 2023: Debug: 3GPP-Charging-Characteristics = "0800" (1745106) Sat Sep 2 13:44:04 2023: Debug: 3GPP-SGSN-MCC-MNC = "41877" (1745106) Sat Sep 2 13:44:04 2023: Debug: 3GPP-IMEISV = "1234568712345688" (1745106) Sat Sep 2 13:44:04 2023: Debug: 3GPP-RAT-Type = EUTRAN (1745106) Sat Sep 2 13:44:04 2023: Debug: 3GPP-User-Location-Info = 0x8214f8772af814f877029bff0c (1745106) Sat Sep 2 13:44:04 2023: Debug: 3GPP-MS-Time-Zone = 0x2100 (1745106) Sat Sep 2 13:44:04 2023: Debug: 3GPP-Negotiated-DSCP = 18 (1745106) Sat Sep 2 13:44:04 2023: Debug: NAS-IP-Address = 10.26.3.10 (1745106) Sat Sep 2 13:44:04 2023: Debug: # Executing section preacct from file /etc/raddb/sites-enabled/default (1745106) Sat Sep 2 13:44:04 2023: Debug: preacct { (1745106) Sat Sep 2 13:44:04 2023: Debug: [preprocess] = ok (1745106) Sat Sep 2 13:44:04 2023: Debug: policy acct_unique { (1745106) Sat Sep 2 13:44:04 2023: Debug: update request { (1745106) Sat Sep 2 13:44:04 2023: Debug: } # update request = noop (1745106) Sat Sep 2 13:44:04 2023: Debug: if (("%{hex:&Class}" =~ /^%{hex:&Tmp-String-9}/) && ("%{string:&Class}" =~ /^ai:([0-9a-f]{32})/i)) { (1745106) Sat Sep 2 13:44:04 2023: Debug: EXPAND %{hex:&Class} (1745106) Sat Sep 2 13:44:04 2023: Debug: --> (1745106) Sat Sep 2 13:44:04 2023: Debug: EXPAND ^%{hex:&Tmp-String-9} (1745106) Sat Sep 2 13:44:04 2023: Debug: --> ^61693a (1745106) Sat Sep 2 13:44:04 2023: Debug: if (("%{hex:&Class}" =~ /^%{hex:&Tmp-String-9}/) && ("%{string:&Class}" =~ /^ai:([0-9a-f]{32})/i)) -> FALSE (1745106) Sat Sep 2 13:44:04 2023: Debug: else { (1745106) Sat Sep 2 13:44:04 2023: Debug: update request { (1745106) Sat Sep 2 13:44:04 2023: Debug: EXPAND %{md5:%{User-Name},%{Acct-Session-ID},%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}},%{NAS-Identifier},%{NAS-Port-ID},%{NAS-Port}} (1745106) Sat Sep 2 13:44:04 2023: Debug: --> aaf13b83bdd8b7cb59846e96d174d76c (1745106) Sat Sep 2 13:44:04 2023: Debug: } # update request = noop (1745106) Sat Sep 2 13:44:04 2023: Debug: } # else = noop (1745106) Sat Sep 2 13:44:04 2023: Debug: update request { (1745106) Sat Sep 2 13:44:04 2023: Debug: } # update request = noop (1745106) Sat Sep 2 13:44:04 2023: Debug: } # policy acct_unique = noop (1745106) Sat Sep 2 13:44:04 2023: Debug: suffix: Checking for suffix after "@" (1745106) Sat Sep 2 13:44:04 2023: Debug: suffix: No '@' in User-Name = "gnet", looking up realm NULL (1745106) Sat Sep 2 13:44:04 2023: Debug: suffix: No such realm "NULL" (1745106) Sat Sep 2 13:44:04 2023: Debug: [suffix] = noop (1745106) Sat Sep 2 13:44:04 2023: Debug: [files] = noop (1745106) Sat Sep 2 13:44:04 2023: Debug: } # preacct = ok (1745106) Sat Sep 2 13:44:04 2023: Debug: # Executing section accounting from file /etc/raddb/sites-enabled/default (1745106) Sat Sep 2 13:44:04 2023: Debug: accounting { (1745106) Sat Sep 2 13:44:04 2023: Debug: sql: EXPAND %{tolower:type.%{%{Acct-Status-Type}:-%{Request-Processing-Stage}}.query} (1745106) Sat Sep 2 13:44:04 2023: Debug: sql: --> type.start.query (1745106) Sat Sep 2 13:44:04 2023: Debug: sql: Using query template 'query' (1745106) Sat Sep 2 13:44:04 2023: Debug: sql: EXPAND %{User-Name} (1745106) Sat Sep 2 13:44:04 2023: Debug: sql: --> gnet (1745106) Sat Sep 2 13:44:04 2023: Debug: sql: SQL-User-Name set to 'gnet' (1745106) Sat Sep 2 13:44:04 2023: Debug: sql: EXPAND INSERT INTO radacct (AcctSessionId, AcctUniqueId, AcctStartTime, AcctUpdateTime, AcctStopTime, CallingStationId, FramedIpAddress, imei, location) VALUES('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', TO_TIMESTAMP(%{%{integer:Event-Timestamp}:-%l}), TO_TIMESTAMP(%{%{integer:Event-Timestamp}:-%l}), NULL, '%{Calling-Station-Id}', NULLIF('%{Framed-IP-Address}', '')::inet, NULLIF('%{3GPP-IMEISV}',''), NULLIF('%{3GPP-User-Location-Info}',''))ON CONFLICT (AcctUniqueId) DO UPDATE SET AcctStartTime = TO_TIMESTAMP(%{%{integer:Event-Timestamp}:-%l}), AcctUpdateTime = TO_TIMESTAMP(%{%{integer:Event-Timestamp}:-%l}) WHERE radacct.AcctUniqueId = '%{Acct-Unique-Session-Id}' AND radacct.AcctStopTime IS NULL (1745106) Sat Sep 2 13:44:04 2023: Debug: sql: --> INSERT INTO radacct (AcctSessionId, AcctUniqueId, AcctStartTime, AcctUpdateTime, AcctStopTime, CallingStationId, FramedIpAddress, imei, location) VALUES('0a1a03169755fbbc', 'aaf13b83bdd8b7cb59846e96d174d76c', TO_TIMESTAMP(1693651444), TO_TIMESTAMP(1693651444), NULL, '1112223334445', NULLIF('10.25.241.108', '')::inet, NULLIF('1234568712345688',''), NULLIF('0x8214f8772af814f877029bff0c',''))ON CONFLICT (AcctUniqueId) DO UPDATE SET AcctStartTime = TO_TIMESTAMP(1693651444), AcctUpdateTime = TO_TIMESTAMP(1693651444) WHERE radacct.AcctUniqueId = 'aaf13b83bdd8b7cb59846e96d174d76c' AND radacct.AcctStopTime IS NULL (1745106) Sat Sep 2 13:44:04 2023: Debug: sql: Executing query: INSERT INTO radacct (AcctSessionId, AcctUniqueId, AcctStartTime, AcctUpdateTime, AcctStopTime, CallingStationId, FramedIpAddress, imei, location) VALUES('0a1a03169755fbbc', 'aaf13b83bdd8b7cb59846e96d174d76c', TO_TIMESTAMP(1693651444), TO_TIMESTAMP(1693651444), NULL, '1112223334445', NULLIF('10.25.241.108', '')::inet, NULLIF('1234568712345688',''), NULLIF('0x8214f8772af814f877029bff0c',''))ON CONFLICT (AcctUniqueId) DO UPDATE SET AcctStartTime = TO_TIMESTAMP(1693651444), AcctUpdateTime = TO_TIMESTAMP(1693651444) WHERE radacct.AcctUniqueId = 'aaf13b83bdd8b7cb59846e96d174d76c' AND radacct.AcctStopTime IS NULL (1745106) Sat Sep 2 13:44:04 2023: Debug: sql: SQL query returned: success (1745106) Sat Sep 2 13:44:04 2023: Debug: sql: 1 record(s) updated (1745106) Sat Sep 2 13:44:04 2023: Debug: [sql] = ok (1745106) Sat Sep 2 13:44:04 2023: Debug: [exec] = noop (1745106) Sat Sep 2 13:44:04 2023: Debug: attr_filter.accounting_response: EXPAND %{User-Name} (1745106) Sat Sep 2 13:44:04 2023: Debug: attr_filter.accounting_response: --> gnet (1745106) Sat Sep 2 13:44:04 2023: Debug: attr_filter.accounting_response: Matched entry DEFAULT at line 12 (1745106) Sat Sep 2 13:44:04 2023: Debug: [attr_filter.accounting_response] = updated (1745106) Sat Sep 2 13:44:04 2023: Debug: } # accounting = updated (1745106) Sat Sep 2 13:44:04 2023: Debug: Sent Accounting-Response Id 222 from 172.16.17.14:1813 to 10.26.3.10:45939 length 20 (1745106) Sat Sep 2 13:44:04 2023: Debug: Finished request (1745106) Sat Sep 2 13:44:05 2023: Debug: Cleaning up request packet ID 222 with timestamp +6115 due to timer It will also depend heavily on the storage - postgres by default does not
complete a commit until the data is written to disk. If you have SSD vs.
spinning rust it will be very different.
Hard drives are HDD SAS 15K EPM, and logical partition set as RAID 50 (6 disks each 300GB) The documentation describes what this parameter is where you configure it,
and says: "This should be 256 multiplied by the number of clients. e.g. With 4 clients, this number should be 1024."
https://github.com/FreeRADIUS/freeradius-server/blob/db3d1924d9a2e8d37c43872...
Thanks for the reference, however this didn't answer the question, what does client here means does it mean NAS client or SQL connections based on max requests? In our case, we set it to max_requests = 32000 How big are your tables (rows and GB) and how big are your indexes (GB)?
Radius DB size: 8748 MB Current radacct table size is 3525 MB Table Index Size: 5213 MB I don’t think your pgbench data is very useful, it is using a different
number of clients, and it is doing completely different queries to FreeRADIUS on completely different schema.
I was thinking of making a custom pgbench queries that mimec the accounting inserts/update and run it against the db, do you think this will help to give an idea about the TPS and delay time of the real case? Are you deleting old data from the tables and vacuuming them regularly? You
probably should be doing that, unless you know to how scale postgres properly.
Not yet, but definitely we will. In fact, we started storing accounting data 2 weeks ago. We assumed we shouldn't face performance issues at the early stages at least for the first couple of weeks. Is there a rule of thumb for the DB delay response time? Perhaps it will help me to tune the db accordingly?
On Sep 2, 2023, at 6:53 AM, Ibrahim Al Mahfooz <ibrahim.nezar@sevennet.net> wrote:
Postgres Dashboard constantly showing these metrics, from pgadmin:
None of that matters. It's like you're trying to make your car go faster, but you're looking at the tire pressure. It's interesting and perhaps useful, but it's not the reason why it's slow.
Here is the accounting part of the queries.conf file
No one is going to debug your customized accounting queries for you. If you're not sure how to optimize the queries, then don't change them. Use the defaults. They're fine. Alan DeKok.
On 2/09/2023 at 10:53:02 PM, Ibrahim Al Mahfooz <ibrahim.nezar@sevennet.net> wrote:
Hard drives are HDD SAS 15K EPM, and logical partition set as RAID 50 (6 disks each 300GB)
With HDDs this sort of write latency seems reasonable. If you want lower latency you should get SSDs. Given you are writing mobile stuff, I can imagine that there are a lot of fairly short sessions. This would mean you have a lot of rows, and your DB size will reflect that. I don’t think you shared how many rows you have, but, 8GB of rows will be a lot. If you need this data to be retained, my suggestion is that you get someone experienced with postgres to help you design this DB and the queries. -- Nathan Ward
On Sep 2, 2023, at 8:19 AM, Nathan Ward <lists+freeradius@daork.net> wrote:
I don’t think you shared how many rows you have, but, 8GB of rows will be a lot.
Yup.
If you need this data to be retained, my suggestion is that you get someone experienced with postgres to help you design this DB and the queries.
A simple thing is to have one database for current information (e.g. last month or so), and a separate database for historical information. I write articles about this: https://networkradius.com/articles/2022/05/24/split-historical-database.html Alan DeKok.
I don’t think you shared how many rows you have, but, 8GB of rows will be a lot.
Yup.
If you need this data to be retained, my suggestion is that you get someone experienced with postgres to help you design this DB and the queries.
A simple thing is to have one database for current information (e.g. last month or so), and a separate database for historical information.
I write articles about this: https://networkradius.com/articles/2022/05/24/split-historical-database.html
Thanks Alan, I will give it a check for sure. Best regar
Hello,
With HDDs this sort of write latency seems reasonable. If you want lower latency you should get SSDs.In what circumstance should we consider SSD instead of HDD, what is the triggering factor such as number of TPS hit or other KPIs? FYI, And postgres, it is pretty default, no change apart from the shared buffer size set to 8GB instead of 128MB memory.
Two questions here: 1- is there an optimal db response time in ms for FreeRadius? 2- we have around 17 million records for two weeks which resulted in 8GB data size. On Sat, 2 Sep 2023 at 15:19 Nathan Ward <lists+freeradius@daork.net> wrote:
On 2/09/2023 at 10:53:02 PM, Ibrahim Al Mahfooz < ibrahim.nezar@sevennet.net> wrote:
Hard drives are HDD SAS 15K EPM, and logical partition set as RAID 50 (6 disks each 300GB)
With HDDs this sort of write latency seems reasonable.
If you want lower latency you should get SSDs.
Given you are writing mobile stuff, I can imagine that there are a lot of fairly short sessions. This would mean you have a lot of rows, and your DB size will reflect that.
I don’t think you shared how many rows you have, but, 8GB of rows will be a lot.
If you need this data to be retained, my suggestion is that you get someone experienced with postgres to help you design this DB and the queries.
-- Nathan Ward - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Sep 2, 2023, at 5:10 AM, Ibrahim Al Mahfooz <ibrahim.nezar@sevennet.net> wrote:
We have implemented freeradius 3.2.3 with Postgresql 12.15 to store accounting packets i.e start, interim-update, stop for reporting purposes. We customized the queries files to store fewer columns than the original schema since this is what we need.
That's fine, if it's done carefully.
The following columns are customized in the accounting queries and same columns in postgres radacct table:
radacctid, acctsessionid, acctuniqueid, acctstarttime, acctudpatetime, acctstoptime, callingstationid, framedipaddress, imei, 3gpplocation.
Except the acctuniqueid column is used to index the table. So if you delete that column, you don't have indexes. And every select / insert / update will require a full table scan. There is pretty much zero downside to leaving in columns which you don't need. But if you do want to remove columns, don't remove ones which are indexed. And don't remove columns which are used as part of the WHERE clauses. Or maybe "customized" here means something different than the first paragraph, where it means "deleted columns".
Also the following indexes are in radacct table:
1. radacct_active_session_idx (this is the original from FR schema of postgresql) 2. framedipaddress, starttime, stoptime, updatetime (This one we added to use upon need in our read queries on report generation)
I would recommend not using the same database for FreeRADIUS and for report generation. What typically happens is that everything is fine until you want to generate a bunch of reports. And then the reporting queries use all of the database resources, and FreeRADIUS will get robbed of resources. Set up DB replication. FreeRADIUS writes to the primary database. That replicates to the standby database. And the reports run against the standby database.
Based on the above observed messages, we tuned two matters:
It is rare to fix these problems by "tuning" the database. You have to build the DB with the correct architecture. Which means primary / secondary replication, correct indexes for your queries, etc. Tuning will usually change the performance slightly. Fixing the design can add enormous amounts of performance.
1. the postgresql db by changing the default shared_buffer from 128MB to 8GB since we have plenty of RAM in the server, and this clearly reflected in the performance and removed around 98% of the above error messages.
That's good.
However we sometimes see the same error message showing up in peak hours and KPI of radius is impacted per the NAS report.
That means the database is undersized. i.e. it can't keep up with the load. Either you need a more powerful database, or you need to make the current database more efficient.
The questions to summarize are : 1. Is ~900 TPS and 10ms acceptable DB performance for accounting?
That is the average case, which isn't relevant. You're looking for the performance under load. And if you get errors during peak hours, the database is slow, and can't keep up with the load.
2. In the max_requests setting, what does the clients mean here, does it mean NAS client or SQL connections based on max requests? how to size it?
It doesn't matter a lot. It's mainly to catch extreme corner cases. It limits how many packets the server will accept before it starts complaining that it's overloaded. It should be set to at least 256 * the number of NASes. i.e. set it based on inputs: the number of NASes. Don't set it based on outputs: SQL connections.
3. What could be the culprit given the fact all the servers are relaxed?
The database is slow. It can't handle the load at peak traffic. My $0.02 is that if you go back to the default schema + indexes, and then add a database replica for the report generation, everything will be fine. This kind of thing falls under the common practice of "I spent a bunch of effort trying to optimize things, and now things are slower". Well, don't do that. The default queries are fine. They're already optimized. The default schema is fine. It's already optimized. Alan DeKok.
Except the acctuniqueid column is used to index the table. So if you delete that column, you don't have indexes. And every select / insert / update will require a full table scan. There is pretty much zero downside to leaving in columns which you don't need. But if you do want to remove columns, don't remove ones which are indexed. And don't remove columns which are used as part of the WHERE clauses. Or maybe "customized" here means something different than the first paragraph, where it means "deleted columns".
By the way, true my queries are customized but not big deal of customization. In fact i just removed the non used columns and they are the ones that have nothing to do with the where clauses or indexes, hence you can consider no change in your original optimized design.
I would recommend not using the same database for FreeRADIUS and for report generation. What typically happens is that everything is fine until you want to generate a bunch of reports. And then the reporting queries use all of the database resources, and >FreeRADIUS will get robbed of resources. Set up DB replication. FreeRADIUS writes to the primary database. That replicates to the standby database. And the reports run against the standby database.
Yeah, in my plan for later, for now I'm spinning the basic solution to check performance and stumbled on this performance issue from day 1.
That means the database is undersized. i.e. it can't keep up with the load. Either you need a more powerful database, or you need to make the current database more efficient.
Any reference article on how to optimize postgresdb for freeradius or generic guidelines you believe useful to start with?
The questions to summarize are : 1. Is ~900 TPS and 10ms acceptable DB performance for accounting? That is the average case, which isn't relevant. You're looking for the performance under load. And if you get errors during peak hours, the database is slow, and can't keep up with the load.
True, that's why I will make a real pgbench real queries case to use and see the delay ms and tps.
It doesn't matter a lot. It's mainly to catch extreme corner cases. It limits how many packets the server will accept before it starts complaining that it's overloaded. It should be set to at least 256 * the number of NASes. i.e. set it based on inputs: the number of NASes. Don't set it based on outputs: SQL connections.
Since I have only two NAS's then I will set it to 512 in this case? Fair enough?
The database is slow. It can't handle the load at peak traffic. My $0.02 is that if you go back to the default schema + indexes, and then add a database replica for the report generation, everything will be fine. This kind of thing falls under the common practice of "I spent a bunch of effort trying to optimize things, and now things are slower". Well, don't do that. The default queries are fine. They're already optimized. The default schema is fine. It's already optimized.
Again, I haven't made a skeleton change in the default queries, just removed unused columns. The indexes, and columns that are involving where clause is left intact. If you have a quick look at the custom queries you can tell, actually they are very minimal, the goal of this change is to reduce the size of stored bits in the table and also since they won't be used, why we store them.
On Sep 2, 2023, at 10:42 AM, Ibrahim Al Mahfooz <ibrahim.nezar@sevennet.net> wrote:
By the way, true my queries are customized but not big deal of customization. In fact i just removed the non used columns and they are the ones that have nothing to do with the where clauses or indexes, hence you can consider no change in your original optimized design.
OK. It's better to say that up front, instead of saying you "customized" acctuniqueid, etc. columns. It's not clear at all what that means.
Any reference article on how to optimize postgresdb for freeradius or generic guidelines you believe useful to start with?
I've given guidelines already. There are any number of documentation pages explaining how to optimize Postgres. There's no interaction with FreeRADIUS which makes it special. It's all just fixing the database.
Since I have only two NAS's then I will set it to 512 in this case? Fair enough?
You're better off setting it higher than lower. There is no impact to setting it higher. The default value is fine, unless your database is too slow. Changing max_requests won't make the database faster. Just leave the defaults along. If they're not causing problems, they're fine.
Again, I haven't made a skeleton change in the default queries, just removed unused columns. The indexes, and columns that are involving where clause is left intact. If you have a quick look at the custom queries you can tell, actually they are very minimal, the goal of this change is to reduce the size of stored bits in the table and also since they won't be used, why we store them.
So you're doing micro-optimizations on things which don't affect performance, but you're running into performance issues. Stop wasting your time poking at irrelevant things when the database is on fire. Fix the database first. Then worry about the details. Alan DeKok.
participants (3)
-
Alan DeKok -
Ibrahim Al Mahfooz -
Nathan Ward