3 ms·
Are there updates to the data, or is it append/delete only? Does each object have a fixed size? (Or can be a fixed size with padding?) What kind of index do y
by ars 18y ago
Are there updates to the data, or is it append/delete only?
Does each object have a fixed size? (Or can be a fixed size with padding?)
What kind of index do you need? Do you need advanced SQL commands, like aggregate data, and complex joins?
MySQL can handle billions http://dev.mysql.com/doc/refman/5.1/en/features.html http://dev.mysql.com/doc/refman/5.1/en/features.html of rows - but it may be overkill for you. Pretty much any database can.
But that's the wrong question. The right question is what do you need the database to do. Mainly what kind of index, and what kind of joins.
PS. If all you need is the aggregate data, why store the details? Just store the aggregate and update it on the fly. (Presumably store the details offline, so you can regenerate the aggregate if needed.)
Read this: http://news.ycombinator.com/item?id=594889 http://news.ycombinator.com/item?id=594889 for updating the agregates on the fly. And this: http://news.ycombinator.com/item?id=587723 http://news.ycombinator.com/item?id=587723 for one type of system for storing large numbers of objects.
- imajes 18y ago96% of change is append. 0.0001% would be delete. Sorts of views might be: "show me objects that have lat xx and long yy but tagged "buttons" and created on mm/dd/yyyy" in the basic instance, but also "show me objects that my friends like" as a more complex example. Makes me think that what I really need is a graph db- object relationship seems the key factor.
- ars 18y agoSo you do have updates? Because updates makes a database more complicated. If you need lat/lng then you need an R-tree and very few databases come with that. Sounds to me like your going to have to write your own database - or more accurately your own index. Basically store data as append only, and handle change as delete/append. Store just the offset to the data as the key. I really hope your index can fit on one machine. Do it like the facebook link I gave you earlier. Your going to have a hard time with index merges (lat/lng merge with tags). Maybe a better idea is don't store the object in the database, but just the offset to it (plus machine ID). Then use any commercial database that has an R-Tree. You have 200GB to 1PT of data to store. Which needs at least 25 machines. But if you can store just the index, but not the data, maybe you can store the index on just one machine - that will makes things easier. (Obviously you'll have multiple machines with the same index for load balancing.) This might help also: http://www.ddj.com/184410998 http://www.ddj.com/184410998 (instead of an R-Tree, you might be able to a regular index).
- imajes 18y agoYeah, i think there's a lot of validity in this comment... I fully expect to do stuff like abstract storage of lat/long to separate stores- so most append is really just creating a new related object- but how to store it is the fun problem :) good tips. thanks. :)
- fhars 18y agoPostgreSQL has spatial indexing capability, too. So you can do something like (the exact syntax for spatial ops escapes me right now): SELECT DISTINCT obj FROM media NATURAL JOIN mediatags WHERE (location INTERSECTS somebox) AND tag IN (SELECT tag FROM usertags WHERE user = 137) You will probably have to spend some quality time with the query planner figuring out how to set up your indexes so the common queries will be fast, but it should be possible. (EDIT: fighting with the formatting. Why is there no preview?)
- ajmm 18y agoRegarding space filling curves some implementations would be: 1) http://code.google.com/p/uzaygezen/ http://code.google.com/p/uzaygezen/ (Hilbert) 2) http://www.h2database.com/html/features.html#multi_dimensional http://www.h2database.com/html/features.html#multi_dimension... (Z-order) Other space transformations could be employed like the Pyramid technique or the iDistance (http://obsearch.net http://obsearch.net). Nevertheless, if the "intrinsic dimensionality" of your objects is too high then the curse of dimensionality comes into play and sequential scan might be cheaper. In such a case the Va-file, IQ-Tree or even "sketches" might be the only way to go.
- kingsley_20 18y agoapparently, you can aggregate lat-long into a geohash http://en.wikipedia.org/wiki/Geohash http://en.wikipedia.org/wiki/Geohash. seems like a good idea, although i havent actually tried it.
- Tichy 18y ago"but it may be overkill for you" I am curious about this line. MySQL is not very complicated to set up, so how could it be overkill? Edit: seriously, "overkill" to me would be to use a sledge hammer to drive in a nail, that is, something that is too fat/heavy/complicated for the problem at hand.
- ars 18y agoTo store a billion records in MySQL if you don't needs it's abilities (indexes, joins and updates) is overkill. If you never do updates, and you write your own index, then a much simpler storage method with far less overhead would be what you need.
- Tichy 18y agoWriting one's own index already seems to be a lot more work than simply setting up MySQL. I get your point, I just have a hard time imagining what could be simpler. Perhaps writing to a sequential file without an index. Although even then I would worry about the capabilities of the file system, which I suppose MySQL would abstract away from me.