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 ...


93

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 ...


60

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, -- ...


58

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. ...


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 ...


22

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 ...


15

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.


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 ...


14

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 ...


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

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

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 ...


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

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 ...


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

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

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

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 ...


7

You can store your index as a list of fixed-size offsets into the block containing your key data. For example: +--------------+ | 3 | number of entries +--------------+ | 16 | offset of first key data +--------------+ | 24 | offset of second key data +--------------+ | 39 | offset of third key data +--------------+ | ...


7

I have been in the NoSQL field since last 3 years. Being a MongoDB DBA you need to work closely with Development and Ops teams. Following are the things you need to do as day to day tasks as a MongoDB DBA. The role can be broadly classified into three parts: Administration: New deployments (manual/automated) Deployment design of your database systems ...


6

Consider also that there is no reason why you can't use a relational database for some things and the nosql database for other things.


6

Though this is years old question... In short, you can understand ACID as guarantee of data integrity/safety in any expected circumstances. As like in generic programming, all the headaches comes from multi-threading. The biggest issue on NoSQL is mostly ACI. D(urability) is usually a separated issue. If your DB is single-threaded - so only one user can ...


6

If eventual consistency is acceptable and all your queries are aggregates then perhaps a low-latency OLAP system might work for you. Your requirement sounds a bit like an algorithmic trading platform. This type of architecture is often used in trading floor systems that have a requirement to carry out aggregate statistical analysis computations on up to ...


6

My answer won't be as well-written as the previous one, but here goes. Michael Stonebraker of Ingres fame has created a MPP shared-nothing column-store (Vertica) and a MPP shared-nothing New SQL database (VoltDB) which distributes data between different nodes in a cluster and maintains ACID. Vertica has since been bought by HP. I believe other New SQL ...


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