[MDEV-4615] PARTITION - Vertical Partitioning (using spider engine) Created: 2013-06-04  Updated: 2015-11-04  Resolved: 2015-11-04

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Spider
Fix Version/s: N/A

Type: Task Priority: Minor
Reporter: roberto spadim Assignee: Unassigned
Resolution: Duplicate Votes: 1
Labels: None

Issue Links:
Blocks
is blocked by MDEV-3932 5.6 merge Closed
Duplicate
is duplicated by MDEV-7795 Merge vertical partitioning storage e... Stalled

 Description   

Hi guys, today we have Horizontal partition, a nice feature that IMHO need a lock per partition instead a lock per table, and query cache invalidate per partition instead invalidate per table (maybe some work is being done at MDEV-252 at mysql server)

Now i'm thinking about Vertical partition (some column in one file, others columns in another files, etc...)

Instead of create table 1 and 2, with same primary key and different columns

Could we create a table X that have all columns and partition it vertically?

one point ... the lock MUST be done per partition
Why?
i'm thinking about partition1 = static values (with only insert)
partition2 = dynamic values (with many updates)
All partitions have the same number of ROWS with the same PRIMARY KEYS (it must have a UNIQUE INDEX, or a PRIMARY KEY to work)

example:

create table X (
 
a int,
 
b int,
 
c int,
 
d int,
 
primary key (a)
 
) partition horizontal p1 (b), partition horizontal p2 (c,d)

when i execute 

update X set b=1 where a=2

only table/partition X#p1 will be locked, in other words...

if i execute:

1)update X set b=1 where a=2   (10 minutes to execute)
 
2)update X set c=1 where a=2   (10 minutes to execute)

the first one (1) will not block the second (2), and the second (2) will not block the first (1), but:

3)update X set a=a+1   (10 minutes to execute)
 
4)update X set c=1 where a=2   (10 minutes to execute)

the first one (3) will block all partitions from table X, and the second (4) will only be executed after (3) be completed

 Comments   
Comment by roberto spadim [ 2013-06-05 ]

problems...
problem 1) lost rows in one partition
since we have "two tables"
a partition1.MAD file can have less or more rows than the other partition2.MAD file

in others words, partition1 have a row that partition2 don't have (like a server crash)

what we should do? well ...
1)if we have default values on all fields of table 2, we could create the row with default values (must think about unique keys)
2)delete the row from table1

i think this will occur only in mysqld startup, or when running check table / repair table

problem 2) keys / unique keys

think about:
create table a (a,b,c,d,e,
key a1(a,b,c),
key b1(b,d)
) partition vertical p1 (a,b,c), partition vertical p2(d,e)

where key b1 should be placed since they are two partitions???
1) (better) we should rewrite the partition to:
partition vertical p1 (a,b,c) partition vertical p2 (a,b,d,e)

problem => since we have a in p1 and p2, they must be in sync (like a raid1), again here a new check/repair function must be implemented)
2) vertical partition can't have different engines, and index are stored in only one file (table.MAI), and data stored in many files (table#p1.MAD, table#p2.MAD)
problem => a write to a column with index will block the index file, instead of blocking only partition index file

Comment by roberto spadim [ 2013-06-18 ]

after mysql 5.6 merge, we will have partition prune lock
with this we can start development of vertical partitioning

Comment by roberto spadim [ 2013-06-18 ]

waiting mysql 5.6 (5.6.6) merge

Comment by roberto spadim [ 2014-09-21 ]

please close since we can do this using spider (VP) engine
https://launchpad.net/vpformysql

Comment by roberto spadim [ 2014-09-21 ]

spider vp don't work with mariadb :/

Comment by Sergei Golubchik [ 2015-11-04 ]

See MDEV-7795

Generated at Thu Feb 08 06:57:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.