Ejabberd Dissection

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

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

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/‎