In addition, triggers, usually, are like stored procedures in its context and at times, might store some good portion of your business logic within the database layer which makes them less visible to someone who's evaluating a particular high-level business use-case just by looking at some code level implementation. So, they usually go unnoticed, I would say, a lot more than it is with a stored procedure. So, IMO, it would be good if we can avoid them whenever possible.
Cheers, Prabath On Fri, Sep 12, 2014 at 1:21 PM, Asitha Nanayakkara <[email protected]> wrote: > According to an offline chat I had with PrabathA SQL triggers should be > avoided since SQL triggers execute outside SQL transactions. > > On Thu, Jul 24, 2014 at 6:30 PM, Hasitha Hiranya <[email protected]> > wrote: > >> Hi, >> >> According to mail arch@ "Removing Global Queue from MB", maybe we need >> to update this design. >> Better we discuss upfront if so. >> >> It will bring changes to messageStore interface. >> >> Thanks >> >> >> On Thu, Jul 24, 2014 at 3:04 PM, Asitha Nanayakkara <[email protected]> >> wrote: >> >>> With the addition of message expiration feature RDBMS design for >>> Metadata in MB needs to be changed. >>> Updated design is as follows. >>> >>> >>> >>> >>> * Design considerations* >>> >>> - Only a subset of messages comes with message expiration and a >>> separate thread handles deletion of expired messages. >>> - Periodically expired message deletion thread queries for a chunk >>> of expired messages. The subset of messages to go through would be less >>> when a separated Expiration table is used. However when deleting a >>> message >>> from Expiration table should trigger a delete of all the messages with >>> the >>> respective message_id from Metadata table. >>> >>> In addition, to update reference count of messages an SQL trigger will >>> be created. Is there any performance hit using triggers? >>> >>> >>> >>> >>> On Thu, Jul 24, 2014 at 12:06 PM, Asitha Nanayakkara <[email protected]> >>> wrote: >>> >>>> With current design in memory message store will be used only in single >>>> node mode. >>>> >>>> >>>> On Wed, Jul 23, 2014 at 11:36 AM, Dhanuka Ranasinghe <[email protected]> >>>> wrote: >>>> >>>>> Also, normally publisher mention whether to persist or not messages in >>>>> message itself (delivery mode). So based on that MB will process messages >>>>> in memory and/or persist to a persistence store. So if it's process in >>>>> memory How do we communicate through the MB cluster? >>>>> >>>>> >>>>> http://activemq.apache.org/what-is-the-difference-between-persistent-and-non-persistent-delivery.html >>>>> >>>>> >>>>> *Dhanuka Ranasinghe* >>>>> >>>>> Senior Software Engineer >>>>> WSO2 Inc. ; http://wso2.com >>>>> lean . enterprise . middleware >>>>> >>>>> phone : +94 715381915 >>>>> >>>>> >>>>> On Mon, Jul 21, 2014 at 12:36 PM, Dhanuka Ranasinghe <[email protected] >>>>> > wrote: >>>>> >>>>>> IMO, If we gonna keep huge messages as chunks in memory and insert >>>>>> into DB as bulk it will heavily affect on MB heap memory. My suggestion >>>>>> is >>>>>> we need to handle this case by case. For example, if it's small messages >>>>>> it >>>>>> will be efficient to keep in memory while huge messages it will be >>>>>> efficient to insert into DB early as possible and let others to use heap >>>>>> memory. For this we will have to make this functionality more >>>>>> configurable >>>>>> but again we will have to think about how gonna support fail over >>>>>> (probably >>>>>> have to change db schema). >>>>>> >>>>>> *Dhanuka Ranasinghe* >>>>>> >>>>>> Senior Software Engineer >>>>>> WSO2 Inc. ; http://wso2.com >>>>>> lean . enterprise . middleware >>>>>> >>>>>> phone : +94 715381915 >>>>>> >>>>>> >>>>>> On Mon, Jul 21, 2014 at 9:00 AM, Asitha Nanayakkara <[email protected]> >>>>>> wrote: >>>>>> >>>>>>> We are planning to insert message chunks as batch insert queries. >>>>>>> >>>>>>> >>>>>>> On Sat, Jul 19, 2014 at 11:00 AM, Dhanuka Ranasinghe < >>>>>>> [email protected]> wrote: >>>>>>> >>>>>>>> Are we going to insert whole message or as chunks >>>>>>>> On 18 Jul 2014 18:06, "Asitha Nanayakkara" <[email protected]> wrote: >>>>>>>> >>>>>>>>> Hi, >>>>>>>>> >>>>>>>>> Following is the RDBMS design for WSO2 MB 3.0.0 >>>>>>>>> >>>>>>>>> Messages model >>>>>>>>> >>>>>>>>> Message metadata model >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> Following are the concerns came across in the discussion >>>>>>>>> >>>>>>>>> *- Why we use reference counting for message meta data?* >>>>>>>>> >>>>>>>>> Reference counting is needed to delete topic messages from the >>>>>>>>> database reliably in a cluster deployment >>>>>>>>> >>>>>>>>> *- How to manage a large tables like Messages table?* >>>>>>>>> >>>>>>>>> for Messages table use database partitioning >>>>>>>>> >>>>>>>>> For Metadata queries there will be no SQL joins, hence even if the >>>>>>>>> table would grow large that won't be an issue. >>>>>>>>> >>>>>>>>> Inserts and delete operation can be done as batch operations. >>>>>>>>> >>>>>>>>> *- Following option to save metadata was rejected due to following >>>>>>>>> reasons* >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> In the above design MB instance will create Node_Queue tables and >>>>>>>>> Topic_Node_Queue >>>>>>>>> tables when each node connects to a cluster. This design was >>>>>>>>> rejected due to following reasons. >>>>>>>>> It's DB admins tasks to create and delete tables. MB should not >>>>>>>>> modify schema when joining to the cluster. There will be instances >>>>>>>>> where MB >>>>>>>>> users might not have privileges to create tables. >>>>>>>>> >>>>>>>>> *- Supporting several SQL implementations.* >>>>>>>>> >>>>>>>>> Since we are using simple SQL operations those will not become an >>>>>>>>> issue. >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> Thanks >>>>>>>>> -- >>>>>>>>> *Asitha Nanayakkara* >>>>>>>>> Software Engineer >>>>>>>>> WSO2, Inc. http://wso2.com/ >>>>>>>>> Mob: + 94 77 85 30 682 >>>>>>>>> >>>>>>>>> >>>>>>>>> _______________________________________________ >>>>>>>>> Architecture mailing list >>>>>>>>> [email protected] >>>>>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>>>>>> >>>>>>>>> >>>>>>>> _______________________________________________ >>>>>>>> Architecture mailing list >>>>>>>> [email protected] >>>>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> *Asitha Nanayakkara* >>>>>>> Software Engineer >>>>>>> WSO2, Inc. http://wso2.com/ >>>>>>> Mob: + 94 77 85 30 682 >>>>>>> >>>>>>> >>>>>>> _______________________________________________ >>>>>>> Architecture mailing list >>>>>>> [email protected] >>>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>>>> >>>>>>> >>>>>> >>>>> >>>>> _______________________________________________ >>>>> Architecture mailing list >>>>> [email protected] >>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>> >>>>> >>>> >>>> >>>> -- >>>> *Asitha Nanayakkara* >>>> Software Engineer >>>> WSO2, Inc. http://wso2.com/ >>>> Mob: + 94 77 85 30 682 >>>> >>>> >>> >>> >>> -- >>> *Asitha Nanayakkara* >>> Software Engineer >>> WSO2, Inc. http://wso2.com/ >>> Mob: + 94 77 85 30 682 >>> >>> >>> _______________________________________________ >>> Architecture mailing list >>> [email protected] >>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>> >>> >> >> >> -- >> *Hasitha Abeykoon* >> Senior Software Engineer; WSO2, Inc.; http://wso2.com >> *cell:* *+94 719363063* >> *blog: **abeykoon.blogspot.com* <http://abeykoon.blogspot.com> >> >> >> _______________________________________________ >> Architecture mailing list >> [email protected] >> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >> >> > > > -- > *Asitha Nanayakkara* > Software Engineer > WSO2, Inc. http://wso2.com/ > Mob: + 94 77 85 30 682 > > -- Prabath Abeysekara Associate Technical Lead, Data TG. WSO2 Inc. Email: [email protected] Mobile: +94774171471
_______________________________________________ Architecture mailing list [email protected] https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
