List:Internals« Previous MessageNext Message »
From:Peter B. Volk Date:June 19 2007 10:36am
Subject:Re: Rationale behind FEDERATED engine queries
View as plain text  
Hi Giuseppe


> What's the reason for not sending the original query
> to
> the remote server?

# Select all rows that have to be deleted
# Send them to the "virtual int delete_row (buf);" function of the storage
engine. The Buf contains the row to be deleted. The storage engine does
not "know" the real query. Actuall this is rather logical. Delete
statements may also contain Select statement. E.g.

Delete From cafeteria Where coke IN (SELECT type FROM pepsi).

Now imagine that the table cafeteria and pepsi are created using different
storage engines. One storage engine does not know from the other.
Especially not the data in it. Therfore the storage engine is told to
simply delete the row containing the data in the bufer. If there is more
than one row than the delete_row function is called multiple times untill
all rows where passed through the delete_row function.

For the limit in the delete statement: It is an optimization. The
federated database is told to delete exactly one row. Since the delete_row
function is called for every row anyway.

The same applies for the Update statement. Here the differnece is that an
additional select is issued befor every update. This is nesseserry to send
the complete buf to the update_row (old_data,new_data); function of the
plugin. Here the new AND old row is passed to the engine. Since not all
engines support transaction isoliation the mySql core selects the old row
every time for every row since it might have changed from the last select
statement.

> Is this intentional?

Yes this behaviour is inteded. All storage engines are handled like this.
The problem is that the federated engine can actually only be used for
small tables. Currently I have a system set up that went very slow because
the I used queries on larger Federated table. If you look how the MySQL
core isses select statements from more complex joins you will see that the
number of Selects on the federated system is extremly high. I have had a
join issuing 250000 on the federated database just to resolve the one
join. One worst case scenario is that the federated engine selects the
federated database multible times for one row returned to the user.

This has been giving me quite some trouble. Also the show table status as
an initialisation for every query gives me some problems. There seems to
be some sort of race condition in MySQL 4 Innodb that creates a deadlock
if issue enough Show Table status. I've been wanting to submit a bugreport
on that but have not put enough of blood into tracking the bug down.

> Can it be improved?

Yes shure it can. This would, as far as I can see, require quite a bit of
work on the plugin interface and the behavior of the MySQL core towoards
the plugins.

Hope this helped.

Cheers,
Peter



Thread
Rationale behind FEDERATED engine queriesGiuseppe Maxia19 Jun
  • Re: Rationale behind FEDERATED engine queriesPeter B. Volk19 Jun
    • Re: Rationale behind FEDERATED engine queriesJay Pipes19 Jun
      • Re: Rationale behind FEDERATED engine queriesBaron Schwartz19 Jun
        • Re: Rationale behind FEDERATED engine queriesPeter B. Volk19 Jun
          • Re: Rationale behind FEDERATED engine queriesEric Prud'hommeaux19 Jun
  • Re: Rationale behind FEDERATED engine queriesPhilip Stoev23 Jun