Re: Big problem with sqlbox
After I insert phone numbers into send_sms table, bearerbox-smsbox-sqlbox starts a lot of Apache processes that essentially increasing RAM. Conclusion: More Phone numbers into send_sms table more Apache processes are started and more memory are used. I increased my server RAM from 250MB to 1GB and now I can insert 100SMS into send_sms table at once. What do you think about this situation? On 15.11.2013 15:21, spameden wrote: 2013/11/15 dorin dorinafte...@mail.ru mailto:dorinafte...@mail.ru you are right! in OOM-killer list is Mysql and Apache. As I understand, I must increase urgent my RAM. As Rene pointed out before it has nothing to do with kannel at all. And I'd suggest avoid using Apache at all on VPS environment, stick to nginx+php-fpm if you need php functionality. For kannel sending service you'd need 1-2GB of memory depends really on your setup and amounts of messages staying in a kannel's internal queue. On 15.11.2013 10:39, spameden wrote: Check your dmesg as well as syslog entries, mysqld might be killed by OOM-killer. 2013/11/15 dorin dorinafte...@mail.ru mailto:dorinafte...@mail.ru: How many entries are there typically in that table? I have a cron that insert's every 1 min 10 SMS - it works fine. If I increase for ex. 50 SMS inserts every 1 min - time by time mysql loses connection for a sec maximum. but if I increase 100SMS per 1min - 100% mysql loses connection for a small time or for some seconds. In this time I lose the incoming data. But realy 10 SMS or 100SMS per second is a very small value. When I sending distribution SMS, processor shows me medium loaded value: 40%; RAM = 60% - 65% Tomorrow want to change MySql version. now it's mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (i486) using readline 6.1 if the problem will persist, I will install MariaDB On 15.11.2013 9:57, Rene Kluwen wrote: The index on 'id' won't help you a lot. How many entries are there typically in that table? Having said that... sqlbox doesn't use the dlr table at all. So it's not your problem. == Rene -Original Message- From: users [mailto:users-boun...@kannel.org mailto:users-boun...@kannel.org] On Behalf Of dorin Sent: donderdag 14 november 2013 12:12 To: spameden Cc: users@kannel.org mailto:users@kannel.org Subject: Re: Big problem with sqlbox Thanks spameden for your reply first of all what's the kannel/sqlbox version you're running? I'm using kannel - 1.5 and sqlbox - I'm not really sure, I think 0.7; you might need to tweak some settings. Really I had set MyIsam all kannel tables. I have changed from MyIsam to InnoDb. Will familiarize soon with MariaDB too. Do you mean only these two tweaks? check indexes on dlr table as well.I have indexed id column. Here is my dlr table CREATE TABLE IF NOT EXISTS `kannel_dlr`( `id` INT(11) NOT NULL AUTO_INCREMENT, `smsc` VARCHAR(40) DEFAULT NULL, `ts` VARCHAR(40) DEFAULT NULL, `destination` VARCHAR(40) DEFAULT NULL, `source` VARCHAR(40) DEFAULT NULL, `service` VARCHAR(40) NOT NULL, `url` VARCHAR(255) DEFAULT NULL, `mask` INT(10) DEFAULT NULL, `status` VARCHAR(40) DEFAULT NULL, `boxc` VARCHAR(40) DEFAULT NULL, `CampaignID` INT(11) NOT NULL, `RelOwnerUserID` INT(11) NOT NULL, `RelOperatorID` INT(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), UNIQUE INDEX UK_oempro_kannel_dlr_id (id) ) ENGINE = INNODB DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1; On 14.11.2013 12:28, spameden wrote: first of all what's the kannel/sqlbox version you're running? check your mysql setup, you might need to tweak some settings. i'm strongly suggest using InnoDB instead of MyISAM, also take a look at MariaDB (it's a fork of mysql, very nicely
Re: Big problem with sqlbox
Either tune Apache2 according to your needs or (better) use nginx + php-fpm. nginx + php-fpm both consumes much less memory than apache2. also you can avoid at all using webserver (calling dlr-url) if you tweak sqlbox source or define a trigger in database to update relevant tables with needed information. 2013/11/16 dorin dorinafte...@mail.ru After I insert phone numbers into send_sms table, bearerbox-smsbox-sqlbox starts a lot of Apache processes that essentially increasing RAM. Conclusion: More Phone numbers into send_sms table more Apache processes are started and more memory are used. I increased my server RAM from 250MB to 1GB and now I can insert 100SMS into send_sms table at once. What do you think about this situation? On 15.11.2013 15:21, spameden wrote: 2013/11/15 dorin dorinafte...@mail.ru you are right! in OOM-killer list is Mysql and Apache. As I understand, I must increase urgent my RAM. As Rene pointed out before it has nothing to do with kannel at all. And I'd suggest avoid using Apache at all on VPS environment, stick to nginx+php-fpm if you need php functionality. For kannel sending service you'd need 1-2GB of memory depends really on your setup and amounts of messages staying in a kannel's internal queue. On 15.11.2013 10:39, spameden wrote: Check your dmesg as well as syslog entries, mysqld might be killed by OOM-killer. 2013/11/15 dorin dorinafte...@mail.ru: How many entries are there typically in that table? I have a cron that insert's every 1 min 10 SMS - it works fine. If I increase for ex. 50 SMS inserts every 1 min - time by time mysql loses connection for a sec maximum. but if I increase 100SMS per 1min - 100% mysql loses connection for a small time or for some seconds. In this time I lose the incoming data. But realy 10 SMS or 100SMS per second is a very small value. When I sending distribution SMS, processor shows me medium loaded value: 40%; RAM = 60% - 65% Tomorrow want to change MySql version. now it's mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (i486) using readline 6.1 if the problem will persist, I will install MariaDB On 15.11.2013 9:57, Rene Kluwen wrote: The index on 'id' won't help you a lot. How many entries are there typically in that table? Having said that... sqlbox doesn't use the dlr table at all. So it's not your problem. == Rene -Original Message- From: users [mailto:users-boun...@kannel.org] On Behalf Of dorin Sent: donderdag 14 november 2013 12:12 To: spameden Cc: users@kannel.org Subject: Re: Big problem with sqlbox Thanks spameden for your reply first of all what's the kannel/sqlbox version you're running? I'm using kannel - 1.5 and sqlbox - I'm not really sure, I think 0.7; you might need to tweak some settings. Really I had set MyIsam all kannel tables. I have changed from MyIsam to InnoDb. Will familiarize soon with MariaDB too. Do you mean only these two tweaks? check indexes on dlr table as well.I have indexed id column. Here is my dlr table CREATE TABLE IF NOT EXISTS `kannel_dlr`( `id` INT(11) NOT NULL AUTO_INCREMENT, `smsc` VARCHAR(40) DEFAULT NULL, `ts` VARCHAR(40) DEFAULT NULL, `destination` VARCHAR(40) DEFAULT NULL, `source` VARCHAR(40) DEFAULT NULL, `service` VARCHAR(40) NOT NULL, `url` VARCHAR(255) DEFAULT NULL, `mask` INT(10) DEFAULT NULL, `status` VARCHAR(40) DEFAULT NULL, `boxc` VARCHAR(40) DEFAULT NULL, `CampaignID` INT(11) NOT NULL, `RelOwnerUserID` INT(11) NOT NULL, `RelOperatorID` INT(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), UNIQUE INDEX UK_oempro_kannel_dlr_id (id) ) ENGINE = INNODB DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1; On 14.11.2013 12:28, spameden wrote: first of all what's the kannel/sqlbox version you're running? check your mysql setup, you might need to tweak some settings. i'm strongly suggest using InnoDB instead of MyISAM, also take a look at MariaDB (it's a fork of mysql, very nicely working). check indexes on dlr table as well. we've been using kannel to send about millions of sms messages daily (MT) without any problems. 2013/11/14 dorin dorinafte...@mail.ru: Hello all! I have a big problem ! I need to send via KANNEL 5000 of SMS. When I insert into table kannel_sqlbox_send_sms every minute 10 SMS - all works fine. But this method is very slow If I try to increase inserting SMS, for example (100SMS) MySql stops working and I got this error: MySQL Error: MySql Error: Couldn't connect to database - Lost connection to MySQL server at 'reading initial communication packet', system error: 111 In ideal I wish to do one insert with 5000 rows Memory,Proccessor is not loaded. My configuration is SMSBox+SQLBox+BearerBox; Please give me some Ideas!
Re: Big problem with sqlbox
also you can avoid at all using webserver (calling dlr-url) if you tweak sqlbox source or define a trigger in database to update relevant tables with needed information. I can't catch the idea, Will be helpful for me if you will describe in detail what do you mean. On 16.11.2013 23:10, spameden wrote: Either tune Apache2 according to your needs or (better) use nginx + php-fpm. nginx + php-fpm both consumes much less memory than apache2. also you can avoid at all using webserver (calling dlr-url) if you tweak sqlbox source or define a trigger in database to update relevant tables with needed information. 2013/11/16 dorin dorinafte...@mail.ru mailto:dorinafte...@mail.ru After I insert phone numbers into send_sms table, bearerbox-smsbox-sqlbox starts a lot of Apache processes that essentially increasing RAM. Conclusion: More Phone numbers into send_sms table more Apache processes are started and more memory are used. I increased my server RAM from 250MB to 1GB and now I can insert 100SMS into send_sms table at once. What do you think about this situation? On 15.11.2013 15:21, spameden wrote: 2013/11/15 dorin dorinafte...@mail.ru mailto:dorinafte...@mail.ru you are right! in OOM-killer list is Mysql and Apache. As I understand, I must increase urgent my RAM. As Rene pointed out before it has nothing to do with kannel at all. And I'd suggest avoid using Apache at all on VPS environment, stick to nginx+php-fpm if you need php functionality. For kannel sending service you'd need 1-2GB of memory depends really on your setup and amounts of messages staying in a kannel's internal queue. On 15.11.2013 10:39, spameden wrote: Check your dmesg as well as syslog entries, mysqld might be killed by OOM-killer. 2013/11/15 dorin dorinafte...@mail.ru mailto:dorinafte...@mail.ru: How many entries are there typically in that table? I have a cron that insert's every 1 min 10 SMS - it works fine. If I increase for ex. 50 SMS inserts every 1 min - time by time mysql loses connection for a sec maximum. but if I increase 100SMS per 1min - 100% mysql loses connection for a small time or for some seconds. In this time I lose the incoming data. But realy 10 SMS or 100SMS per second is a very small value. When I sending distribution SMS, processor shows me medium loaded value: 40%; RAM = 60% - 65% Tomorrow want to change MySql version. now it's mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (i486) using readline 6.1 if the problem will persist, I will install MariaDB On 15.11.2013 9:57, Rene Kluwen wrote: The index on 'id' won't help you a lot. How many entries are there typically in that table? Having said that... sqlbox doesn't use the dlr table at all. So it's not your problem. == Rene -Original Message- From: users [mailto:users-boun...@kannel.org mailto:users-boun...@kannel.org] On Behalf Of dorin Sent: donderdag 14 november 2013 12:12 To: spameden Cc: users@kannel.org mailto:users@kannel.org Subject: Re: Big problem with sqlbox Thanks spameden for your reply first of all what's the kannel/sqlbox version you're running? I'm using kannel - 1.5 and sqlbox - I'm not really sure, I think 0.7; you might need to tweak some settings. Really I had set MyIsam all kannel tables. I have changed from MyIsam to InnoDb. Will familiarize soon with MariaDB too. Do you mean only these two tweaks? check indexes on dlr table as well.I have indexed id column. Here is my dlr table CREATE TABLE IF NOT EXISTS `kannel_dlr`( `id` INT(11) NOT NULL AUTO_INCREMENT, `smsc` VARCHAR(40) DEFAULT NULL, `ts` VARCHAR(40) DEFAULT NULL, `destination` VARCHAR(40) DEFAULT NULL, `source` VARCHAR(40) DEFAULT NULL, `service` VARCHAR(40) NOT NULL, `url` VARCHAR(255) DEFAULT NULL
Re: Big problem with sqlbox
Quoting Rene: This post explains about using mysql triggers to keep track of dlr reports without the need of smsbox or http calls: http://www.blogalex.com/archives/322 Special thanks to Alejandro Guerrieri for letting me guest-blog on his blog. 2013/11/17 dorin dorinafte...@mail.ru also you can avoid at all using webserver (calling dlr-url) if you tweak sqlbox source or define a trigger in database to update relevant tables with needed information. I can't catch the idea, Will be helpful for me if you will describe in detail what do you mean. On 16.11.2013 23:10, spameden wrote: Either tune Apache2 according to your needs or (better) use nginx + php-fpm. nginx + php-fpm both consumes much less memory than apache2. also you can avoid at all using webserver (calling dlr-url) if you tweak sqlbox source or define a trigger in database to update relevant tables with needed information. 2013/11/16 dorin dorinafte...@mail.ru After I insert phone numbers into send_sms table, bearerbox-smsbox-sqlbox starts a lot of Apache processes that essentially increasing RAM. Conclusion: More Phone numbers into send_sms table more Apache processes are started and more memory are used. I increased my server RAM from 250MB to 1GB and now I can insert 100SMS into send_sms table at once. What do you think about this situation? On 15.11.2013 15:21, spameden wrote: 2013/11/15 dorin dorinafte...@mail.ru you are right! in OOM-killer list is Mysql and Apache. As I understand, I must increase urgent my RAM. As Rene pointed out before it has nothing to do with kannel at all. And I'd suggest avoid using Apache at all on VPS environment, stick to nginx+php-fpm if you need php functionality. For kannel sending service you'd need 1-2GB of memory depends really on your setup and amounts of messages staying in a kannel's internal queue. On 15.11.2013 10:39, spameden wrote: Check your dmesg as well as syslog entries, mysqld might be killed by OOM-killer. 2013/11/15 dorin dorinafte...@mail.ru: How many entries are there typically in that table? I have a cron that insert's every 1 min 10 SMS - it works fine. If I increase for ex. 50 SMS inserts every 1 min - time by time mysql loses connection for a sec maximum. but if I increase 100SMS per 1min - 100% mysql loses connection for a small time or for some seconds. In this time I lose the incoming data. But realy 10 SMS or 100SMS per second is a very small value. When I sending distribution SMS, processor shows me medium loaded value: 40%; RAM = 60% - 65% Tomorrow want to change MySql version. now it's mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (i486) using readline 6.1 if the problem will persist, I will install MariaDB On 15.11.2013 9:57, Rene Kluwen wrote: The index on 'id' won't help you a lot. How many entries are there typically in that table? Having said that... sqlbox doesn't use the dlr table at all. So it's not your problem. == Rene -Original Message- From: users [mailto:users-boun...@kannel.org] On Behalf Of dorin Sent: donderdag 14 november 2013 12:12 To: spameden Cc: users@kannel.org Subject: Re: Big problem with sqlbox Thanks spameden for your reply first of all what's the kannel/sqlbox version you're running? I'm using kannel - 1.5 and sqlbox - I'm not really sure, I think 0.7; you might need to tweak some settings. Really I had set MyIsam all kannel tables. I have changed from MyIsam to InnoDb. Will familiarize soon with MariaDB too. Do you mean only these two tweaks? check indexes on dlr table as well.I have indexed id column. Here is my dlr table CREATE TABLE IF NOT EXISTS `kannel_dlr`( `id` INT(11) NOT NULL AUTO_INCREMENT, `smsc` VARCHAR(40) DEFAULT NULL, `ts` VARCHAR(40) DEFAULT NULL, `destination` VARCHAR(40) DEFAULT NULL, `source` VARCHAR(40) DEFAULT NULL, `service` VARCHAR(40) NOT NULL, `url` VARCHAR(255) DEFAULT NULL, `mask` INT(10) DEFAULT NULL, `status` VARCHAR(40) DEFAULT NULL, `boxc` VARCHAR(40) DEFAULT NULL, `CampaignID` INT(11) NOT NULL, `RelOwnerUserID` INT(11) NOT NULL, `RelOperatorID` INT(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), UNIQUE INDEX UK_oempro_kannel_dlr_id (id) ) ENGINE = INNODB DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1; On 14.11.2013 12:28, spameden wrote: first of all what's the kannel/sqlbox version you're running? check your mysql setup, you might need to tweak some settings. i'm strongly suggest using InnoDB instead of MyISAM, also take a look at MariaDB (it's a fork of mysql, very nicely working). check indexes on dlr table as well. we've been using kannel to send about millions of sms messages daily (MT) without any problems. 2013/11/14 dorin dorinafte...@mail.ru: Hello all! I have a big problem ! I need to send via KANNEL 5000 of SMS. When I insert
Re: Big problem with sqlbox
How many entries are there typically in that table? I have a cron that insert's every 1 min 10 SMS - it works fine. If I increase for ex. 50 SMS inserts every 1 min - time by time mysql loses connection for a sec maximum. but if I increase 100SMS per 1min - 100% mysql loses connection for a small time or for some seconds. In this time I lose the incoming data. But realy 10 SMS or 100SMS per second is a very small value. When I sending distribution SMS, processor shows me medium loaded value: 40%; RAM = 60% - 65% Tomorrow want to change MySql version. now it's mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (i486) using readline 6.1 if the problem will persist, I will install MariaDB On 15.11.2013 9:57, Rene Kluwen wrote: The index on 'id' won't help you a lot. How many entries are there typically in that table? Having said that... sqlbox doesn't use the dlr table at all. So it's not your problem. == Rene -Original Message- From: users [mailto:users-boun...@kannel.org] On Behalf Of dorin Sent: donderdag 14 november 2013 12:12 To: spameden Cc: users@kannel.org Subject: Re: Big problem with sqlbox Thanks spameden for your reply first of all what's the kannel/sqlbox version you're running? I'm using kannel - 1.5 and sqlbox - I'm not really sure, I think 0.7; you might need to tweak some settings. Really I had set MyIsam all kannel tables. I have changed from MyIsam to InnoDb. Will familiarize soon with MariaDB too. Do you mean only these two tweaks? check indexes on dlr table as well.I have indexed id column. Here is my dlr table CREATE TABLE IF NOT EXISTS `kannel_dlr`( `id` INT(11) NOT NULL AUTO_INCREMENT, `smsc` VARCHAR(40) DEFAULT NULL, `ts` VARCHAR(40) DEFAULT NULL, `destination` VARCHAR(40) DEFAULT NULL, `source` VARCHAR(40) DEFAULT NULL, `service` VARCHAR(40) NOT NULL, `url` VARCHAR(255) DEFAULT NULL, `mask` INT(10) DEFAULT NULL, `status` VARCHAR(40) DEFAULT NULL, `boxc` VARCHAR(40) DEFAULT NULL, `CampaignID` INT(11) NOT NULL, `RelOwnerUserID` INT(11) NOT NULL, `RelOperatorID` INT(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), UNIQUE INDEX UK_oempro_kannel_dlr_id (id) ) ENGINE = INNODB DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1; On 14.11.2013 12:28, spameden wrote: first of all what's the kannel/sqlbox version you're running? check your mysql setup, you might need to tweak some settings. i'm strongly suggest using InnoDB instead of MyISAM, also take a look at MariaDB (it's a fork of mysql, very nicely working). check indexes on dlr table as well. we've been using kannel to send about millions of sms messages daily (MT) without any problems. 2013/11/14 dorin dorinafte...@mail.ru: Hello all! I have a big problem ! I need to send via KANNEL 5000 of SMS. When I insert into table kannel_sqlbox_send_sms every minute 10 SMS - all works fine. But this method is very slow If I try to increase inserting SMS, for example (100SMS) MySql stops working and I got this error: MySQL Error: MySql Error: Couldn't connect to database - Lost connection to MySQL server at 'reading initial communication packet', system error: 111 In ideal I wish to do one insert with 5000 rows Memory,Proccessor is not loaded. My configuration is SMSBox+SQLBox+BearerBox; Please give me some Ideas!
Re: Big problem with sqlbox
Check your dmesg as well as syslog entries, mysqld might be killed by OOM-killer. 2013/11/15 dorin dorinafte...@mail.ru: How many entries are there typically in that table? I have a cron that insert's every 1 min 10 SMS - it works fine. If I increase for ex. 50 SMS inserts every 1 min - time by time mysql loses connection for a sec maximum. but if I increase 100SMS per 1min - 100% mysql loses connection for a small time or for some seconds. In this time I lose the incoming data. But realy 10 SMS or 100SMS per second is a very small value. When I sending distribution SMS, processor shows me medium loaded value: 40%; RAM = 60% - 65% Tomorrow want to change MySql version. now it's mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (i486) using readline 6.1 if the problem will persist, I will install MariaDB On 15.11.2013 9:57, Rene Kluwen wrote: The index on 'id' won't help you a lot. How many entries are there typically in that table? Having said that... sqlbox doesn't use the dlr table at all. So it's not your problem. == Rene -Original Message- From: users [mailto:users-boun...@kannel.org] On Behalf Of dorin Sent: donderdag 14 november 2013 12:12 To: spameden Cc: users@kannel.org Subject: Re: Big problem with sqlbox Thanks spameden for your reply first of all what's the kannel/sqlbox version you're running? I'm using kannel - 1.5 and sqlbox - I'm not really sure, I think 0.7; you might need to tweak some settings. Really I had set MyIsam all kannel tables. I have changed from MyIsam to InnoDb. Will familiarize soon with MariaDB too. Do you mean only these two tweaks? check indexes on dlr table as well.I have indexed id column. Here is my dlr table CREATE TABLE IF NOT EXISTS `kannel_dlr`( `id` INT(11) NOT NULL AUTO_INCREMENT, `smsc` VARCHAR(40) DEFAULT NULL, `ts` VARCHAR(40) DEFAULT NULL, `destination` VARCHAR(40) DEFAULT NULL, `source` VARCHAR(40) DEFAULT NULL, `service` VARCHAR(40) NOT NULL, `url` VARCHAR(255) DEFAULT NULL, `mask` INT(10) DEFAULT NULL, `status` VARCHAR(40) DEFAULT NULL, `boxc` VARCHAR(40) DEFAULT NULL, `CampaignID` INT(11) NOT NULL, `RelOwnerUserID` INT(11) NOT NULL, `RelOperatorID` INT(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), UNIQUE INDEX UK_oempro_kannel_dlr_id (id) ) ENGINE = INNODB DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1; On 14.11.2013 12:28, spameden wrote: first of all what's the kannel/sqlbox version you're running? check your mysql setup, you might need to tweak some settings. i'm strongly suggest using InnoDB instead of MyISAM, also take a look at MariaDB (it's a fork of mysql, very nicely working). check indexes on dlr table as well. we've been using kannel to send about millions of sms messages daily (MT) without any problems. 2013/11/14 dorin dorinafte...@mail.ru: Hello all! I have a big problem ! I need to send via KANNEL 5000 of SMS. When I insert into table kannel_sqlbox_send_sms every minute 10 SMS - all works fine. But this method is very slow If I try to increase inserting SMS, for example (100SMS) MySql stops working and I got this error: MySQL Error: MySql Error: Couldn't connect to database - Lost connection to MySQL server at 'reading initial communication packet', system error: 111 In ideal I wish to do one insert with 5000 rows Memory,Proccessor is not loaded. My configuration is SMSBox+SQLBox+BearerBox; Please give me some Ideas!
RE: Big problem with sqlbox
Yes. And please always copy the users list. -Original Message- From: dorin [mailto:dorinafte...@mail.ru] Sent: vrijdag 15 november 2013 13:51 To: Rene Kluwen Subject: Re: Big problem with sqlbox Sorry for my stupid question, but the latest sqlbox version can I get from next link? https://svn.kannel.org/sqlbox/trunk On 15.11.2013 9:44, Rene Kluwen wrote: Current sqlbox head revision has some improvements that will certainly help your problem. Even if it doesn't disappear, then it sure will help to increase the number of inserted messages at a time. Having said that, your problem seems to be mysql-related. Not a Kannel issue. 250 MB of Ram sure isn't a whole lot, nowadays. But for sure it should be enough to send more than 20 messages at a time. What does top tell you? Is your VPS using swap space? == Rene -Original Message- From: users [mailto:users-boun...@kannel.org] On Behalf Of spameden Sent: donderdag 14 november 2013 15:44 To: dorin Cc: users@kannel.org Subject: Re: Big problem with sqlbox 2013/11/14 dorin dorinafte...@mail.ru: Thanks spameden for your reply first of all what's the kannel/sqlbox version you're running? I'm using kannel - 1.5 and sqlbox - I'm not really sure, I think 0.7; What's revision you're using? Try using latest revision from SVN. you might need to tweak some settings. Really I had set MyIsam all kannel tables. I have changed from MyIsam to InnoDb. Will familiarize soon with MariaDB too. Do you mean only these two tweaks? check indexes on dlr table as well.I have indexed id column. Here is my dlr table CREATE TABLE IF NOT EXISTS `kannel_dlr`( `id` INT(11) NOT NULL AUTO_INCREMENT, `smsc` VARCHAR(40) DEFAULT NULL, `ts` VARCHAR(40) DEFAULT NULL, `destination` VARCHAR(40) DEFAULT NULL, `source` VARCHAR(40) DEFAULT NULL, `service` VARCHAR(40) NOT NULL, `url` VARCHAR(255) DEFAULT NULL, `mask` INT(10) DEFAULT NULL, `status` VARCHAR(40) DEFAULT NULL, `boxc` VARCHAR(40) DEFAULT NULL, `CampaignID` INT(11) NOT NULL, `RelOwnerUserID` INT(11) NOT NULL, `RelOperatorID` INT(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), UNIQUE INDEX UK_oempro_kannel_dlr_id (id) ) ENGINE = INNODB DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1; add an index on smsc and ts: CREATE INDEX smsc_ts ON dlr (smsc,ts); when DLR arrives kannel looks for it in dlr table and does a DELETE from dlr where ts=XXX and smsc=. I have some questions about this problem: I run KANNEL under a small VPS, with processor 1Gb and RAM 250Mb this may be a problem? how much memory needs for KANNEL+SQLBOX well working? This might be a problem if you have large amounts of messages in a queue. Ram 256Mb ? that's certainly not enough if you plan to do billions of MTs. Our VM runs on 3gb of memory. Also, you need good upstream SMSC uplinks which could handle large amounts of messages per second. I'd recommend using transciever mode and multiple connections. because sqlbox use tables(kannel_dlr,kannel_sqlbo x_send_sms) at the same time with me, may be the cause that I don't use TRANSACTIONS. The best way to modify these tables are to use transactions? The best way is to use InnoDB over MyISAM because its much faster if tuned properly and failsafe in case of database corruption. If I want to send billions of SMS needs I do some specific changes under config files? check throughput and max-pending-submits parameters in User Guide and contact your smsc operators if you need to tune them properly. On 14.11.2013 12:28, spameden wrote: first of all what's the kannel/sqlbox version you're running? check your mysql setup, you might need to tweak some settings. i'm strongly suggest using InnoDB instead of MyISAM, also take a look at MariaDB (it's a fork of mysql, very nicely working). check indexes on dlr table as well. we've been using kannel to send about millions of sms messages daily (MT) without any problems. 2013/11/14 dorin dorinafte...@mail.ru: Hello all! I have a big problem ! I need to send via KANNEL 5000 of SMS. When I insert into table kannel_sqlbox_send_sms every minute 10 SMS - all works fine. But this method is very slow If I try to increase inserting SMS, for example (100SMS) MySql stops working and I got this error: MySQL Error: MySql Error: Couldn't connect to database - Lost connection to MySQL server at 'reading initial communication packet', system error: 111 In ideal I wish to do one insert with 5000 rows Memory,Proccessor is not loaded. My configuration is SMSBox+SQLBox+BearerBox; Please give me some Ideas!
Re: Big problem with sqlbox
you are right! in OOM-killer list is Mysql and Apache. As I understand, I must increase urgent my RAM. On 15.11.2013 10:39, spameden wrote: Check your dmesg as well as syslog entries, mysqld might be killed by OOM-killer. 2013/11/15 dorin dorinafte...@mail.ru: How many entries are there typically in that table? I have a cron that insert's every 1 min 10 SMS - it works fine. If I increase for ex. 50 SMS inserts every 1 min - time by time mysql loses connection for a sec maximum. but if I increase 100SMS per 1min - 100% mysql loses connection for a small time or for some seconds. In this time I lose the incoming data. But realy 10 SMS or 100SMS per second is a very small value. When I sending distribution SMS, processor shows me medium loaded value: 40%; RAM = 60% - 65% Tomorrow want to change MySql version. now it's mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (i486) using readline 6.1 if the problem will persist, I will install MariaDB On 15.11.2013 9:57, Rene Kluwen wrote: The index on 'id' won't help you a lot. How many entries are there typically in that table? Having said that... sqlbox doesn't use the dlr table at all. So it's not your problem. == Rene -Original Message- From: users [mailto:users-boun...@kannel.org] On Behalf Of dorin Sent: donderdag 14 november 2013 12:12 To: spameden Cc: users@kannel.org Subject: Re: Big problem with sqlbox Thanks spameden for your reply first of all what's the kannel/sqlbox version you're running? I'm using kannel - 1.5 and sqlbox - I'm not really sure, I think 0.7; you might need to tweak some settings. Really I had set MyIsam all kannel tables. I have changed from MyIsam to InnoDb. Will familiarize soon with MariaDB too. Do you mean only these two tweaks? check indexes on dlr table as well.I have indexed id column. Here is my dlr table CREATE TABLE IF NOT EXISTS `kannel_dlr`( `id` INT(11) NOT NULL AUTO_INCREMENT, `smsc` VARCHAR(40) DEFAULT NULL, `ts` VARCHAR(40) DEFAULT NULL, `destination` VARCHAR(40) DEFAULT NULL, `source` VARCHAR(40) DEFAULT NULL, `service` VARCHAR(40) NOT NULL, `url` VARCHAR(255) DEFAULT NULL, `mask` INT(10) DEFAULT NULL, `status` VARCHAR(40) DEFAULT NULL, `boxc` VARCHAR(40) DEFAULT NULL, `CampaignID` INT(11) NOT NULL, `RelOwnerUserID` INT(11) NOT NULL, `RelOperatorID` INT(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), UNIQUE INDEX UK_oempro_kannel_dlr_id (id) ) ENGINE = INNODB DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1; On 14.11.2013 12:28, spameden wrote: first of all what's the kannel/sqlbox version you're running? check your mysql setup, you might need to tweak some settings. i'm strongly suggest using InnoDB instead of MyISAM, also take a look at MariaDB (it's a fork of mysql, very nicely working). check indexes on dlr table as well. we've been using kannel to send about millions of sms messages daily (MT) without any problems. 2013/11/14 dorin dorinafte...@mail.ru: Hello all! I have a big problem ! I need to send via KANNEL 5000 of SMS. When I insert into table kannel_sqlbox_send_sms every minute 10 SMS - all works fine. But this method is very slow If I try to increase inserting SMS, for example (100SMS) MySql stops working and I got this error: MySQL Error: MySql Error: Couldn't connect to database - Lost connection to MySQL server at 'reading initial communication packet', system error: 111 In ideal I wish to do one insert with 5000 rows Memory,Proccessor is not loaded. My configuration is SMSBox+SQLBox+BearerBox; Please give me some Ideas!
Re: Big problem with sqlbox
2013/11/15 dorin dorinafte...@mail.ru you are right! in OOM-killer list is Mysql and Apache. As I understand, I must increase urgent my RAM. As Rene pointed out before it has nothing to do with kannel at all. And I'd suggest avoid using Apache at all on VPS environment, stick to nginx+php-fpm if you need php functionality. For kannel sending service you'd need 1-2GB of memory depends really on your setup and amounts of messages staying in a kannel's internal queue. On 15.11.2013 10:39, spameden wrote: Check your dmesg as well as syslog entries, mysqld might be killed by OOM-killer. 2013/11/15 dorin dorinafte...@mail.ru: How many entries are there typically in that table? I have a cron that insert's every 1 min 10 SMS - it works fine. If I increase for ex. 50 SMS inserts every 1 min - time by time mysql loses connection for a sec maximum. but if I increase 100SMS per 1min - 100% mysql loses connection for a small time or for some seconds. In this time I lose the incoming data. But realy 10 SMS or 100SMS per second is a very small value. When I sending distribution SMS, processor shows me medium loaded value: 40%; RAM = 60% - 65% Tomorrow want to change MySql version. now it's mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (i486) using readline 6.1 if the problem will persist, I will install MariaDB On 15.11.2013 9:57, Rene Kluwen wrote: The index on 'id' won't help you a lot. How many entries are there typically in that table? Having said that... sqlbox doesn't use the dlr table at all. So it's not your problem. == Rene -Original Message- From: users [mailto:users-boun...@kannel.org] On Behalf Of dorin Sent: donderdag 14 november 2013 12:12 To: spameden Cc: users@kannel.org Subject: Re: Big problem with sqlbox Thanks spameden for your reply first of all what's the kannel/sqlbox version you're running? I'm using kannel - 1.5 and sqlbox - I'm not really sure, I think 0.7; you might need to tweak some settings. Really I had set MyIsam all kannel tables. I have changed from MyIsam to InnoDb. Will familiarize soon with MariaDB too. Do you mean only these two tweaks? check indexes on dlr table as well.I have indexed id column. Here is my dlr table CREATE TABLE IF NOT EXISTS `kannel_dlr`( `id` INT(11) NOT NULL AUTO_INCREMENT, `smsc` VARCHAR(40) DEFAULT NULL, `ts` VARCHAR(40) DEFAULT NULL, `destination` VARCHAR(40) DEFAULT NULL, `source` VARCHAR(40) DEFAULT NULL, `service` VARCHAR(40) NOT NULL, `url` VARCHAR(255) DEFAULT NULL, `mask` INT(10) DEFAULT NULL, `status` VARCHAR(40) DEFAULT NULL, `boxc` VARCHAR(40) DEFAULT NULL, `CampaignID` INT(11) NOT NULL, `RelOwnerUserID` INT(11) NOT NULL, `RelOperatorID` INT(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), UNIQUE INDEX UK_oempro_kannel_dlr_id (id) ) ENGINE = INNODB DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1; On 14.11.2013 12:28, spameden wrote: first of all what's the kannel/sqlbox version you're running? check your mysql setup, you might need to tweak some settings. i'm strongly suggest using InnoDB instead of MyISAM, also take a look at MariaDB (it's a fork of mysql, very nicely working). check indexes on dlr table as well. we've been using kannel to send about millions of sms messages daily (MT) without any problems. 2013/11/14 dorin dorinafte...@mail.ru: Hello all! I have a big problem ! I need to send via KANNEL 5000 of SMS. When I insert into table kannel_sqlbox_send_sms every minute 10 SMS - all works fine. But this method is very slow If I try to increase inserting SMS, for example (100SMS) MySql stops working and I got this error: MySQL Error: MySql Error: Couldn't connect to database - Lost connection to MySQL server at 'reading initial communication packet', system error: 111 In ideal I wish to do one insert with 5000 rows Memory,Proccessor is not loaded. My configuration is SMSBox+SQLBox+BearerBox; Please give me some Ideas!
Re: Big problem with sqlbox
first of all what's the kannel/sqlbox version you're running? check your mysql setup, you might need to tweak some settings. i'm strongly suggest using InnoDB instead of MyISAM, also take a look at MariaDB (it's a fork of mysql, very nicely working). check indexes on dlr table as well. we've been using kannel to send about millions of sms messages daily (MT) without any problems. 2013/11/14 dorin dorinafte...@mail.ru: Hello all! I have a big problem ! I need to send via KANNEL 5000 of SMS. When I insert into table kannel_sqlbox_send_sms every minute 10 SMS - all works fine. But this method is very slow If I try to increase inserting SMS, for example (100SMS) MySql stops working and I got this error: MySQL Error: MySql Error: Couldn't connect to database - Lost connection to MySQL server at 'reading initial communication packet', system error: 111 In ideal I wish to do one insert with 5000 rows Memory,Proccessor is not loaded. My configuration is SMSBox+SQLBox+BearerBox; Please give me some Ideas!
Re: Big problem with sqlbox
Thanks spameden for your reply first of all what's the kannel/sqlbox version you're running? I'm using kannel - 1.5 and sqlbox - I'm not really sure, I think 0.7; you might need to tweak some settings. Really I had set MyIsam all kannel tables. I have changed from MyIsam to InnoDb. Will familiarize soon with MariaDB too. Do you mean only these two tweaks? check indexes on dlr table as well.I have indexed id column. Here is my dlr table CREATE TABLE IF NOT EXISTS `kannel_dlr`( `id` INT(11) NOT NULL AUTO_INCREMENT, `smsc` VARCHAR(40) DEFAULT NULL, `ts` VARCHAR(40) DEFAULT NULL, `destination` VARCHAR(40) DEFAULT NULL, `source` VARCHAR(40) DEFAULT NULL, `service` VARCHAR(40) NOT NULL, `url` VARCHAR(255) DEFAULT NULL, `mask` INT(10) DEFAULT NULL, `status` VARCHAR(40) DEFAULT NULL, `boxc` VARCHAR(40) DEFAULT NULL, `CampaignID` INT(11) NOT NULL, `RelOwnerUserID` INT(11) NOT NULL, `RelOperatorID` INT(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), UNIQUE INDEX UK_oempro_kannel_dlr_id (id) ) ENGINE = INNODB DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1; On 14.11.2013 12:28, spameden wrote: first of all what's the kannel/sqlbox version you're running? check your mysql setup, you might need to tweak some settings. i'm strongly suggest using InnoDB instead of MyISAM, also take a look at MariaDB (it's a fork of mysql, very nicely working). check indexes on dlr table as well. we've been using kannel to send about millions of sms messages daily (MT) without any problems. 2013/11/14 dorin dorinafte...@mail.ru: Hello all! I have a big problem ! I need to send via KANNEL 5000 of SMS. When I insert into table kannel_sqlbox_send_sms every minute 10 SMS - all works fine. But this method is very slow If I try to increase inserting SMS, for example (100SMS) MySql stops working and I got this error: MySQL Error: MySql Error: Couldn't connect to database - Lost connection to MySQL server at 'reading initial communication packet', system error: 111 In ideal I wish to do one insert with 5000 rows Memory,Proccessor is not loaded. My configuration is SMSBox+SQLBox+BearerBox; Please give me some Ideas!
Re: Big problem with sqlbox
I have some questions about this problem: I run KANNEL under a small VPS, with processor 1Gb and RAM 250Mb this may be a problem? how much memory needs for KANNEL+SQLBOX well working? because sqlbox use tables(kannel_dlr,kannel_sqlbox_send_sms) at the same time with me, may be the cause that I don't use TRANSACTIONS. The best way to modify these tables are to use transactions? If I want to send billions of SMS needs I do some specific changes under config files? Thanks! On 14.11.2013 12:28, spameden wrote: first of all what's the kannel/sqlbox version you're running? check your mysql setup, you might need to tweak some settings. i'm strongly suggest using InnoDB instead of MyISAM, also take a look at MariaDB (it's a fork of mysql, very nicely working). check indexes on dlr table as well. we've been using kannel to send about millions of sms messages daily (MT) without any problems. 2013/11/14 dorin dorinafte...@mail.ru: Hello all! I have a big problem ! I need to send via KANNEL 5000 of SMS. When I insert into table kannel_sqlbox_send_sms every minute 10 SMS - all works fine. But this method is very slow If I try to increase inserting SMS, for example (100SMS) MySql stops working and I got this error: MySQL Error: MySql Error: Couldn't connect to database - Lost connection to MySQL server at 'reading initial communication packet', system error: 111 In ideal I wish to do one insert with 5000 rows Memory,Proccessor is not loaded. My configuration is SMSBox+SQLBox+BearerBox; Please give me some Ideas!
Re: Big problem with sqlbox
2013/11/14 dorin dorinafte...@mail.ru: Thanks spameden for your reply first of all what's the kannel/sqlbox version you're running? I'm using kannel - 1.5 and sqlbox - I'm not really sure, I think 0.7; What's revision you're using? Try using latest revision from SVN. you might need to tweak some settings. Really I had set MyIsam all kannel tables. I have changed from MyIsam to InnoDb. Will familiarize soon with MariaDB too. Do you mean only these two tweaks? check indexes on dlr table as well.I have indexed id column. Here is my dlr table CREATE TABLE IF NOT EXISTS `kannel_dlr`( `id` INT(11) NOT NULL AUTO_INCREMENT, `smsc` VARCHAR(40) DEFAULT NULL, `ts` VARCHAR(40) DEFAULT NULL, `destination` VARCHAR(40) DEFAULT NULL, `source` VARCHAR(40) DEFAULT NULL, `service` VARCHAR(40) NOT NULL, `url` VARCHAR(255) DEFAULT NULL, `mask` INT(10) DEFAULT NULL, `status` VARCHAR(40) DEFAULT NULL, `boxc` VARCHAR(40) DEFAULT NULL, `CampaignID` INT(11) NOT NULL, `RelOwnerUserID` INT(11) NOT NULL, `RelOperatorID` INT(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), UNIQUE INDEX UK_oempro_kannel_dlr_id (id) ) ENGINE = INNODB DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1; add an index on smsc and ts: CREATE INDEX smsc_ts ON dlr (smsc,ts); when DLR arrives kannel looks for it in dlr table and does a DELETE from dlr where ts=XXX and smsc=. I have some questions about this problem: I run KANNEL under a small VPS, with processor 1Gb and RAM 250Mb this may be a problem? how much memory needs for KANNEL+SQLBOX well working? This might be a problem if you have large amounts of messages in a queue. Ram 256Mb ? that's certainly not enough if you plan to do billions of MTs. Our VM runs on 3gb of memory. Also, you need good upstream SMSC uplinks which could handle large amounts of messages per second. I'd recommend using transciever mode and multiple connections. because sqlbox use tables(kannel_dlr,kannel_sqlbo x_send_sms) at the same time with me, may be the cause that I don't use TRANSACTIONS. The best way to modify these tables are to use transactions? The best way is to use InnoDB over MyISAM because its much faster if tuned properly and failsafe in case of database corruption. If I want to send billions of SMS needs I do some specific changes under config files? check throughput and max-pending-submits parameters in User Guide and contact your smsc operators if you need to tune them properly. On 14.11.2013 12:28, spameden wrote: first of all what's the kannel/sqlbox version you're running? check your mysql setup, you might need to tweak some settings. i'm strongly suggest using InnoDB instead of MyISAM, also take a look at MariaDB (it's a fork of mysql, very nicely working). check indexes on dlr table as well. we've been using kannel to send about millions of sms messages daily (MT) without any problems. 2013/11/14 dorin dorinafte...@mail.ru: Hello all! I have a big problem ! I need to send via KANNEL 5000 of SMS. When I insert into table kannel_sqlbox_send_sms every minute 10 SMS - all works fine. But this method is very slow If I try to increase inserting SMS, for example (100SMS) MySql stops working and I got this error: MySQL Error: MySql Error: Couldn't connect to database - Lost connection to MySQL server at 'reading initial communication packet', system error: 111 In ideal I wish to do one insert with 5000 rows Memory,Proccessor is not loaded. My configuration is SMSBox+SQLBox+BearerBox; Please give me some Ideas!
Re: Big problem with sqlbox
I made some tests, and I got that server wit processor 1GHz and 250Mb works stable if I insert 10 rows at once in 1 min. At 20,50 rows or more, stability decrease. With word stability I mean that Mysql lose connection for a small time. In this time some responses are lost. Hey people, what do you think about this situation? how much rows can be inserted in dlr at once? On 14.11.2013 16:43, spameden wrote: 2013/11/14 dorin dorinafte...@mail.ru: Thanks spameden for your reply first of all what's the kannel/sqlbox version you're running? I'm using kannel - 1.5 and sqlbox - I'm not really sure, I think 0.7; What's revision you're using? Try using latest revision from SVN. you might need to tweak some settings. Really I had set MyIsam all kannel tables. I have changed from MyIsam to InnoDb. Will familiarize soon with MariaDB too. Do you mean only these two tweaks? check indexes on dlr table as well.I have indexed id column. Here is my dlr table CREATE TABLE IF NOT EXISTS `kannel_dlr`( `id` INT(11) NOT NULL AUTO_INCREMENT, `smsc` VARCHAR(40) DEFAULT NULL, `ts` VARCHAR(40) DEFAULT NULL, `destination` VARCHAR(40) DEFAULT NULL, `source` VARCHAR(40) DEFAULT NULL, `service` VARCHAR(40) NOT NULL, `url` VARCHAR(255) DEFAULT NULL, `mask` INT(10) DEFAULT NULL, `status` VARCHAR(40) DEFAULT NULL, `boxc` VARCHAR(40) DEFAULT NULL, `CampaignID` INT(11) NOT NULL, `RelOwnerUserID` INT(11) NOT NULL, `RelOperatorID` INT(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), UNIQUE INDEX UK_oempro_kannel_dlr_id (id) ) ENGINE = INNODB DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1; add an index on smsc and ts: CREATE INDEX smsc_ts ON dlr (smsc,ts); when DLR arrives kannel looks for it in dlr table and does a DELETE from dlr where ts=XXX and smsc=. I have some questions about this problem: I run KANNEL under a small VPS, with processor 1Gb and RAM 250Mb this may be a problem? how much memory needs for KANNEL+SQLBOX well working? This might be a problem if you have large amounts of messages in a queue. Ram 256Mb ? that's certainly not enough if you plan to do billions of MTs. Our VM runs on 3gb of memory. Also, you need good upstream SMSC uplinks which could handle large amounts of messages per second. I'd recommend using transciever mode and multiple connections. because sqlbox use tables(kannel_dlr,kannel_sqlbo x_send_sms) at the same time with me, may be the cause that I don't use TRANSACTIONS. The best way to modify these tables are to use transactions? The best way is to use InnoDB over MyISAM because its much faster if tuned properly and failsafe in case of database corruption. If I want to send billions of SMS needs I do some specific changes under config files? check throughput and max-pending-submits parameters in User Guide and contact your smsc operators if you need to tune them properly. On 14.11.2013 12:28, spameden wrote: first of all what's the kannel/sqlbox version you're running? check your mysql setup, you might need to tweak some settings. i'm strongly suggest using InnoDB instead of MyISAM, also take a look at MariaDB (it's a fork of mysql, very nicely working). check indexes on dlr table as well. we've been using kannel to send about millions of sms messages daily (MT) without any problems. 2013/11/14 dorin dorinafte...@mail.ru: Hello all! I have a big problem ! I need to send via KANNEL 5000 of SMS. When I insert into table kannel_sqlbox_send_sms every minute 10 SMS - all works fine. But this method is very slow If I try to increase inserting SMS, for example (100SMS) MySql stops working and I got this error: MySQL Error: MySql Error: Couldn't connect to database - Lost connection to MySQL server at 'reading initial communication packet', system error: 111 In ideal I wish to do one insert with 5000 rows Memory,Proccessor is not loaded. My configuration is SMSBox+SQLBox+BearerBox; Please give me some Ideas!
RE: Big problem with sqlbox
Current sqlbox head revision has some improvements that will certainly help your problem. Even if it doesn't disappear, then it sure will help to increase the number of inserted messages at a time. Having said that, your problem seems to be mysql-related. Not a Kannel issue. 250 MB of Ram sure isn't a whole lot, nowadays. But for sure it should be enough to send more than 20 messages at a time. What does top tell you? Is your VPS using swap space? == Rene -Original Message- From: users [mailto:users-boun...@kannel.org] On Behalf Of spameden Sent: donderdag 14 november 2013 15:44 To: dorin Cc: users@kannel.org Subject: Re: Big problem with sqlbox 2013/11/14 dorin dorinafte...@mail.ru: Thanks spameden for your reply first of all what's the kannel/sqlbox version you're running? I'm using kannel - 1.5 and sqlbox - I'm not really sure, I think 0.7; What's revision you're using? Try using latest revision from SVN. you might need to tweak some settings. Really I had set MyIsam all kannel tables. I have changed from MyIsam to InnoDb. Will familiarize soon with MariaDB too. Do you mean only these two tweaks? check indexes on dlr table as well.I have indexed id column. Here is my dlr table CREATE TABLE IF NOT EXISTS `kannel_dlr`( `id` INT(11) NOT NULL AUTO_INCREMENT, `smsc` VARCHAR(40) DEFAULT NULL, `ts` VARCHAR(40) DEFAULT NULL, `destination` VARCHAR(40) DEFAULT NULL, `source` VARCHAR(40) DEFAULT NULL, `service` VARCHAR(40) NOT NULL, `url` VARCHAR(255) DEFAULT NULL, `mask` INT(10) DEFAULT NULL, `status` VARCHAR(40) DEFAULT NULL, `boxc` VARCHAR(40) DEFAULT NULL, `CampaignID` INT(11) NOT NULL, `RelOwnerUserID` INT(11) NOT NULL, `RelOperatorID` INT(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), UNIQUE INDEX UK_oempro_kannel_dlr_id (id) ) ENGINE = INNODB DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1; add an index on smsc and ts: CREATE INDEX smsc_ts ON dlr (smsc,ts); when DLR arrives kannel looks for it in dlr table and does a DELETE from dlr where ts=XXX and smsc=. I have some questions about this problem: I run KANNEL under a small VPS, with processor 1Gb and RAM 250Mb this may be a problem? how much memory needs for KANNEL+SQLBOX well working? This might be a problem if you have large amounts of messages in a queue. Ram 256Mb ? that's certainly not enough if you plan to do billions of MTs. Our VM runs on 3gb of memory. Also, you need good upstream SMSC uplinks which could handle large amounts of messages per second. I'd recommend using transciever mode and multiple connections. because sqlbox use tables(kannel_dlr,kannel_sqlbo x_send_sms) at the same time with me, may be the cause that I don't use TRANSACTIONS. The best way to modify these tables are to use transactions? The best way is to use InnoDB over MyISAM because its much faster if tuned properly and failsafe in case of database corruption. If I want to send billions of SMS needs I do some specific changes under config files? check throughput and max-pending-submits parameters in User Guide and contact your smsc operators if you need to tune them properly. On 14.11.2013 12:28, spameden wrote: first of all what's the kannel/sqlbox version you're running? check your mysql setup, you might need to tweak some settings. i'm strongly suggest using InnoDB instead of MyISAM, also take a look at MariaDB (it's a fork of mysql, very nicely working). check indexes on dlr table as well. we've been using kannel to send about millions of sms messages daily (MT) without any problems. 2013/11/14 dorin dorinafte...@mail.ru: Hello all! I have a big problem ! I need to send via KANNEL 5000 of SMS. When I insert into table kannel_sqlbox_send_sms every minute 10 SMS - all works fine. But this method is very slow If I try to increase inserting SMS, for example (100SMS) MySql stops working and I got this error: MySQL Error: MySql Error: Couldn't connect to database - Lost connection to MySQL server at 'reading initial communication packet', system error: 111 In ideal I wish to do one insert with 5000 rows Memory,Proccessor is not loaded. My configuration is SMSBox+SQLBox+BearerBox; Please give me some Ideas!
RE: Big problem with sqlbox
The index on 'id' won't help you a lot. How many entries are there typically in that table? Having said that... sqlbox doesn't use the dlr table at all. So it's not your problem. == Rene -Original Message- From: users [mailto:users-boun...@kannel.org] On Behalf Of dorin Sent: donderdag 14 november 2013 12:12 To: spameden Cc: users@kannel.org Subject: Re: Big problem with sqlbox Thanks spameden for your reply first of all what's the kannel/sqlbox version you're running? I'm using kannel - 1.5 and sqlbox - I'm not really sure, I think 0.7; you might need to tweak some settings. Really I had set MyIsam all kannel tables. I have changed from MyIsam to InnoDb. Will familiarize soon with MariaDB too. Do you mean only these two tweaks? check indexes on dlr table as well.I have indexed id column. Here is my dlr table CREATE TABLE IF NOT EXISTS `kannel_dlr`( `id` INT(11) NOT NULL AUTO_INCREMENT, `smsc` VARCHAR(40) DEFAULT NULL, `ts` VARCHAR(40) DEFAULT NULL, `destination` VARCHAR(40) DEFAULT NULL, `source` VARCHAR(40) DEFAULT NULL, `service` VARCHAR(40) NOT NULL, `url` VARCHAR(255) DEFAULT NULL, `mask` INT(10) DEFAULT NULL, `status` VARCHAR(40) DEFAULT NULL, `boxc` VARCHAR(40) DEFAULT NULL, `CampaignID` INT(11) NOT NULL, `RelOwnerUserID` INT(11) NOT NULL, `RelOperatorID` INT(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), UNIQUE INDEX UK_oempro_kannel_dlr_id (id) ) ENGINE = INNODB DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1; On 14.11.2013 12:28, spameden wrote: first of all what's the kannel/sqlbox version you're running? check your mysql setup, you might need to tweak some settings. i'm strongly suggest using InnoDB instead of MyISAM, also take a look at MariaDB (it's a fork of mysql, very nicely working). check indexes on dlr table as well. we've been using kannel to send about millions of sms messages daily (MT) without any problems. 2013/11/14 dorin dorinafte...@mail.ru: Hello all! I have a big problem ! I need to send via KANNEL 5000 of SMS. When I insert into table kannel_sqlbox_send_sms every minute 10 SMS - all works fine. But this method is very slow If I try to increase inserting SMS, for example (100SMS) MySql stops working and I got this error: MySQL Error: MySql Error: Couldn't connect to database - Lost connection to MySQL server at 'reading initial communication packet', system error: 111 In ideal I wish to do one insert with 5000 rows Memory,Proccessor is not loaded. My configuration is SMSBox+SQLBox+BearerBox; Please give me some Ideas!