Forum: Ruby on Rails Help with AR design on a large (~100'sM) partition table

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Rob R. (Guest)
on 2009-03-24 16:33
(Received via mailing list)
First, I'm a physicist, not a computer scientist.  I need to store 65M
rows now, with a growth rate of  ~21M/year whose entries arrive daily.

The purpose of this DB is to store metadata for environmental
observations for the ionosphere.  Basically it's a glorified file
catalogue.  I need the usual: fast enough inserts, reasonable
recovery, REST like access, etc.  I have access to Ruby AR/AS 2.1.1,
Ruby 1.8.5 and eventually MySQL 5.1.x.

I'm trying to trade some storage space and lack of normalization for
simplicity of design and ease of maintenance by me.  Basically, I want
to partition one very large table.  My PK is essentially across 3
columns: filename, md5 checksum, destination.  I'd like to partition
by year which is discernible by the filename and by obs_time if I can
but obs_time is not currently part of the PK.  If that happens, then I
would have up to 40M rows in a single table though most tables would
have a few million; perhaps that is too many; perhaps a table for each
half year would work.

Currently, I have no foreign keys but I may want them one day.

Any help and suggestions would be really appreciated!

My schema follows.  I have created a simple model off this table and
I'm using these decorators: validation, and before_save and after_save
to build an inventory summary (in another table not shown).  I've
tried this on a non-partitioned table so far awaiting the installation
of MySQL 5.1.x by our IT department.

How do I go about integrating AR with a partitioned table?  Can you
advise a simple pattern I might follow for such a large but simple
relationship?

create table `ionofiles` (

-- TODO optimize variable sizes
    `id`             int(15)      NOT NULL auto_increment PRIMARY KEY,
    `file`           varchar(100) NOT NULL,                -- Any
valid filename without the ".~N~" backup extensions.
    `md5`            varchar(100) NOT NULL,                -- MD5 Sum
    `destination`    varchar(20)  NOT NULL,                -- E.g.
MIDS, ADIC, AFWA, SWPC, ...

    `version`        varchar(20)  default NULL,            -- Optional
version.

    `ursi`           char(5)      default NULL,       -- E.g. BC840,
WI937. default NULL because we allow any filename.

    `time_scope`     varchar(20)  default NULL,     -- E.g.
(individual, daily, monthly, yearly, etc).  default NULL

-- because we allow non time-scoped files.

    `content_type`   varchar(20)  default NULL,            -- E.g.
(root, scaled, image, ionogram)

    `obs_time1`      datetime     default NULL,            -- Start of
measurement.  Default NULL because we allow

-- non data files.
    `obs_time2`      datetime     default NULL,            -- End of
measurement, in the case file represents

-- multiple measurements.  Default NULL because we allow

-- non data files.

    `extension`      varchar(5)   default NULL,            -- file
extension (any case).  E.g. SAO, MMM, 16C, TXT, png.

    `size`           int(11)      NOT NULL,                -- file
size in bytes

    `backup`         int(2)       default NULL,            -- N of the
"~N~" tilda backup file.

    `is_ready`       boolean       NOT NULL default 0,      -- Is this
file's links ready?  0 = No, 1 = Yes.
    `is_public`      boolean       NOT NULL default 1,      -- Is this
file publicly available?  0 = Public, 1 = Private.
    `is_removed`     boolean       NOT NULL default 0,      -- Has
this file been marked deleted?,
                                                       -- s.t. we
don't ever want to see it again?
                                                       -- This is
useful in the event that we want to reject future submissions of file.

    `file_on_disk`   varchar(100) NOT NULL,   -- Actual file name on
disk: E.g. BC840_2009001000000.SAO.~10~
    `path`           varchar(200) NOT NULL,      -- A directory or a
full path to a ZIP or TAR or other aggregate file.
                                                           -- E.g.
data/BC840/individual/2009/001/scaled/
                                                           -- E.g.
data/BC840/individual/2009/BC840_2009001_2009-01-01.tar.gz

    `last_verified`  datetime default NULL,   -- last time we checked
that this file actually exists at stated destination

-- TODO: ActiveRecord fields.  What should be the data type?
Timestamp or Datetime?
--       The auto set and update features are currently handled by
AR.
--       Should we put some ON UPDATE, etc actions here for non AR
interaction?
    `created_on`     datetime     NOT NULL,                 --
ActiveRecord auto set.
    `updated_on`     datetime     NOT NULL,                 --
ActiveRecord auto updated.

    `note`           TEXT         default NULL,            -- User
note

    UNIQUE KEY `key_file_md5_destination_public` ( `file`, `md5`,
`destination`, `is_public` ),

    KEY `key_file`          ( `file` ),
    KEY `key_md5`           ( `md5` ),
    KEY `key_destination`   ( `destination` ),
    KEY `key_version`       ( `version` ),
    KEY `key_ursi`          ( `ursi` ),
    KEY `key_time_scope`    ( `time_scope` ),
    KEY `key_content_type`  ( `content_type` ),
    KEY `key_obs_time1`     ( `obs_time1` ),
    KEY `key_obs_time2`     ( `obs_time2` ),
    KEY `key_extension`     ( `extension` ),
    KEY `key_size`          ( `size` ),
    KEY `key_backup`        ( `backup` ),
    KEY `key_is_ready`      ( `is_ready` ),
    KEY `key_is_public`     ( `is_public` ),
    KEY `key_is_removed`    ( `is_removed` ),
    KEY `key_file_on_disk`  ( `file_on_disk` ),
    KEY `key_last_verified` ( `last_verified` ),
    KEY `key_created_on`    ( `created_on` ),
    KEY `key_updated_on`    ( `updated_on` )

) ENGINE=InnoDB;


R
Ar C. (Guest)
on 2009-03-24 18:29
Interesting issue: your record count is several orders of magnitude
larger than I have to deal with.

A quick google brought up this page:

http://blog.new-bamboo.co.uk/2007/11/20/database-o...

which might have some points for you to consider.
Rob R. (Guest)
on 2009-03-24 21:52
(Received via mailing list)
Thank you.  That's a handy suggestion.

On Mar 24, 10:29 am, Ar Chron <removed_email_address@domain.invalid>
Jeremy K. (Guest)
on 2009-03-24 23:44
(Received via mailing list)
On Tue, Mar 24, 2009 at 7:32 AM, RobR <removed_email_address@domain.invalid> 
wrote:
> First, I'm a physicist, not a computer scientist.  I need to store 65M
> rows now, with a growth rate of  ~21M/year whose entries arrive daily.

MySQL can deal with this out-of-the-box without breaking a sweat. I
wouldn't worry about partitioning until you actually hit a wall. I
think Moore's law is on your team for this project.

(Note: you probably don't want indexes on every single column
individually. Add compound indexes tuned to your actual queries.)

Best,
jeremy
Rob R. (Guest)
on 2009-03-27 18:58
(Received via mailing list)
Jeremy,

I did remove many indices as they aren't needed for the most likely
use cases.  Thank you for the sage suggestion.  I saw an incredible
improvement in insert speed (1.5x) and reduction in disk space usage
(0.6x).

I have to say I think I'm already hitting the wall with this
simplistic approach for these reasons:
1) I see performance degradation on indexed searches at 90 million
records in a much more slimmed down version of the table, i.e. only 3
columns, with 1 PK and 3 individual indexes.
2) It takes ~24 hours to rebuild a MyIsam version of the 90 million
record table + indices via a mysqldump load; especially it's PK.  The
entire table is inaccessible during this PK rebuild since it ensures
consistency.  This happens ~1/year.
3) With a 5 year forecast in mind, I expect > 200million relationships
or rows needed and hence a significant design goal.
With the originally posted schema and most indices removed, I'm still
looking at ~ 250 GB of physical disk space needed.  Our DBs run using
an attached storage environment with many small heavily redundant
disks.  I don't know much more about that except that 70 GB is the
elemental partition size for DB's.  So my design will need to be
partition flexible in the near term and hence one reason why I looked
toward Partition Tables.

Onward, I did realize finally that 95% of my queries inherently know a
pretty good key a priori, i.e. the timestamp of the measurement is
embedded in the file name.  Most users will be able to specify the
time range of interest, etc.

So, I've taken a stab at partitioning on month.  MySQL has a
limitation of 1024 partitions per table and I needed to represent data
from 1940.  I've designed this set of partitions on month boundaries
from 1940 to 2020.  I assume adding more partitions later is easy but
since I didn't know how I went ahead and partitioned up to the year
2020.  A better solution would probably use a histogram of my data and
add more partitions during time periods of higher data density.  The
problem is that I do not yet know this histogram...  though it would
be easy to ascertain.

My present schema follows.  Any comments are highly welcome!  I'm
currently bulk testing and see these numbers:
1) ~120 inserts + 120 selects per second which is pretty good.
I have 2 primary problems to resolve.
1) MySQL doesn't have "global" indices so queries on Key that is not a
PK, require a scan across the indices of all ~800 tables which takes
~1 second.
2) I need to wittle the forecasted ~280 GB down to ~200 GB.

create table `ionofiles` (

-- TODO optimize variable sizes
    `id`             int(15)      NOT NULL auto_increment,
-- Any valid filename without the ".~N~" backup extensions.
    `file`           varchar(100) NOT NULL,
-- MD5 Sum
    `md5`            varchar(100) NOT NULL,
-- E.g. MIDS, ADIC, AFWA, SWPC, ...
    `destination`    varchar(20)  NOT NULL,

-- Optional version.
    `version`        varchar(20)  default NULL,

-- E.g. BC840, WI937.  default NULL because we allow any filename.
    `ursi`           char(5)      default NULL,

-- E.g. (individual, daily, monthly, yearly, etc).  default NULL
because we allow non time-scoped files.
    `time_scope`     varchar(20)  default NULL,

-- E.g. (root, scaled, image, ionogram)
    `content_type`   varchar(20)  default NULL,

-- Start of measurement.  Not NULL because we partition on this.
    `obs_time`       datetime     NOT NULL,

-- file extension (any case).  E.g. SAO, MMM, 16C, TXT, png.
    `extension`      varchar(5)   default NULL,

-- file size in bytes
    `size`           int(11)      NOT NULL,

-- N of the "~N~" tilda backup file.
    `backup`         int(2)       default 0,

-- Is this file's links ready?  0 = No, 1 = Yes.
    `is_ready`       boolean      NOT NULL default 0,

-- Is this file publicly available?  0 = Public, 1 = Private.
    `is_public`      boolean      NOT NULL default 1,

-- Has this file been marked deleted?,
    `is_removed`     boolean      NOT NULL default 0,

-- Actual file name on disk: E.g. BC840_2009001000000.SAO.~10~
    `file_on_disk`   varchar(100) NOT NULL,
-- A directory or a full path to a ZIP or TAR or other aggregate file.
    `path`           varchar(200) default NULL,
                                                           -- E.g.
data/BC840/individual/2009/001/scaled/
                                                           -- E.g.
data/BC840/individual/2009/BC840_2009001_2009-01-01.tar.gz

-- last time we checked that this file actually exists at stated
destination
    `last_verified`  datetime     default NULL,

-- TODO: ActiveRecord fields.  What should be the data type?
Timestamp or Datetime?
--       The auto set and update features are currently handled by
AR.  Should we put some ON UPDATE, etc actions here for non AR
interaction?
    `created_on`     datetime     NOT NULL,                 --
ActiveRecord auto set.
    `updated_on`     datetime     NOT NULL,                 --
ActiveRecord auto updated.

    `note`           TEXT         default NULL,            -- User
note

-- TODO: reorder the key constituents to reflect best sub selection
capability
-- Rows are unique by this
    PRIMARY KEY `key_destination_is_public_obs_time_file_md5`
( `destination`, `is_public`, `obs_time`, `file`, `md5` ),

-- TODO: Is there any value in reordering these keys?  Does order
matter?
    KEY `key_obs_time`      ( `obs_time` ),
    KEY `key_id`            ( `id` ),
    KEY `key_ursi`          ( `ursi` ),
    KEY `key_extension`     ( `extension` ),
    KEY `key_size`          ( `size` ),
    KEY `key_updated_on`    ( `updated_on` )

) ENGINE=InnoDB
PARTITION BY RANGE ( TO_DAYS( obs_time ) ) (
  PARTITION p195001 VALUES LESS THAN (TO_DAYS
( "1950-01-01" )),PARTITION p195002 VALUES LESS THAN (TO_DAYS
( "1950-02-01" )),PARTITION p195003 VALUES LESS THAN (TO_DAYS
( "1950-03-01" )),PARTITION p195004 VALUES LESS THAN (TO_DAYS
( "1950-04-01" )),PARTITION p195005 VALUES LESS THAN (TO_DAYS
( "1950-05-01" )),PARTITION p195006 VALUES LESS THAN (TO_DAYS
( "1950-06-01" )),PARTITION p195007 VALUES LESS THAN (TO_DAYS
( "1950-07-01" )),PARTITION p195008 VALUES LESS THAN (TO_DAYS
( "1950-08-01" )),PARTITION p195009 VALUES LESS THAN (TO_DAYS
( "1950-09-01" )),PARTITION p195010 VALUES LESS THAN (TO_DAYS
( "1950-10-01" )),PARTITION p195011 VALUES LESS THAN (TO_DAYS
( "1950-11-01" )),PARTITION p195012 VALUES LESS THAN (TO_DAYS
( "1950-12-01" )),
...
-- up to year 2020
    PARTITION p999999 VALUES LESS THAN MAXVALUE
);
This topic is locked and can not be replied to.