Tag Info

Hot answers tagged

89

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 deep in to the depths of my dimly remembered distributed systems papers I did at university (roughly 15 years ago) I'll try to explain some of the ...


15

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


14

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


13

There's no simple answer to your question, but here are a few things to think about. First, scale isn't the only thing to worry about. What you do with your data is. If you have 500 tables 30 TB of data, and you are doing simple OLTP with very little reporting, I don't think you will have too many problems. There are 32TB databases on PostgreSQL out ...


12

MS just had a tech talk in the Netherlands where they discussed some of this stuff. It starts off slowly, but gets into the meat of Hadoop around the 20 minute mark. The gist of it is that "it depends". If you have a sensibly arranged, (at least somewhat) easy to partition set of data that (at least somewhat) is homogeneous, it should be fairly easy to ...


9

SQLQueryStress is a great tool for small scale concurrency testing but it isn't really up to the job of load testing. A surprisingly little known toolset is available for free, from Microsoft, that can cope with most SQL Server stress testing scenarios, the RML Utilities. A very brief outline of the tools: ReadTrace converts Profiler traces from .trc ...


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

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


7

Asynchronous implies that the running totals do not need to be completely accurate at all times, or your data change patterns are such that a one-off running total build will be valid and accurate until the next load. Anyway, I'm sure you have thought that part through, so I won't labour the point. Your main options for a high-performance, supported, ...


7

I think you see a false dichotomy that does not exist. It can be useful to have connection pooling in place even if you expect a 1:1 mapping of clients to back-ends. If your connections are long-lived, you won't benefit from reducing backend setup/teardown overhead, as it's small and amortized across a long period. A pool like PgBouncer may remain useful ...


7

I am going to disagree on large and complicated queries with datagod here. I see these only as problems if they are disorganized. Performance-wise, these are almost always better because the planner has much more freedom in how to go about retrieving the information. However, large queries do need to be written with maintainability in mind. In general, I ...


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

As somebody who has to support/cleanup these large and complicated queries, I would say it is far better to break them apart into several small easy to understand chunks. It is not necessarily better from a performance point of view, but you are at least giving SQL a better chance to come up with a good query plan. Make life easier on the people that ...


5

The only thing I would like to comment on is Xeround I tried out the XEROUND MySQL Instance I found out that it only has three(3) Stroage Engines mysql show engines; +---------+---------+-----------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment ...


5

One tool you can use is SQLQueryStress. For a quick start provide: the stored procedure the iterations and the number of threads. Then start the stress testing. Metrics like CPU seconds, logical reads, etc... will be shown.


5

I'm not sure why you want asynchronous, but a couple of indexed view sounds like just the ticket here. If you want a simple SUM per some group that is: define running total. If you really want asynchronous, with 160 new rows per second your running totals will always be out of date. Asynchronous would mean no triggers or indexed views


5

Database pioneer and researcher Michael Stonebraker co-wrote a paper that discusses the limitations of traditional database architectures. Generally, they scale up with more expensive hardware, but have difficulty scaling out with more commodity hardware in parallel, and are limited by legacy software architecture that was designed for an older era. He ...


5

I suggest you take a very close look at this : http://codahale.com/you-cant-sacrifice-partition-tolerance/ It explains fairly well why "stall all reads and writes" is nothing more than an inevitable logical consequence if you want partitions and the data they contain to be consistent.


5

"Isn't it possible to scale database servers through virtualization, like forever?" Yes, you can make more and more database servers and you can also make a larger and larger database server (but the scale up limit can be reached very quickly), but in the scale out scenario are the servers you mention supposed to cooperate in any way or are they ...


4

Option 2 is known as "EAV" or Entity-Attribute-Value not relational no DB level constraints requires contortions to read the data unless a simple list But, it depends what you mean by "settings". If you have a few 1000 rows that are not objects and don't require constraints then, yes, use this pattern. This is what SQL Server does with sys.configurations ...


4

It is not entirely true that RDBMS cannot scale. However, the partial truth in the statement depends on the architecture. In the list that you gave, Oracle RAC is different from the rest (sharded MySQL and Teradata). The major difference is shared disk vs shared nothing architectures. Shared disk architectures like Oracle RAC suffer from scaling because at ...


4

Calculating running totals is notoriously slow, whether you do it with a cursor or with a triangular join. It is very tempting to denormalize, to store running totals in a column, especially if you select it frequently. However, as usual when you denormalize, you need to guarantee the integrity of your denormalized data. Fortunately, you can guarantee the ...


4

SQL Server doesn't scale out as such. It scales up. There are 3 areas to do this, subject to edition limitations CPU cores RAM Storage And of course, use a higher edition eg Enterprise SQL Server doesn't shard and any such solution (you can research MySQL sharding solutions) adds complexity and overhead to a system. Scaling up one server (+ standby ...


4

Your abstract requirements scream "PostgreSQL" to me. However, I think it's worth staying abreast of what the bourgeoisie are up to, so here's a list of various stuff you might want to check into. Free stuff CouchDB - one of the first NoSQL databases, powerful map/reduce querying system, highly distributed and fault tolerant. One of the better NoSQL ...


4

Barry Morris here from NuoDB. Answers below: 1) DBaaS (can be self hosted too) the can easily scale to terabytes of data (ie. big data) NuoDB is a downloadable product that runs anywhere (laptop, rack, public cloud). We have not announced DBAAS at this point. NuoDB uses Key/Value stores at the storage layer so supported DB sizes will be related ...


4

For point 1), you need to read the Storage Page Layout chapter of the documentation and in particular consider the HeapTupleHeaderData Layout table for the metadata at the row level. The 4-bytes per-row OID is obsolete for user tables. PostgreSQL no longer have them by default since 8.1. This is now controlled by the default_with_oids config parameter or ...


4

I'm not a fan. It's about as good an idea as creating a relational table named OrdersOrCustomers with columns defined for both. The storage-engine penalty is slightly lower in Cassandra because of the sparse-cell storage under the hood, but it's still bad practice. This bites you later when you want to map/reduce over your data; each task will have to ...


4

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


4

While 100,000 transactions per minute sounds like a lot, it's generally classed as a "moderate" transaction rate (100,000 tx/min / 60 sec/min = 1,666.7 tx/sec). There are systems out there running typical RDBMS systems (not in-memory) that can handle well over 10x that transaction rate (sustained) on a single box. Granted, these are well-tuned systems at ...


4

You can go through the following embedded json document to implement your message collection and can ensure index on "mail_id" key to avoid scanning all documents. For your information here collection serve as table and each document serve as row of a table. { mail_id:string sentMails:{ [ { sent_at: ...



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