ejabberd - Comments for "Performance Mnesia vs. MySQL and even row numbers after export/import to MySQL"

MySQL stuff

Roi — Mon, 22 Dec 2008 23:57:17 +0000

Roi wrote:

Oh I'm sorry. I was not writing what I wanted to. The database names did not contain dots. It looked like this:

localhost_ejabberd_jabber-localhost-com

Dots are replaced with dashes.

With phpMyAdmin it is possible to create datebase and user names like this.

badlop wrote:

Doesn't help to use slash:

Hm that's interesting. phpMyAdmin does allow it. Does anybody know what database and user names are "legal" in MySQL?!?

Roi wrote:

there is a 64 character limit for database names and a 16 character limit for usernames

badlop wrote:

Regarding the reported limit of 16-char for usernames: I can register an account in ejabberd, with MySQL storage, with up to 40 characters:

NO NO! ;-)

I was talking about a 16 character limit for MySQL user names! :-)

The column for user names is limited to 16 characters so there has to be this limit. Also the column for database names is limited to 64 characters.

I did not check the limits for jabber users.

Limit in database name imposed by MySQL?

mfoss — Mon, 22 Dec 2008 23:42:34 +0000

Roi wrote:

Oh I'm sorry. I was not writing what I wanted to. The database names did not contain dots. It looked like this:

localhost_ejabberd_jabber-localhost-com

Dots are replaced with dashes.

With phpMyAdmin it is possible to create datebase and user names like this.

Doesn't help to use slash:

Quote:

$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 88
Server version: 5.0.67-1 (Debian)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE DATABASE localhost_ejabberd_jabber-localhost-com DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-localhost-com DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci' at line 1

mysql> CREATE DATABASE localhost_ejabberd_jabber_localhost_com DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.02 sec)

mysql> \q
Bye

So, I can't create a database with name that contains . or -

Roi wrote:

there is a 64 character limit for database names and a 16 character limit for usernames

I didn't verify the 64-char limit in database name.

Regarding the reported limit of 16-char for usernames: I can register an account in ejabberd, with MySQL storage, with up to 40 characters:

Quote:

=INFO REPORT==== 23-Dec-2008::00:31:47 ===
I(<0.368.0>:ejabberd_listener:119) : (#Port<0.474>) Accepted connection {{127,0,0,1},38521} -> {{127,0,0,1},5222}

=INFO REPORT==== 23-Dec-2008::00:31:48 ===
I(<0.383.0>:ejabberd_c2s:697) : ({socket_state,gen_tcp,#Port<0.474>,<0.382.0>}) Accepted authentication for a234567890b234567890c234567890d234567890

=INFO REPORT==== 23-Dec-2008::00:31:48 ===
I(<0.383.0>:ejabberd_c2s:809) : ({socket_state,gen_tcp,#Port<0.474>,<0.382.0>}) Opened session for a234567890b234567890c234567890d234567890@localhost/Tka

The account exists in the database:

Quote:

Re: Can database name have dots?

Roi — Mon, 22 Dec 2008 23:00:10 +0000

Oh I'm sorry. I was not writing what I wanted to. The database names did not contain dots. It looked like this:

localhost_ejabberd_jabber-localhost-com

Dots are replaced with dashes.

With phpMyAdmin it is possible to create datebase and user names like this. Also there is a 64 character limit for database names and a 16 character limit for usernames. ejabberd seems to have problems eigher with the length of the database and/or username or _ and/or - in database and/or user names.

Can database name have dots?

mfoss — Mon, 22 Dec 2008 16:00:18 +0000

Roi wrote:

It seems that ejabberd has problems with special database names (and maybe also with special user names). My database naming schema is localhost_ejabberd_jabber.domain.tld (localhost because no webspace domain is affected, then the program name and then the jabber domain which is affected). Usernames are similar but shorter because of the 16 character limitation. I got errors that ejabberd failed_changing_database and after finally changing database name AND username to ejabberd01 and everything went through smoothly.

I think this is a issue to fix or at least talk about in the documentation.

Note that I knoe few of MySQL. But I can't create a database with special characters:

Quote:

$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 87
Server version: 5.0.67-1 (Debian)

mysql> CREATE DATABASE localhost_ejabberd_jabber.localhost.com DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.localhost.com DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci' at line 1

mysql> CREATE DATABASE localhost_ejabberd_jabber_localhost_com DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

Roi wrote:

Is this list correct or is there any file missing or wrong?

Yes, all your comments are correct.

Re: With 150 concurrent users Mnesia is good.

Roi — Mon, 22 Dec 2008 15:16:11 +0000

Hello,

I just migrated to MySQL. I got the MySQL driver from a nice German guy who already compiled it. I just had to copy the four beam files to the right directory and everything was set. Perfect! :-) But thank you for the instructions above! I'm sure other people will love and need it, too.

Then the problems started. It seems that ejabberd has problems with special database names (and maybe also with special user names). My database naming schema is localhost_ejabberd_jabber.domain.tld (localhost because no webspace domain is affected, then the program name and then the jabber domain which is affected). Usernames are similar but shorter because of the 16 character limitation. I got errors that ejabberd failed_changing_database and after finally changing database name AND username to ejabberd01 and everything went through smoothly.

I think this is a issue to fix or at least talk about in the documentation.

BTW: What Mnesia database files can I move to backup?

The export command created these files for me:

last.txt
offline.txt
passwd.txt
private_storage.txt
roster.txt
vcard_search.txt
vcard.txt

I now use the following modules as odbc version:

mod_last_odbc
mod_offline_odbc
mod_privacy_odbc
mod_private_odbc
mod_roster_odbc
mod_vcard_odbc

When I checked ejabberd's mnesia directory I saw some files which could be useless and are better stored at my backup location (as I don't want to try to remember in a year what files are still necessary and which not):

last_activity.DCD
last_activity.DCL
offline_msg.DAT
passwd.DCD
passwd.DCL
private_storage.DAT
roster.DCD
vcard.DAT
vcard_search.DCD
vcard_search.DCL

Is this list correct or is there any file missing or wrong?

Regards,
Martin

With 150 concurrent users Mnesia is good. Instructions for mysql

mfoss — Mon, 22 Dec 2008 11:39:45 +0000

Roi wrote:

2500 accounts at all and about 50 to 150 concurrent users on the server.

But it sounds like I'm going for MySQL after what I heard from you.

I help to administer a server with 25000 registered accounts, 500 concurrent users maximum, 2400 different accounts were used in the last month. It uses the default Mnesia, and there hasn't been any problem, and CPU/RAM usage is low.

So, I think right now with just 150 max concurrent users, performance of Mnesia is not an issue for you.

Roi wrote:

Problem now is that the Debian package of ejabberd isn't supporting MySQL and I won't compile my own package. I hope there will be a development in this issue shortly as other people also request MySQL support for the Debian package.

You already have the erlang compiler intalled. You only need to download with Subversion, compile and install with the ejabberd *.beam files:

$ svn co http://svn.process-one.net/ejabberd-modules/mysql/trunk mysql-erlang
A    mysql-erlang/Emakefile
A    mysql-erlang/build.bat
A    mysql-erlang/INSTALL
A    mysql-erlang/src
A    mysql-erlang/src/mysql_conn.erl
A    mysql-erlang/src/mysql_recv.erl
A    mysql-erlang/src/mysql.erl
A    mysql-erlang/src/mysql_auth.erl
A    mysql-erlang/src/mysql.hrl
A    mysql-erlang/build.sh
A    mysql-erlang/ebin
 U   mysql-erlang
Revisión obtenida: 843

$ cd mysql-erlang/

$ ./build.sh
Recompile: src/mysql_recv
Recompile: src/mysql_conn
Recompile: src/mysql_auth
Recompile: src/mysql

$ sudo cp ebin/*.beam /var/lib/ejabberd/ebin/

Re: *SQL performs better. Compare size of tables

Roi — Mon, 22 Dec 2008 09:17:23 +0000

Hello and thanks for your reply. :-)

No, 2500 accounts at all and about 50 to 150 concurrent users on the server.

But it sounds like I'm going for MySQL after what I heard from you.

Thank you for the hint with the Web Admin. It really seems that I have 800 vcards even, interesting. ;-)

Regards,
Martin

*SQL performs better. Compare size of tables

mfoss — Sun, 21 Dec 2008 22:35:23 +0000

Roi wrote:

I'm running a ejabberd server with ~2500 users.

As I'm thinking about migrating to MySQL: Can anybody tell my how the performance is when comparing Mnesia with MySQL?

2500 concurrent users, or total registered accounts? The important factor is concurrent users.

ejabberd consumes less computational resources when using some *SQL database than when using internal Mnesia. You are probably interested in that topic when you have many concurrent users (more than 1000, for example). With few concurrent users ejabberd's CPU consumption is negligible so admins of small servers don't care to setup an external SQL server and database.

Roi wrote:

Then I imported the data to a MySQL database.

This is the result (cut-n-pasted from phpMyAdmin):

last 2,000
privacy_default_list 0
privacy_list 0
privacy_list_data 0
private_storage 642
rostergroups 8,283
rosterusers ~46,566
spool 2,936
users 2,464
vcard 800
vcard_search 800

The numbers are the rows of each table. I'm wondering why "last", "vcard" and "vcard_search" contain a even number of rows. Is this just a coincidence or did the export not work properly?

It's suspicious. You can check the number of items in the tables in Mnesia, and compare with the exported results. Go to ejabberd Web Admin -> Nodes -> your node -> Database.