What is federated server
Mostly used storage engines types:
eBay Memory Engine
MySQL storage engines
eBay Memory Engine
why we needed federated server:
To devise a better solution, in MySQL 5.1, the idea of a Federated Server was developed. This concept
was part of the SQL/MED specifi cation. It essentially lets you create a named database object
called a SERVER that is associated with various connection meta-data information. The other half
of this functionality is that the Federated storage engine can merely specify the server name (as well
as table if it is desired to name the table differently than the Federated table). This means you can
change the connection information of the table that one or more Federated tables use to connect to
their remote data source with a single SQL statement against the SERVER. So, in the 1000-table
scenario, not a single table would have to be changed!
The Federated storage engine is a storage engine that, instead of accessing data from a local fi le or
tablespace, accesses data from a remote MySQL table through the MySQL client API. It builds SQL
statements internally, based on what the query was against the Federated table, and runs those
statements on the remote MySQL table.
If the query against the Federated table is a write statement such as INSERT or UPDATE, the
Federated storage engine builds a query, deriving the column names and values from internal data
structures that are dependent on the fi elds and values of the original query. Then it executes the SQL
to perform that write operation on the remote table, reporting back to the storage engine the number
of rows affected.
If it’s a read operation, it constructs a SELECT statement also built using internal data structures for column names, as well as WHERE clauses for ranges and indexes. It then executes that statement after which the Federated storage engine retrieves the result set from the remote table and iterates over that result. It then converts the results into the same internal format that all other storage engines use and then returns the data to the user.
A DELETE statement is similar to a SELECT statement in how the column names are built into the constructed SQL statement, as well as in building the WHERE clause. The main difference is that the operation is DELETE FROM versus SELECT, resulting in the rows specifi ed in the SQL statement being deleted and the count of the rows affected being returned to the storage engine, which in turn decrements its total row count.
Characteristics of the Federated Storage Engine:
Transparency: The remote data sources and details thereof are not necessarily known by the
user, such as how the data is stored, what the underlying schema is, and what dialect of SQL is used to retrieve information from that data source.
High degree of function: To be able to have, as much as possible, the same functionality that is had with regular tables.
Extensibility and openness: To adhere to a standard as defi ned in the ANSI SQL/MED (Management of External Data) extension to the SQL standard. Autonomy of data sources: Not affecting the remote data source, not interfering with its
normal operation. This also means that the Federated storage engine cannot modify the definition of the remote data source, as in the case of statements such as ALTER and DROP TABLE
not being sent to the remote data source.
Optimized performance: Utilizing the optimizer to create the most effi cient statements to run on the remote data source. Also, the long-term goal is to have a means of delegating
operations to the local server and remote server according to which is best suited for each operation.
characteristics of the Federated storage engine Column Requirements When creating a Federated table, the table must have the same named columns as the remote table, and no more columns than the remote table.
The remote table can have more columns than the Federated table.
Queries and Results in federated server:
A query on a Federated table internally produces an entire result set from a table on a remote server, and as such, if that table contains a lot of data,
all of that data will be retrieved. One way to deal with huge result sets is to define an index on a column of the Federated table, even if that column
is not indexed on the remote table, and try to use any means to limit the result set. However, note that LIMIT does not aff ect the size of the result
set from the remote table.
The remote table must be in existence prior to creating the Federated table that references it.
The Federated storage engine supports indexes insofar as the column that is defined as an index is specifi ed in a WHERE clause in the SQL query the table generates, and that the column it specifi es is an index on the remote table. This means that you could have a Federated table with an index on a column that is not an index on the remote table, which is not a problem, and in fact can be used to reduce result set size.
can federated table reference other:
The manual states a Federated table can reference a Federated table. This is considered not good.
Transactions aren’t supported in Federated, but are support with FederatedX if using a transactional engine (InnoDB, MariaDB, Falcon, and so on) as the remote data source.
What Federated Supports
Federated supports SELECT, INSERT, UPDATE, and DELETE. However, ALTER TABLE cannot be used to change the remote table’s definition but it can be used to modify the local Federated table’s definition.
Using DROP TABLE
This only drops the local Federated table. It’s worthwhile to mention that although the Federated storage engine may not support some features such as transactions as well as other enhancements, there is a fork of Federated called FederatedX, which is a more active development branch of Federated initiated by the authors of
Federated: Patrick Galbraith and Antony Curtis.