Questions tagged [storage]
For questions about the persistent storage of database data.
300
questions
5
votes
4
answers
3k
views
Is database partitioning for performance reasons meaningful in a virtualized environment?
When running SQL server on physical hardware, placing database data and log files on separate disks or RAID arrays can benefit performance, resilience, and maintenance. Partitioning the database over ...
- 401
2
votes
4
answers
2k
views
What is a Scalable Storage Mechanism for large TTL data collections
We currently have a legacy webservice that stores each xml request/response in Sql Server. The data only needs to persist for 3 days before it is considered expired. Sql Server is not good at deleting ...
- 151
1
vote
2
answers
230
views
Single disk for Data, System, User, Temp and Backup when installing Fail Over Cluster?
I have a Windows cluster with two nodes. I am trying to make Fail Over Cluster using SQL Server 2012.
On both nodes there is 2 TB storage available which I can access as:
C:\Storage_For_Cluster\...
- 441
3
votes
1
answer
290
views
Help with Cybernetics SAN Configuration
There are tons of articles out there recommending RAID/ LUN/ drive configurations for SQL Servers, but most leave me still questioning as they usually state "it depends" and for this reason ...
- 31
2
votes
1
answer
2k
views
Need a solution to store cheaply a big MongoDB collection
We have the typical problem of Twitter's timeline tweets, or Facebook profile's news:
we need to show the users only the new information (storing them in a fast db), and put all the old ones somewhere ...
- 121
0
votes
1
answer
419
views
Help with Postgres 9.1 data import (is 4x expected data size, 4x slower than MySQL, and index still needed)
I have 25 billion <int, int, float> rows that I'm trying to import into Postgres, and after 77% of the data being imported, the Postgres data folder is taking up 840GB, about 4x the storage ...
- 239
2
votes
1
answer
178
views
Meaning of SQLIO output "counts per second"
I realise that my question isn't strictly 'database' but I know that a lot of DBA's will use the SQLIO tool to check I/O performance on their systems so I'm hoping that somebody here will be able to ...
- 1,414
2
votes
0
answers
391
views
always on availability group with different speed disks
I'm looking to set up SQL Server 2012 installation with an Always On Availability Group, where the 'passive' replica will be hosted at another site on the WAN and using synchronous data commit - the ...
- 1,414
0
votes
1
answer
233
views
How would you track DB storage use and then extrapolate it?
I need to track the size of various DBs we have, then see it's growth rate so that we can guess where it will be at certain times. I'm using Oracle 11g first, but will eventually be applying this to ...
5
votes
1
answer
8k
views
How to determine Oracle LOB storage footprint?
With SECUREFILE storage I can specify whether I want compression (and the level of it) and deduplication, and it's all a trade-off between time and space.
Timing is fairly easy to profile but what's ...
- 153
1
vote
1
answer
2k
views
Any way to estimate the required space for a SQL dump file?
I want to import a Wikipedia XML dump file into MySQL. The uncompressed XML dump file is 65GB. Does this mean I should free up almost 64GB in the MySQL data path? Is there any way to estimate the ...
- 261
3
votes
1
answer
1k
views
How un-clustered is a CLUSTER USING table in PostgreSQL
I have some tables which benefit greatly from CLUSTER ON/CLUSTER USING in Postgres SQL:
# CLUSTER table USING index_name;
# ANALYZE VERBOSE table;
# CLUSTER VERBOSE;
A maintenance task periodically ...
- 135
8
votes
2
answers
492
views
Are database query optimizers aware of storage performance differences?
As I understand it, the query optimizer in SQL Server (or any other RDBMS, really) isn't aware of the performance of the storage underneath the database, and will make decisions as if all storage has ...
- 1,228
6
votes
2
answers
10k
views
do MYSQL views occupy physical space?
do MYSQL views occupy physical space? Or in other words, is a MYSQL database limited to a certain number of views?
- 163
-1
votes
1
answer
104
views
best database for a real estate system [closed]
Which is the best database suits for a real estate system.
Considering the storage space, security, crash recoveries, speed etc.
There will be around 1000 fields and trillions of rows!!!
- 1,466
7
votes
2
answers
4k
views
Multiple SQL Server data files on same SAN disk
I'm currently in the process of creating a new database, and have previously only ever used a single data file and a single log file. I've done some research online regarding the benefits of multiple ...
Shadowfoxx
8
votes
2
answers
8k
views
Stop SQL Server service(s) before defragmenting drive?
Our production SQL Server 2005 database's data files live on a separate physical drive, which Microsoft Windows 2003's Disk Defragmenter tool reports as 99% fragmented.
We scheduled a task to ...
- 281
4
votes
2
answers
4k
views
How to Correctly Estimate the Average Number of I/O Operations my RAID 10 disk can offer?
The average Input/Output Operations per Second a disk can sustain is a very important factor in the tuning of MySQL / InnoDB databases, as it drives the speed with which Dirty InnoDB Buffer Pool pages ...
- 175
9
votes
4
answers
11k
views
SQL Server - Separating data, log, and TempDB files on a SAN
I have a SQL Server connected to a SAN. Our new storage vendor recommends that all LUNs span the entire disk array, which is RAID5. I would normally request 3 separate LUNs (data, log, and TempDB) ...
- 1,973
4
votes
1
answer
303
views
Performance for fast filling table
I am currently creating a web application using the twitter streaming API and would need some advice concerning my database scheme.
I basically have a list of hashtags that I track in real-time. All ...
- 141
8
votes
2
answers
11k
views
Splitting TempDB into multiples files equal to number of CPUs
The article SQL Server tempdb Best Practices Increase Performance suggests that I should split tempdb into a number of files equal to the number of cores. So for 4 cores you get 4 files.
By having ...
- 897
3
votes
4
answers
7k
views
How to prevent SQL Server database server from running out of disk space?
What are the best preventive approaches that we can do to prevent the SQL Server database server from running out of disk space ?
Is there a way to set the maximum database file size (both the ...
- 379
3
votes
1
answer
8k
views
How to determine the read / write ratio of your server?
When looking at the read / write ratio of your server would you look at the NUMBER of reads and writes or the number of BYTES read and written? For example, consider the following numbers from sys....
- 1,236
2
votes
2
answers
110
views
Can I see the RAID group name for my SQL Server Database files?
Is it possible to see the RAID group name that my database (and log) files are sitting on, in one of the DMVs (or some other system table) ?
- 137
0
votes
1
answer
2k
views
Too many records on database and how to handle it? MySQL is it good to clone?
I am doing a social network and came up with the following concern regarding database storage. Let's put facebook as an example, imagine 2,000 photos per user, just 1,000 users, it would equal to 2,...
lbennet
4
votes
1
answer
854
views
Query to check where database growth was?
I have a database that grew very significantly overnight and I'd like to find out why. I've restored yesterdays backup to another location. Is there a query I can run to query the size of tables or ...
- 263
1
vote
1
answer
736
views
Setting up DRBD on an active MySQL server
When it comes to setting up DRBD and MySQL, is the following possible?
Set up DRBD on an active MySQL server
Set up DRBD with no downtime allowed
- 1,497
4
votes
2
answers
1k
views
Enumerate drives available to SQL Server
I'm trying to work out a way of listing the drives that a particular instance of SQL Server is "aware" of, and understand the process.
I can run EXEC master..xp_fixeddrives which gives me a list of ...
- 629
9
votes
1
answer
4k
views
Optimal placement of tempdb, mdf and ldf files in SQL Server 2012 on SSD's?
I realize this is probably a very open ended question and the answers could vary, but what is the optimal placement for the tempdb, mdf and ldf files in SQL Server 2012 when talking SSD's?
Pre-new-...
- 313
2
votes
1
answer
693
views
Creating the database on different file groups
What is the pros and cons of creating the database for a operational system on different file groups? I know it is useful if you want to keep the static part of a database online when the other part (...
- 3,674
2
votes
2
answers
408
views
Removing physical fragmentation
If I have inherited db that has grown to 400GB 1 MB chunk at at time. Is an offline defrag the only way to remove all the physical fragmentation. Another issue I have is that the data and log files ...
- 2,581
3
votes
2
answers
358
views
SQL database backups methods-tools-procedures
Currently, I am investigating some way to more efficiently backup 4 Production SQL Databases (3 different servers/instances) and would like to know if anyone else has had the problems we are having ...
- 33
2
votes
2
answers
2k
views
Oracle extents - How can I have so few with this large size?
Note on duplication:
This is not a duplicate of "Oracle extents - How can I have so many with this small size?" This is a separate question I entered which happens to be in the same domain.
This is ...
- 255
5
votes
2
answers
3k
views
Oracle extents - How can I have so many with this small size?
This is a different question than the one with the almost-identical title.
Again, this is more curiosity about how Oracle Database works than anything I need to change (yes I know multiple extents ...
- 255
3
votes
1
answer
210
views
What are some key configuration settings for using an InnoDb table for session storage?
I want to start clustering my PHP app servers but don't want to go for a sticky-sessions load balancing setup at this point. However, I want sessions that are persisted to disk and implement proper ...
- 373
12
votes
1
answer
22k
views
How to get actually allocated tablespace sizes?
A Postgres tablespace is cluster-wide.
Therefore, how do I calculate how much space is allocated by each database in Postgres tablespaces?
stevenatadv
2
votes
1
answer
59
views
What will happen when a forum uses 24 char length as an URL ID?
I found a forum using self made/custom engine still in beta version, previously using vBulletin.
Now, their new engine uses very long URL id, 24 char length.
http://example.com/thread/...
- 21
0
votes
2
answers
613
views
shall I use multiple separete tables or one table with multiple partition on oracle
I have an entity lets call it Response, and another entity called ResponseType, Response entity contains the ResponseType; so when I am building the physical model I confused with how to implement it; ...
- 163
13
votes
1
answer
725
views
Disk file effects of delete and vacuum
I have a very frequently updated table with 240 million rows (and growing). Every three hours 1.5 million rows are inserted and 1.5 million are deleted. When I moved the cluster to a SSD this bulk ...
- 1,075
12
votes
1
answer
475
views
Highly Concurrent Storage System
Imagine your requirement is that you have 3 huge tables (structured data) with say 30 billion rows in each (total size of 4TB) and your many concurrent users (which are parallel os threads on remote ...
- 223
9
votes
1
answer
1k
views
Adding SPARSE made table much bigger
I have a generic log table, about 5m rows.
There's a "strongly typed" field that stores event type, and a bunch of "losely typed" columns that contain data relevant to the event. That is, meaning of ...
- 1,342
5
votes
2
answers
4k
views
Storing Passport Numbers in a database
We're developing a travel/tourist application for a tours company who want to be able to input traveler information into an online database.
Is there any security best practice for storing passport ...
- 59
4
votes
1
answer
1k
views
How to best store the Google Web Ngram data?
This is a continuation of How to best store Google ngrams in a database?, which covers how to store the Google Ngram Book data.
I'm looking to store the Google NGram Web data, which is slightly ...
- 239
1
vote
1
answer
198
views
String Pooling for MySQL
So I'm having this table that logs all errors happened in our production code. It looks like this:
id | date | type | message | file | line
----+------+--------------+----------...
- 113
3
votes
1
answer
179
views
Extreme storage beasts
Intro
I have derived this question from my previous question Single Drive vs Multiple Drives
After getting a very detailed answer from a very helpful member RolandoMySQLDBA and learning the fact that ...
- 1,271
6
votes
2
answers
191
views
Database placement on abstract storage structure
Often times we all hear the same thing. The recommendations on where to put OLAP/OLTP databases, where to put the tempdb, where to put transaction logs...etc.
But say you are in an environment where ...
- 41.7k
3
votes
2
answers
2k
views
Effective backup and archive strategy for database and linked files
I am using Postgres to store a variety of application data for a webapp. Part of the application involves storing and retrieving user uploaded files. I am storing the files in the filesystem with ...
- 33
2
votes
1
answer
670
views
SAN Storage & Server for Oracle Replication
I need a 2-3 TB storage server group for Oracle Database. We want SAN Storage & Server for Oracle Replication, but not Microsoft Storage Server.
For example : HP DL380 G6 as a server and EMC ...
- 1,078
5
votes
1
answer
382
views
MySQL and Solid State Disks on EMC
Since all writes and sequential reads are cached by the EMC storage system, the best use for solid state disk is to store data that has a random READ pattern. On Oracle, that is the temp tablespace ...
- 483
6
votes
2
answers
654
views
SQL Filegroup degrading performance
On our dev boxes, our database resides entirely in the PRIMARY filegroup, and everything works fine.
On one of our production servers, recently upgraded from 2005 to 2008, we noticed it was ...
- 161