Re: [PATCH] DB-Based Store

Hi Alej,

I agree with you on most points, just a few clarifications and hopefully exhaust it:

1) Parachute is mentioned because it can take the message queue from another bearerbox process and process it. This is only a proof of concept that spool contains all necessary information for a faliover service (reliability). Impimentation will require at least an additional server.

2) Good to know that Mysql has implemented row locking with InnoDB. However, reports from the field indicate that overall performance has dropped significantly due to the archive logs (not indexes or otherwise).

3) We are not talking about backups. With backups you have already lost 24 hrs worth data (depending on frequency and time of failure), and a backup is equivalent whether for FS or DB. We are talking of actually salvaging data from the failed media. You can salvage some if not most of the FS partition, but if you crash your archive logs partition, you cannot talk about recovery unless you salvage 100% of those. That's the DB disadvantage.

In general I find FS and DBs quite similar. Both of them are data repositories, and both of them use indexes (called directories in FS). There are also several utilities (see htdig, ctags) that can index an FS. I see them as the same thing, the one being the logical extension of the other. All depends in the case at hand.

BR,
Nikos
  ----- Original Message ----- 
  From: Alejandro Guerrieri 
  To: Nikos Balkanas 
  Cc: Tony Kirkham ; Kannel Devel 
  Sent: Wednesday, February 04, 2009 2:53 PM
  Subject: 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]