Answering my own question with big thanks to Alejandro, who already did that!
(I promise I will search better next time before posting) Here is a link to the thread and patch in case anyone interested: http://www.kannel.org/pipermail/devel/2008-February/001597.html http://www.magicom-bcn.net/kannel/sqlbox-standalone-multi-20080227.patch Would be nice to incorporate this into SVN as an option maybe! 2012/10/13 spameden <spame...@gmail.com> > Rene wrote: > >> 1. You can increase the LIMIT without breaking things. This might >> increase speed for large batches. >> > > Actually after looking into *sqlbox_mysql.c* code I noticed even if you > increase the LIMIT, you won't benefit from this, because sqlbox always gets > only first row of the resulting query, proof: > > -----snip from sqlbox_mysql.c----- > 107 sql = octstr_format(SQLBOX_MYSQL_SELECT_QUERY, > sqlbox_insert_table); > 108 #if defined(SQLBOX_TRACE) > 109 debug("SQLBOX", 0, "sql: %s", octstr_get_cstr(sql)); > 110 #endif > 111 res = mysql_select(sql); > 112 if (res == NULL) { > 113 debug("sqlbox", 0, "SQL statement failed: %s", > octstr_get_cstr(sql)); > 114 } > 115 else { > 116 if (mysql_num_rows(res) >= 1) { > *117 row = mysql_fetch_row(res);* > 118 id = octstr_null_create(row[0]); > 119 /* save fields in this row as msg struct */ > 120 msg = msg_create(sms); > ----- > > I'm not sure how to properly inject while loop here without breaking whole > sqlbox architecture, because this function is being called from > *sqlbox.c*code and returned value is a pointer to msg (I guess): > > 604 while (sqlbox_status == SQL_RUNNING && boxc->alive) { > 605 *if ((msg = gw_sql_fetch_msg()) != NULL) {* > 606 if (charset_processing(msg) == -1) { > 607 error(0, "Could not charset process message, dropping > it!"); > 608 msg_destroy(msg); > 609 continue; > 610 } > 611 if (global_sender != NULL && (msg->sms.sender == NULL || > octstr_len(msg->sms.sender) == 0)) { > 612 msg->sms.sender = octstr_duplicate(global_sender); > 613 } > 614 send_msg(boxc->bearerbox_connection, boxc, msg); > > So this is a general thing related to ALL engines used in SQLBox. > > As a fix without breaking much inside the sqlbox architecture, I'd > probably suggest collecting query results into MSG array and return it's > pointer back? > > But potentially it could consume a lot of memory if you'd want to use > LIMIT 0,10000 or maybe even bigger value. > > Also in this scenario, you'd need to loop again through the array to send > every single message via send_msg() function, so probably implementing a > loop inside sqlbox.c file would be a much cleaner solution imho. > > I'm not skilled in C, as *Rene*, so I'm asking for help! > > Please suggest how to properly fix this. > > SQLBox is awesome apart from few things missing, but I've already > implemented them via editing it's source :) > > Another question I had for a long time, how altering > *max-connections*variable affects performance? > > Are connections concurrent? If so how locking is implemented to not get > the same results in same SELECT query? > > Thanks. > > > 2012/7/19 Rene Kluwen <rene.klu...@chimit.nl> > >> Doesn’t really matter. As long as it is different from any other port >> that you are using already.**** >> >> ** ** >> >> *From:* Ashish Agarwal [mailto:ashisha...@gmail.com] >> *Sent:* Thursday, 19 July, 2012 17:54 >> *To:* Rene Kluwen >> *Cc:* users; Eric Turner; spameden >> *Subject:* RE: sqlbox select speed**** >> >> ** ** >> >> What should be the smsbox-port value I should mention in my new sqlbox >> configuration.**** >> >> On Jul 19, 2012 6:27 PM, "Rene Kluwen" <rene.klu...@chimit.nl> wrote:**** >> >> There’s no absolute need for two smsboxes to be connected, as long as you >> set your boxc_id to the smsbox that you have.**** >> >> Having said that, in your case, two smsboxes might improve speed as well. >> **** >> >> **** >> >> == Rene**** >> >> **** >> >> *From:* Ashish Agarwal [mailto:ashisha...@gmail.com] >> *Sent:* Thursday, 19 July, 2012 09:09 >> *To:* Rene Kluwen >> *Cc:* Eric Turner; spameden; users >> *Subject:* Re: sqlbox select speed**** >> >> **** >> >> Hi Rene,**** >> >> **** >> >> Thanks for the response. I have configured two sqlboxes that are reading >> from two different database and that has helped in increasing the overall >> speed. I have not recompiled sqlbox with INSERT DELAY.**** >> >> >> Also, I want an understanding whether if two smsboxes are also necessary >> with two sqlboxes.**** >> >> On Tue, Jul 17, 2012 at 3:37 AM, Rene Kluwen <rene.klu...@chimit.nl> >> wrote:**** >> >> To shed some light in this matter.**** >> >> **** >> >> 1. You can increase the LIMIT without breaking things. This might >> increase speed for large batches.**** >> >> 2. If you want to have 2 (or more) sqlboxes, they can connect to >> the same bearerbox without any problem. But yes, use a separate send_sms >> table for each instance.**** >> >> 3. Adding the DELAYED option to the INSERT query might help. If it >> does help, please post to the list. Possibly we can add it to the SVN >> version of sqlbox.**** >> >> 4. Maybe the problem is not sqlbox itself, but your MySQL that >> takes too long to process your queries. This goes beyond the scope of this >> mailinglist but it is worth checking. From experience I know that running >> the mysql databases on a solid state disk increases speed significantly.* >> *** >> >> **** >> >> **** >> >> == Rene**** >> >> **** >> >> **** >> >> *From:* users-boun...@kannel.org [mailto:users-boun...@kannel.org] *On >> Behalf Of *Eric Turner >> *Sent:* Monday, 16 July, 2012 21:10 >> *To:* spameden >> *Cc:* users >> *Subject:* Re: sqlbox select speed**** >> >> **** >> >> Sounds like it would be safer to have your application insert into two >> different sqlbox tables and then have two different sqlbox instances feed >> the same bearerbox.**** >> >> On Mon, Jul 16, 2012 at 11:07 AM, spameden <spame...@gmail.com> wrote:*** >> * >> >> most likely you didn't optimize mysql >> >> most work sqlbox is being done on INSERT rather than SELECT and DELETE, >> try using INSERT DELAYED instead of INSERT in SQLBOX_MYSQL_INSERT_QUERY in >> gw/sqlbox_mysql.h or optimize your MySQL setup. >> >> try also to test kannel's speed without sqlbox involved at all without >> database, might be a problem on your smsc operator side as well.**** >> >> **** >> >> 2012/7/16 Ashish Agarwal <ashisha...@gmail.com>**** >> >> Hello Eric,**** >> >> Sqlbox selects query uses limit 0,1 where it is selecting only one row at >> a time. So running one more instance on the same database may duplicate the >> record. Does it make sense to change the limit value and recompile sqlbox, >> but I doubt this may not delete the row with relative sqlbox_id after >> selecting. **** >> >> On Jul 16, 2012 8:35 PM, "Eric Turner" <e...@txtwire.com> wrote:**** >> >> I have never done it but I would guess that all you would need to do is >> create a separate instance of sqlbox either on the same computer or >> a separate computer what points to the same bearerbox. **** >> >> **** >> >> Not sure if it is possible. Not sure how smart sqlbox is with two >> sqlboxes reading out of the same table. If it isn't that smart you could >> have two sqlboxes pointing at the same bearerbox but reading from different >> tables and you make your application smart enough to put half of the >> messages in one table and half in the other table. That >> should theoretically work get two sqlboxes feeding the same bearerbox.*** >> * >> >> **** >> >> On Mon, Jul 16, 2012 at 8:37 AM, Ashish Agarwal <ashisha...@gmail.com> >> wrote:**** >> >> Hello Eric,**** >> >> I think adding another sqlbox is a good option but how can I add another >> sqlbox?**** >> >> On Jul 16, 2012 6:37 PM, "Eric Turner" <e...@txtwire.com> wrote:**** >> >> Since it is compiled code. You could either look through the source code >> and see where you could make improvements or you could add a second sqlbox. >> **** >> >> On Sun, Jul 15, 2012 at 2:47 AM, Ashish Agarwal <ashisha...@gmail.com> >> wrote:**** >> >> Hello,**** >> >> **** >> >> With sqlbox I have around 15,00,000 number of record in send_sms table >> and bearerbox is sending sms at approximately 200 to 300 TPS, but bearerbox >> with my smsc has much higher capacity to submit sms to smsc around 400 to >> 500 TPS but since sqlbox is not able to send too many sms to the store of >> bearerbox at a time I am not able to achieve good throughput with my smsc. >> **** >> >> **** >> >> Therefore, is there a way wherein sqlbox can read messages from send_sms >> table at a very high speed so that message can be stored in queue and my >> smsc connections can be utilize to the maximum. **** >> >> **** >> >> Please suggest. >> **** >> >> **** >> >> -- >> Regards, >> >> Ashish Agarwal**** >> >> **** >> >> **** >> >> **** >> >> **** >> >> >> >> **** >> >> **** >> >> -- >> Regards, >> >> Ashish Agarwal**** >> > >
