Details
-
Task
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Duplicate
-
None
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
Attachments
Issue Links
Activity
after mysql 5.6 merge, we will have partition prune lock
with this we can start development of vertical partitioning
please close since we can do this using spider (VP) engine
https://launchpad.net/vpformysql
spider vp don't work with mariadb :/
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