CODEWITHSUNDEEP
mysqlbigdatamonetdb
marcin_koss
marcin_koss

Reputation: 5882

Looking for a database solution for mostly read application, querying 300 million records

Looking for right database technology to query efficiently 300 mil record tables. Tables may contain 5-30 columns mostly tinyint + zip, state. Users can issue arbitrary queries with where conditions on many columns and group with count either by state or zip. Datasets are static in a sense data is reloaded regularly and there is no inserts, just reads.

I already tried Mysql (RDS) with InnoDB. Because of big number of records and nature of queries I could not get satisfactory performance.

Tried MonetDB (columnar store) with very good results but it doesn't seem it is being used by many which raises some concerns.

Requirement is response time quick enough for responsive web UIs for analytics.

What other technologies I should explore?

Upvotes: 0

Views: 592

Answers (3)

dnedev
dnedev

Reputation: 142

I would encourage you to try MonetDB, it depends on your queries, but generally - it being a column-store you should get good performance (even over 300 mil record). Plus you don't need to manually create indices and such - it is mostly self-optimizing. MonetDB also has a Node.js driver/connector, which may be used for writing quick analytical web apps. There are also connectors for popular languages/frameworks: Ruby, Python, Perl, PHP, Java (via JDBC).

And don't worry, MonetDB is being actively developed (disclaimer: I am actually a developer) and you can get answers relatively quickly on the users mailing list.

Upvotes: 3

yurgis
yurgis

Reputation: 4067

Try Cassandra. It is being used very widely nowadays (Facebook, Netflix and many others).

If you want to keep your code relatively abstract from underlying database technology, you can use kundera JPA 2.0.

Additionally it is super easy to use Cassandra locally (for debugging and unit testing). Just take this class.

Upvotes: -1

Tom McClure
Tom McClure

Reputation: 6729

Take a look at Google App Engine's Datastore. They automatically optimize your queries with indexes for speedy responses and it's Google, so they make it easy to scale from zero traffic to bajillions of hits.

Applications can be written in Java or Python, using your own framework or theirs.

Upvotes: 0

Related Questions