Re: how many records in radacct
On Tue, 23 Nov 2004, Alexander Serkin wrote: Kostas Kalevras wrote: On Tue, 23 Nov 2004, Alexander Serkin wrote: ... Well, how do you deal with stop records lost for some reason? There should be some tool to remove these stale records from active table. dialupadmin/bin/clean_radcct :-) dialup_admin is not an Oracle's friend yet? Will it? Yes. Just not immediately. -- Alexander - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html -- Kostas Kalevras Network Operations Center [EMAIL PROTECTED] National Technical University of Athens, Greece Work Phone: +30 210 7721861 'Go back to the shadow' Gandalf - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: how many records in radacct
Thank you all for the hints. Really stupid was it not to create index on acctuniqueid. And 'explain' is my best friend ad finem seculorum. -- Alexander Kostas Kalevras wrote: On Mon, 22 Nov 2004, Alexander Serkin wrote: Hello, how many records in radacct table do you manage to keep, guys? I see that radius stops working properly after about 15 accounting records in Oracle (9.2.0.4) database or ~3 in PostgreSQL 7.4.6. After that amount accounting records are not written into table and FR (v1.0.1) claims about no DB handles to use. I see this with Oracle and Postgres. The symptoms are the same on two different Solaris8 machines - Netra1120 with 2x440MHz processors and SunFire V240 with 2x1GHz processors. All recomendations about tuning are met - noatime on partitions with DB, no detail accounting, indexes on the accounting table. I'm fighting with that for a couple of months with no understanding what else could be wrong. Our DBA did some tunings on Oracle table and configuration - with no visible results. PostgreSQL is not tuned - just 'configure,make,make install, initdb, createdb radius,etc'. I 've got more than 1,000,000 rows in my radacct table (MySQL+InnoDB). The numbers you are reporting are really small, your database should be able to handle them just fine. One guess would be that your Session-Ids are not that random so the corresponding update queries have too many candidate rows (explain select is your friend to find out bottlenecks like that). Try using Acct-Unique-Id if that is the case. Do an Explain select on the queries run by the freeradius server (mainly the accounting_stop query and the simul_count query if you 've enabled it) and you should quickly find the problem. I need to add a few notes on an alternative high performance accounting structure for freeradius in the tuning guide one of these days... -- Alexander - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html -- Kostas KalevrasNetwork Operations Center [EMAIL PROTECTED]National Technical University of Athens, Greece Work Phone:+30 210 7721861 'Go back to the shadow'Gandalf - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: how many records in radacct
On Tue, 23 Nov 2004, Alexander Serkin wrote: Kostas Kalevras wrote: On Tue, 23 Nov 2004, jesk wrote: ... 10,000,000 rows can be a lot depending on your candidate rows for each query and your available memory for caching. A more scalable structure (which i 'll start using on my installation) is this: Create a memory mapped table (HEAP type in MySQL) storing only live sessions. That means that on accounting stop we do a delete instead of an update. That way double login detection works with the least overhead while also accounting is fast. For full accounting we also use a detail file and radsqlrelay (that's the reason i wrote it) to insert accounting directly on the radacct table. Since radius does not need to perform queries on the radacct table and radsqlrelay can cope with sql server delays/downtime we don't really mind that much how big our accounting table gets and we can also perform maintainance work on it without affecting the radius service. My 0,02E Well, how do you deal with stop records lost for some reason? There should be some tool to remove these stale records from active table. dialupadmin/bin/clean_radcct :-) -- Alexander - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html -- Kostas Kalevras Network Operations Center [EMAIL PROTECTED] National Technical University of Athens, Greece Work Phone: +30 210 7721861 'Go back to the shadow' Gandalf - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
how many records in radacct
Hello, how many records in radacct table do you manage to keep, guys? I see that radius stops working properly after about 15 accounting records in Oracle (9.2.0.4) database or ~3 in PostgreSQL 7.4.6. After that amount accounting records are not written into table and FR (v1.0.1) claims about no DB handles to use. I see this with Oracle and Postgres. The symptoms are the same on two different Solaris8 machines - Netra1120 with 2x440MHz processors and SunFire V240 with 2x1GHz processors. All recomendations about tuning are met - noatime on partitions with DB, no detail accounting, indexes on the accounting table. I'm fighting with that for a couple of months with no understanding what else could be wrong. Our DBA did some tunings on Oracle table and configuration - with no visible results. PostgreSQL is not tuned - just 'configure,make,make install, initdb, createdb radius,etc'. -- Alexander - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: how many records in radacct
Alexander Serkin wrote: I see that radius stops working properly after about 15 accounting records in Oracle (9.2.0.4) database or ~3 in PostgreSQL 7.4.6. After that amount accounting records are not written into table and FR (v1.0.1) claims about no DB handles to use. I see this with Oracle and Postgres. The symptoms are the same on two different Solaris8 machines - Netra1120 with 2x440MHz processors and SunFire V240 with 2x1GHz processors. All recomendations about tuning are met - noatime on partitions with DB, no detail accounting, indexes on the accounting table. I'm fighting with that for a couple of months with no understanding what else could be wrong. Our DBA did some tunings on Oracle table and configuration - with no visible results. PostgreSQL is not tuned - just 'configure,make,make install, initdb, createdb radius,etc'. Maybe try setting the maximum requests per server configuration item of freeradius to something like 1 and see if the problem disappears. -- Regards, Thor Spruyt E: [EMAIL PROTECTED] W: www.thor-spruyt.com M: +32 (0)475 67 22 65 Bestel nu uw exemplaar van Operationele verkoop (Walter Spruyt - Liesbeth Huysmans) via www.salesguide.be Ontdek de Telenet Hotspot service op www.telenet.be/hotspots - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: how many records in radacct
I have 1,736,884 in my current MySQL table. -Original Message- From: [EMAIL PROTECTED] [mailto:freeradius- [EMAIL PROTECTED] On Behalf Of Alexander Serkin Sent: Monday, November 22, 2004 1:11 PM To: [EMAIL PROTECTED] Subject: how many records in radacct Hello, how many records in radacct table do you manage to keep, guys? I see that radius stops working properly after about 15 accounting records in Oracle (9.2.0.4) database or ~3 in PostgreSQL 7.4.6. After that amount accounting records are not written into table and FR (v1.0.1) claims about no DB handles to use. I see this with Oracle and Postgres. The symptoms are the same on two different Solaris8 machines - Netra1120 with 2x440MHz processors and SunFire V240 with 2x1GHz processors. All recomendations about tuning are met - noatime on partitions with DB, no detail accounting, indexes on the accounting table. I'm fighting with that for a couple of months with no understanding what else could be wrong. Our DBA did some tunings on Oracle table and configuration - with no visible results. PostgreSQL is not tuned - just 'configure,make,make install, initdb, createdb radius,etc'. -- Alexander - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: how many records in radacct
On Mon, Nov 22, 2004 at 10:10:53PM +0300, Alexander Serkin wrote: Hello, how many records in radacct table do you manage to keep, guys? About of 1.3M without any problem. I see that radius stops working properly after about 15 accounting records in Oracle (9.2.0.4) database or ~3 in PostgreSQL 7.4.6. After that amount accounting records are not written into table and FR (v1.0.1) claims about no DB handles to use. I see this with Oracle and Postgres. The symptoms are the same on two different Solaris8 machines - Netra1120 with 2x440MHz processors and SunFire V240 with 2x1GHz processors. All recomendations about tuning are met - noatime on partitions with DB, no detail accounting, indexes on the accounting table. I'm fighting with that for a couple of months with no understanding what else could be wrong. Our DBA did some tunings on Oracle table and configuration - with no visible results. PostgreSQL is not tuned - just 'configure,make,make install, initdb, createdb radius,etc'. Can't say anything about Oracle but here's several advices on PG. First, you should ANALYZE, or better VACUUM ANALYZE RadAcct table at least every time it grows 1.5-2 times. We do VACUUM ANALYZE nightly. If your radius server receives and processes Accounting-Update's you will probably need even more often. See PostgreSQL documentation on database maintenance: http://www.postgresql.org/docs/7.4/static/maintenance.html#ROUTINE-VACUUMING Second, did you modify standard schema and/or postgresql.conf to fit your needs? If so, try to determine which queries are slowest and try to understand why, e.g. there's no appropriate index or something. The default ones should work OK. Third, make sure you have no dead locks. ps auxww | grep postgres | grep waiting Several words for Peter Nixon about default PostgreSQL schema/queries... 1. now() returns timestamp with time zone, so there's no need to cast it once more. This applies to AcctStartTime::timestamp with time zone too. 2. accounting_onoff_query shound not have AcctSessionTime IS NULL condition in where clause, otherwise those records which were updated by Accounting-Update will not be closed. Active sessions just have AcctStopTime IS NULL. 3. I don't understand why there's DATE_SUB function at all :) One can simply say CURRENT_DATE - some_integer * '1minute'::interval. Is it simpler to call date_sub(CURRENT_DATE, some_integer, 'minute')? -- Fduch M. Pravking - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: how many records in radacct
On Mon, Nov 22, 2004 at 10:10:53PM +0300, Alexander Serkin wrote: Hello, how many records in radacct table do you manage to keep, guys? About of 1.3M without any problem. Here, exactly 4,657,586, and growing, running on freebsd 4.8 , two 2.4 Ghz, 1024 MB ram , i havent done any tunning, freeradius compiled from source and mysql 3.23 from ports, Miguel - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
