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****
>
