Re: SQLBOX queued messages question

[spameden]

2015-12-09 12:43 GMT+03:00 Grant Saicom :

> We process the sent_sms into another table on they fly. Maximum size the
> sent_sms table gets is maybe 40k tops but mostly it averages around 10K. We
> see this once 1 week maybe.
>
> I have really made every attempt to remove any bottlenecks in terms
> unwieldy database sizes to allow kannel to work in a favourable environment.
>
> Is there reason to add multiple sqlboxes to feed bearer box?
>
> Is there maybe a concurrency setting I can do for bearer box to receive
> the messages? I did not come across documentation aside from email posts
> with respect to the limit-per-cycle setting.
>
> I have another question, would we be able to get faster performance if we
> went flat file for the kannel operations?
>

Well you can exclude bottlenecks by simply testing same setup with fakesmsc
daemon and see if speed will be better.

It might be that delay is caused by your SMSC uplinks overall speed and not
database.

You can also try classic smsbox implementation for sending instead of
sqlbox. But I think sqlbox is fastest and more convinient way because of DB
storage.




>
> Regards
>
>
> On 08 Dec 2015, at 15:12, spameden  wrote:
>
>
>
> 2015-12-08 12:51 GMT+03:00 Grant Vorenberg :
>
>> 
>> 
>>
>> Hi
>>
>> We manage how big send_sms gets. The queue builder puts in 500 messages
>> at a time to a total maximum of 3000 from a larger main queue which can go
>> as big as 2M.
>>
>
> 2M is kinda big table, how big is sent_sms? 10-30M ?
>
> I think your issue happens when kannel tries to move from send_sms to
> sent_sms table already submitted message this is where it hangs. You can
> try testing it yourself with simple query:
>
> INSERT INTO sent_sms SELECT * from send_sms where sql_id= and measure
> time per query.
>
> if it's instant there should be no problem.
>
> Generally it's better to leave sent_sms table at around 1M records not
> more, old records you can move to other table daily.
>
>
>>
>> Actual hardware is a VCenter on blades with plenty ram, cpu and hp
>> 3PAR(144GB raid card ram for caching in total) fibre attached storage with
>> dedicated SSD specifically for DB. Calculated IOPS are stupidly good.
>>
>> The VMs are as follows:
>> Queuebuilder: 4 vcpu, 16Gb on SAS
>> Kannel: 4 vcpu, 8GB on SAS
>> MysqlDB-Master: 8 vcpu, 32GB on SSD
>> MysqlDB-Slave: 8 vcpu, 32GB on SSD
>>
>
> MySQL on SSDs should work just fine and you should have big number of
> iops. Btw, I recommend to use MariaDB instead of regular MySQL (
> mariadb.org) it's very fast and reliable, for InnoDB it uses modified
> XtraDB engine which has some tweaks.
>
> did you check mysqladmin status to indicate number of queries processed
> per second?
>
>
>> The Load on the MysqlDB-Master averages around 0.4 with max of 0.6
>> (single spike). Memory usage hangs around 24GB. I will need to check the
>> process list to double check, but we generally don’t see much strain here
>> either, but I stand to be corrected.
>>
> DB optimasations as follows:
>>
>> key_buffer   = 16M
>> max_allowed_packet   = 16M
>>
>
> maybe increase a bit max_allowed_packet to 64mb
>
> innodb_buffer_pool_size = 12G
>>
>
> this is fine
>
> query_cache_limit   = 20M
>> query_cache_size = 128M
>>
>
> query_cache in kannel's case doesn't affect much, so it's ok to have it
> like that.
>
>
>>
>> No extra indexes on the send_sms as we limit its size to 3000.
>>
>
> make sure both send_sms and sent_sms tables are InnoDB type.
>
>
>>
>> All reporting is done on the slaveDB, so no extra strain on monitoring
>> and reporting.
>>
>
> under 'reporting' do you mean your dlr_url is called to external script
> which is connected to slaveDB?
>
>
>>
>> Historically, we have queued at the SMPP connector and not at the smsbox.
>> We generally reached a top (AVG) speed of 73 msg/s but when I see the
>> smsbox queued figure rise and the internal smpp queue drop to 0, we only
>> hit half of this speed.
>>
>> I did not see the limit-per-cycle setting in the sqlbox documentation
>> (2011). I also checked the code and saw that the select limit was a
>> variable instead of hard-limited.
>>
>
> Yeah, it was introduced some time ago alongside with other optimisations
> (year ago or so, can't remember now), I think it should be in new
> documentation. However, you need to build documentation to get more recent
> version of it.
>
>
>> Regards
>> G
>>
>> On 08 Dec 2015, at 10:52, spameden  wrote:
>>
>>
>>
>>
>> Grant Vorenberg
>>
>> Technical Manager
>> Office  0861 SAICOM (724 266) Direct  010 140 5012 Support  010 140 5050
>> Cell  - Fax  010 140 5001 Email  gr...@saicomvoice.co.za Visit our
>> website  www.saicomvoice.co.za
>>
>>  
>>
>> 2015-12-08 11:23 GMT+03:00 Grant Vorenberg :
>>
>>> 
>>> 

Actual Working of Kannel using Mysql Database

[Robin C]

Dear Team,

I want to know the exact working behind kannel and
smpp. There is dlr-db and sqlbox group in the kannel configuration. I want
to know what exactly happening when we sent message. What is the role of
 send_sms & sent_sms table in kannel. How messages come to these tables and
what happens after that. Anybody please help me. Thanks in advance :


group = dlr-db
id = mydlr
table = tablename
field-smsc = smsc
field-timestamp = ts
field-destination = destination
field-source = source
field-service = service
field-url = url
field-mask = mask
field-status = status
field-boxc-id = boxc


group = sqlbox
id = sqlbox-db
smsbox-id = smsboxid
bearerbox-port = 14000
smsbox-port = 14001
smsbox-port-ssl = false
sql-log-table = sent_sms
sql-insert-table = send_sms
log-file = "/var/log/kannel/sql.log"
log-level = 4

-- 

   *Thanks & Regards,*



  Robin C

 Linux System Administrator



*ZIN-CRON | Bulk Sms, Voice Calls, Long Codes, Short Codes, Software
Development, Web & Graphic Designing.*


*www.zincron.co.in  | www. easyhops.co.in
 | www.equipe.co.in   *

*Corporate Office: NRA-99, Observatory Lane, Palayam, Trivandrum, Kerala*


*Mobile: +919544861010 *| Phone: 91-471- 4140414 |  Fax : 91-471-4010413 |
Raise Your Ticket: zincron-ticketing.com


Connect with us @ Facebook

 | LinkedIn


Disclaimer:  This message contains confidential information and is intended
only for the individual named.  If you are not the named addressee you
should not disseminate, distribute or copy this e-mail.  Please notify the
sender immediately by e-mail if you have received this e-mail by mistake
and delete this e-mail from your system.  E-mail transmission cannot be
guaranteed to be secure or error-free as information could be intercepted,
corrupted, lost, destroyed, arrive late or incomplete, or contain
viruses.  The sender therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a result of
e-mail transmission.  If verification is required please request a
hard-copy version.

*7* Switch off as you go |*q *Recycle always | P Print only if absolutely
necessary

Re: SQLBOX queued messages question

[Grant Saicom]

We process the sent_sms into another table on they fly. Maximum size the 
sent_sms table gets is maybe 40k tops but mostly it averages around 10K. We see 
this once 1 week maybe.

I have really made every attempt to remove any bottlenecks in terms unwieldy 
database sizes to allow kannel to work in a favourable environment.

Is there reason to add multiple sqlboxes to feed bearer box?

Is there maybe a concurrency setting I can do for bearer box to receive the 
messages? I did not come across documentation aside from email posts with 
respect to the limit-per-cycle setting.

I have another question, would we be able to get faster performance if we went 
flat file for the kannel operations?

Regards


> On 08 Dec 2015, at 15:12, spameden  wrote:
> 
> 
> 
> 2015-12-08 12:51 GMT+03:00 Grant Vorenberg  >:
>  
> 
> Hi
> 
> We manage how big send_sms gets. The queue builder puts in 500 messages at a 
> time to a total maximum of 3000 from a larger main queue which can go as big 
> as 2M.
> 
> 2M is kinda big table, how big is sent_sms? 10-30M ?
> 
> I think your issue happens when kannel tries to move from send_sms to 
> sent_sms table already submitted message this is where it hangs. You can try 
> testing it yourself with simple query:
> 
> INSERT INTO sent_sms SELECT * from send_sms where sql_id= and measure 
> time per query.
> 
> if it's instant there should be no problem.
> 
> Generally it's better to leave sent_sms table at around 1M records not more, 
> old records you can move to other table daily.
>  
> 
> Actual hardware is a VCenter on blades with plenty ram, cpu and hp 3PAR(144GB 
> raid card ram for caching in total) fibre attached storage with dedicated SSD 
> specifically for DB. Calculated IOPS are stupidly good.
> 
> The VMs are as follows:
> Queuebuilder: 4 vcpu, 16Gb on SAS
> Kannel: 4 vcpu, 8GB on SAS
> MysqlDB-Master: 8 vcpu, 32GB on SSD
> MysqlDB-Slave: 8 vcpu, 32GB on SSD
> 
> MySQL on SSDs should work just fine and you should have big number of iops. 
> Btw, I recommend to use MariaDB instead of regular MySQL (mariadb.org 
> ) it's very fast and reliable, for InnoDB it uses 
> modified XtraDB engine which has some tweaks.
> 
> did you check mysqladmin status to indicate number of queries processed per 
> second?
> 
> 
> The Load on the MysqlDB-Master averages around 0.4 with max of 0.6 (single 
> spike). Memory usage hangs around 24GB. I will need to check the process list 
> to double check, but we generally don’t see much strain here either, but I 
> stand to be corrected. 
> DB optimasations as follows:
> 
> key_buffer  
> = 16M
> max_allowed_packet  
> = 16M
> 
> maybe increase a bit max_allowed_packet to 64mb
> 
> innodb_buffer_pool_size 
> = 12G
>  
> this is fine
> 
> query_cache_limit   
> = 20M
> query_cache_size
> = 128M
> 
> query_cache in kannel's case doesn't affect much, so it's ok to have it like 
> that.
>  
> 
> No extra indexes on the send_sms as we limit its size to 3000.
> 
> make sure both send_sms and sent_sms tables are InnoDB type.
>  
> 
> All reporting is done on the slaveDB, so no extra strain on monitoring and 
> reporting.
> 
> under 'reporting' do you mean your dlr_url is called to external script which 
> is connected to slaveDB?
>  
> 
> Historically, we have queued at the SMPP connector and not at the smsbox. We 
> generally reached a top (AVG) speed of 73 msg/s but when I see the smsbox 
> queued figure rise and the internal smpp queue drop to 0, we only hit half of 
> this speed.
> 
> I did not see the limit-per-cycle setting in the sqlbox documentation (2011). 
> I also checked the code and saw that the select limit was a variable instead 
> of hard-limited.
> 
> Yeah, it was introduced some time ago alongside with other optimisations 
> (year ago or so, can't remember now), I think it should be in new 
> documentation. However, you need to build documentation to get more recent 
> version of it.
> 
> 
> Regards
> G
> 
>> On 08 Dec 2015, at 10:52, spameden > > wrote:
>> 
>> 
>> 
>> 
>> Grant Vorenberg
>> Technical Manager
>> Office   0861 SAICOM (724 266)
>> Direct   010 140 5012
>> Support  010 140 5050
>> Cell -
>> Fax  010 140 5001
>> Emailgr...@saicomvoice.co.za 
>> Visit our websitewww.saicomvoice.co.za 
>> 
>>   
>> 
>> 2015-12-08 11:23 GMT+03:00 Grant Vorenberg > >:
>>  
>> 
>> 
>> Here is my config:
>> 
>> group = sqlbox
>> id = sqlbox-db
>> smsbox-id = sqlbox
>> #global-sender = ""
>> bearerbox-host = localhost
>> bearerbox-port = 13001
>> smsbox-port = 13005
>> smsbox-port-ssl =