Astrad V006
Oussama Hammami, 2011-05-23
Switzernet
Avec cette version on a pratiquement les mêmes résultats que Astrad4 (fichiers) et on n'avait pas besoin d’activer le cache Asterisk.
http://switzernet.com/3/public/110421-astrad5-reg-stat
1- Agrégation des requêtes DELETE
Une requête REPLACE déclenche les deux triggers DELETE et INSERT ce qui implique que pour chaque enregistrement on envoi deux requêtes (DELETE et REPLACE) au Master.
Ce trafic inutile des requêtes DELETE est supprimé à l'aide d'une procédure stockée REGISTER :
ASTRAD
DELIMITER |
DROP PROCEDURE IF EXISTS `REGISTER`|
CREATE PROCEDURE `REGISTER`(IN USR VARCHAR(80), IN CON VARCHAR(255), IN EXR DATETIME)
BEGIN
IF (SELECT 1 FROM location3 WHERE username=USR AND contact=CON) THEN
UPDATE location3 SET expires=EXR WHERE username=USR AND contact=CON;
ELSE
INSERT INTO location3 VALUES (USR,CON,EXR);
END IF;
END |
DELIMITER ;
DB3
DELIMITER |
DROP PROCEDURE IF EXISTS `REGISTER`|
CREATE PROCEDURE `REGISTER`(IN USR VARCHAR(80),IN DOM VARCHAR(15), IN CON VARCHAR(255), IN EXR DATETIME)
BEGIN
IF (SELECT 1 FROM location2 WHERE username=USR AND contact=CON AND domain=DOM) THEN
UPDATE location2 SET expires=EXR WHERE username=USR AND contact=CON AND domain=DOM;
ELSE
INSERT INTO location2 VALUES (USR,DOM,CON,EXR);
END IF;
END |
DELIMITER ;
Ces procédures gèrent l'intégration des enregistrement dans les deux tables location3 et location2 dans respectivement Astrad et DB3.
Cette procédure (version astrad) est appelée par le trigger UPDATE de sippeer2 pour insérer correctement l'enregistrement dans location3
DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Update_sippeers2`|
CREATE TRIGGER `Trg_Update_sippeers2` AFTER UPDATE ON `sippeers2` FOR EACH ROW BEGIN
IF NEW.regseconds IS NOT NULL AND NEW.port IS NOT NULL AND NEW.ipaddr IS NOT NULL AND NEW.name IS NOT NULL AND FROM_UNIXTIME(NEW.regseconds) > (NOW() - INTERVAL 30 SECOND) AND NEW.ipaddr NOT LIKE '0.0.0.0' THEN
CALL REGISTER (NEW.name,CONCAT('sip:',NEW.name,'@',NEW.ipaddr,':',NEW.port),FROM_UNIXTIME(NEW.regseconds));
END IF;
END |
DELIMITER ;
Elle est aussi appelée (version DB3) par l'ASTRAD pour intégrer ses enregistrements dans location2 (le script perl ast-registration.pl)
astrad4:~# ngrep -pql -W byline "" dst host 94.23.28.61
interface: eth0 (91.121.16.0/255.255.255.0)
filter: (ip or ip6) and ( dst host 94.23.28.61 )
T 91.121.16.79:49539 -> 94.23.28.61:3306 [AP]
.....set autocommit=1
T 91.121.16.79:49539 -> 94.23.28.61:3306 [AP]
h....CALL REGISTER ('412155081XX','91.121.16.79','sip:412155081XX@84.227.34.16:10003','2011-05-17 16:17:32')
T 91.121.16.79:49539 -> 94.23.28.61:3306 [AP]
h....CALL REGISTER ('412155007XX','91.121.16.79','sip:412155007XX@85.218.37.164:5060','2011-05-17 16:10:32')
T 91.121.16.79:49539 -> 94.23.28.61:3306 [AP]
.....set autocommit=1
T 91.121.16.79:49539 -> 94.23.28.61:3306 [AP]
g....CALL REGISTER ('412155036XX','91.121.16.79','sip:412155036XX@78.155.15.91:5060','2011-05-17 16:17:33')
2- Le champ contact est une clé !
Avant cette version le champ contact n'était pas considéré comme une clé contrairement au master.
mysql> desc location;
+---------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| username | varchar(50) | | PRI | | |
| domain | varchar(100) | | PRI | | |
| contact | varchar(255) | | PRI | | |
| i_env | int(11) | | PRI | 1 | |
| expires | datetime | YES | | NULL | |
| q | float(10,2) | YES | | NULL | |
| callid | varchar(255) | YES | | NULL | |
| cseq | int(11) | YES | | NULL | |
| last_modified | timestamp(14) | YES | | NULL | |
| replicate | int(10) unsigned | YES | | NULL | |
| state | tinyint(1) unsigned | YES | | NULL | |
| flags | int(11) | | | 0 | |
| user_agent | varchar(50) | | | | |
| received | varchar(255) | YES | | NULL | |
+---------------+---------------------+------+-----+---------+-------+
14 rows in set (0.00 sec)
Cette contradiction a pausé un problème qu'on peut facilement le voir avec des clients derrière NAT:
mysql> select min(expires),max(expires),domain,count(*) from location where domain in ('91.121.143.56','91.121.147.45','91.121.16.79','91.121.178.108','91.121.142.9') group by domain;
+---------------------+---------------------+----------------+----------+
| min(expires) | max(expires) | domain | count(*) |
+---------------------+---------------------+----------------+----------+
| 2011-05-06 00:00:17 | 2011-05-06 11:27:23 | 91.121.142.9 | 2023 |
| 2011-05-06 00:00:40 | 2011-05-06 11:28:45 | 91.121.143.56 | 2039 |
| 2011-05-06 00:02:52 | 2011-05-06 11:22:10 | 91.121.147.45 | 504 |
| 2011-05-06 00:00:35 | 2011-05-06 11:27:10 | 91.121.16.79 | 1184 |
| 2011-05-06 00:01:04 | 2011-05-06 11:22:52 | 91.121.178.108 | 1023 |
+---------------------+---------------------+----------------+----------+
5 rows in set (0.06 sec)
La commande replace mis à jours l'enregistrement uniquement dans le cas de 'duplicate key' sinon elle exécute une commande d'insertion, dans notre cas les clés sont :
- Username
- Domain
- Contact
- i_env
C'est la NAT qui cause ce nombre très élevé d'enregistrement:
mysql> select username,expires,contact from location where username=412155014XX order by last_modified desc ;
+-------------+---------------------+-----------------------------------+
| username | expires | contact |
+-------------+---------------------+-----------------------------------+
| 412155014XX | 2011-05-06 10:26:45 | sip:412155014XX@85.3.92.171:49208 |
| 412155014XX | 2011-05-06 10:17:02 | sip:412155014XX@85.3.92.171:49202 |
| 412155014XX | 2011-05-06 10:07:19 | sip:412155014XX@85.3.92.171:49201 |
| 412155014XX | 2011-05-06 09:57:37 | sip:412155014XX@85.3.92.171:49197 |
| 412155014XX | 2011-05-06 09:47:54 | sip:412155014XX@85.3.92.171:49187 |
| 412155014XX | 2011-05-06 09:38:12 | sip:412155014XX@85.3.92.171:49186 |
| 412155014XX | 2011-05-06 09:28:29 | sip:412155014XX@85.3.92.171:49181 |
| 412155014XX | 2011-05-06 09:18:47 | sip:412155014XX@85.3.92.171:49175 |
| 412155014XX | 2011-05-06 09:09:04 | sip:412155014XX@85.3.92.171:49174 |
| 412155014XX | 2011-05-06 08:59:21 | sip:412155014XX@85.3.92.171:49169 |
| 412155014XX | 2011-05-06 08:49:39 | sip:412155014XX@85.3.92.171:49158 |
| 412155014XX | 2011-05-06 08:39:56 | sip:412155014XX@85.3.92.171:49156 |
| 412155014XX | 2011-05-06 08:30:14 | sip:412155014XX@85.3.92.171:49153 |
| 412155014XX | 2011-05-06 08:20:31 | sip:412155014XX@85.3.92.171:65531 |
| 412155014XX | 2011-05-06 08:10:49 | sip:412155014XX@85.3.92.171:65530 |
| 412155014XX | 2011-05-06 08:01:06 | sip:412155014XX@85.3.92.171:65525 |
| 412155014XX | 2011-05-06 07:51:23 | sip:412155014XX@85.3.92.171:65515 |
| 412155014XX | 2011-05-06 07:40:19 | sip:412155014XX@85.3.92.171:65514 |
| 412155014XX | 2011-05-06 07:30:37 | sip:412155014XX@85.3.92.171:65511 |
| 412155014XX | 2011-05-06 07:20:54 | sip:412155014XX@85.3.92.171:65504 |
| 412155014XX | 2011-05-06 07:11:12 | sip:412155014XX@85.3.92.171:65483 |
| 412155014XX | 2011-05-06 07:01:29 | sip:412155014XX@85.3.92.171:65471 |
| 412155014XX | 2011-05-06 06:51:46 | sip:412155014XX@85.3.92.171:65470 |
| 412155014XX | 2011-05-06 06:42:04 | sip:412155014XX@85.3.92.171:65459 |
| 412155014XX | 2011-05-06 06:32:21 | sip:412155014XX@85.3.92.171:65453 |
| 412155014XX | 2011-05-06 06:22:39 | sip:412155014XX@85.3.92.171:65451 |
| 412155014XX | 2011-05-06 06:12:56 | sip:412155014XX@85.3.92.171:65446 |
| 412155014XX | 2011-05-06 06:03:14 | sip:412155014XX@85.3.92.171:65438 |
| 412155014XX | 2011-05-06 05:53:31 | sip:412155014XX@85.3.92.171:65437 |
| 412155014XX | 2011-05-06 05:43:48 | sip:412155014XX@85.3.92.171:65427 |
| 412155014XX | 2011-05-06 05:34:06 | sip:412155014XX@85.3.92.171:65419 |
| 412155014XX | 2011-05-06 05:24:23 | sip:412155014XX@85.3.92.171:65418 |
| 412155014XX | 2011-05-06 05:14:41 | sip:412155014XX@85.3.92.171:65412 |
| 412155014XX | 2011-05-06 05:04:58 | sip:412155014XX@85.3.92.171:65403 |
| 412155014XX | 2011-05-06 04:55:16 | sip:412155014XX@85.3.92.171:65402 |
| 412155014XX | 2011-05-06 04:45:33 | sip:412155014XX@85.3.92.171:65393 |
| 412155014XX | 2011-05-06 04:35:50 | sip:412155014XX@85.3.92.171:65388 |
| 412155014XX | 2011-05-06 04:26:08 | sip:412155014XX@85.3.92.171:65384 |
| 412155014XX | 2011-05-06 04:16:25 | sip:412155014XX@85.3.92.171:65373 |
| 412155014XX | 2011-05-06 04:06:43 | sip:412155014XX@85.3.92.171:65366 |
| 412155014XX | 2011-05-06 03:57:00 | sip:412155014XX@85.3.92.171:65363 |
| 412155014XX | 2011-05-06 03:47:18 | sip:412155014XX@85.3.92.171:65354 |
| 412155014XX | 2011-05-06 03:37:35 | sip:412155014XX@85.3.92.171:65350 |
| 412155014XX | 2011-05-06 03:27:52 | sip:412155014XX@85.3.92.171:65346 |
| 412155014XX | 2011-05-06 03:18:10 | sip:412155014XX@85.3.92.171:65340 |
| 412155014XX | 2011-05-06 03:08:27 | sip:412155014XX@85.3.92.171:65336 |
| 412155014XX | 2011-05-06 02:58:45 | sip:412155014XX@85.3.92.171:65331 |
| 412155014XX | 2011-05-06 02:49:02 | sip:412155014XX@85.3.92.171:65317 |
| 412155014XX | 2011-05-06 02:39:19 | sip:412155014XX@85.3.92.171:65308 |
| 412155014XX | 2011-05-06 02:29:37 | sip:412155014XX@85.3.92.171:65303 |
| 412155014XX | 2011-05-06 02:19:54 | sip:412155014XX@85.3.92.171:65296 |
| 412155014XX | 2011-05-06 02:10:12 | sip:412155014XX@85.3.92.171:65291 |
| 412155014XX | 2011-05-06 02:00:29 | sip:412155014XX@85.3.92.171:65282 |
| 412155014XX | 2011-05-06 01:50:47 | sip:412155014XX@85.3.92.171:65279 |
| 412155014XX | 2011-05-06 01:41:04 | sip:412155014XX@85.3.92.171:65267 |
| 412155014XX | 2011-05-06 01:31:22 | sip:412155014XX@85.3.92.171:65206 |
| 412155014XX | 2011-05-06 01:21:39 | sip:412155014XX@85.3.92.171:65202 |
| 412155014XX | 2011-05-06 01:11:56 | sip:412155014XX@85.3.92.171:65189 |
| 412155014XX | 2011-05-06 01:02:14 | sip:412155014XX@85.3.92.171:65183 |
| 412155014XX | 2011-05-06 00:52:31 | sip:412155014XX@85.3.92.171:65182 |
| 412155014XX | 2011-05-06 00:42:49 | sip:412155014XX@85.3.92.171:65174 |
| 412155014XX | 2011-05-06 00:33:06 | sip:412155014XX@85.3.92.171:65171 |
| 412155014XX | 2011-05-06 00:23:24 | sip:412155014XX@85.3.92.171:65170 |
| 412155014XX | 2011-05-06 00:13:41 | sip:412155014XX@85.3.92.171:65161 |
| 412155014XX | 2011-05-06 00:03:58 | sip:412155014XX@85.3.92.171:65153 |
+-------------+---------------------+-----------------------------------+
65 rows in set (0.04 sec)
3- abandonner les tables FEDRATED location2:
On a remplacé les tables FEDERATED par un script PERL (ast-registration) qui exécute les requêtes reconstruit à partir du table registration (ASTRAD & DB3):
mysql> desc registration;
+----------+--------------+------+-----+----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+----------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(80) | NO | | | |
| domain | varchar(15) | NO | | | |
| contact | varchar(255) | YES | | NULL | |
| expires | datetime | YES | | NULL | |
| action | varchar(15) | YES | | register | |
+----------+--------------+------+-----+----------+----------------+
6 rows in set (0.04 sec)
Avec ce script on a pu regroupé les requêtes REPLACE et DELETE:
T 94.23.28.61:48471 -> 66.234.138.71:3306 [AP]
....REPLACE INTO location (username,domain,contact,expires,q,callid,cseq,flags,user_agent) VALUES
('412255036XX','91.121.142.9','sip:412255036XX@85.218.40.34:26084','2011-05-17 16:47:08',-1.00,'Astrad-V005', 1, 1,'Not Available')
,('412155015XX','91.121.143.56','sip:412155015XX@178.192.218.131:49153','2011-05-17 16:39:08',-1.00,'Astrad-V005', 1, 1,'Not Available')
,('418150918XX','91.121.143.56','sip:418150918XX@217.71.204.12:2048','2011-05-17 16:39:08',-1.00,'Astrad-V005', 1, 1,'Not Available')
T 94.23.28.61:48471 -> 66.234.138.71:3306 [AP]
,....DELETE FROM location WHERE
(username='412155013XX' AND domain='91.121.143.56' AND contact='sip:412155013XX@85.2.181.168:49695' AND expires='2011-05-17 16:36:33')
OR
(username='412450421XX' AND domain='91.121.143.56' AND contact='sip:412450421XX@85.3.82.91:57554' AND expires='2011-05-17 16:36:36')
4- Utilisation CPU n’augmente pas lors du changement du mot de passe
Puisque en utilise le module REALTIME (MySQL) pour la configuration des comptes SIP (les tables sippeers2 et sipusers), l’utilisation de CPU n’augmente plus lors un RELOAD du démon Asterisk contrairement à la version 004 (fichier) l’utilisation peut augmenté jusqu’au 90% durant 2 minutes, en effet le RELOAD n’est plus nécessaire lors du changement d’un mot de passe (l’option RT cache est désactivé)
Ce problème a été fixé et réglé dans la version 005 qui n’était pas lancé en production.
Statistique
1-
Ce graphe montre qu’avec cette version 006 l’utilisation de CPU est optimisée par rapport à la version 005 (fichier).
2-
Ce graphe montre que l’utilisation de CPU est liée au nombre d’appel simultané.
Référence
http://switzernet.com/3/public/110523-astrad-wish-list/
http://switzernet.com/3/public/110421-astrad5-reg-stat/
http://switzernet.com/3/public/110411-astrad-test-case/
http://switzernet.com/3/public/110405-db3/
http://switzernet.com/3/public/110328-astrad-alternative-radius-auth/
http://switzernet.com/3/public/110322-astrad9-lost-registering-study/
http://switzernet.com/3/public/110321-db3-tests/
http://switzernet.com/3/public/110317-db3-versions/
http://switzernet.com/3/public/110317-astrad-no-problem-at-9h/
http://switzernet.com/3/public/110316-db3-setup/
http://switzernet.com/3/public/110316-astrad-triggers/
http://switzernet.com/3/public/110316-astrad-call-tests/
http://switzernet.com/3/public/110315-astrad-triggers-tests/
http://switzernet.com/3/public/110314-astrad-triggers/
http://switzernet.com/3/public/110303-asterisk-nat/
http://switzernet.com/3/public/110302-test-asterisk-mysql/
http://switzernet.com/3/public/110225-radius-invite/
http://switzernet.com/3/public/110126-astrad-versions/