Working on Ejabbered 2.1.13, in order to understand ejabbered db calls and processes, I followed this presentation - http://www.slideshare.net/ShaojieYang/ejabberd-installation-configuration-15508995
.
It showed an easy process to connect Ejabberd to an external database.
Here I would be going a step ahead and saving the DB calls Ejabberd makes, assuming this will be same, when it using its internal Mnesia DB.
When adding a new user(registration) from web admin - test3@192.168.1.250
----------------------------------------------------------------------------------------------------------
113 Query select password from users where username='admin'
112 Query select password from users where username='admin'
112 Query select password from users where username='test3'
104 Query insert into users(username, password) values ('test3', 'test3')
Below for count for Offline Messages and Last Activity shown in admin
-----------------------------------------------------------------------
108 Query select username from users
104 Query select count(*) from spool where username='admin'
104 Query select seconds, state from last where username='admin'
107 Query select count(*) from spool where username='admin3'
108 Query select seconds, state from last where username='admin3'
Connecting a user - test2@192.168.1.250
-------------------------------------------------------
109 Query select password from users where username='test2'
113 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test2'
104 Query select name from privacy_default_list where username='test2'
105 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test2'
112 Query select jid, grp from rostergroups where username='test2'
109 Query select password from users where username='test2'
113 Query select name from privacy_default_list where username='test2'
105 Query select name from privacy_list where username='test2'
105 Query select id from privacy_list where username='test2' and name='invisible'
110 Query begin
110 Query select id from privacy_list where username='test2' and name='invisible'
110 Query insert into privacy_list(username, name) values ('test2', 'invisible')
110 Query select id from privacy_list where username='test2' and name='invisible'
110 Query delete from privacy_list_data where id='1'
110 Query insert into privacy_list_data(id, t, value, action, ord, match_all, match_iq, match_message, match_presence_in, match_presence_out ) values ('1', 'n', '', 'd', '1', '0', '0', '0', '0', '1')
110 Query commit
104 Query begin
104 Query select username, xml from spool where username='test2' order by seq
104 Query delete from spool where username='test2'
104 Query commit
108 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test2'
110 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test2'
113 Query select jid, grp from rostergroups where username='test2'
105 Query select subscription from rosterusers where username='test2' and jid='test2@192.168.1.250'
112 Query select subscription from rosterusers where username='test2' and jid='test2@192.168.1.250'
112 Query select subscription from rosterusers where username='test2' and jid='test2@192.168.1.250'
104 Query select subscription from rosterusers where username='test2' and jid='test2@192.168.1.250'
111 Query select subscription from rosterusers where username='test2' and jid='test2@192.168.1.250'
Adding contact - test1@192.168.1.250 by test4
--------------------------------------------------------------
122 Query begin
122 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test4' and jid='test1@192.168.1.250'
122 Query update rosterusers set username='test4', jid='test1@192.168.1.250', nick='', subscription='N', ask='N', askmessage='', server='N', subscribe='', type='item' where username='test4' and jid='test1@192.168.1.250'
122 Query insert into rosterusers(username, jid, nick, subscription, ask, askmessage, server, subscribe, type) values ('test4', 'test1@192.168.1.250', '', 'N', 'N', '', 'N', '', 'item')
122 Query delete from rostergroups where username='test4' and jid='test1@192.168.1.250'
122 Query commit
131 Query begin
131 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test4' and jid='test1@192.168.1.250'
131 Query select grp from rostergroups where username='test4' and jid='test1@192.168.1.250'
131 Query update rosterusers set username='test4', jid='test1@192.168.1.250', nick='', subscription='N', ask='O', askmessage='', server='N', subscribe='', type='item' where username='test4' and jid='test1@192.168.1.250'
131 Query commit
129 Query select name from privacy_default_list where username='test1'
128 Query select password from users where username='test1'
123 Query begin
123 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test1' and jid='test4@192.168.1.250'
123 Query update rosterusers set username='test1', jid='test4@192.168.1.250', nick='', subscription='N', ask='I', askmessage='', server='N', subscribe='', type='item' where username='test1' and jid='test4@192.168.1.250'
123 Query insert into rosterusers(username, jid, nick, subscription, ask, askmessage, server, subscribe, type) values ('test1', 'test4@192.168.1.250', '', 'N', 'I', '', 'N', '', 'item')
123 Query commit
Memory footprint
-----------------------
connecting 100 clients to ejabberd - no issue,
there are 9 instances running with around 70mb each.
Used this script to hit to server (Uses XMPPY )
-----------------------------------------
It assumes that we have test users already registered with username test1, test2.. and pass test1 and test2. (ejabberdctl register command can be used to make such users)
from xmpp import *
clients = []
for i in range(100):
clients.append(Client('192.168.1.250'))
for cl in clients:
cl.connect(server=('192.168.1.250',5222))
for i in range(1,101):
clients[i-1].auth('test%d'%i,'test%d'%i)
while True:
for cl in clients:
cl.sendPresence()
cl.Process(2)
Sum good post
http://metajack.im/2008/08/27/migrating-to-ejabberd-the-gory-details/
.
It showed an easy process to connect Ejabberd to an external database.
Here I would be going a step ahead and saving the DB calls Ejabberd makes, assuming this will be same, when it using its internal Mnesia DB.
When adding a new user(registration) from web admin - test3@192.168.1.250
----------------------------------------------------------------------------------------------------------
113 Query select password from users where username='admin'
112 Query select password from users where username='admin'
112 Query select password from users where username='test3'
104 Query insert into users(username, password) values ('test3', 'test3')
Below for count for Offline Messages and Last Activity shown in admin
-----------------------------------------------------------------------
108 Query select username from users
104 Query select count(*) from spool where username='admin'
104 Query select seconds, state from last where username='admin'
107 Query select count(*) from spool where username='admin3'
108 Query select seconds, state from last where username='admin3'
Connecting a user - test2@192.168.1.250
-------------------------------------------------------
109 Query select password from users where username='test2'
113 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test2'
104 Query select name from privacy_default_list where username='test2'
105 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test2'
112 Query select jid, grp from rostergroups where username='test2'
109 Query select password from users where username='test2'
113 Query select name from privacy_default_list where username='test2'
105 Query select name from privacy_list where username='test2'
105 Query select id from privacy_list where username='test2' and name='invisible'
110 Query begin
110 Query select id from privacy_list where username='test2' and name='invisible'
110 Query insert into privacy_list(username, name) values ('test2', 'invisible')
110 Query select id from privacy_list where username='test2' and name='invisible'
110 Query delete from privacy_list_data where id='1'
110 Query insert into privacy_list_data(id, t, value, action, ord, match_all, match_iq, match_message, match_presence_in, match_presence_out ) values ('1', 'n', '', 'd', '1', '0', '0', '0', '0', '1')
110 Query commit
104 Query begin
104 Query select username, xml from spool where username='test2' order by seq
104 Query delete from spool where username='test2'
104 Query commit
108 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test2'
110 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test2'
113 Query select jid, grp from rostergroups where username='test2'
105 Query select subscription from rosterusers where username='test2' and jid='test2@192.168.1.250'
112 Query select subscription from rosterusers where username='test2' and jid='test2@192.168.1.250'
112 Query select subscription from rosterusers where username='test2' and jid='test2@192.168.1.250'
104 Query select subscription from rosterusers where username='test2' and jid='test2@192.168.1.250'
111 Query select subscription from rosterusers where username='test2' and jid='test2@192.168.1.250'
Adding contact - test1@192.168.1.250 by test4
--------------------------------------------------------------
122 Query begin
122 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test4' and jid='test1@192.168.1.250'
122 Query update rosterusers set username='test4', jid='test1@192.168.1.250', nick='', subscription='N', ask='N', askmessage='', server='N', subscribe='', type='item' where username='test4' and jid='test1@192.168.1.250'
122 Query insert into rosterusers(username, jid, nick, subscription, ask, askmessage, server, subscribe, type) values ('test4', 'test1@192.168.1.250', '', 'N', 'N', '', 'N', '', 'item')
122 Query delete from rostergroups where username='test4' and jid='test1@192.168.1.250'
122 Query commit
131 Query begin
131 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test4' and jid='test1@192.168.1.250'
131 Query select grp from rostergroups where username='test4' and jid='test1@192.168.1.250'
131 Query update rosterusers set username='test4', jid='test1@192.168.1.250', nick='', subscription='N', ask='O', askmessage='', server='N', subscribe='', type='item' where username='test4' and jid='test1@192.168.1.250'
131 Query commit
129 Query select name from privacy_default_list where username='test1'
128 Query select password from users where username='test1'
123 Query begin
123 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test1' and jid='test4@192.168.1.250'
123 Query update rosterusers set username='test1', jid='test4@192.168.1.250', nick='', subscription='N', ask='I', askmessage='', server='N', subscribe='', type='item' where username='test1' and jid='test4@192.168.1.250'
123 Query insert into rosterusers(username, jid, nick, subscription, ask, askmessage, server, subscribe, type) values ('test1', 'test4@192.168.1.250', '', 'N', 'I', '', 'N', '', 'item')
123 Query commit
Now asks for permission
in rosterusers table - subscription field is N (related to pending as others which are already accepted contacts have B) and ask field is O for test4 and I for test1 (test4 added test1)
Ater accepting
-------------------
122 Query begin
122 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test4' and jid='test1@192.168.1.250'
122 Query update rosterusers set username='test4', jid='test1@192.168.1.250', nick='', subscription='N', ask='N', askmessage='', server='N', subscribe='', type='item' where username='test4' and jid='test1@192.168.1.250'
122 Query insert into rosterusers(username, jid, nick, subscription, ask, askmessage, server, subscribe, type) values ('test4', 'test1@192.168.1.250', '', 'N', 'N', '', 'N', '', 'item')
122 Query delete from rostergroups where username='test4' and jid='test1@192.168.1.250'
122 Query commit
131 Query begin
131 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test4' and jid='test1@192.168.1.250'
131 Query select grp from rostergroups where username='test4' and jid='test1@192.168.1.250'
131 Query update rosterusers set username='test4', jid='test1@192.168.1.250', nick='', subscription='N', ask='O', askmessage='', server='N', subscribe='', type='item' where username='test4' and jid='test1@192.168.1.250'
131 Query commit
129 Query select name from privacy_default_list where username='test1'
128 Query select password from users where username='test1'
123 Query begin
123 Query select username, jid, nick, subscription, ask, askmessage, server, subscribe, type from rosterusers where username='test1' and jid='test4@192.168.1.250'
123 Query update rosterusers set username='test1', jid='test4@192.168.1.250', nick='', subscription='N', ask='I', askmessage='', server='N', subscribe='', type='item' where username='test1' and jid='test4@192.168.1.250'
123 Query insert into rosterusers(username, jid, nick, subscription, ask, askmessage, server, subscribe, type) values ('test1', 'test4@192.168.1.250', '', 'N', 'I', '', 'N', '', 'item')
123 Query commit
-----------------------
connecting 100 clients to ejabberd - no issue,
there are 9 instances running with around 70mb each.
Used this script to hit to server (Uses XMPPY )
-----------------------------------------
It assumes that we have test users already registered with username test1, test2.. and pass test1 and test2. (ejabberdctl register command can be used to make such users)
from xmpp import *
clients = []
for i in range(100):
clients.append(Client('192.168.1.250'))
for cl in clients:
cl.connect(server=('192.168.1.250',5222))
for i in range(1,101):
clients[i-1].auth('test%d'%i,'test%d'%i)
while True:
for cl in clients:
cl.sendPresence()
cl.Process(2)
http://metajack.im/2008/08/27/migrating-to-ejabberd-the-gory-details/
