Master Mysql Astrad DBA V002 (V003 mise à jour)

Oussama Hammami, 2011-05-24

Switzernet

Avant de foncer tête baissée dans la lecture de ce document, vous devez tenir compte des modifications et les bugs fixés dans les versions précédentes [http://switzernet.com/3/public/110405-db3/]

Dans la version 3 on a simplement ajouté une table ‘location2_history’ qui pressente l’historique des adresses et ports utilisés lors des enregistrements SIP.

Le fichier SQL pour une mise à jour manuelle depuis la version 2 : [http://switzernet.com/3/public/110317-db3-versions/data2/110615-db3-update.sql]

Pour Installation complète avec Puppet vous devez télécharger le ZIP depuis: http://switzernet.com/3/public/110317-db3-versions/

Vue d'ensemble

Les modifications par rapport à la version précédente (001) sont:

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')

Observation (au moment d’activation de ce changement dabs DB3)

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 location:

On a remplacé les tables FEDERATED par un script PERL (ast-registration) qui exécute les requêtes reconstruites à partir de la 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- astrad real time scalability

Avec cette version on a pratiquement les mêmes résultats que Astrad4 (fichiers) et on n'a pas besoin d'activer le cache Asterisk.

http://switzernet.com/3/public/110421-astrad5-reg-stat

Référence

MySQL Replication monitoring

http://portasip-monitor.switzernet.com/110418-check-rep/

Liste des fonctionnalités à ajouter dans Astrad

http://switzernet.com/3/public/110523-astrad-wish-list/

Astrad V006

http://switzernet.com/3/public/110509-astrad-correction/

Etude du fonctionnement d’Astrad V005

http://switzernet.com/3/public/110421-astrad5-reg-stat/

Installation d'un serveur ASTRAD V005: Tests de validation

http://switzernet.com/3/public/110411-astrad-test-case/

Master Mysql Astrad DB3 V001

http://switzernet.com/3/public/110405-db3/

Authentification Radius alternative

http://switzernet.com/3/public/110328-astrad-alternative-radius-auth/

Compréhension du problème de perte de l’enregistrement.

http://switzernet.com/3/public/110322-astrad9-lost-registering-study/

Tests sur les BDD du Serveur DB3

http://switzernet.com/3/public/110321-db3-tests/

Versions du Master MySQL-Astrad (DB3)

http://switzernet.com/3/public/110317-db3-versions/

SIP calls establishment problem from CET 9h to 10h in the morning

http://switzernet.com/3/public/110317-astrad-no-problem-at-9h/

Installation du Serveur DB3 (Master MySQL-Astrad V000)

http://switzernet.com/3/public/110316-db3-setup/

Préparation de la base de données (MySQL) pour Asterisk

http://switzernet.com/3/public/110316-astrad-triggers/

Tests d’appels sur Astrad 9 avec les bases de données répliqués

http://switzernet.com/3/public/110316-astrad-call-tests/

Tests de la préparation pour la base de données pour Asterisk

http://switzernet.com/3/public/110315-astrad-triggers-tests/

Préparation de la base de données (MySQL) pour Asterisk

http://switzernet.com/3/public/110314-astrad-triggers/

Asterisk et NAT

http://switzernet.com/3/public/110303-asterisk-nat/

Validation de la configuration d’Asterisk avec MySQL

http://switzernet.com/3/public/110302-test-asterisk-mysql/

Processing time statistics of invite authentication and authorization requests of the AAA radius server

http://switzernet.com/3/public/110225-radius-invite/

Installation of fail2ban for asterisk

http://switzernet.com/3/public/110216-fail2ban-asterisk/

Shared Iptables Blacklist with Puppet

http://switzernet.com/3/public/110207-puppet-iptables-blacklist/

Astrad Versionning

http://switzernet.com/3/public/110126-astrad-versions/

Introduction of Puppet

http://switzernet.com/3/public/110127-puppet-introduction/

Command execution with MySQL UDF

http://switzernet.com/3/public/110111-mysql-udf-sys/

Asterisk The Future of Telephony

http://switzernet.com/3/company/110301-asterisk-doc-pdf/1-Asterisk-The-Future-of-Telephony.pdf

Téléphonie sur IP

http://switzernet.com/3/company/110301-asterisk-doc-pdf/2-T%c3%a9l%c3%a9phonie-sur-IP.pdf

Asterisk La telephonie d'entreprise libre

http://switzernet.com/3/company/110301-asterisk-doc-pdf/3-Asterisk-La-telephonie-d-entreprise-libre.pdf

Asterisk Realtime (mysql)

http://switzernet.com/3/company/110217-asterisk-mysql/

Etude de problème d’enregistrement SIP avec des différents Time-Zone

http://switzernet.com/3/company/110210-register-time-zome/

Description des tâches 2010-03-03

http://switzernet.com/1/public/100303-planning-asterisk-project/

Implémentation de la fonction ‘Follow Me’ dans Asterisk avec PortaBilling

http://switzernet.com/1/public/100427-follow-me-asterisk/

Installer un nouveau serveur SIP Asterisk

http://switzernet.com/1/public/100520-install-asterisk/

Gestion des messages vocaux d’information dans Asterisk et installation de cette Mise à jour

http://switzernet.com/2/public/100719-asterisk-update/

Asterisk SIP proxy using RADIUS

http://switzernet.com/1/company/091106-asterisk-radius/

Asterisk SIP proxy using RADIUS

http://switzernet.com/1/company/091208-asterisk-radius/

Installation des codecs sur le serveur Asterisk (astrad.switzernet.com)

http://switzernet.com/1/company/091229-asterisk-codecs/

Configurer « astrad2.switzernet.com »

http://switzernet.com/1/company/100125-conf-astrad2/

Description des tâches

http://switzernet.com/1/public/100212-planning-asterisk-project/

* * *