MySQL sharding and stored procedures

Question

Is there a way to dispatch stored procedure invocation on particular shard, based on sharding key (passed as procedure argument or somehow separately)?

For example we have a table, sharded between 3 nodes using HASH sharding strategy. All rows with key A are stored on node 1. I'd like to be sure that when I run procedure(A), it applies on node1.

We are going to use Mysql Fabric tool, but other options considered as well.

Answer 1

Off the wall...

1. Turn the Procedure into a function. Simply return 0, or something irrelevant.

2. Instead of CALLing the Procedure, invoke the Function this way:

   SELECT 0 WHERE key = 'A' AND routine(key);

The hope is that the sharding will kick in for the first part of the WHERE and send the SELECT only to the desired shard.

BTW, it appears that Fabric is being decommissioned.

• silent-box: Hi, thank you for the answer! Indeed, Fabric github repo looks abandoned. What's the alternative might be? We hope to avoid app-level sharding if possible
• Rick James: Check out Spider. Sharding across only 3 nodes does not sound very severe; are you expecting a lot more? What brick wall has sent you to look at sharding - CPU? I/O? disk space? something else?
• silent-box: Our use case is very specific actually - there is a huge amount of rows in one big table, and we can easily avoid multi-shard queries. The main goal is to scale writes and be sure indexes fit in RAM.
• Rick James: "Fit in RAM" because you have lots of SELECTs?
• silent-box: We want indexes to fit in RAM for performance reasons - the table is going to be huge + many fields on it have indexes