oneDatabase

Currently, FreePBX has it settings strewn all over the place. This makes it difficult to manage the settings in anything but the default way. For example, using MySql? in a any redundant manner (master/master / master/slave) does not guaranty fully redundant FreePBX configs. Clustering or HA solutions are also much harder with the current implementation as there are multiple sources of data that need to be mirrored. The purpose of this page is to identify the different sources of data, the reason for the current way of using them and solutions to unify them. Yet an additional benefit here is backups (while the current backup module work - its more of a temporary work around for the poor implementation of things than a proper method for backing up)

For purpose of this page, the default default database is assumes to be mysql. Obviously this can be replaces with any other supported database (currently, sqlite)

databases & data sources

• Mysql
  • This is the main store for all things FreePBX. This is a great way of storage the configs as it s easy to mange, easy to backup, and easy to restore.
• AstDB
  • This is where FreePBX stores 'real time' information, such as the proper callerid for an extensions or its DID. This is also one of the hardest datastores to mirror. There are two approaches that can be taken here:
    1. To totally kill the AstDB as it relates to FreePBX and to pull all data in realtime from mysql. The obvious question here is what, if any, overhead consciousness are there here. update: see speed section below. --mbrevda
    2. To continue to use the AstDB, but to re-write it every time the configs are rewriten (this is relatively simple - first do a 'database deltree AMPUSER' and 'database deltree device' and then rewrite those keys.
    • There is also the issue that storing data in the astdb (in its current implementation) writes the data as soon as you hit submit (as apposed to after you click the orange bar). While this isn't a 'problem' per se, it leads to alot of user confusion due to its timing inconstancy.
    • nevertheless, I personally think that we are much better off storing everything in one central location, namely mysql, as this solves many, many more issues that an inconsistency that hasn't really bothered anyone all this time --mbrevda
• voicemail.conf
  • I totaly fail to understand why were still reading this file, instead of just storing the data in mysql and writing the file

speed

While it was a bit harder to clock cpu/memory usage, ive compiled a simple to test to see which is faster: astdb or mysql

the following is the dialplan that I used:

[load-test-ast]
exten => s,1,Answer
exten => s,n,System(echo `date +%s` > /tmp/loadtest)
exten => s,n,Set(DB(TEST/key)=0)
exten => s,n,While($[${DB(TEST/key)} < 10])
exten => s,n,Set(DB(TEST/key)=$[${DB(TEST/key)}+1])
exten => s,n,Endwhile
exten => s,n,System(echo `date +%s` >> /tmp/loadtest)

[load-test]
exten => s,1,Answer
exten => s,n,System(echo `date +%s` > /tmp/loadtest)
exten => s,n,Macro(update-mysql|UPDATE\ loadtest\ set\ count\=\"0"\ WHERE\ testnum\ like\ "1")
;exten => s,n,Hangup
exten => s,n,Macro(select-mysql|SELECT\ count\ from \loadtest\ WHERE\ testnum\ like\ "1")
exten => s,n,While($[${retval} < 100])
exten => s,n,Macro(update-mysql,UPDATE\ loadtest\ set\ count\=\"$[${retval}+1]"\ WHERE\ testnum\ like\ "1")
exten => s,n,Macro(select-mysql|SELECT\ count\ from \loadtest\ WHERE\ testnum\ like\ "1")
exten => s,n,Endwhile
exten => s,n,System(echo `date +%s` >> /tmp/loadtest)

[macro-select-mysql]
exten => s,1,MYSQL(Connect connid localhost root 123456 asterisk)
exten => s,n,MYSQL(Query resultid ${connid} ${ARG1})
exten => s,n,MYSQL(Fetch fetchid ${resultid} retval)
exten => s,n,Noop(retval is ${retval})
exten => s,n,MYSQL(Clear ${resultid})
exten => s,n,MYSQL(Disconnect ${connid})

[macro-update-mysql]
exten => s,1,MYSQL(Connect connid localhost root 123456 asterisk)
exten => s,n,MYSQL(Query resultid ${connid} ${ARG1})
exten => s,n,MYSQL(Fetch fetchid ${resultid} count)

exten => s,n,Noop(count is ${count})
exten => s,n,MYSQL(Clear ${resultid})
exten => s,n,MYSQL(Disconnect ${connid})

My system is:

[root@host ~]# uname -a
Linux host.pbx.local 2.6.18-53.el5 #1 SMP Mon Nov 12 02:22:48 EST 2007 i686 i686 i386 GNU/Linux
[root@host ~]# cat /proc/cpuinfo
processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 15
model name      : Intel(R) Xeon(R) CPU            3060  @ 2.40GHz
stepping        : 6
cpu MHz         : 2394.166
cache size      : 4096 KB

processor       : 1
vendor_id       : GenuineIntel
cpu family      : 6
model           : 15
model name      : Intel(R) Xeon(R) CPU            3060  @ 2.40GHz
stepping        : 6
cpu MHz         : 2394.166
cache size      : 4096 KB

[root@host ~]# cat /proc/meminfo
MemTotal:      2074144 kB

Using the above code, I changed the while statements to 10, 100, 1000, and 10,000. The following is the execution time in seconds (as they appeared in /tmp/loadtest): (seconds, less is better)

	10	100	1000	10000
astdb	<1	1	11	109
mysql	<1	<1	5	56

If you would like to replicate the test on your system, here is the mysql db info:

CREATE TABLE IF NOT EXISTS `loadtest` (
  `testnum` varchar(100) NOT NULL,
  `count` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `loadtest` (`testnum`, `count`) VALUES
('1', '0');

It seems like the winner is quite obvious!