[asterisk-users] Rotating CDR records inside mysql - anyone does it?

[Atis Lezdins]

On 1/22/08, tloginbr-asterisk at yahoo.com.br
<tloginbr-asterisk at yahoo.com.br> wrote:
> Hi everyone,
>
> I have a few asterisk machines doing PSTN calls, and I keep track of
> all cdr in a single machine running mysql 5. Since I have a very
> large amount of records in there, its getting pretty slow to query
> the database, so I'm wondering if anyone does some type of log
> rotating, like save the data for a single month inside a separate
> table and do that every month, so I keep the tables small enough to
> build my reports. I know this is mainly a mysql question, but maybe
> someone here has some stored procedures that do this already...

Well, i wouldn't recommend delete, as that would keep mysql very
unhappy. you could do RENAME TABLE and CREATE TABLE, or mysqldump and
TRUNCATE TABLE, but they have to happen almost instantly (without
asterisk trying to do INSERT). I have nearly none experience with
transactions, but probably those would be helpful.

Btw, you can block access to mysql by firewall (to move existing data)
or stop mysql (to physycally copy binary database files) and then take
it back up - asterisk will post it's CDRs later when db comes
accessible.


Btw - how many records do you have that it gets slow? On what machine?

I currently have 3 million CDR records in MySQL with well created
indexes - and most reports are dynamic. Usually from 0 to 2 seconds,
but sometimes up to minute for joins :p. Well, that's 2x Quad core
xeons of 3GHz and 8Gb RAM (2 of which are used by MySQL indexes).
Asterisk is running on same machine.

Regards,
Atis


-- 
Atis Lezdins
VoIP Developer,
IQ Labs Inc.
atis at iq-labs.net
Skype: atis.lezdins
Cell Phone: +371 28806004
Work phone: +1 800 7502835