asterisk users - May 2006 - MySQL replication for voicemail

Hi -

We've got a number of offices, and they're all using ODBC message
storage using MySQL.  I've been trying to get MySQL replication set up
so messages left in a voicemail box at one office will get copied to
the corresponding voicemail box at all the offices.

We're also using MySQL replication for the voicemail user info, and
that part works just fine.  I'd like to actually copy the voice
messages themselves, too, but I've encountered an error that I can't
seem to get past.  I've asked on the MySQL replication forums, but no
one has responded.  When I do a "LOAD DATA FROM MASTER" the error I
get is:

ERROR 1187 (HY000): Failed rebuilding the index of  dumped table
'voicemessages'

The MySQL error log shows this:

060508  9:36:48 [ERROR] Delete link points outside datafile at 73504
060508  9:36:48 [Note] Retrying repair of: './asterisk/voicemessages'
with keyc$
060508  9:36:48 [ERROR] Delete link points outside datafile at 73504
060508  9:36:48 [ERROR] Found block that points outside data file at 73584
060508  9:36:48 [ERROR] Found block that points outside data file at 73624
060508  9:36:48 [ERROR] Found block that points outside data file at 73772
060508  9:36:48 [ERROR] Found block that points outside data file at 74504
060508  9:36:48 [ERROR] Found block that points outside data file at 74664
060508  9:36:48 [ERROR] Found block that points outside data file at 74852
060508  9:36:48 [ERROR] Not enough memory for blob at 74884 (need 1612498357)


I'm using the standard table description from the wiki:

CREATE TABLE `voicemessages` (
 `id` int(11) NOT NULL auto_increment,
 `msgnum` int(11) NOT NULL default '0',
 `dir` varchar(80) default '',
 `context` varchar(80) default '',
 `macrocontext` varchar(80) default '',
 `callerid` varchar(40) default '',
 `origtime` varchar(40) default '',
 `duration` varchar(20) default '',
 `mailboxuser` varchar(80) default '',
 `mailboxcontext` varchar(80) default '',
 `recording` longblob,
 PRIMARY KEY  (`id`),
 KEY `dir` (`dir`)
) ENGINE=MyISAM;


Does anybody have any ideas on what's causing this error?  Why would
MySQL not have enough memory?  What does it mean, "points outside data
file"?  Is anybody else doing this successfully, or am I a lone freak?

Any assistance is greatly appreciated.

Thanks,
Noah

I have voicemessage ODBC storage working and MySQL replicating, but I
didn't setup the replication after-the-fact, therefore didn't need the
'LOAD DATA FROM MASTER'.   You may want to try a different method
(more manual) of synching your machines.

Does your voicemessages table contain specially large messages?   The
errors you get don't suggest this, but the only change I had to make
was to increase the maximum packet size -- it was 1M by default and I
changed it to 16M.

On 5/8/06, Noah Miller <noahisaacmiller@gmail.com>
wrote:
>
> Does anybody have any ideas on what's causing this error?  Why would
> MySQL not have enough memory?  What does it mean, "points outside data
> file"?  Is anybody else doing this successfully, or am I a lone freak?
>

Another approach you may want to consider for data redundancy that does
not rely on MySQL's finicky replication stuff is DRBD. Think of it as
RAID-1 across Ethernet. I have used it in production on some VERY busy
(> 1200 qps) MySQL servers for a couple years with no problems. Another
nice side effect is a relatively simple means of doing a total point in
time "cold" backup of all you DB's without regard to the db
engine(s)
your using and minimal down time. If you can afford ~ 1 minute/day of
downtime... stop MySQL, break DRBD replication, restart mysql, mount
data on the rep. slave, backup data files, unmount, restart replication.
DRBD tracks the deltas so it will catch up quickly, and that entire
process can be automated.

Anyways, if you're interested, check our http://drbd.org. Please make
sure you fully understand what it's doing before using it in production
though. A lack of understanding can lead to nasty things like
replicating the wrong way.

Note, that this can be used as a very simple means of providing warm
standby * servers as well. Coupled with something like mon, you can
provide for automatic failover as well.

Josh McAllister


-----Original Message-----
From: asterisk-users-bounces@lists.digium.com
[mailto:asterisk-users-bounces@lists.digium.com] On Behalf Of Noah
Miller
Sent: Monday, May 08, 2006 10:17 AM
To: Asterisk Users Mailing List - Non-Commercial Discussion
Subject: Re: [Asterisk-Users] MySQL replication for voicemail

Hi Gary -
> I have voicemessage ODBC storage working and MySQL replicating,
> but I didn't setup the replication after-the-fact, therefore didn't
need the
> 'LOAD DATA FROM MASTER'.   You may want to try a different method
> (more manual) of synching your machines.
Yes, I've been reading about some errors with LOAD DATA FROM MASTER
that might not get fixed until after 5.1 is released.  Yikes!  I'll
take your advice and move a mysqldump of the info over to the slaves,
and manually set the replication points.

> Does your voicemessages table contain specially large messages?
> The errors you get don't suggest this, but the only change I had to
make
> was to increase the maximum packet size -- it was 1M by default and I
> changed it to 16M.
Aha.  I didn't even know there was such a setting.  I'm sure there
will come a time when someone leaves a message larger than 1M.


Thanks!
Noah



> On 5/8/06, Noah Miller <noahisaacmiller@gmail.com> wrote:
> >
> > Does anybody have any ideas on what's causing this error?  Why
would
> > MySQL not have enough memory?  What does it mean, "points outside
data
> > file"?  Is anybody else doing this successfully, or am I a lone
freak?
> >
> _______________________________________________
> --Bandwidth and Colocation provided by Easynews.com --
>
> Asterisk-Users mailing list
> To UNSUBSCRIBE or update options visit:
>    http://lists.digium.com/mailman/listinfo/asterisk-users
>
_______________________________________________
--Bandwidth and Colocation provided by Easynews.com --

Asterisk-Users mailing list
To UNSUBSCRIBE or update options visit:
   http://lists.digium.com/mailman/listinfo/asterisk-users

> 
> Hi Josh -
> 
> > Another approach you may want to consider for data redundancy that
> > does not rely on MySQL's finicky replication stuff is DRBD. Think
of
it
> > as RAID-1 across Ethernet. I have used it in production on some VERY
> > busy (> 1200 qps) MySQL servers for a couple years with no
problems.
> 
> I would very much welcome not having to use a MySQL's replication
> (sorry to all you MySQL geeks)!  I've read up a little on DRBD before,
> but I've avoided it in this particular application because of a few
> questions I had about it:
> 
> 1) Does it work over slow-ish links? In this case, I'm going over WAN
> links to offices around the country.

This would be a function of the amount of data being changed, and the
speed of your link. DRBD does support ASYNC mode, and there is a good
chance this could work for you.
> 2) Can it do two-way replication?
No. This is primarily because of the limitations of the overlying
filesystem. (DRBD sits below the FS). With newer fs's like GFS, this is
now feasible, but DRBD is a little behind. Keep your eyes open for 0.8x
to be released as it will support 2-way with GFS. If you want to
maximize hardware resources, you can of course setup a partition
replicating A -> B, and another replicating B -> A.
> 3) Can it do N-way replication (i.e. multiple slaves)?  We have
> several offices offices that I'd like to have the replicated info.

I know this was something that was in the works a while back. I haven't
followed this issue that closely though, so I'm not sure if this has
been worked out or not. I believe the proposed approach was to simply
get rid of some arbitrary limitations DRBD imposed on letting you layer
drbd on a drbd device.
> 
> Thanks!
> Noah
> _______________________________________________
> --Bandwidth and Colocation provided by Easynews.com --
To summarize, if you are currently doing MySQL replication with multiple
masters, and multiple+N slaves, and you're trying to duplicate that...
DRBD is probably not going to work for you. But I can say that what it
does it does very well, and with little overhead. I've seen < 5%
reduction in write performance with 15K SCSI drives, IE ~ 70MB/sec
writes with DRBD vs ~72MB/sec without.

How many users are you supporting on a single 8 box using obdc/mysql
voicemail storage? Performance issues?
> -----Original Message-----
> From: asterisk-users-bounces@lists.digium.com [mailto:asterisk-users-
> bounces@lists.digium.com] On Behalf Of Josh McAllister
> Sent: Monday, May 08, 2006 2:06 PM
> To: Asterisk Users Mailing List - Non-Commercial Discussion
> Subject: RE: [Asterisk-Users] MySQL replication for voicemail
> 
> >
> > Hi Josh -
> >
> > > Another approach you may want to consider for data redundancy
that
> > > does not rely on MySQL's finicky replication stuff is DRBD.
Think
of
> it
> > > as RAID-1 across Ethernet. I have used it in production on some
VERY
> > > busy (> 1200 qps) MySQL servers for a couple years with no
problems.
> >
> > I would very much welcome not having to use a MySQL's replication
> > (sorry to all you MySQL geeks)!  I've read up a little on DRBD
before,
> > but I've avoided it in this particular application because of a
few
> > questions I had about it:
> >
> > 1) Does it work over slow-ish links? In this case, I'm going over
WAN
> > links to offices around the country.
> 
> 
> This would be a function of the amount of data being changed, and the
> speed of your link. DRBD does support ASYNC mode, and there is a good
> chance this could work for you.
> 
> > 2) Can it do two-way replication?
> No. This is primarily because of the limitations of the overlying
> filesystem. (DRBD sits below the FS). With newer fs's like GFS, this
is
> now feasible, but DRBD is a little behind. Keep your eyes open for
0.8x
> to be released as it will support 2-way with GFS. If you want to
> maximize hardware resources, you can of course setup a partition
> replicating A -> B, and another replicating B -> A.
> 
> > 3) Can it do N-way replication (i.e. multiple slaves)?  We have
> > several offices offices that I'd like to have the replicated info.
> 
> 
> I know this was something that was in the works a while back. I
haven't
> followed this issue that closely though, so I'm not sure if this has
> been worked out or not. I believe the proposed approach was to simply
> get rid of some arbitrary limitations DRBD imposed on letting you
layer
> drbd on a drbd device.
> 
> >
> > Thanks!
> > Noah
> > _______________________________________________
> > --Bandwidth and Colocation provided by Easynews.com --
> 
> To summarize, if you are currently doing MySQL replication with
multiple
> masters, and multiple+N slaves, and you're trying to duplicate that...
> DRBD is probably not going to work for you. But I can say that what it
> does it does very well, and with little overhead. I've seen < 5%
> reduction in write performance with 15K SCSI drives, IE ~ 70MB/sec
> writes with DRBD vs ~72MB/sec without.
> 
> _______________________________________________
> --Bandwidth and Colocation provided by Easynews.com --
> 
> Asterisk-Users mailing list
> To UNSUBSCRIBE or update options visit:
>    http://lists.digium.com/mailman/listinfo/asterisk-users

I had similar concerns, but wanted to get a feel for what others are
experiencing.

What about network based file storage for VM, any experience?

The goal is to be able to extend our web portal to be able to
view/listen/delete VM without having to put the load on the * box.

Already using realtime mysql for VM config.
> -----Original Message-----
> From: asterisk-users-bounces@lists.digium.com [mailto:asterisk-users-
> bounces@lists.digium.com] On Behalf Of Gary Reuter
> Sent: Tuesday, May 09, 2006 11:48 AM
> To: Asterisk Users Mailing List - Non-Commercial Discussion
> Subject: Re: [Asterisk-Users] MySQL replication for voicemail
> 
> On 5/9/06, Damon Estep <damon@suburbanbroadband.net> wrote:
> > How many users are you supporting on a single 8 box using obdc/mysql
> > voicemail storage? Performance issues?
> >
> If you are concerned about your system's performance, then do NOT use
> ODBC message storage!
> Reading through app_voicemail last weekend, I realized that the ODBC
> storage code writes/erases the recordings to disk temporarily
> everytime it is required -- the recording fetched from the database is
> not streamed from memory.
> Also note, the current app_directory has absolutely no code for ODBC
> storage, so user's name recordings do not get used -- names are always
> spelled out.
> _______________________________________________
> --Bandwidth and Colocation provided by Easynews.com --
> 
> Asterisk-Users mailing list
> To UNSUBSCRIBE or update options visit:
>    http://lists.digium.com/mailman/listinfo/asterisk-users