Hi Timour,
> You, and anyone from the community are more than welcome to provide
> feedback (either via this list, or even better by commenting the
> WL on MySQL Forge). Please look at the other subtasks of WL#4292
> under the "Dependent Tasks" tab, these should give you a better
> idea what are we working on.
thanks for the update of the Tasks. I already took a bit deeper look at
the sub tasks.
>
> Could you give us more detail what kind of usage do you envision
> for such a pushdown API? What kind of operations can your storage
> engine compute?
Well currently we have two visions. The one is connected with
distributed computing and the other is connected to data analysis. For
the Distributed engine: Currently we distribute the data among many
different transparently kept locations. Then by some magic we distribute
the data among the nodes (I can provide details but they are not
relevant to the discussion). Now if we look at the execution plan of a
query we can perform several operations in a distributed fashion. Such
as Distributed Joins and aggregation and projection functions. So what
information would we need from the QEP? I would need to know what
subtree of the QEP we should execute. Not an explicit subtree but more
an abstract subtree. Meaning. an explicit subtree would say (SELECT *
FROM T1 JOIN T2 ON T1.a = T2.b ):
HashJoin | T1.a = T2.b --> J1
/ \
TS(T1) IS(T2)
An abstracted subtree would look like:
Join | T1.a = T2.b --> J1
/ \
T1 T2
Of course this would only work if T1 and T2 are in the same engine. Also
I would not restrict only to the last nodes of the subtree but possibly
also the complete execution tree. consisting of:
COUNT(*)
|
Filter | J1.b < 15 OR J1.a >10
|
Join | T1.a = T2.b --> J1
/ \
T1 T2
So what would we make out of it? We would perform the query on each node
in the cluster with the local data only and then aggregate the count
results and return it to the MySQL core. This is a bottom up principle
for the storage engine and also the full blown version. As of for how
the server should decide if it is suppose to push so much down to the
engine it would have to request a cost estimation from the engine for a
specific sub tree of the QEP.
In the case if we are considering heterogeneous joins (with tables from
different engines) then it would be interesting to know if we can work
with vertically cut subtrees. Meaning:
Join | T1.a = T2.b --> J1
/ \
T1(DD) T2(InnoDB)
would be transformed to:
Join | T1.a = VT.b WITH VT.* --> J1
/
T1(DD)
VT is a virtual table create by the table/Indexscan from T2. Now for a
cost estimation the engine would require selectivity estimates on VT.a
and a size estimate of VT.*. If our engine would say "gosh this is so
chep if you do it with me" then the core would create a virtual table
that is derived from the InnoDB table T2. On the other hand if we say
that this subtree would be extremely expensive then it would perform a
TS on T1 and then join it it self instead of pushing it down to the engine.
The other vision is connected to statistical data modeling. In the filed
of Data Mining we perform operations that create a function from a cloud
of data points. So with a dataset D consisting of the attributes
x_1..x_n we can derive a function f(x_1,..,x_{n-1}) returning x_n as a
prediction. To create that function (I'm only limiting it to parametric
regressions but also non parametric regressions should be possible) we
have to manage the available data in a table.So the Idea is to put the
management of the models where the data is: in the storage engine. DML
operations would manipulate the data. Select statements would request a
prediction from the model. But for the prediction the model requires a
_full_ projection pushdown in terms that a model can only produce a
prediction if all variables are filled. So lets take this QEP (a join on
an innodb table and a prediction table where variable t1.a is derived by
values from the innodb table and j1 contains the prediction and T2.*):
Join | T1.a = T2.b --> J1
/ \
T1(P) T2(InnoDB)
Would have to be pushed down to the engine by:
TS(T1) | T1.a IN (......)
So all projection variables available are pushed down completely. Again
here the MySQL core could ask the engine for a specific operation how
costly it would be. e.g. in this case I would trick the MySQL server an
say that the projection T1.a is almost 0 forcing the server to push the
projections all the way down. So in this case I would make an estimate
on an operator base and not a subtree base.
I hope this helps a bit to understand where we are going with the
engines. If you have any questions I would be happy to help.
Cheers ,
Peter
--
Peter Benjamin Volk
Project Head DDEngine
