Hot answers tagged

141

Distributed Database Systems 101 Or, Distributed Databases - what the FK does 'web scale' actually mean? Distributed database systems are complex critters and come in a number of different flavours. If I dig in to the depths of my dimly remembered studies on this at university I'll try to explain some of the key engineering problems to building a ...


88

This is exactly what I do every day, except instead of using the hourly data, I use the 5 minute data. I download about 200 million records everyday, so the amount you talk about here is not a problem. The 5 minute data is about 2 TB in size and I have weather data going back 50 years at an hourly level by location. So let me answer you questions based on my ...


57

In a company I work for we are dealing with similar amount of data (around 10 TBs of realtime searchable data). We solve this with Cassandra and I would like to mention couple of ideas that will allow you to do O(1) search on a multi TBs database. This is not specific to Cassandra db though, you can use it with other db as well. Theory Shard your data. ...


55

PostgreSQL and BRIN indexes Test it for yourself. This isn't a problem on a 5 year old laptop with an ssd. EXPLAIN ANALYZE CREATE TABLE electrothingy AS SELECT x::int AS id, (x::int % 20000)::int AS locid, -- fake location ids in the range of 1-20000 now() AS tsin, -- static timestmap 97.5::numeric(5,2) AS temp, -- ...


41

If I was going to put this into SQL Server, I would suggest a table something like: CREATE TABLE tcp_traffic ( tcp_traffic_id bigint constraint PK_tcp_traffic primary key clustered IDENTITY(1,1) , tcp_flags smallint /* at most 9 bits in TCP, so use SMALLINT */ , src_as int /* Since there are less than 2 billion A.S.'s possible, use INT ...


27

In general, for such a structured dataset I suspect you could write a custom data format which was faster for most daily operations (i.e. small data pulls from an arbitrary time). The benefit of moving to a standard DB tool is likely in some of the extras, for example ad hoc queries, multiple access, replication, availability etc. It's also easier to hire ...


24

Horizontal Scaling Horizontal Scaling is essentially building out instead of up. You don't go and buy a bigger beefier server and move all of your load onto it, instead you buy 1+ additional servers and distribute your load across them. Horizontal scaling is used when you have the ability to run multiple instances on servers simultaneously. Typically it is ...


21

Relational databases can cluster like NoSQL solutions. Maintaining ACID properties may make this more complex and one must be aware of the tradeoffs made to maintain these properties. Unfortunately, exactly what the trade-offs are depends on the workload and of course the decisions made while designing the database software. For example, a primarily OLTP ...


17

The key words here are: "heavily updated" "in the table for 2-3 hours". Point 1. is indication for a lower fill factor, while 2. is the opposite. It helps performance if multiple row versions are stored on the same data page. H.O.T. updates would achieve that. Read here or here. They need some wiggle room on the data page - like dead tuples or space ...


14

The fundamental answer is that the consistency model is different. I am writing this to expand ConcernedOfTunbridge's answer which really ought to be the reference point for this. The basic point of the ACID consistency model is that it makes a bunch of fundamental guarantees as to the state of the data globally within the system. These guarantees are ...


13

Bigtable doesn't use SQL (a query language) so SQL can't be used directly to query the database. And Bigtable doesn't have "relations" in the same way as relational databases, it's more like bare tables. If you want to get data from two tables, you have to do two lookups, and combine the result set in the application code. In other words the "join" ...


13

CAP is basically a continuum along which BASE and ACID are on opposite ends. CAP is Consistency, Availability, and Partition tolerance. Basically you can pick 2 of those but you can't do all 3. ACID focuses on Consistency and availability. BASE focuses on Partition tolerance and availability and throws consistency out the window.


13

IMO you are making what is probably a pretty common mistake when it comes to web pages which is to assume that the answer to performance problems due to initial result size on MySQL is to jump to NoSQL solutions often with little understanding of what the tradeoffs are or how to use them appropriately and effectively. I would be surprised if a well-tuned db ...


13

It amazes me me that nobody here has mentioned benchmarking - that is until @EvanCarroll came along with his excellent contribution! If I were you, I would spend some time (and yes, I know it's a precious commodity!) setting up systems, running what you think will be (get end-user input here!), say, your 10 most common queries. My own thoughts: NoSQL ...


12

Ultimately, it depends on the architecture that your machine has. (background) Nodes can solely store data in their properties. Their properties are stored using a key-value store. (per here) The value in each property is limited to Java primitives (ints, floats, etc.), strings, and arrays of primitives/strings. Therefore, the maximum amount of data a ...


12

Lets start with your first link. It says clearly: Working on large databases with referential integrity just does not perform well. And that is right. Just you likely have no clue that "large database" is terabyte size with billions of rows in a table. A simple select may cascade into hundreds of millions of related elements to be deleted, and then you ...


12

Redis doesn't support nested data structures, and specifically it doesn't support a Hash inside a Hash :) You basically have a choice between two options: either serialize the internal Hash and store it in a Hash field or use another Hash key and just keep a reference to it in a field of the outer Hash.


11

Option 1 There are several reasons for this, which I'll explain below. First, here's how to do it. Use your choice of standard RDBMS platform. Set up your schema with several user-configurable fields, and make your application facilitate the configuration on a per-tenant basis. From the per-tenant metadata, you can create a per-tenant view of their data, ...


11

Since (a) the information you are working with appears to be, in a of itself, a very valuable organizational resource, and (b) the volume of data will be considerable, I would decidedly (c) build a relational database on one of the major SQL platforms. That of course —from a very general perspective— requires three essential factors: A clearly defined ...


10

The Quick Answer - Yes. Happens all the time. There are plenty of good solutions. What solutions are already in your environment? I am helping one client that takes their web site/session activity information from their web application, they write it to xml then deserialize that xml into Hadoop. They then use Hive on top of Hadoop to create aggregations and ...


10

cqlsh -k mykeyspace -e 'COPY fromTable(columnNames) TO STDOUT' | head -n -1 | cqlsh -k mykeyspace -e 'COPY toTable(columnNames) FROM STDIN'


9

If you can't scale a major RDBMS then your database design (includes indexing, queries and the like) or hardware is wrong. The choice of platform is almost irrelevant. It is that simple. Especially when you mention "few hundred megabytes" which implies low volumes (I mean a few dozen writes per second)


9

This seems a little outside the scope of a StackExchange question. However..... NoSQL databases are, typically, build to resolve specific issues with the relational model. The most common issue addressed is scalability. However, because they're all designed to address different aspects of certain problems that some applications have with the relational ...


8

Sounds like my Cassandra Cluster Admin is exactly what you want! Take a look at it here: https://github.com/sebgiroux/Cassandra-Cluster-Admin


8

This is a tough question in the sense that there are several NoSQL databases out there, and they're all slightly (sometimes radically) different from each other. An important concept to understand for NoSQL technology is that of Brewer's CAP Theorem, so I've provided a link to a good article on it. For a brief, graphical overview on the CAP Theorem, you ...


8

This question is really far too vague to answer effectively. There are dozens of "NoSQL" data stores out there which have various use cases. Here is a 10,000 foot view of what's out there. In my mind, there are basically 3 main categories of NoSQL data stores commonly used, key/value stores, document databases, and big data (hadoop). This is a somewhat ...


8

As far as I know there are no "nosql" databases that promise ACID transactions, so for banking purposes they are a non starter. Referential consistency support is not usually in their key feature sets either. mySQL claims ACID transactions when using innodb tables, but I believe there are some caveats around that which may be show stoppers (any mix of other ...


8

Use the maxmemory to set a limit to how much your Redis database can grow too. Failing to do so, Redis will grow until the OS will kill it once memory is exhausted (per your current experience). The usage of maxmemory should be coupled with maxmemory-policy - you can choose from different eviction policies depending on your use case's requirements. For ...


8

DBA is a small acronym but a large role. At various times I have seen a DBA look after storage network VMs compute nodes installation and configuration of the above backups, and testing restores DR strategy enterprise data integrity ETL data security data modelling, normalisation and database design data tier programing performance tuning operational ...


8

Right now the table only has about 320k records and I can use ALLOW FILTERING with no problem, but I realize this might not always be the case. So here's the thing: Cassandra is very good at querying data by a specific key. It is also good at retrieving a range of data within a partition. "SELECT * FROM {}.{} WHERE timestamp > {} ALLOW FILTERING;" But ...


Only top voted, non community-wiki answers of a minimum length are eligible