1

I'm not very experienced in database design so bear with me.

I've taken over a ruby/rails app. with a connected MySQL/innodb database. As I'm updating the server to the latest ruby/rails version I also wanted to look at the DB structure. My main concern is a specific database design decision which makes it very hacky to use with rails and I wanted to get some external input on this.

We are tracking spatial data for various systems from customers every x minutes which results in a lot of data. We don't have that data in one big "positions" table but split into a table for each system and each year. So e.g. for a system with id=1 and in the current year we have a "positions_1_2015" table.

So for, like, 1,000 systems over 3 years we already have 3,000 tables which seems a lot to me. When I want to change a column I have to change each single table instead of just one. I also have to do some hacking for this mechanism to work with rails activerecord logic.

Right now, summed up, we have about 300,000,000 positions from various customers. They are write once/read often and by multiple concurrent users.

So my question: is this table splitting a sensible design?

The argument from my clients is that they can more easily backup/archive all data and a lot of small tables are more manageable than one single huge table. But above all each client has access to their specific tables so that performance should be better when 100+ customers want to access their spatial data at the same time.

So is this true? Will performance be worse if Ihave 100+ concurrent users on a single big 300M row table?

Thanks for your help!

Edit: SHOW CREATE for one position table

CREATE TABLE `positions_1000_2015` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `world_x` double DEFAULT NULL,
  `world_y` double DEFAULT NULL,
  `timestamp` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `status` int(11) DEFAULT NULL,
  `coworker_id` int(11) DEFAULT NULL,
  `raw_data` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci    DEFAULT NULL,
  `user_data` varchar(1024) DEFAULT NULL,
  `map_text` varchar(512) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `drive_number` int(11) DEFAULT NULL,
  `course` int(11) DEFAULT NULL,
  `speed` int(11) DEFAULT NULL,
  `max_speed` int(11) DEFAULT NULL,
  `distance` double DEFAULT NULL,
  `gsm_quality` int(11) DEFAULT NULL,
  `sat_count` int(11) DEFAULT NULL,
  `private` int(11) DEFAULT NULL,
  `user_created` tinyint(1) NOT NULL DEFAULT '0',
  `io_01` int(11) DEFAULT NULL,
  `io_02` int(11) DEFAULT NULL,
  `io_03` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `timestamp` (`timestamp`),
  KEY `world_x` (`world_x`),
  KEY `world_y` (`world_y`),
  KEY `status` (`status`),
  KEY `timestamp_status` (`timestamp`,`status`),
  KEY `status_timestamp` (`status`,`timestamp`),
  KEY `idx_raw_data` (`raw_data`),
  KEY `drive_number` (`drive_number`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=1412811 DEFAULT CHARSET=utf8
  • Can you provide a SHOW CREATE TABLE My_Table\G? – Vérace Jun 11 '15 at 10:22
  • For one of the position tables? – acj Jun 11 '15 at 10:27
  • Yes please. Maybe a couple of them, but not if they're identical, which if I've understood, they are. – Vérace Jun 11 '15 at 10:32
  • Yes they are completely identical - i edited my post with one SHOW CREATE. – acj Jun 11 '15 at 10:44
  • How are your access times at the moment? – Vérace Jun 11 '15 at 10:50
0

OK - I'll try and answer this question, however you should realise that there is no "right" answer - just assessments based on situation and experience.

This is a classic question about multi-tenancy - shared versus isolated. There's a very good article here from Microsoft.

AIUI, MySQL has a limit of 32,000 databases per server - but if you're running into that limit, you should obtain more server instances long before that happens. Anyway, there are file limits associated with various OS configurations - but I don't have a clue about Amazon's limitations.

I'm more of a believer in isolated schemas with a caveat about reference tables and the like - use a qualified schema.table.name for, say, cities or similar.

Just a note, when I refer to a given post in my answer, please take the time to look at the entire thread - as I said, there are valid differences of opinion on this matter.

From my answer here, "You may have some data duplication issues with a multi-tenant approach, but that's a matter for you to assess in the light of your application/usage/servers/budget..."

One of the better answers that I have seen is here - however it requires monitoring, expertise and effort - you'll get nothing for nothing (except here where the advice is free! :-) )

My own recommendation is that you change your partitioning/sharding scheme to make it by customer (and possibly by year if that suits you), but I would NOT have a database per monitored system - that strikes me as overkill/unproductive/not_necessary/confusing. That way, your number of schemas is either your number of clients (or no. clients * years).

My understanding is that these data are a logging kind of scenario, so therefore, previous years would not be modified very frequently.

Under my scenario, any database modifications would only occur for no._of_clients (or no._of_clients * years) and I believe that it may present a simplification of your application and your job.

Again, I emphasise that there are alternative valid viewpoints that could be argued - this is my 0.02c based on my own experience and knowledge. Another good thread is here.

For GIS tasks, I would definitely prefer PostgreSQL, but it could be that MySQL 5.7 (and greater) will be a big improvement with the adoption of the Boost.Geometry library for GIS functions.

  • As i said i'm not an expert so thanks for the links - i gathered some good knowledge today. As it seems we're using a highly shared database with some manual horizontal partitioning which isn't as uncommon as i thought. Unfortunately RoR doesnt seem to have an integrated solution for utilizing this kind of scaling so in regards of my time/money budget i'll stick with the existing db design solution for now. Thanks for your great answer. – acj Jun 11 '15 at 15:18
  • 1
    Maybe I've misunderstood, but isn't Ruby on Rails (RoR) a Web Development Framework? I would have thought that it would be database agnostic? You could use MySQL Proxy perhaps? – Vérace Jun 11 '15 at 15:29
  • RoR or better ActiveRecord which the App is using expects the tables to have a specific naming. As soon as i leave this path i have to mess with table renaming or getting back to manual SQL-querying through rails. Even more so if the Data for one Model is split/partitioned over different tables. There are some gems out there but they all looked not production ready. – acj Jun 11 '15 at 15:51
  • I think the database limit is how many subdirectories can be put in a directory. That makes it an OS problem. Anyway, 32000 is so many that the inefficiency of looking up a database may be noticeably slow. Don't do it. Ditto for tables. Ditto for partitions. – Rick James Jun 12 '15 at 22:36
0

Performance for picking one row from a one of 100 tables with 3M rows each versus using the system_id as the first part of an index in a 300M-row table is essentially the same. Ditto with partitioning.

If you need to scale a lot more, then shard on system_id. That is have multiple servers, and all requests from certain system_ids go to a certain server. But it does not sound like you are there yet.

If you need to purge old data, PARTITION BY RANGE and use months or years for partitioning, but keep it to no more than, say, 50 partitions.

INT is 4 bytes; SMALLINT is 2 (etc). I suspect that many of your INT fields could be make smaller. And UNSIGNED. Smaller --> faster.

world_x, world_y -- are they longitude and latitude? DOUBLE gives you 3.5nm precision, which is probably unachievable and probably overkill. The pair takes 16 bytes. world_y DECIMAL(8,6) and world_x DECIMAL(9,6) would take only 9 bytes for 16cm (1/2 ft) precision.

Show us some of the queries. That may make a difference for discussing how sensible the schema is.

Who does the backup/archive? You or the customer?

  • Thanks for the tips, this might save us some db space on amazon :) Amazon does the backup and we archiv older years of data from time to time. Right now its not that of a performance problem, more like a handling through rails inconvenience as i tried to point out. So basically im collecting arguments to convince them to switch to one big table, perhaps with transparent (at least for RoR) partioning/sharding - but better more native handling through RoR as a beneficial result. – acj Jun 15 '15 at 1:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.