Re: Big table (too big?) how to handle?
Posted by: Rick James
Date: March 23, 2015 04:05PM

InnoDB !

20-30GB for one bigass table -- not a problem. And better than 40 'identical' tables.

Daily summary data -- Yes. Or even hourly. And it might include max, min, avg, etc for the day (or hour). An hourly table would be only 1GB or so; daily would be much smaller.

Probably it is not worth having both hourly and daily, since daily can be derived from hourly.

What kinds of queries will you have? If "most" queries look only at "recent" data, then it _may_ be worth PARTITIONing on the date. If so, then PARTITION BY RANGE and start with yearly partitions until, say Jan of 2014. Then have monthly after that. (There are several reasons for that unbalanced approach.)

If you are partitioning the time column must be in every UNIQUE (or PRIMARY) key; put it last.

Let's discuss the likely SELECTs -- a lot of the design needs to center around them. Please show what you are thinking about there.

Let's see the CREATE TABLE. There is no use storing temperature in DOUBLE (8 bytes) when TINYINT (1 byte) would do for -128 to +128 or DECIMAL(4,2) (2 bytes) for -99.99 to +99.99, which would suffice for Celsius.

You have all FLOAT now? At 4 bytes, that is not bad. (Big range; 6-7 significant digits.) Carefully chosen DECIMAL can be smaller. Scaled MEDIUMINT (3 bytes) or SMALLINT (2 bytes) would probably work. (Example, temperature to one decimal place would fit in SMALLINT after multiplying by 10.)

Smaller structure improves performance. You _might_ be able to cut the table size in half.

Another trick might be possible fields that are 0 most of the time. (Example: precipitation) How many do you have?

Condensing "old" data to 10-minute intervals is certainly feasible, but does not seem like a requirement, based on the size, etc.

Data Warehousing (which is what you are doing): http://mysql.rjweb.org/doc.php/datawarehouse and Summary Tables: http://mysql.rjweb.org/doc.php/summarytables

I don't think you need my companion blog on high speed ingestion, since you are INSERTing only about 1 row per second.

As for transferring gigabytes of data over the Internet... Suggest you get your new machine started and collecting data. Then, with some leisure, copy a compressed dump of the data from the other machine. You can keep both alive for SELECTs, but your clients would need to understand that "old" data comes from old system. new from new, and they can't get both easily.

Because of MyISAM, you can't be adding new data to the old system while taking a dump. Hmmm... Maybe you can! Set up a MERGE table containing the current huge table, plus an empty, new, table. SELECTs go to the MERGE table. Inserts go to the new table. mysqldump reads the huge old table. I think it will work!

Well, you should get your new system started before building the Merge table. That way, you never have to dump the "new" table from the "old" system.

Options: ReplyQuote


Subject
Written By
Posted
Re: Big table (too big?) how to handle?
March 23, 2015 04:05PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.