0

I have been playing around with grib2 data from the National Weather Service lately, and have been databasing some model forecast data.

The current design model I have is probably bad design, but I have 87 columns of data per model per forecast hour I want to save. The long range forecast models have 52 forecast hour intervals I'm working off of, but I could increase that later. I'm collecting from 3 models... so right now that means I have 87 columns, 1 table per model per forecast hour - in other words, somewhere around 80 tables of data with 87 columns each.

Already, this is causing problems, as MySQL apparently has a hard limit for how many table joins you can do and I have hit this a few times. Also, I'm starting to get PHP errors trying to execute multiple queries at once and then joining the results in PHP to one data set - too many buffered connections, etc. - I currently have PHP doing the queries looping through forecast hours to auto-generate the table joins... but I want a better way to do this. It's very hacky sloppy.

Would it make more sense to:

  • Save the current table structure? (~80 tables per location - 1 right now)

  • Create a table just for hour intervals? (This could still hit the table limit if I increase the hour frequency - but would be a bit less tables. Each table would have ~240 columns.)

  • Create a huge table with all hour data per each model. Would have 3 tables, but one of those tables would have 6,880 columns of data. I understand there is a hard limit of around 4,000 columns? This would not work, probably.

1

1 Answer 1

0

Hard limits:

  • JOINs, including implicit ones in Views: 61.
  • Columns per table < 1000 (1017 in 5.6.9, InnoDB); 2598(?) for MyISAM -- But don't use MyISAM.
  • others

But why do you need a lot of JOINs? Please elaborate on the schema (SHOW CREATE TABLE, leaving out many of the columns) and SELECT (leaving out all but a couple of representative JOINs).

Do you actually do arithmetic or searching in MySQL on the 240 readings? If not, simply pack them in a JSON string and let the app break them apart. Further, compress the JSON string (in the client) and store in a BLOB. (Smaller --> faster) PHP would have no problem unpacking 4000 fields with json_decode(). And it could be in the desired structure (array / hash / nested arrays / etc)

The main thing is to structure the schema for what works well in the database, then leave the rest for the application. The database is a repository; the app is the compute engine.

One table per hour or one table per location -- this smells bad. One table per location per hour would be really bad. Once you have thousands of tables in a database (or thousands of databases), the OS gets sluggish in accessing them.

So... 3 (or 1?) tables with

  • location_id (normalized, to save space)
  • hour (probably a DATETIME)
  • one (or a few or maybe 80) JSON strings each with 87(?) numbers, structure for the convenience of the app.

(Sorry, I got lost in the dimensions -- 3 somethings * 80 something else * 87 * 52 * ???)

Plus, PRIMARY KEY(location_id, hour) (or possibly in the opposite order. And (probably) a plain INDEX with those columns in the opposite order. Pick the order in the PK based on which order is more important for the SELECTs. (Don't worry about INSERTs.)

1
  • 1
    The concept "the database is the repository" and "the app the compute engine" is debatable, to say the least. You can compute lots of things within the database, and you save the time necessary to move data in and out of it. You can do Machine Learning within the database, for instance, with some tools like MADlib. I would try to balance computing things within and outside of the database depending on where calculations and access to the data are easier.
    – joanolo
    Commented Jan 6, 2017 at 1:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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