Choose the Optimal MySQL Data Type

How to Choose the Optimal MySQL Data Type

21st January 2010, 22:03

The MySQL relational database management system (RDBMS) supports a wide range of data types. The data type that you chose for your table columns is important because it determines how MySQL will store your data. It’s possible that any one of multiple data types could be used to store a piece of data, but which one is the best or optimal data type to use?

What’s Optimal?

I would define optimal as taking the least amount of storage space and/or being the fastest in terms of search or data retrieval. The MySQL manual says:

“For optimum storage, you should try to use the most precise type in all cases. For example, if an integer column is used for values in the range from 1 to 99999, MEDIUMINT UNSIGNED is the best type. Of the types that represent all the required values, this type uses the least amount of storage.”

MySQL Data Types

MySQL supports a number of data types in several categories: numeric types, date and time types, and string (character) types. Data types have been created to serve a specific purpose. Some are optimal for storing small amounts of text, others large amounts of text, others dates, others numbers etc… When creating your tables it is best practice to understand what type of data each column will be storing and choose the appropriate data type. This is a large and complex area so I aim to give a brief overview with some useful examples and tips.

Example of Incorrect Data Type Usage

The TEXT data type is designed to store string types and has a maximum length of 65,535 bytes (roughly equivalent to the same number of characters). Therefore it’s intended for storing relatively large strings. The VARCHAR data type stores variable-length strings with a range of 0 to 255 before (MySQL 5.0.3). Therefore this is intended for storing relatively small strings. The storage required for TEXT columns is the length of the data plus 2 bytes, whereas the VARCHAR columns require the length of the data plus one byte. It’s not much of a saving but when you have a table with millions of records every little helps!
When storing a number you could theoretically use TEXT or VARCHAR data types and you’d still be able to add, edit, delete and search your data as you would expect. However, it’s much better to store the number in one of MySQL’s numeric data types. Storing a number using the optimal data type means that it uses less storage space and your table can be searched faster.

Examples and Best Practices

Numbers

Numbers should usually be stored using one of MySQL’s numeric data types. The numeric data types that you’ll probably use most often are TINYINT, SMALLINT, MEDIUMINT, INT and DECIMAL.
The first four numeric data types are used to store integers (whole numbers including negative numbers) with varying minimum and maximum values. For example, TINYINT columns can store integers ranging from -128 to 127 and INT can store integers ranging from -2147483648 to 2147483647. The storage requirement for TINYINT columns is 1 byte and for INT columns it is 4 bytes. (SMALLINT and MEDIUMINT cover the numbers in between these values.) Therefore if you know that a column will hold integers ranging from 1 to 99 you should use the TINYINT data type, as it requires less storage.

Unsign Your Integers!

You can extend the numeric range of these numeric data types by giving them the UNSIGNED attribute. This means that negative values are not permitted and allows TINYINT columns to store integers ranging from 0 to 255 and INT columns to store integers ranging from 0 to 4294967295. This means that you can use a data type with a smaller storage requirement for a larger number of negative values are not needed.

Storing Money in MySQL

Or more accurately, storing monetary values! Whilst you could, or course, store monetary values as strings (e.g. VARCHAR), the optimal data type is DECIMAL. This numeric data type is used to store exact numeric data values (i.e. numbers with a decimal point). The MySQL manual explains that DECIMAL is “used to store values for which it is important to preserve exact precision, for example with monetary data“.
When declaring a DECIMAL column the precision and scale are specified, for example DECIMAL(5,2). MySQL explains that:

“In this example, 5 is the precision and 2 is the scale. The precision represents the number of significant digits that are stored for values, and the scale represents the number of digits that can be stored following the decimal point. If the scale is 0, DECIMAL and NUMERIC values contain no decimal point or fractional part.“

Strings

Strings (characters, text etc…) can also be stored using a number of different data types. The main types that you’ll come across are CHAR, VARCHAR and TEXT. As we saw previously, the TEXT data type is used to store large strings (65,535 bytes) so you’d usually use this to store larger amounts of text.
So what about storing short strings? Should you use CHAR or VARCHAR? Both data types can hold between 0 and 255 bytes (VARCHAR can hold up to 65,535 bytes as of MySQL 5.0.3) so how to you choose? Each data type has its advantages and disadvantages.

CHAR

The length of a CHAR column is fixed to the length that you declare when you create the table, and stored values are right-padded with spaces to the specified length. So if you define a column as CHAR(255) even if you only insert a few characters the storage requirement will be 255 bytes. However, CHAR columns also have a speed advantage. Because they are fixed-length, MySQL can search CHAR columns faster than variable-length columns. However, a caveat is that the whole table must be fixed-length in order to benefit from the increased performance. If you have any other VARCHAR or TEXT columns in the table then the rows become variable-length and there is no speed increase.

VARCHAR

Values in VARCHAR columns are variable-length strings. In contrast to CHAR, VARCHAR values are stored as a one-byte length prefix plus data. So as with the example above, if you define a column as VARCHAR(255) and only insert 5 characters the storage requirement will only be 6 bytes. Therefore if your strings will be of variable length, the VARCHAR data type will require less storage.

CHAR vs. VARCHAR

If your table will only contain fixed-length columns then use CHAR to take advantage of MySQL’s faster searching. However, if any of your columns will be variable length then use VARCHAR for its smaller storage requirement.

Dates

Whilst you could store a date as a text string, MySQL has more optimal ways of storing dates!

Unix Timestamp

One perfectly valid way to store dates would be as a Unix timestamp. A Unix timestamp is a 10-digit representation of the number of seconds since the Unix epoch of 1st January 1970. This could be stored as a CHAR column, requiring 10 bytes storage, but the most optimal data type is the numeric INT(UNSIGNED) type, requiring just 4 bytes storage.
Unix timestamps are particularly useful if you’re using PHP, as the date() function will display them in your chosen format and you can calculate dates by adding or subtracting a number of seconds (86400 is equivalent to 24 hours etc…).
One drawback of using Unix timestamps is that they’re not human readable straight from the database. Would you know what date 1262986313 refers to? Another is that due to the 10-digit length, Unix timestamps may have problems at 3:14:07AM on 19th January 2038 when the value 9999999999 is reached!

DATETIME

Until recently I used Unix timestamps to store dates in MySQL, but I’ve now started using the DATETIME data type. This is designed specifically to store both the date and time as 2010-01-08 15:00:00 (for example).
One disadvantage is that this requires 8 bytes storage – double that of a Unix timestamp. But I believe that the advantages of using DATETIME rather than Unix timestamps outweighs this disadvantage.
Firstly dates stored using DATETIME are human-readable straight out of the database. So if you’re browsing your data with phpMyAdmin you can see what your dates actually mean!
Secondly MySQL has a whole host of built-in functions to work with native DATETIME formats. You can add and subtract dates, calculate the difference between dates, output the date in your preferred format and much more using MySQL alone and without involving additional PHP (for example) scripting.
And finally, if you really want to output your DATETIME column as a Unix timestamp, MySQL has a built-in function to do that, too!
Note that MySQL can also store the year alone, the date alone and a number of related types.

Miscellaneous

ENUM

ENUM columns provide an efficient way to define columns that can contain only a given set of values. So if a column will only ever contain ‘var 1′, ‘var 2′ or ‘var 3′ (for example), you should use the ENUM data type. Due to the way that MySQL stores the data it is much more efficient than storing the data as a string.
An example of when this is useful is setting a switch to specify whether a user account is active or disabled. Instead of storing ‘active’ or ‘disabled’ as VARCHAR values, use ENUM instead.

Conclusion

Although data can often be stored in various data type columns, there’s usually one data type that is optimal for your data. Using that optimal data type will bring benefits in terms of storage and possibly speed, and is best practice for any website developer.
Reference Link
http://www.developwebsites.net/choose-optimal-mysql-data-type/

mysql query optimization tips

Three easy ways to optimize your MySQL queries

Any database programmer will tell you that in high-traffic database-driven applications, a single badly-designed SQL query can significantly impact the overall performance of your application. Not only does such a query consume more database time than it ideally should, but it can have an exponential effect on the performance of other application components.
Optimizing query performance is as much a black art as a science, as heavily dependent on the developer's intuition as on hard statistical performance data. Fortunately, databases likes MySQL come with some tools to aid the process, and this article discusses three of them briefly: using indexes, analyzing queries with EXPLAIN, and adjusting MySQL's internal configuration.

#1: Using indexes

MySQL allows you to index database tables, making it possible to quickly seek to records without performing a full table scan first and thus significantly speeding up query execution. You can have up to 16 indexes per table, and MySQL also supports multi-column indexes and full-text search indexes.
Adding an index to a table is as simple as calling the CREATE INDEX command and specifying the field(s) to index. Listing A shows you an example:

Listing A

mysql> CREATE INDEX idx_username ON users(username);
Query OK, 1 row affected (0.15 sec)
Records: 1  Duplicates: 0  Warnings: 0 Here, indexing the username field of the users table ensures that SELECT queries which reference this field in their WHERE or HAVING clause will run a little faster than in the pre-indexed state. You can check that the index was created (Listing B) with the SHOW INDEX command:

Listing B

mysql> SHOW INDEX FROM users;
--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| users |          1 | idx_username |            1 | username    | A         |      NULL |     NULL | NULL   | YES  | BTREE      |         |
--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec) It's important to note that indexes are a double-edged sword. Indexing every field of a table is usually unnecessary, and is quite likely to slow things down significantly when inserting or updating data because of the additional work MySQL has to do to rebuild the index each time. On the other hand, avoiding indexes altogether isn't such a great idea either, because while this will speed up INSERTs, it will cause SELECT operations to slow down. There is thus always a trade-off to be made, and it's wise to consider what the primary function of the table will be (data retrieval or data edit) when designing the indexing system.

#2: Optimizing query performance

When analyzing query performance, it's also useful to consider the EXPLAIN keyword. This keyword, when placed in front of a SELECT query, describes how MySQL intends to execute the query and the number of rows it will need to process to successfully deliver a result set. To illustrate, consider the following simple example (Listing C):

Listing C

mysql> EXPLAIN SELECT city.name, city.district FROM city, country WHERE city.countrycode = country.code AND country.code = 'IND';
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | country | const | PRIMARY       | PRIMARY | 3       | const |    1 | Using index |
|  1 | SIMPLE      | city    | ALL   | NULL          | NULL    | NULL    | NULL | 4079 | Using where |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec) Here, the query is structured as a join between two tables and the EXPLAIN keyword describes how MySQL will process the join. It should be clear the current design will require MySQL to process only one record in the country table (which is indexed) but all 4079 records in the city table (which isn't). This then suggests scope for improvement using other optimization tricks - for example, adding an index to the city table as follows (Listing D):

Listing D

mysql> CREATE INDEX idx_ccode ON city(countrycode);
Query OK, 4079 rows affected (0.15 sec)
Records: 4079  Duplicates: 0  Warnings: 0 And now, when you re-run the query with EXPLAIN, you'll see a noticeable improvement (Listing E):

Listing E

mysql> EXPLAIN SELECT city.name, city.district FROM city, country WHERE city.countrycode = country.code AND country.code = 'IND';
+----+-------------+---------+-------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table   | type  | possible_keys | key       | key_len | ref   | rows | Extra       |
+----+-------------+---------+-------+---------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | country | const | PRIMARY       | PRIMARY   | 3       | const |    1 | Using index |
|  1 | SIMPLE      | city    | ref   | idx_ccode     | idx_ccode | 3       | const |  333 | Using where |
+----+-------------+---------+-------+---------------+-----------+---------+-------+------+-------------+
2 rows in set (0.01 sec) As this illustrates, MySQL now only needs to scan 333 records in the city table to produce a result set -- a reduction of almost 90 percent! Naturally, this translates into faster query execution time and more efficient usage of database resources.

#3: Adjusting internal variables

MySQL is so open that it's fairly easy to further fine-tune its default settings to obtain greater performance and stability. Some of the key variables that should be optimized are listed below.

• Altering Index Buffer Size (key_buffer)
  This variable controls the size of the buffer used when handling table indices (both read and write operations). The MySQL manual recommends that this variable be increased "to as much as you can afford" to ensure you get the best performance on indexed tables, and recommends a value equivalent to about 25 percent of the total system memory. This is one of the more important MySQL configuration variables and if you're interested in optimizing and improving performance, trying different values for the key_buffer_size variable is a good place to start.
• Altering Table Buffer Size (read_buffer_size)
  When a query requires a table to be scanned sequentially, MySQL allocates a memory buffer to this query. The read_buffer_size variable controls the size of this buffer. If you find that sequential scans are proceeding slowly, you can improve performance by increasing this value, and hence the size of the memory buffer.
• Setting The Number Of Maximum Open Tables (table_cache)
  This variable controls the maximum number of tables MySQL can have open at any one time, and thus controls the server's ability to respond to incoming requests. This variable is closely related to the max_connections variables -- increasing this value allows MySQL to keep a larger number of tables open, just as increasing max_connections increases the number of allowed connections. Consider altering this value if you have a high-volume server which receives queries on multiple different databases and tables.
• Deciding A Time Limit For Long Queries (long_query_time)
  MySQL comes with a so-called "slow query log", which automatically logs all queries that do not end within a particular time limit. This log is useful to track inefficient or misbehaving queries, and to find targets for optimization algorithms. The long_query_time variable controls this maximum time limit, in seconds.

The previous discussion should give you some insight into three tools you can use to analyze and optimize your SQL queries, and help you squeeze better performance out of your application. Go on and try them out -- and happy optimizing!

Reference Link
http://www.techrepublic.com/article/three-easy-ways-to-optimize-your-mysql-queries/6137168

 

Dates in PHP (Tomorrow, Yesterday, Today of a Given Date)

Dates in PHP (Tomorrow, Yesterday, Today of a Given Date)

<?php
echo “<h4>Date Yesterday</h4>”;
$yesterday = date(“Y-m-d”, time()-86400);
echo $yesterday;
echo “<h4>Date Today</h4>”;
echo date(“Y-m-d”);
echo “<h4>Date Tomorrow</h4>”;
$tomorrow = date(“Y-m-d”, time()+86400);
echo $tomorrow;
echo “<hr>”;
echo “<h4>Previous Date from User-defined Date</h4>”;
$gdate = “2008-07-11″;
echo “(” . $gdate . ” supplied.) <br>”;
$dt = strtotime($gdate);
echo date(“Y-m-d”, $dt-86400);
echo “<h4>Next Date from User-defined Date</h4>”;
echo “(” . $gdate . ” supplied.) <br>”;
$dt = strtotime($gdate);
echo date(“Y-m-d”, $dt+86400);
// DAYS IN BETWEEN TWO DATES
function days_in_between($s, $e){
$d = array();
$s = strtotime($s);
$e = strtotime($e);
for($n=$s; $n<=$e; $n=$n+86400){
array_push($d, strftime(“%Y-%m-%d”, $n));
}
return $d;
}
?>

 

OpenSIPS 1.8.2: Load Balancer on a Redis Cluster

OpenSIPS 1.8.2: Load Balancer on a Redis Cluster

OpenSIPS 1.8.2: Load Balancer on a Redis Cluster

In this article we will see how to configure OpenSIPS with two servers in a cluster module Redis Load_Balancer (a database non-relational NoSQL Family). This module via chachedb_redis. In this way the two servers share the load in real time server configuration presented in BALANCING load. This type of scenario may be an optimal solution for SIP termination providers or generally for any needed configuration where redundant media server and OpenSIPS Server Cluster.

In the two servers is installed minimal version of CentOS 6.3

A server Local IP: 192.168.168.60

Local IP Server B: 192.168.180.70

In both servers:

yum update -y
reboot
We entered again on both servers and continue with the installation of the required packages for the whole process:

yum install mysql mysql-devel mysql-server gcc gcc-c++ bison bison-devel flex make expat expat-devel unixODBC-devel net-snmp-devel
yum install subversion libxml2 libxml2-devel openssl-devel xmlrpc-c-devel lynx pcre pcre-devel ncurses-devel ncurses-libs

To Redis:

yum install tcl git ruby

OpenSIPS CACHEDB_REDIS module is based on client Redis "hiredis". It will download, compile and install:

cd /usr/src
git clone https://github.com/redis/hiredis.git hiredis
cd hiredis
make
make install

The download version 1.8.2 of OpenSIPS:

cd /usr/src
svn co https://opensips.svn.sourceforge.net/svnroot/opensips/branches/1.8 opensips_1_8
cd opensips_1_8
make menuconfig

You enter the menu "Set Compile Options" and then "Configure Excluded Modules". Modules are selected "cachedb_redis", "db_mysql" and "regex".

He turns back to the <-, select "Save Changes" and press the Send key twice. He turns back to the <- and select "" Exit & Save All Changes ".

Shipping key is pressed to exit the menu. It compiles and installs:

make prefix=/ all
make prefix=/ install

Is installed OpenSIPS startup script:

cd packaging/fedora
nano opensips.init

We change this line:

oser=/usr/sbin/$prog

to read:

oser=/sbin/$prog

Save the changes and end the configuration:

chmod 755 opensips.init
cp opensips.init /etc/init.d/opensips
chkconfig --add opensips
chkconfig opensips on

We can pass compilation and installation of Redis. The version that supports the Cluster is unstable that came down from github:

cd /usr/src
git clone https://github.com/antirez/redis.git redisuns
cd redisuns
make
make test
make install

After installation pass to the server configuration A.

Server A

On this server we will create the database for OpenSIPS then be shared with the second server OpenSIPS:
chkconfig mysqld on
service mysqld start
mysqladmin-u root password sesame
We entered on the client:
mysql-u root-psesamo
We created the OpenSIPS database:
mysql> create database OpenSIPS;
We left the client and configure OpenSIPS to create tables:
mv / etc / OpenSIPS / opensipsctlrc / etc / OpenSIPS / opensipsctlrc.old
nano / etc / OpenSIPS / opensipsctlrc
copy the following lines:
SIP_DOMAIN = 198.168.168.60
DBENGINE = MYSQL
DBHOST = localhost
DBNAME = OpenSIPS
DBRWUSER = OpenSIPS
DBRWPW = "opensipsrw"
DBROOTUSER = "root"
INSTALL_EXTRA_TABLES = ask
INSTALL_PRESENCE_TABLES = ask
OSIPS_FIFO = "/ tmp / opensips_fifo"
VERIFY_ACL = 1
PID_FILE = / var / run / opensips.pid
SIP_DOMAIN is the IP address or domain name of our server Linux.Guardamos changes and create the tables:
opensipsdbctl create

 

We return to enter the MySQL client and create access permissions for the second server OpenSIPS:

mysql-u root-psesamo
mysql> grant all privileges on OpenSIPS. * to 'OpenSIPS' @ '192 .168.180.70 'identified by' opensipsrw ';
We insert two servers load_balancer table:
mysql> insert into load_balancer (group_id, dst_uri, resources, probe_mode, description) values ​​('1 ',' sip: sip.servidor1.org ',' voip / s = 20 ', '2', '20 SIP channels') , ('1 ',' sip: sip.servidor2.org ',' voip / s = 20 ', '2', '20 SIP channels');
mysql> quit
When we share resources through the Redis Cluster, we have to indicate the name of the resource / s at the end. In this case: voip / s
We passed OpenSIPS configuration:
mv / etc / OpenSIPS / opensips.cfg / etc / OpenSIPS / opensips.cfg.old
nano / etc / OpenSIPS / opensips.cfg
We copy the following lines:
# # # # # # # Global Parameters # # # # # # # # #
debug = 3
log_stderror = no
log_facility = LOG_LOCAL6
fork = yes
children = 4
listen = udp: 192.168.168.60:5060
disable_tcp = yes
disable_tls = yes
auto_aliases = no
# # # # # # # Modules Section # # # # # # # #
# Set module path
mpath = "/ lib / OpenSIPS / modules /"
# # # # SIGNALING module
loadmodule "signaling.so"
# # # # Stateless module
loadmodule "sl.so"
# # # # Transaction Module
loadmodule "tm.so"
modparam ("tm", "fr_timer", 5)
modparam ("tm", "fr_inv_timer", 30)
modparam ("tm", "restart_fr_on_each_reply", 0)
modparam ("tm", "onreply_avp_mode", 1)
# # # # Record Route Module
loadmodule "rr.so"
modparam ("rr", "append_fromtag", 0)
# # # # MAX FORWARD module
loadmodule "maxfwd.so"
# # # # SIP MSG Operations Module
loadmodule "sipmsgops.so"
# # # # FIFO Management Interface
loadmodule "mi_fifo.so"
modparam ("mi_fifo", "fifo_name", "/ tmp / opensips_fifo")
modparam ("mi_fifo", "fifo_mode", 0666)
# # # # URI module
loadmodule "uri.so"
modparam ("uri", "use_uri_table", 0)
# # # # User module LOCation
loadmodule "usrloc.so"
modparam ("usrloc", "nat_bflag", 10)
modparam ("usrloc", "db_mode", 0)
# # # # REGISTER module
loadmodule "registrar.so"
modparam ("registrar", "tcp_persistent_flag", 7)
# # # # Accounting module
loadmodule "acc.so"
modparam ("acc", "early_media", 0)
modparam ("acc", "report_cancels", 0)
modparam ("acc", "detect_direction", 0)
modparam ("acc", "failed_transaction_flag", 3)
modparam ("acc", "log_flag", 1)
modparam ("acc", "log_missed_flag", 2)
# # # # CACHEDB_REDIS module
loadmodule "cachedb_redis.so"
modparam ("cachedb_redis", "cachedb_url", "redis :/ / localhost: 6379 /")
loadmodule "db_mysql.so"
loadmodule "dialog.so"
loadmodule "load_balancer.so"
modparam ("dialog", "cachedb_url", "redis :/ / localhost: 6379 /")
modparam ("load_balancer", "db_url", "mysql :/ / opensipsrw: sesame @ localhost / OpenSIPS")
modparam ("load_balancer", "probing_interval", 60)
modparam ("load_balancer", "probing_reply_codes", "404")
# # # # # # # Routing Logic # # # # # # # #
# Main request routing logic
route {
if (! mf_process_maxfwd_header ("10")) {
sl_send_reply ("483", "Too Many Hops");
exit;
}
if (has_totag ()) {
# Sequential request withing a dialog Should
# Take the path determined to by record-routing
if (loose_route ()) {
if (is_method ("BYE")) {
setflag (1); # do accounting ...
setflag (3); # ... even if the transaction fails
} Else if (is_method ("INVITE")) {
# Even if in most of it the cases is useless, do RR for
# Re-INVITEs alos, as some buggy clients do change route in September
# During the dialog.
RECORD_ROUTE ();
}
           
# Route it out to whatever destination was September by loose_route ()
# In $ du (destination URI).
route (1);
Else {}
if (is_method ("ACK")) {
if (t_check_trans ()) {
# Non loose-route, but stateful ACK; must be an ACK after
# A 487 or eg 404 from upstream server
t_relay ();
exit;
Else {}
# ACK without matching transaction ->
# Ignore and discard
exit;
}
}
sl_send_reply ("404", "Not here");
}
exit;
}
# CANCEL processing
if (is_method ("CANCEL"))
{
if (t_check_trans ())
t_relay ();
exit;
}
t_check_trans ();
# Preloaded route checking
if (loose_route ()) {
xlog ("L_ERR"
"Attempt to route with preloaded Route's [$ fu / $ tu / $ ru / $ ci]");
if (! is_method ("ACK"))
sl_send_reply ("403", "Preload Route denied");
exit;
}
# Record routing
if (! is_method ("REGISTER | MESSAGE"))
RECORD_ROUTE ();
# Account only INVITEs
if (is_method ("INVITE")) {
setflag (1); # do accounting
}
if ($ rU == NULL) {
# Request with no Username in RURI
sl_send_reply ("484", "Address Incomplete");
exit;
}
if (! load_balance ("1", "voip / s", "1")) {
send_reply ("500", "Failure to route");
exit;
}
route (1);
}
route [1] {
# For INVITEs enable some additional helper routes
if (is_method ("INVITE")) {
t_on_branch ("2");
t_on_reply ("2");
t_on_failure ("1");
}
if (! t_relay ()) {
send_reply ("500", "Internal Error");
};
exit;
}
branch_route [2] {
xlog ("new branch at $ ru \ n");
}
onreply_route [2] {
xlog ("incoming reply \ n");
}
failure_route [1] {
if (t_was_cancelled ()) {
exit;
}
}
The important lines:

• modparam ("cachedb_redis", "cachedb_url", "redis :/ / localhost: 6379 /") - To connect to the local Redis server
• modparam ("dialog", "cachedb_url", "redis :/ / localhost: 6379 /") - To share the dialogues through Redis Cluster
• if (! load_balance ("1", "voip / s", "1")) {- To call resources configured in the table load_balancer

Save the changes and ended up with Redis:
mkdir / etc / redis
nano / etc / redis / redis.conf
not daemonize
pidfile / var / run / redis.pid
port 6379
timeout 0
loglevel notice
logfile stdout
save 900 1
save 300 10
save 60 10000
stop-writes-on-bgsave and error yes
rdbcompression yes
rdbchecksum yes
dbfilename dump.rdb
dir. /
slave-serve-stale-data yes
read-only slave-yes
slave-priority 100
maxclients 10000
3GB maxmemory
-policy maxmemory noeviction
AppendOnly not
appendfsync everysec
-not-on-rewrite appendfsync not
auto-aof-rewrite-percentage 100
auto-aof-rewrite-min-size 64mb
lua-time-limit 5000
cluster-enabled yes
cluster-config-file / etc/redis/nodes-6379.conf
slowlog-log-slower-than 10000
ZipList hash-max-512-entries
ZipList hash-max-64-value
ZipList list-max-512-entries
ZipList list-max-64-value
set-max-512-entries IntSet
ZipList zset-max-128-entries
ZipList zset-max-64-value
activerehashing yes
client-limit output-buffer-Normal 0 0 0
client-output-buffer-limit slave 256mb 64mb 60
client-output-buffer-limit 8mb 32mb pubsub 60
The important lines:

• cluster-enabled yes - To enable the Cluster
• cluster-config-file / etc/redis/nodes-6379.conf - Redis file name will be stored in automatic data where Cluster and nodes

Save the changes and start Redis:
redis-server / etc / redis / redis.conf

Redis Cluster not found any yet because it has not yet set the second server. To exit: CTRL-C

Server B
first configure OpenSIPS:
mv / etc / OpenSIPS / opensips.cfg / etc / OpenSIPS / opensips.cfg.old
nano / etc / OpenSIPS / opensips.cfg
We copy the following lines:
# # # # # # # Global Parameters # # # # # # # # #
debug = 3
log_stderror = no
log_facility = LOG_LOCAL6
fork = yes
children = 4
listen = udp: 192.168.180.70:5060
disable_tcp = yes
disable_tls = yes
auto_aliases = no
# # # # # # # Modules Section # # # # # # # #
# Set module path
mpath = "/ lib / OpenSIPS / modules /"
# # # # SIGNALING module
loadmodule "signaling.so"
# # # # Stateless module
loadmodule "sl.so"
# # # # Transaction Module
loadmodule "tm.so"
modparam ("tm", "fr_timer", 5)
modparam ("tm", "fr_inv_timer", 30)
modparam ("tm", "restart_fr_on_each_reply", 0)
modparam ("tm", "onreply_avp_mode", 1)
# # # # Record Route Module
loadmodule "rr.so"
modparam ("rr", "append_fromtag", 0)
# # # # MAX FORWARD module
loadmodule "maxfwd.so"
# # # # SIP MSG Operations Module
loadmodule "sipmsgops.so"
# # # # FIFO Management Interface
loadmodule "mi_fifo.so"
modparam ("mi_fifo", "fifo_name", "/ tmp / opensips_fifo")
modparam ("mi_fifo", "fifo_mode", 0666)
# # # # URI module
loadmodule "uri.so"
modparam ("uri", "use_uri_table", 0)
# # # # User module LOCation
loadmodule "usrloc.so"
modparam ("usrloc", "nat_bflag", 10)
modparam ("usrloc", "db_mode", 0)
# # # # REGISTER module
loadmodule "registrar.so"
modparam ("registrar", "tcp_persistent_flag", 7)
# # # # Accounting module
loadmodule "acc.so"
modparam ("acc", "early_media", 0)
modparam ("acc", "report_cancels", 0)
modparam ("acc", "detect_direction", 0)
modparam ("acc", "failed_transaction_flag", 3)
modparam ("acc", "log_flag", 1)
modparam ("acc", "log_missed_flag", 2)
# # # # CACHEDB_REDIS module
loadmodule "cachedb_redis.so"
modparam ("cachedb_redis", "cachedb_url", "redis :/ / localhost: 6380 /")
loadmodule "db_mysql.so"
loadmodule "dialog.so"
loadmodule "load_balancer.so"
modparam ("dialog", "cachedb_url", "redis :/ / localhost: 6380 /")
modparam ("load_balancer", "db_url", "mysql :/ / OpenSIPS: opensipsrw@192.168.168.60 / OpenSIPS")
modparam ("load_balancer", "probing_interval", 60)
modparam ("load_balancer", "probing_reply_codes", "404")
# # # # # # # Routing Logic # # # # # # # #
# Main request routing logic
route {
if (! mf_process_maxfwd_header ("10")) {
sl_send_reply ("483", "Too Many Hops");
exit;
}
if (has_totag ()) {
# Sequential request withing a dialog Should
# Take the path determined to by record-routing
if (loose_route ()) {
if (is_method ("BYE")) {
setflag (1); # do accounting ...
setflag (3); # ... even if the transaction fails
} Else if (is_method ("INVITE")) {
# Even if in most of it the cases is useless, do RR for
# Re-INVITEs alos, as some buggy clients do change route in September
# During the dialog.
RECORD_ROUTE ();
}
# Route it out to whatever destination was September by loose_route ()
# In $ du (destination URI).
route (1);
Else {}
if (is_method ("ACK")) {
if (t_check_trans ()) {
# Non loose-route, but stateful ACK; must be an ACK after
# A 487 or eg 404 from upstream server
t_relay ();
exit;
Else {}
# ACK without matching transaction ->
# Ignore and discard
exit;
}
}
sl_send_reply ("404", "Not here");
}
exit;
}
# CANCEL processing
if (is_method ("CANCEL"))
{
if (t_check_trans ())
t_relay ();
exit;
}
t_check_trans ();
# Preloaded route checking
if (loose_route ()) {
xlog ("L_ERR"
"Attempt to route with preloaded Route's [$ fu / $ tu / $ ru / $ ci]");
if (! is_method ("ACK"))
sl_send_reply ("403", "Preload Route denied");
exit;
}
# Record routing
if (! is_method ("REGISTER | MESSAGE"))
RECORD_ROUTE ();
# Account only INVITEs
if (is_method ("INVITE")) {
setflag (1); # do accounting
}
if ($ rU == NULL) {
# Request with no Username in RURI
sl_send_reply ("484", "Address Incomplete");
exit;
}
if (! load_balance ("1", "voip / s", "1")) {
send_reply ("500", "Failure to route");
exit;
}
route (1);
}
route [1] {
# For INVITEs enable some additional helper routes
if (is_method ("INVITE")) {
t_on_branch ("2");
t_on_reply ("2");
t_on_failure ("1");
}
if (! t_relay ()) {
send_reply ("500", "Internal Error");
};
exit;
}
branch_route [2] {
xlog ("new branch at $ ru \ n");
}
onreply_route [2] {
xlog ("incoming reply \ n");
}
failure_route [1] {
if (t_was_cancelled ()) {
exit;
}
}

Save the changes and configure redistribution:
mkdir / etc / redis
nano / etc / redis / redis.conf
We copy the configuration to the second server:
not daemonize
pidfile / var / run / redis.pid
port 6380
timeout 0
loglevel notice
logfile stdout
save 900 1
save 300 10
save 60 10000
stop-writes-on-bgsave and error yes
rdbcompression yes
rdbchecksum yes
dbfilename dump.rdb
dir. /
slave-serve-stale-data yes
read-only slave-yes
slave-priority 100
maxclients 10000
3GB maxmemory
-policy maxmemory noeviction
AppendOnly not
appendfsync everysec
-not-on-rewrite appendfsync not
auto-aof-rewrite-percentage 100
auto-aof-rewrite-min-size 64mb
lua-time-limit 5000
cluster-enabled yes
cluster-config-file / etc/redis/nodes-6380.conf
slowlog-log-slower-than 10000
ZipList hash-max-512-entries
ZipList hash-max-64-value
ZipList list-max-512-entries
ZipList list-max-64-value
set-max-512-entries IntSet
ZipList zset-max-128-entries
ZipList zset-max-64-value
activerehashing yes
client-limit output-buffer-Normal 0 0 0
client-output-buffer-limit slave 256mb 64mb 60
client-output-buffer-limit 8mb 32mb pubsub 60
Save the changes and start Redis on both servers:
Server A:
redis-server / etc / redis / redis.conf
Server B:
redis-server / etc / redis / redis.conf
For each cluster node must know the other "introduce him." On Server A console enter Redis (in another terminal window):
redis-cli
We look at the situation of the Cluster:
redis 127.0.0.1:6379> cluster nodes
28cc1625f86f777065a057fd19cc0cb8d49cf4b3: 0 myself - 0 0 connected
There is still only one (local). We write:
redis 127.0.0.1:6379> 6380 192.168.180.70 cluster meet
OK
We look again:
redis 127.0.0.1:6379> cluster nodes
192.168.180.70:6380 a862b68040167034f20a1ec535769fc6e2e175e5 master - 1352482127 1352482127 connected
28cc1625f86f777065a057fd19cc0cb8d49cf4b3: 0 myself - 0 0 connected
Since the local node knows the other and vice versa.
We leave the console:
redis 127.0.0.1:6380> quit
In the console of both servers Redis appear:
A:
[15170] November 9 17:28:40.440 * Connecting with Node d376333ff55910a6d54c9c7dace20d7d92a5ef9a at 192.168.180.70:16380
B:
[13763] November 9 17:29:03.183 * Connecting with Node 86d0dc855d790c8ec42f63486e08b87613971d05 at 192.168.168.60:16379
Redis Cluster system provides a total of 4096 nodes Hash Slots . As nodes are two, the Hash Slots are allocated as follows:
Server A
Open another terminal window and type:
echo '(0 .. 2047). each {| x | puts "CLUSTER ADDSLOTS" + x.to_s}' | ruby | redis-cli-p 6379> / dev / null
Server B
Open another terminal window and type:
echo '(2048 .. 4095). each {| x | puts "CLUSTER ADDSLOTS" + x.to_s}' | ruby | redis-cli-p 6380> / dev / null
We left both Redis server with CTRL-C to return to start:
redis-server / etc / redis / redis.conf
We entered Redis server console A:
redis-cli
redis 127.0.0.1:6379> cluster info
cluster_state: ok
cluster_slots_assigned: 4096
cluster_slots_ok: 4096
cluster_slots_pfail: 0
cluster_slots_fail: 0
cluster_known_nodes 2
Both Cluster are working perfectly.
We leave the console:
redis 127.0.0.1:6379> quit
Bookseller OpenSIPS libhiredis.so.0.10 looking in the / usr / lib while in / usr / local / lib. In both servers create a symbolic link:
cd / usr / lib
ln-s / usr/local/lib/libhiredis.so.0.10 libhiredis.so.0.10
Now we can start both servers OpenSIPS:
OpenSIPS service start
Test
On another server (AsteriskA) in the same local network with Asterisk installed, send all calls to OpenSIPS servers:
nano / etc / asterisk / sip.conf
end of the file add the two OpenSIPS servers:
[OpensipsA]
type = peer
context = from-OpenSIPS
host = 192.168.180.70
disallow = all
allow = alaw
qualify = yes
[OpensipB]
type = peer
context = from-OpenSIPS
host = 192.168.168.60
disallow = all
allow = alaw
qualify = yes
Save the changes and reload the SIP settings
asterisk-rx "sip reload"
Timely Configure dialplan to send the calls to the server OpenSIPsA and if the server fails OpenSIPsB
Asterisk configure both servers present in the loading configuration BALANCING to accept INVITE arriving from the two servers OpenSIPS:
[OpensipA]
type = peer
context = opensipscluster
host = 192.168.180.70
disallow = all
allow = alaw
dtmfmode = rfc2833
nat = no
[OpensipB]
type = peer
context = opensipcluster
host = 192.168.160.60
disallow = all
allow = alaw
dtmfmode = rfc2833
nat = no
Save the changes and reload the SIP configuration:
asterisk-rx "sip reload"
Now create the context "opensipscluster" nel dialplan:
nano / etc / asterisk / extensions.conf
[Opensipscluster]
exten => _X., 1, Answer
same => n, Playback (tt-monkeys)
same => n, hangup
Save the changes and reload the dialplan:
asterisk-rx "dialplan reload"
Now from a softphone connected to the server AsteriskA, mark any number that comes out of the trunk OpeSIPs configured:
The result in the Asterisk where the call arrives:

 OpenSIPsA:

 OpenSIPsB:

 The Redis Cluster and chachedb_redis module, function correctly.
Final note: it is not advisable to leave the MySQL server in one of the two servers is installed OpenSIPS. Better to have it on a third server.
Sources:
OpenSIPS CACHEDB_REDIS module
Presentation " Distributed SIP clusters with OpenSIPS "By Bogdan

Reference Link
http://www.voztovoice.org/?q=node/585