Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4615

PARTITION - Vertical Partitioning (using spider engine)

    XMLWordPrintable

    Details

      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

              People

              Assignee:
              Unassigned
              Reporter:
              rspadim roberto spadim
              Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: