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 150000 accounting > records in Oracle (9.2.0.4) database or ~30000 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
