Re: [PATCH] DB-Based Store

Nikos,

Answering inline

Regards,

Alejandro

On 03/02/2009, at 17:49, Nikos Balkanas wrote:

> Sorry, for interfering.
>
> Hot-swaping is possible with the filebased system as well. On  
> startup, bb checks and queues messages already in the store-file.  
> That's how parchute works, after all.
>

Arguing about hot-swapping on the filesystem is nonsense imho: You  
need specialized hardware to do that, which is not the norm on many  
cases.

Parachute has nothing to do with the store. It's just a method kannel  
uses to restart itself after a crash.

> Performance should depend on use. DBs process the queue in memory.  
> This is plus. However, to be reliable you need to enable logging  
> which is again I/O comparable to the message in the spool directory.  
> This is minus. Mysql is especially notorious for slowing down when  
> you switch to INNODB to enable logs. Otherwise, once you loose the  
> DB all messages are gone.

I wouldn't speculate on performance issues without real tests. BTW,  
kannel processes the queue in memory as well. The store is only there  
to restore unsent messages after a crash and kannel only reads from it  
when starting. Then it just add new messages when arriving and deletes  
it when it has been sent.

What I can tell you is that I've specially tuned my queries to take  
advantage of MySQL (and other DB's) strongest points. I've specially  
focused on InnoDB.

You'll notice that:

1. I'm not using auto-incremented keys. This are slow on InnoDB and  
many other engines because a master record must be kept to keep track  
of the numbering. This also complicates Oracle (requiring a trigger  
and a sequence).
2. I'm using kannel's uid as the primary key and I only search using  
that same key. This is fast on all engines (is there anything faster  
than a search by PK?).
3. If you're using InnoDB (and other more "serious" DB engines) you'd  
probably benefit from the row-locking feature, so on highly loaded  
systems I think this is a plus, not a minus.

> The spool store should have the same design as DB archive log  
> partitions (mirrored, etc.). Same chance of loosing your spool  
> partition as your archive log partition. You can always recover  
> something on the FS, but not in the DB. Plus a DB can fail because  
> of application errors.

You can easily backup a DB table without disturbing it and there's a  
ton of tools to properly manage a DB. Try doing the same with a live  
filesystem while you're reading/writing on it. Hint: LVM.

Why do you say that you can't recover data from a DB?

>
> There are reasons why one would prefer in this case to use a  
> database (easier sorting, reporting, data mining, etc.) but they are  
> not performance or reliability. I just hope that whoever uses it  
> knows precisely why he needs it, and is not a fad.
>

My point is that there's no difference here. It's just two different  
ways to store data, one at a higher level than the other and each one  
having advantages and disadvantages. For example, the spool dir  
requires special partitioning to be able to handle big numbers (ext3  
won't cut it).

I agree this is not the only way to store the data and probably won't  
be the recommended way to do it on most cases, but for some scenarios  
it would make sense to be able to separate the data storage from the  
kannel services.

> My 2 cents worth,
> Nikos


> ----- Original Message -----
> From: Alejandro Guerrieri
> To: Tony Kirkham
> Cc: Kannel Devel
> Sent: Tuesday, February 03, 2009 6:14 PM
> Subject: Re: [PATCH] DB-Based Store
>
> Tony
>
> Yes, once other db types are supported you'll be able to use store- 
> type = postgres, oracle, etc. My first approach was to use store- 
> type = mysql because using store-location to indicate a db type  
> would be misleading imho ("mysql" is not a location). I'm open to  
> opinions from fellow developers and users about alternatives of  
> course.
>
> The reason why I've developed this in first place wasn't  
> performance: I was trying to be able to perform hot-swapping of  
> boxes (if a kannel server goes down, I could start a new one on  
> another box and since the messages are stored on a db, the messages  
> would be easily re-enqueued on the new box).
>
> Another advantage I see is offloading disk I/O from the server. I  
> didn't have time yet to test if this ends up being more performant  
> than file based under heavy load or if it provides any advantages  
> compared to a file-based storage (as I've said, it wasn't my first  
> goal when I've developed it). I think it would probably extend HDD's  
> life, specially for the spool dir.
>
> I don't thing the file store would cause the double-send, looks more  
> like a wrong return code from a modem (maybe one of the modems  
> failed at the last moment but the message got thru, so kannel  
> retried and that caused the double-send). The DB-store is abstracted  
> at the same level than the other storage engines, even if the  
> problem _were_ on the store I don't think that using a DB would fix  
> it.
>
> Regards,
> --
> Alejandro Guerrieri
> [email protected]
>
>
>
> On 03/02/2009, at 16:50, Tony Kirkham wrote:
>
>> I have just started looking at this and am interested to play with  
>> it.  For now, I have just a couple of questions:
>>
>> In the documentation, I see that the store-type in the core  
>> configuration will be set to "mysql".  Do you intend that each  
>> different database engine will have its own type of store e.g., if  
>> I am using Postgres then store-type = postgres?  Or could this type  
>> of store be indicated by something like "db" or "database" to  
>> better support multiple database engines and another param, such as  
>> the store-location, be used to indicate which engine is being used?
>>
>> Also, we had a discussion a while ago about the advantages of the  
>> spool store method over the file store method.  From that I was  
>> lead to believe that the spool method had several advantages over  
>> the file method.  What do you see are the advantages of a database  
>> store over the spool store method?
>>
>> I ask this not only from general curiosity but, I have been using  
>> two modems and I have had a couple of cases where, it appears that,  
>> a single sms was sent by both modems and I have wondered if this  
>> was really a duplicated send that was an artifact of the file store  
>> I was using at the time.  If so, does the spool type store prevent  
>> this possibility and/or is this something that this db-store would  
>> prevent also?
>>
>> Thanks,
>>
>> -Tony
>>
>> On Tue, Feb 3, 2009 at 4:24 AM, Alejandro Guerrieri <[email protected] 
>> > wrote:
>> Did anyone had a chance to check on this?
>>
>>
>> Regards,
>> --
>> Alejandro Guerrieri
>> [email protected]
>>
>>
>>
>> On 30/01/2009, at 0:27, Alejandro Guerrieri wrote:
>>
>> Hi,
>>
>> Proposed patch is a revised version from my previous patch to add  
>> support for store-type = mysql
>>
>> I've modified my previous patch aiming at supporting other DB types  
>> for storage (not implemented yet). I've also added automatic table  
>> creation at startup.
>>
>> Please check further details and download the patches here:
>>
>> http://www.blogalex.com/archives/88
>>
>> Regards,
>> --
>> Alejandro Guerrieri
>> [email protected]
>>
>>
>>
>>
>>
>>
>>
>
>