Hey all,
I've been experimenting with the best way to store a large (~100GB) of data for retrieval.
Essentially, I'm storing 9 variables for approximately 1,000,000 locations a day for the
last ten years. This can work out at around 4MB a variable a day - but not all variables
are always present (could be null).
(If you don't care about the details, I'll summarize at the end of this email).
Inserting and retrieving from a MyISAM table seemed to be approaching impossible. (Not
totally surprising.) I originally had a table along the lines of:
create table basic_daily_report(
date DATE NOT NULL,
location_id MEDIUMINT UNSIGNED NOT NULL,
variable1 float,
variable2 float....
variable9 float,
primary key (date, location_id)
);
(Just a summary of the actual table)
With this I had a maxiumum table size of around 100GB - just barely enough to do it. I
expected I would end up segmenting by year, or something similar, as ugly as that is. I
tested InnoDB as an alternative to this, but we'll get to that in a second. Basically,
inserting a day's worth of data would take ages, and pretty much require an analyze table
for a couple hours every morning. Selecting was getting to be pretty slow, as well.
Eventually, I hit on the idea of including one row per day:
create table basic_daily_grid(
date DATE NOT NULL PRIMARY KEY,
variable1 MEDIUMBLOB,
variable2 MEDIUMBLOB ...
variable9 MEDIUMBLOB
);
And wrote a UDF such that you pass it the variable and a location, and it'll return the
exact value. This works well because every day has a constant number of locations in a
grid format, so it's simply an array lookup. So,
select grid_point(location_id, variable1) from basic_daily_grid where date=20030101
would return the right value for locationid. It turns out this is almost (95%) as fast as
the first version in selecting, but it has the added bonus of inserts now only take ~5
seconds per day!
Alas, after inserting 260 days (less than a year) I hit the MyISAM table size limit of
4.2GB - because a BLOB is a variable length field.
-----------------------------------------------------------------
I mention all the above in case someone has an alternative solution I'm looking over.
Possible solutions I've found are a) use InnoDB instead, b) work with the source to
create a new field type BLOBARRAY of a constant width instead of dynamic, c) work with
the source to somehow overcome the 4.2GB limit on a dynamic table.
c) Seems unlikely - if the actual developers can't do it, I probably can't
b) Seems possible, I assume no one saw a need for a constant width column of 4MB, so
hopefully its not too difficult
a) Was my first try. Inserting takes about twice as long as myisam... sure, I can deal
with that. Selecting a specific date is in the same ballpark as well, so little problem
there. What I'm having severe performance issues on are querys that group, or do a
count(*). For example:
mysql> explain select date from basic_daily_grid_innodb;
+-------------------------+-------+---------------+---------+---------+------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------------------------+-------+---------------+---------+---------+------+------+-------------+
| basic_daily_grid_innodb | index | NULL | PRIMARY | 3 | NULL |301 | Using index |
+-------------------------+-------+---------------+---------+---------+------+------+-------------+
mysql> select date from basic_daily_grid_innodb;
...
317 rows in set (0.00 sec)
mysql> explain select date, count(*) from basic_daily_grid_innodb group by date;
+-------------------------+-------+---------------+---------+---------+------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------------------------+-------+---------------+---------+---------+------+------+-------------+
| basic_daily_grid_innodb | index | NULL | PRIMARY | 3 | NULL |301 | Using index |
+-------------------------+-------+---------------+---------+---------+------+------+-------------+
mysql> explain select date, count(*) from basic_daily_grid_innodb group by date;
...
317 rows in set (2 min 54.95 sec)
I assume this is due to versioning or some other transactional feature. Or, is this a bug,
or am I doing something wrong? I don't quite see why grouping items that are all unique
should be that much slower than not grouping. I need InnoDB for the unlimited table size,
but I don't (Really) need transactions, commit/rollback, or checkpoints.
Any suggestions on solving this last hurdle? Its entirely likely I'll need to group by
year and average the results, or something similar - and at 3 minutes a pop, thats a
little high for my liking. Perhaps I'm expecting too much?
Thanks,
Nick Elliott
