On 1/22/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> 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. [EMAIL PROTECTED] Skype: atis.lezdins Cell Phone: +371 28806004 Work phone: +1 800 7502835 _______________________________________________ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users