Hot answers tagged scalability
60
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 ...
13
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 ...
12
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 ...
10
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 ...
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
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 ...
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 ...
6
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 ...
5
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, ...
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
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 ...
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
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 ...
4
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.
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
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
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
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 ...
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
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 ...
3
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 ...
3
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 ...
3
In terms of MyISAM, I have seen it work to the advantage of many since there does exists syntax to create a MyISAM table where the .MYD exists on one disk and the .MYI file exists on another. This can reduce disk latency accessing data and index pages for any given MyISAM table. However, you can achieve at least 90% of this effect by just making judicious ...
3
I'm not aware of a specific term for this practice but you can specify different data and/or index dirs using the create table syntax.
This only works for MyISAM tables and your OS must support symlinks.
In my practice though, we generally just segment sets databases into their own LV. These are generally a segment per functional application. Having a ...
3
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 ...
3
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 ...
3
"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 ...
2
Sounds like you are a .NET developer. One way would be to write a small application that uses multithreading and have just a set amount of concurrent hits to the database and the stored procedure. Run a trace while this is happening.
Write the application code to increment the amount of concurrent threads on a specific interval if you want to automatically ...
2
Like gbn says, SQL doesn't really scale out like other RDBMs's do. However, there is one aspect of scaling out that many people overlook and that is to always have a separate system for reporting purposes.
Never allow reports to run against production. Build yourself a reporting database on another server.
Ideally, your reporting system would only ...
2
The issue with storing your database in memory is if you have any sort of memoery issue or server has to be restarted or anything of that issue all your memory will get flushed.
That is the reason people don't store their database in memory.
Now, there are caching tools which are in-memory and can work as a very simple database like memcached. That may ...
Only top voted, non community-wiki answers of a minimum length are eligible
