Questions tagged [storage]
For questions about the persistent storage of database data.
232
questions
-1
votes
0answers
29 views
Replace column by LEFT OUTER join to save space
I have a table t1 with 100M row. For column A, 95% of the rows have value NULL0, the other records have values from 1 to 9.
To shrink my table, I'm thinking about moving column A to table t2. t2 ...
0
votes
0answers
51 views
Windows Server 2019 allows Allocation Unit Size up to 2mb … how does this effect SQL Server 2017? [on hold]
Starting with Windows Server 2019, disks may now be formatted with an allocation unit size up to 2mb; in prior versions the maximum was 64kb. All of the documention and BKMs for SQL Server 2017 ...
0
votes
0answers
8 views
How to switch MBR to GPT without losing any existed data originally in MBR, AWS Ubuntu EC2
I'm trying to extend my diskspace for my AWS EC2 Ubuntu instance, the partition I've started with is MBR and the maximum it can go is 2TB.
Currently the diskspace is full with important data, I need ...
13
votes
3answers
1k views
SQL Server has encountered occurences of I/O requests taking longer than 15 seconds
On Production SQL Server, we have following config:
3 Dell PowerEdge R630 servers, combined into Availability Group
All 3 are connected to single Dell SAN storage unit which is a RAID array
From ...
0
votes
1answer
28 views
Backup Compression SQL Server vs PURE
We are having a SQL 2016 Standard Edition Server holding 4TB of data. And the compressed backup using maintenance plans is taking around 1.5 hrs. Our storage team claims that as PURE FlashArray is ...
0
votes
1answer
42 views
Is it better to install a very large Postgres database (billions of rows) in a real or a virtual machine? [closed]
From my common sense, my physical machine hosting a very large Postgres database (for big data) should be real rather than virtual or container.
Some people say it is better to install on a virtual ...
2
votes
1answer
93 views
Understanding the differences in the storage space used by two tables
I have two tables in which, more or less, one contains a subset of data of the other one. There is some data I delete from the first table, which is inserted in the second one as historical data.
...
1
vote
0answers
34 views
Postgres configuration for high-latency storage
I'm trying to setup a postgres server using Ceph as storage. After taking benchmarks, it was obvious that even though the storage has a really good throughput, the higher latency brought by network ...
1
vote
1answer
74 views
Which Oracle datafiles grow in size?
I have downloaded and started up Oracle's pre-built OTN Developer Day VM. The VM is running on an SSD. I want to move datafiles that can grow to a separate mount point (setup as a vmdk on a HDD. more ...
1
vote
1answer
57 views
How to point tablespace in Oracle's pre-built Developer Day VM to another mount point
I have downloaded and started up Oracle's pre-built OTN Developer Day VM. The VM is running on an SSD. I want to load some data for development purposes. I am planning to create a new user and load ...
5
votes
2answers
284 views
Slow checkpoint and 15 second I/O warnings on flash storage
Last couple of weeks we've been working on getting to the root cause of what could likely be the cause of the occurrence of these I/O issues and slowdown of the checkpoints.
At first glance it looks ...
0
votes
1answer
89 views
SQL Server Logical IO Block Sizes and Physical Reads
I am trying to understand cache churn caused by SQL statements that are doing some table scans.
Assuming LRU caches, if something is doing 400K logical IOs, how much memory is streamed across (IO ...
0
votes
1answer
139 views
Should TempDB SSD Drive be formatted with 64 Kb Allocation Unit Size?
We have installed dedicated SSD drives for TempDB
First question - should these drives be formatted with 64 Kb allocation size ? Currently it shows 4 Kb (Bytes per Cluster = 4096)
Does TempDB ...
0
votes
2answers
47 views
How does MySQL store data, can I detach a mysql Database?
I have used SQL Server in the past which each database is kept in separate files (a data file and a log file) but things are different in MySQL as it seems. The file sizes of these files translates to ...
2
votes
1answer
134 views
Calculating total “on row” bytes for each row … the easy way
We want to calculate the total "on row" storage bytes for each row in the table. As we understand it, we must add up the DATALENGTH() of each column while also accounting for NULLs and things like ...
3
votes
1answer
148 views
RDS MySQL read replica larger than master
We have a Multi AZ AWS MySQL 5.6.40 server with a matching single AZ read replica. Both have 200GB of storage available. However, the read replica is rapidly running out of free storage space, while ...
0
votes
0answers
43 views
fix oracle 11.2 asm performances (bad disks repartition)
i've a single Oracle 11.2 node with really bad performances and i figure out that there's a lun always running at 100% IO.
This is the "DATA_0000" lun configured in asm in this way
SQL> SELECT name, ...
0
votes
2answers
67 views
Does an OFFLINE Database use space? [closed]
I took my SQL Server Database OFFLINE. Is it still occupying the same space as when it was ONLINE?
Should I keep it OFFLINE or is it better to BACKUP the database and DROP it?
0
votes
0answers
35 views
How Blockchain Databases Work
I feel like I read somewhere back about blockchain databases but I can't seem to find anything too solid on the topic. This paper called Blockchain Databases doesn't offer much in terms of description....
1
vote
0answers
82 views
“Slow Storage Writes on Drive T” warning from sp_Blitz
We migrated our platform to new SQL Server 2016 instances a couple of nights ago. Immediately following the migration, we ran sp_Blitz again, but noticed the following entry, which relates to the ...
0
votes
2answers
72 views
Is there a disk space impact when choosing NULL vs empty array {} for the default of an ARRAY[] column? [duplicate]
My intuition is NULL will take up less space than {} when used as the default value of an ARRAY[] column in PostgreSQL.
Am I correct?
0
votes
0answers
48 views
IO Test for SQL Server
I was trying to use DiskSpd to check IO subsystem performance but one thing I was confused about is regarding the -T switch, which (according to the documentation) is the number of threads per file. ...
0
votes
3answers
38 views
Can I move / Modify the Object in Object Storage Service (OSS)
I am using Alibaba Cloud - ECS and have Storage Bucket - Object Storage Service (OSS).
File Storage structure is like
oss:/bucket/abc/xyz/123.jpg/
My Question is
Can I move or modify the objects ...
1
vote
0answers
34 views
How should I organize my physical storage on 5 ssd single server for 2.2 TB database
I need to migrate a 2.2 TB database to a better organized single server with 12 physical cores. New server has 5 SATA SSDs 1 TB each, will upgrade to 6 SSDs 1 TB each.
My current design is (this is ...
9
votes
2answers
205 views
Increased waits during checkpoint after upgrading to better storage
When we migrated from an older all flash array, to a newer all flash array (different, but well established vendor), we started seeing increased waits in SQL Sentry during checkpoints.
Version: ...
-3
votes
1answer
51 views
Can we shrink a 1.6 TB SQL Server DB in Simple mode with only 12 GB Storage left?
We saw your article about this topic and wondered if you can answer this question:
Can we shrink a 1.6 TB SQL Server DB in Simple mode with only 12 GB Storage left?
Here is your article that we saw:
...
3
votes
0answers
235 views
How Graph Databases Store Data on Disk
I have seen these papers:
TurboGraph
GraphChi
BiShard Parallel Processor
G-Store
NXgraph
GridGraph
...and a few others. I am new to databases (other than having used them for web apps without ...
2
votes
1answer
41 views
How to Store Trees on Disk
I am wondering how to store trees on a physical disk, for perhaps a tree-oriented database like an XML database.
Wikipedia states about XML databases:
...custom optimized data structures are used ...
1
vote
1answer
52 views
How Database Formats Files for Disk
This SO answer outlines how MySQL organizes its database into files, but doesn't describe the structure of the files themselves.
I am wondering, at the physical level, how relational database ...
7
votes
1answer
3k views
What are the current best practices concerning varchar sizing in SQL Server?
I'm trying to understand the best way to decide how big varchar columns should be, both from storage and performance perspectives.
Performance
From my research, it seems that varchar(max) should ...
4
votes
2answers
1k views
In what cases are BLOB and TEXT stored in-line on InnoDB?
I've googled this and can't find it, you can see Rick James mention it here.
... BLOB and TEXT are not always stored separately
The answer on What is the difference between MySQL VARCHAR and TEXT ...
0
votes
1answer
34 views
Shrinking the database partition if autogrow is turned off?
I want to know:
Are there chances of shrinking the database partition if autogrow is turned off?
Also, are there chances of data fragmentation and increase in seek times if we fully extend size ...
-3
votes
2answers
65 views
What are some real world applications that generate large numbers of records in a database every day and what are those metrics? [closed]
I'm trying to get some perspective in today's day and age what is considered a large amount of data or large number of records generated in a day and what real world applications are doing that. I ...
1
vote
2answers
42 views
How does the optimizer in Oracle Database use I/O statistics?
I would like to help Oracle's query optimizer make better-informed decisions. I have been unable to find much information on how the optimizer determines the cost of I/O operations, other than ...
16
votes
1answer
435 views
CREATE DATABASE on RAW partitions no longer works?
I'm attempting to create a database using two raw, i.e. unformatted, partitions.
Microsoft Docs states you can do this, you simply need to specify only the drive letter of the raw partition, as in:
...
3
votes
1answer
149 views
Tempdb size strategy
I've recently started my role as a DBA and my initial task has been to take ownership and redeploy a database provided by a 3rd party to help us conform to GDPR legislation.
As a consequence, I've ...
5
votes
4answers
1k views
Best way to store physical stock data in a SQL db
I work for a retail company and we are in the process of building a new data warehouse. One element we need to store is physical stock data as to how many units of any sku are available at a certain ...
1
vote
1answer
34 views
How can I find the stored binary value of a DOUBLE value in MySQL?
Given the following table, backed by MySQL 5.7/InnoDB:
CREATE TABLE mytable(mydouble DOUBLE) SELECT 0.005 `mydouble`;
how can I find what exactly InnoDB is storing on the disk for the value 0.005?
...
0
votes
1answer
42 views
Is it possible to allocate data free for certain table?
After deleted a large dataset (~50GB) and optimized tables, some tables have data free (~50GB) and parts of tables have only data free (~KB). As my understanding, the data free are reused and not ...
1
vote
1answer
232 views
Post Migration error in sql server error log
I am seeing below error messages when we have moved/migrated databases to new server/instances from SQL2K12 stanadlone to cluster
last target outstanding: 20800, avgWriteLatency 30
average throughput:...
0
votes
1answer
58 views
Storage provisioning for Microsoft SQL Server
I need help with my Microsft SQL Server file setup!
I have a TEST Microsoft SQL Server with 3 RAID1 (SSDs):
C: (240 GB) - O/S installed
L: (460 GB)
M: (900 GB)
I have a PROD Microssoft SQL Server ...
0
votes
0answers
28 views
Storing a lot of text / string data strategies - analysis and solution
I have a LOB application backed by SQL Server 2016.
Over the past few weeks, theres been issues whereby the SQL Server start throwing timeout errors - which has led to a bit more digging into the ...
2
votes
1answer
74 views
Durability of storage in second generation google cloud sql
How durable should I expect data for a Google Cloud SQL instance to be? Just a single instance, with the high-availability option disabled.
AWS explicitly mention that Amazon Aurora data is stored in ...
1
vote
1answer
105 views
Optimizing column types for reduced overall database size
Given the following table:
CREATE TABLE avl_historico (
fecha timestamp with time zone NOT NULL,
latitud double precision DEFAULT 0 NOT NULL,
longitud double precision DEFAULT 0 NOT NULL,
...
1
vote
0answers
503 views
why SSD is slower than HDD SAS 15K on DBMS PostgreSQL?
Someone help me analyze the two execution plans below (Explain ANALYZE used), is the query 9 of TPC-H benchmark.
I'm using two servers HP Intel Xeon 2.8GHz/4-core - Memory 8GB. O.S. Debian8, using ...
1
vote
0answers
36 views
EMC Recoverpoint works for database?
As a solution for replication, to protect VM, LUNs and recovery including database, EMC Recoverpoint was presented by our vendor.
But in EMC paper's, the only one database related example ilustrates ...
2
votes
3answers
393 views
How to create a normalized database to manage inventory for a small business? [closed]
I am writing a Java application to manage the inventory of a small furniture business. I know little about databases, I have a bit of experience with SQL queries and more experience with Java ...
1
vote
3answers
251 views
SQL Server 2016 SP 1 on HP 3PAR environment causes weird head blocker
My client is on HP 3PAR hardware. Three servers are on different geographical locations, connected with a 40 gbit fibre connection. The SAN is SSD only, the machines have lots of cores.
The ETL ...
3
votes
1answer
472 views
Why is my database 12 times bigger than inserted data?
I have a short question concerning my database size. I need to insert data in a database. Before the insert, some calculations need to be done.
The point is: from 50 mb plain data (~700,000 lines), ...
1
vote
0answers
49 views
What does column family-oriented datastore mean in practice
I was reading this article about HBase. Within it, the storage format of HBase is described:
HBase is referred to as a column family-oriented data store. It’s also row-oriented: each row is indexed ...
