Mysql, handlersocket and partitioning?

Question

Is handlersocket plugin aware of possible partitioning on a table? I haven't find mentions on the documentations of this, I don't even know if partitioning is transparent to the handler socket or it's something that the sql optimizer does.

Answer 1

Short answer:

HandlerSocket works fine against partitioned tables (meaning that all operations are supported), but it is not aware of any partitioning. Partition pruning is not attempted, and therefore, there is a performance overhead when handlersocket is used with partitioned tables.

Long answer:

Partitioning in MySQL is implemented at different levels: parser, generic handler, query optimizer. The generic handler (ha_partition) provides partitioning to engines which do not natively support it (all of them except NDB). This handler implements a kind of chain of responsability pattern: it plugs itself between the server and the normal handlers of the underlying engine (one per partition).

When a query is executed, the ha_partition handler forwards the operations to all the underlying handlers corresponding to each partition. This is why you can have the same partitioning support for InnoDB, MyISAM, etc ...

Partition pruning (i.e. filtering out useless lookups/scans on partitions) is implemented in the query optimizer, not in the ha_partition handler. So basically when a lookup is done through ha_partition, if the optimizer has not restricted the list of partitions, lookups are done on all partitions, and then a merge algorithm is used to read the n cursors in parallel.

The following presentation by Mattias Jonsson and Mikael Ronström (Oracle) is very useful to understand how partitioning is implemented in MySQL.

Now HandlerSocket plugin is directly based on the generic handler. There is no partition knowledge at the HandlerSocket level. When HandlerSocket query is applied against a partitioned table, the ha_partition handler will be used in a transparent way.

The good news is HandlerSocket works fine against partitioned tables at no extra cost. The bad news is it does not benefit from partition pruning since this is only implemented in the SQL query optimizer.

Here is an example to prove it (tested against Percona Server 5.5). We will use 2 tables: mytable_np is not partitioned, mytable is partitioned.

create table mytable_np ( id int, x varchar(100), primary key(id), key(x) )
engine=InnoDB ;

insert into mytable_np values ( 1,  'A' );
insert into mytable_np values ( 11, 'B' );
insert into mytable_np values ( 21, 'C' );
insert into mytable_np values ( 31, 'D' );
insert into mytable_np values ( 41, 'E' );
insert into mytable_np values ( 51, 'F' );
commit;

create table mytable ( id int, x varchar(100), primary key(id), key(x) )
engine=InnoDB
partition by range (id) (
   partition p0 values less than (10),
   partition p1 values less than (20),
   partition p2 values less than (30),
   partition p3 values less than (40),
   partition p4 values less than (50),
   partition pend values less than (1000)
);

insert into mytable values ( 1,  'A' );
insert into mytable values ( 11, 'B' );
insert into mytable values ( 21, 'C' );
insert into mytable values ( 31, 'D' );
insert into mytable values ( 41, 'E' );
insert into mytable values ( 51, 'F' );
commit;

The following queries can be done to perform a simple primary key access:

select * from mytable where id = 51 ;

select * from mytable_np where id = 51 ;

The following netcat/telnet scripts can be used to query using HandlerSocket (beware to the TAB characters):

P       0       test    mytable PRIMARY id,x                                                                                                                                                                                                 
0       =       1       51

P       0       test    mytable_np PRIMARY id,x                                                                                                                                                                                                 
0       =       1       51

To evaluate the number of lookups, the following query can be done before and after each query execution to count the number of handler key accesses:

show global status like 'Handler_read_key' ;

If we measure the number of handler key accesses done in the four cases, we got:

SQL query against non partitioned table:      2
SQL query against partitioned table:          2
HandlerSocket against non partitioned table:  2
HandlerSocket against partitioned table:      7

In the three first cases, we have one lookup to find the row, plus one additional key access to check this is the last row to read. In the last case, we have one lookup per non empty partition. There are 6 of them. The key will be found in just one of them, and an extra access is done to check there is just one matching row. So the result is 7.

This example proves that even in the simplest case (primary key access), HandlerSocket is not able to prune the partitions. A performance penalty should always be expected when HandlerSocket is used against partitions. The more partitions, the higher overhead (linear).