MySQL Federated Tables

I’ve been hearing for MySQL FEDERATED Tables for a while, today went through them and figure out how amazing they are.
In simple terms a MySQL FEDERATED Table is a proxy to a regular table located in another DB server. You will see the table as a local table while it’s being located in another MySQL Database Server.

The remote table could be defined in as any Storage-Engine as you want, you just need to map the columns and set the connection. So in your local DB server you can easily query your remote table as if it’s a local table located in your Database.

If you have a remote table like this :

CREATE TABLE test_table (
id     INT(20) NOT NULL AUTO_INCREMENT,
name   VARCHAR(32) NOT NULL DEFAULT '',
other  INT(20) NOT NULL DEFAULT '0',
PRIMARY KEY  (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1;

You can set your Federated table like this :

CREATE TABLE federated_table (
id     INT(20) NOT NULL AUTO_INCREMENT,
name   VARCHAR(32) NOT NULL DEFAULT '',
other  INT(20) NOT NULL DEFAULT '0',
PRIMARY KEY  (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

where the connection is :

scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

And according to MySQL Documents here is the procedure that MySQL follows for a query from a Federated table [1]:

  1. The storage engine looks through each column that the FEDERATED table has and constructs an appropriate SQL statement that refers to the remote table.
  2. The statement is sent to the remote server using the MySQL client API.
  3. The remote server processes the statement and the local server retrieves any result that the statement produces (an affected-rows count or a result set).

 

  • If the statement produces a result set, each column is converted to internal storage engine format that the FEDERATED engine expects and can use to display the result to the client that issued the original statement.

 

 

You have to keep in mind that indexes should be created in the remote server, also you can not alter the table. Also Federated tables can not support transaction. And as another disadvantage of Federated tables, you have to be careful that “the size of the insert cannot exceed the maximum packet size between servers. If the insert exceeds this size, it is broken into multiple packets and the rollback problem can occur” [2].

Here you can find very useful information about the MySQL Federated Tables.

MySQL FEDERATED Storage-Engine may not be something you deal with every day and for every project. But for distributed projects it makes a lot of sense to use and consider them. Just knowing the concept opens your eyes to a new way of solving the distributed architectures problem.

References :

[1] : Description of the FEDERATED Storage Engine
[2] : Limitations of the FEDERATED Storage Engine

Advertisements

Dynamic / Static Typing vs. Strong / Weak Typing

Here is a great article about Static vs. Dynamic typing and Strong vs. Weak typing.

But as to summarize it ; Dynamic typing languages are which variables’ type declarations are not mandatory and they will be generated on the fly, by their first use [PHP, Python]. While Static typing languages are the ones which variable declarations are mandatory before usage [Java, C].

/* C code */
static int num, sum;
num = 5;
sum = 10;
sum = sum + num;
/* PHP Code */
$sum = 10;
$sum++;
echo $sum;

On the other hand the difference between Strong and Weak typing is different; Strong typing is when a variable is declared as a specific type (no matter by Dynamic or Static typing) it is necessarily bound to that particular data type [Python, Java]. While in Weak typing variables are not of a specific data type ;  it does mean that variables are not “bound” to a specific data type [C,PHP] [1].

# Python code
>>> foo = "x"
>>> foo = foo + 2
>>>>>
Traceback (most recent call last):
File "", line 1, in ?
foo = foo + 2
TypeError: cannot concatenate 'str' and 'int' objects
>>>
/* PHP code */
$foo = "x";
$foo = $foo + 2; // not an error
echo $foo;

[1] Introduction to Static and Dynamic Typing

Lazy PHP

I would say I love this site. It’s a very simple to use and handy but on the other hand, knowledgeable and informative website containing tons of interesting articles, tutorials and forum discussions.

When I was about to start learning PHP and MySQL I was introduced to an article by the name of “Build you own Database Driven Website Using PHP and MySQL”. Now this is the closes thing I can find to that article, which is sample PDF of a book. I believe even this sample is enough to get you started.

All I wanted to say is this site has it all, various resources for all levels.

Today I came across another interesting article about PHP, how it works and how to make its performance better. The article is quite old but still applicable and useful.

It is very useful to know behind the scenes of the technology you’re using. The low-level knowledge of “How things work” will give you better understanding of how to improve you application and how to increase the performance. And the more you know the tools provided, the better and faster you can develop.

The way PHP scripts are being executed and interpreted, gives you an idea of how you can improve the performance. In this article it speaks about the 4 stage execution of each request by WebServer/PHP :

  1. Incoming Request : When WebServer receives the Request for the script
  2. Prepare OPCODEs : When the PHP script is being parsed and OPCODES are being generated.
  3. Execution of script and responding back to user
  4. Trash the lot including all the OPCODEs, variables and etc

We all know that now a days there are several different tools to improve the stages 2 and 4 which are the most resource consuming and duplicated tasks of the PHP Interpreter. PHP Accelerators Like APC , eAccelerator,XCache and so on help a lot to eliminate the phases #2 and #4.

Most PHP accelerators work by caching the compiled bytecode of PHP scripts to avoid the overhead of parsing and compiling source code on each request[1].

Also in this great series of articles “Anonymous functions” known as “Closures” and “Variable Functions” are being discussed and there are some simple but very helpful samples of how they work and how they can help.

Also techniques like ‘Lazy includes’ which seems to be pretty obvious but many of us forget it, have been discussed.

I will let you read the articles by yourself ; Part-1 , Part-2, Part-3 and enjoy it 🙂

[1] : http://en.wikipedia.org/wiki/PHP_accelerator

1. Incoming Request:Incoming Request