Re: [asterisk-users] Rotating CDR records inside mysql - anyone does it?
Thanks. I have about 1 million records, but my machine is not so good. Its a core2 duo with 2 Gig of RAM. When I do only select it takes a few seconds, but some of my reports require joins, and thats a big problem. Thiago 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 Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento! http://br.mail.yahoo.com/ ___ -- 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
[asterisk-users] Rotating CDR records inside mysql - anyone does it?
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... Thanks for all help, Thiago Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento! http://br.mail.yahoo.com/ ___ -- 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
Re: [asterisk-users] Rotating CDR records inside mysql - anyone does it?
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
Re: [asterisk-users] Rotating CDR records inside mysql - anyone does it?
You may speed up your queries with proper indexing. The default indexes are included with the table creation script here: http://www.voip-info.org/wiki-Asterisk+cdr+mysql ALTER TABLE `cdr` ADD INDEX ( `calldate` ); ALTER TABLE `cdr` ADD INDEX ( `dst` ); ALTER TABLE `cdr` ADD INDEX ( `accountcode` ); You could look at running a select/insert query to dump older CDRs off to an archive table (compressed, supports inserts and selects only): http://dev.mysql.com/tech-resources/articles/storage-engine.html After that's good, delete the older entires. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Tuesday, January 22, 2008 11:44 To: Asterisk Users Mailing List - Non-Commercial Discussion Subject: [asterisk-users] Rotating CDR records inside mysql - anyone does it? 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... Thanks for all help, Thiago Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento! http://br.mail.yahoo.com/ ___ -- 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 ___ -- 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
