2015-12-08 12:51 GMT+03:00 Grant Vorenberg <gr...@saicomvoice.co.za>:
> [image: Saicom-Voice-Services] > <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) 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> 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 > > [image: logo image] <http://www.saicomvoice.co.za> > > 2015-12-08 11:23 GMT+03:00 Grant Vorenberg <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> 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 >> >> <logo-image.png> <http://www.saicomvoice.co.za/> >> >> 2015-12-07 14:03 GMT+03:00 Grant Vorenberg <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 >>> >>> 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 Visit >>> our website www.saicomvoice.co.za >>> >>> <logo-image.png> <http://www.saicomvoice.co.za/> >>> >> >
