31

There's really no one 'best way' to store time series data, and it honestly depends on a number of factors. However, I'm going to focus on two factors primarily, with them being: (1) How serious is this project that it deserves your effort to optimize the schema? (2) What are your query access patterns really going to be like? With those questions in ...


16

You have pretty much 4 choices: NoSQL - definition Every record is stored as a set of Key/Value pairs. It is very flexible and fast. Not all of the report writers out there support this style of storage. There are many example database implementations of NoSQL. The one that seems to be most popular right now, is MongoDB. EAV - definition This is where you ...


13

It depends on what's at the other end of the mount point. If the mounts are all LUNS spread across the same physical drives, then no gains. If the LUNS are all over a shared, slow, iSCSI channel, maybe no gains. If the LUNS are all under 1 controller...you see how many variables are at play. There's no one answer. To determine the configuration of the mount ...


11

Why are you not getting minimal logging? I have found the Data Loading Performance Guide, which you reference, to be an extremely valuable resource. However, it is also not 100% comprehensive, and I suspect that the grid is complex enough already that the author did not add a column Table Partitioning to break out differences in behavior depending on ...


10

Generally you would not install Pgpool on the backend servers. What you see in your picture is the most common configuration. Pgpool is a standalone server which essentially sits in front of the databases. The two Postgres servers are often configured with streaming replication; with one being the master and the other the slave. This allows Pgpool to load ...


9

I would say the term Database Administrator covers the full gamut of database duties: design, development, production support, performance tuning, report writing, OLAP, etc. A Database Architect is involved in the design and development of large or complicated database solutions. However, the Architect is usually not involved in the day-to-day operations of ...


9

FILETABLE data is built upon the FILESTREAM feature of SQL Server, and uses the Windows Server filesystem to store VARBINARY(MAX) data in discrete NTFS files on the filesystem of the server itself. This data is not stored in the database, therefore the database file size will not include these 10GB+ files. Database backups will, by default, include the ...


9

You touch several subjects, therefore it is important to establish their scopes and the links that exist among them. Firstly, if the specific business domain you are working with implies (a) multiple one-to-many associations along with just one associative entity type —or many-to-many [M:N] association—, and (b) you are representing said characteristics with ...


8

Ah, what is the problem? a table with 54 million records A small table. Nice. I have one here with 8.5 billion rows. we have !300 inserts and !100 updates per minute Yeah. Small. I know. I have one here with around 500 million inserts per day. That is 347222.2222222222 per minute. All that running on stock hardware. Seriously. THough not totally as ...


8

Based on the results of numerous internet searches I can't find any (post-SQL Server 2000) reason to not use mount points. The main reason is someone had a bad experience with them (or, conversely, no experience with them) and has completely ridden them off... forever. This is otherwise known as personal preference. Now, there are some reasons that you ...


8

In addition to CM_Dayton's answer and Sean Gallardy's answer, one issue not yet identified with Mount Points is related to security. To quote Guidelines for Setting SQL Permissions on Mount Point Folders: Although the mount-point root folders may look like regular folders and are accessed in the same way folders are accessed, they are not regular folders....


7

At this point, I guess SQL Server with an AlwaysOn cluster will satisfy HA, but what about read/write? I think this SQL Server 2012 whitepaper will be a good read for you at this point to get a feel for what's possible with the two different features (Availability Groups and Fail-over Clustered Instances) that come under the AlwaysOn brand name. Deploy ...


6

I agree with mrdenny to have 1 database per Application. There are many good reasons to do that : Most importantly Log : SQL Server uses transaction log to be able to allow a point-in-time recovery in case of any disaster, provided that log backups are regularly taken when the database is online. Full Backups : Easy to manage per application and can be ...


6

These two terms have been used interchangeably by many. I look at them distinctly. DATABASE ADMINISTRATOR (DBA) The DBA does just what the name suggests...administrator of the database. Based on the specific RDBMS being managed, the role DBA involves implementing the following aspects: user authentication backup strategies DB Metrics (Recording and Display) ...


6

I thought that Jeffrey Yao did a fantastic job answering this question here. I've edited it a little bit and sometimes include my own mission statement on my resume, but here's my "slightly" modified version of Jeffrey Yao. This is not specific to mariadb, but I don't think it has to be. "Mission: I am committed to providing a robust and secure database ...


5

The question I would ask is whether the direct relationship between Anthology and Composer is "important" to the system? There are all kinds of incidental relationships between tangible things that are recorded in any system. However, only certain of these are important for the purposes of the system itself. These are the ones that belong in a relational ...


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


5

As Aaron Bertrand mentioned in his comment, AlwaysOn is only designed to support offloading of read-only operations, in addition to its (really neat) redundancy capabilities. If all you're looking for is a place where your developers can check out the production data without putting load on the production server, then perhaps it's a technology that might be ...


5

With respect to your question about using Vertica during the ETL, it's very rarely necessary (although not unknown) to use a different type of database for the ETL. I would not do that unless you perceive a specific need to do so. The only times I've ever heard of this being done due to interactions with legacy data sources. Although @Kermit works with ...


5

So let's look at the "roles" here represented in the referenced architecture. Keep in mind, this is the "2 cent tour" of what the architecture is all about. Mongos This process is the "router" in that it acts as the interface for a "client" to connect to a sharded cluster. You can have many mongos instances, so you are not limited to "one" only. As pointed ...


5

in what circumstances would it be justified to use a primary key that is a combination of the type of the record(let's say, CH for Chinese food, In for Indian and MX for Mexican) and a sequence number ? Under no circumstances would it be justified. A column containing two different data elements as one value is a violation of first normal form. 1NF ...


5

Here is a structure that is at least very close. There is no programmatic way to get the parameters (unfortunately). You need to format them into XML to pass in. The Login that initiates a SQL Agent job seems to only be logged in the message column of msdb.dbo.sysjobhistory, for step_id = 0. This value can be extracted, but not during the execution of the ...


5

I would use a server level trigger on CREATE DATABASE. Something like this: CREATE TRIGGER [CreateDatabase_OwnerMapper] ON ALL SERVER WITH EXECUTE AS 'sa' FOR CREATE_DATABASE AS BEGIN SET NOCOUNT ON; DECLARE @DatabaseName NVARCHAR(128) = (SELECT CAST(eventdata().query('/EVENT_INSTANCE/DatabaseName[1]/text()') as NVARCHAR(...


4

For maximum performance, caching (i.e., precomputation) is pretty much a requirement. Unfortunately, though, there's no cookie-cutter answer for how to implement it. It's not as simple as picking either solution x or solution y. What you want to do is come up with a strategy that caches the information you need as close to the request for the information as ...


4

Your caching layer sits between Model and View Controller. You should not hit database for needless requests. These requests include in my opinion. Almost all lookup tables. You read city and state list already. Why go to database again. Facts used in almost every page. if you show User's detail every page. Hit to Database once and cache it. Slow queries ...


4

The raw data for that table is 1.4GB (54m * 28 bytes), the indexes will add some data but nowhere near a huge amount, let's say 5GB for the index. So whatever you do: why only 4GB ram? Make it 32GB for room for growth. MySQL on an SSD will be nicely fast. NoSQL is a buzzword that means nothing, so unless you have a specific product in mind, I will ignore ...


4

I'm going to assume that you already have the budget to implement some data warehouse solution. Just briefly talking about Vertica; it is a load and read optimized platform, and certainly not designed for OLTP. The piece on staging and processing data would need some more thought. Vertica isn't really designed to have data staged, cleansed, and moved into ...


4

Having done this type of project before, here's some of the practical gotchas to look out for: "2. This master then does one-way slave propagation to up to 200 read-only servers via VPN." Think carefully about the network traffic required here. In the simplest terms, if the insert/update/delete load on the database takes up 1/200th of a network cable's ...


4

There is no particular policy to faster insertion (loading), which can direct you to your target. Instead, there are some configuration and prerequisites which can help you to improve write (insert) performance. The performance of any application, installed on OS lies on the hardware of the server. So, first, you should identify how much IOps disk you ...


4

The script that SSMS generates is not the best one. You should be able to use simple ALTER TABLE: ALTER TABLE TableName ALTER COLUMN ColumnName VARCHAR(255) NOT NULL Obviously, make sure in advance that the text that you store in this column would fit into 255 characters limit. I'd strongly recommend to try it in test environment first to get an idea how ...


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