I am looking for advice on table/index design for the following situation:
I have a large table (stock price history data, InnoDB, 35 million rows and growing) with a compound primary key (assetid (int),date (date)). in addition to the pricing information, i have 200 double values that need to correspond to each record.
CREATE TABLE `mytable` (
`assetid` int(11) NOT NULL,
`date` date NOT NULL,
`close` double NOT NULL,
`f1` double DEFAULT NULL,
`f2` double DEFAULT NULL,
`f3` double DEFAULT NULL,
`f4` double DEFAULT NULL,
... skip a few …
`f200` double DEFAULT NULL,
PRIMARY KEY (`assetid`, `date`)) ENGINE=`InnoDB` DEFAULT CHARACTER SET latin1 COLLATE
latin1_swedish_ci ROW_FORMAT=COMPACT CHECKSUM=0 DELAY_KEY_WRITE=0
PARTITION BY RANGE COLUMNS(`date`) PARTITIONS 51;
i initially stored the 200 double columns directly in this table for ease of update and retrieval, and this had been working fine, as the only querying done on this table was by the assetid and date (these are religiously included in any query against this table), and the 200 double columns were only read. My database size was around 45 Gig
However, now i have the requirement where i need to be able to query this table by any combination of these 200 columns (named f1,f2,...f200), for example:
select from mytable
where assetid in (1,2,3,4,5,6,7,....)
and date > '2010-1-1' and date < '2013-4-5'
and f1 > -0.23 and f1 < 0.9
and f117 > 0.012 and f117 < .877
etc,etc
i have not historically had to deal with this large of an amount of data before, so my first instinct was that indexes were needed on each of these 200 columns, or i would wind up with large table scans, etc. To me this meant that i needed a table for each of the 200 columns with primary key, value, and index the values. So i went with that.
CREATE TABLE `f1` (
`assetid` int(11) NOT NULL DEFAULT '0',
`date` date NOT NULL DEFAULT '0000-00-00',
`value` double NOT NULL DEFAULT '0',
PRIMARY KEY (`assetid`, `date`),
INDEX `val` (`value`)
) ENGINE=`InnoDB` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ROW_FORMAT=COMPACT CHECKSUM=0 DELAY_KEY_WRITE=0;
i filled up and indexed all 200 tables. I left the main table intact with all 200 columns, as regularly it is queried over assetid and date range and all 200 columns are selected. I figured that leaving those columns in the parent table (unindexed) for read purposes, and then additionally having them indexed in their own tables (for join filtering) would be most performant. I ran explains on the new form of the query
select count(p.assetid) as total
from mytable p
inner join f1 f1 on f1.assetid = p.assetid and f1.date = p.date
inner join f2 f2 on f2.assetid = p.assetid and f2.date = p.date
where p.assetid in(1,2,3,4,5,6,7)
and p.date >= '2011-01-01' and p.date < '2013-03-14'
and(f1.value >= 0.96 and f1.value <= 0.97 and f2.value >= 0.96 and f2.value <= 0.97)
Indeed my desired result was achieved, explain shows me that the rows scanned are much smaller for this query. However i wound up with some undesirable side effects.
1) my database went from 45 Gig to 110 Gig. I can no longer keep the db in RAM. (i have 256Gig of RAM on the way however)
2) nightly inserts of new data now need to be done 200 times instead of once
3) maintenance/defrag of the new 200 tables take 200 times longer than just the 1 table. It cannot be completed in a night.
4) queries against the f1, etc tables are not necessarily performant. for example:
select min(value) from f1
where assetid in (1,2,3,4,5,6,7)
and date >= '2013-3-18' and date < '2013-3-19'
the above query, while explain shows that it lookgin at < 1000 rows, can take 30+ seconds to complete. I assume this is because the indexes are too large to fit in memory.
Since that was alot of bad news, I looked further and found partitioning. I implemented partitions on the main table, partitioned on date every 3 months. Monthly seemed to make sense to me but i have read that once you get over 120 partitions or so, performance suffers. partitioning quarterly will leave me under that for the next 20 years or so. each partition is a bit under 2 Gig. i ran explain partitions and everything seems to be pruning properly, so regardless i feel the partitioning was a good step, at the very least for analyze/optimize/repair purposes.
I spent a good deal of time with this article
http://ftp.nchu.edu.tw/MySQL/tech-resources/articles/testing-partitions-large-db.html
my table currently is partitioned with primary key still on it. The article mentions that primary keys can make a partitioned table slower, but if you have a machine that can handle it, primary keys on the partitioned table will be faster. Knowing i have a big machine on the way (256 G RAM), i left the keys on.
so as i see it, here are my options
Option 1
1) remove the extra 200 tables and let the query do table scans to find the f1, f2 etc values. non-unique indexes can actually hurt performance on a properly partitioned table. run an explain before the user runs the query and deny them if the number of rows scanned is over some threshold i define. save myself the pain of the giant database. Heck, it will all be in memory soon anyways.
sub-question:
does it sound like i have chosen an appropriate partition scheme?
Option 2
Partition all the 200 tables using the same 3 months scheme. enjoy the smaller row scans and allow the users to run larger queries. now that they are partitioned at least i can manage them 1 partition at a time for maintenance purposes. Heck, it will all be in memory soon anyways. Develop efficient way to update them nightly.
sub-question:
Do you see a reason that i may avoid primary key indexes on these f1,f2,f3,f4... tables, knowing that i always have assetid and date when querying? seems counter intuitive to me but i am not used to data sets of this size. that would shrink the database a bunch i assume
Option 3
Drop the f1,f2,f3 columns in the master table to reclaim that space. do 200 joins if i need to read 200 features, maybe it wont be as slow as it sounds.
Option 4
You all have a better way to structure this than I have thought of so far.
* NOTE: I will soon be adding another 50-100 of these double values to each item, so I need to design knowing that is coming.
Thanks for any and all help
Update #1 - 3/24/2013
I went with the idea suggested in the comments I got below and created one new table with the following setup:
create table 'features'{
assetid int,
date date,
feature varchar(4),
value double
}
I partitioned the table in 3 month intervals.
I blew away the earlier 200 tables so that my database was back down to 45 Gig and started filling up this new table. A day and a half later, it completed, and my database now sits at a chubby 220 Gigs!
It does allow the possibility of removing these 200 values from the master table, as i can get them from one join, but that would really only give me back 25 Gigs or so maybe
I asked it to create a primary key on assetid, date,feature and an index on value, and after 9 hours of chugging it really hadn't made a dent and seemed to freeze up so i killed that part off.
I rebuilt a couple of the partitions but it did not seem to reclaim much/any space.
So that solution looks like it probably isn't going to be ideal. Do rows take up significantly more space than columns i wonder, could that be why this solution took up so much more space?
I came across this article:
http://www.chrismoos.com/2010/01/31/mysql-partitioning-tables-with-millions-of-rows
it gave me an idea. It says:
At first, I thought about RANGE partitioning by date, and while I am using the date in my queries, it is very common for a query to have a very large date range, and that means it could easily span all partitions.
Now I am range partitioning by date as well, but will also be allowing searches by large date range, which will decrease the effectiveness of my partitioning. I will always have a date range when i search, however i will also always have a list of assetids. Perhaps my solution should be to partition by assetid and date, where i identify typically searched assetid ranges (which i can come up with, there are standard lists, S&P 500, Russell 2000, etc). This way I would almost never look at the entire data set.
Then again, I am primary keyed on assetid and date anyways, so maybe that wouldn't help much.
Any more thoughts/comments would be appreciated.
(value_name varchar(20), value double)
would be able to store everything (value_name
beingf1
,f2
, ...)