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 <spame...@gmail.com> wrote: > > > > 2015-12-08 12:51 GMT+03:00 Grant Vorenberg <gr...@saicomvoice.co.za > <mailto:gr...@saicomvoice.co.za>>: > <saicom-voice-services.gif> > <https://branding.synaq.com/api/r/id/22474050/map/0> > 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=XXXX 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 > <http://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 <spame...@gmail.com >> <mailto:spame...@gmail.com>> 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 <mailto:gr...@saicomvoice.co.za> >> Visit our website www.saicomvoice.co.za <http://www.saicomvoice.co.za/> >> >> <logo-image.png> <http://www.saicomvoice.co.za/> >> >> 2015-12-08 11:23 GMT+03:00 Grant Vorenberg <gr...@saicomvoice.co.za >> <mailto:gr...@saicomvoice.co.za>>: >> <saicom-now-offers-cloud-pbx.gif> >> <https://branding.synaq.com/api/r/id/22469522/map/0> >> >> 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 = false >> sql-log-table = sent_sms >> sql-insert-table = send_sms >> log-file = "/var/log/kannel/kannel-sqlbox.log" >> log-level = 0 >> #sqlbox optimisation GAV 20151207 >> limit-per-cycle = 100 >> >> >> Try monitoring your database workload. >> >> Is send_sms table is big? >> >> >>> On 07 Dec 2015, at 15:06, spameden <spame...@gmail.com >>> <mailto:spame...@gmail.com>> 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 <mailto:gr...@saicomvoice.co.za> >>> Visit our website www.saicomvoice.co.za <http://www.saicomvoice.co.za/> >>> >>> <logo-image.png> <http://www.saicomvoice.co.za/> >>> 2015-12-07 14:03 GMT+03:00 Grant Vorenberg <gr...@saicomvoice.co.za >>> <mailto:gr...@saicomvoice.co.za>>: >>> <saicom-now-offers-cloud-pbx.gif> >>> <https://branding.synaq.com/api/r/id/22451334/map/0> >>> >>> Hi Guys >>> >>> I am a new to the list subscription and am looking for a little clarity on >>> the SQLBOX. >>> >>> I have a Debian Wheezy box running: >>> Kannel sqlbox version 1.4.4 >>> Libxml version 2.7.2 >>> MySQL 5.5.43 >>> >>> The front end is custom and drops message into the send_sms table. These >>> messages are terminated via smpp to another system of ours. We process and >>> clean out the sent_sms table. >>> >>> I gather stats on the performance of the system using the status page: >>> http://localhost:13000/status <http://localhost:13000/status> >>> >>> I am trying to understand the following output from the screen: >>> Box connections: >>> smsbox:sqlbox, IP 127.0.0.1 (2532 queued), (on-line 0d 2h 48m 19s) >>> >>> This means there is a queue on sqlbox. >>> >>> Queue works like this: >>> >>> First sqlbox gets messages from DB backend, then it adds messages to own >>> queue, then it sends message into bearerbox queue and bearerbox splits this >>> queue over your connected SMSC/upstream operators. >>> >>> So if there is a huge queue on sqlbox it means there is a big amount of MTs >>> in your send_sms table and sqlbox is still submitting those to bearerbox. >>> >>> To optimize sqlbox I'd recommend adding this parameter into sqlbox section >>> (after group = sqlbox): >>> limit-per-cycle = 50 this means sqlbox will get 50 bulk messages from DB >>> per query at once instead of 1. >>> >>> >>> >>> What I noticed is when our send speeds start dipping on the smpp connection >>> (internal/default route), this smsbox:sql queue starts building up. >>> >>> When the smsbox:sqlbox queue starts building up like this: >>> 1)What causes this? >>> 2) What does this signify? >>> >>> We generally don’t see this behaviour very often, but its effect is >>> detrimental to the performance of the system so I would like to know what >>> causes the growth and how to combat it so our send speed is safe-guarded. >>> >>> >>> Here is an excerpt from my config files: >>> >>> <smsbox conf>: >>> group = sqlbox >>> id = sqlbox-db >>> smsbox-id = sqlbox >>> #global-sender = "" >>> bearerbox-host = localhost >>> bearerbox-port = 13001 >>> smsbox-port = 13005 >>> smsbox-port-ssl = false >>> sql-log-table = sent_sms >>> sql-insert-table = send_sms >>> >>> >>> <kannel.conf>: >>> group = smsbox >>> bearerbox-host = 127.0.0.1 >>> sendsms-port = 13013 >>> global-sender = 13013 >>> smsbox-id=my_smsbox >>> >>> group=smsbox-route >>> smsbox-id=sqlbox >>> smsc-id=internal >>> >>> >>> Regards >>> >>> >>> >>> >>> >>> 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 <mailto:gr...@saicomvoice.co.za> >>> Visit our website www.saicomvoice.co.za <http://www.saicomvoice.co.za/> >>> >>> <logo-image.png> <http://www.saicomvoice.co.za/> >
