Questions tagged [storage]
For questions about the persistent storage of database data.
31
questions
51
votes
2
answers
55k
views
Best way to populate a new column in a large table?
We have a 2.2 GB table in Postgres with 7,801,611 rows in it. We are adding a uuid/guid column to it and I am wondering what the best way to populate that column is (as we want to add a NOT NULL ...
- 735
14
votes
1
answer
6k
views
How do completely empty columns in a large table affect performance?
I have 400 million rows in a Postgres db, and the table has 18 columns:
id serial NOT NULL,
a integer,
b integer,
c integer,
d smallint,
e timestamp without time zone,
f smallint,
g timestamp without ...
- 243
31
votes
2
answers
16k
views
How to store one-byte integer in PostgreSQL?
In PostgreSQL documentation, it is said that integer data types can be stored in either two-, four- or eight-byte space. One of the columns of a table in my database contains a one-byte integer value ...
- 741
6
votes
2
answers
3k
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 ...
- 507
6
votes
2
answers
1k
views
Seperate volumes for tempdb, data, and backups on SQL Servers in VMs
When configuring new SQL Servers on VMs, it is a good idea to have the Database, TempDB, logs, and backups on separate logical drives, even if the underlying storage is the same? I know that this is a ...
- 625
22
votes
4
answers
27k
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 a single Dell SAN storage unit which is a RAID array
From ...
- 5,144
16
votes
5
answers
2k
views
Attempts to reclaim unused space causes the used space to increase significantly in SQL Server
I have a table in a production database that has a size of 525 GB, of which 383 GB is unused:
I'd like to reclaim some of this space, but, before messing with the production DB, I'm testing some ...
- 193
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
6
votes
2
answers
2k
views
How to reclaim the storage space used by a heap?
I have a table without a clustered index in SQL Server 2008R2 Standard with a lot of unused space, as shown in the following image:
How do I reclaim unused space from MyTable?
Said table is declared ...
- 71
1
vote
2
answers
4k
views
varchar(n) size?
When I use varchar(5) in a INSERT query it means that the attribute in the table will take exactly 5 bytes in memory? (Given that one printable character takes one byte)?
- 35
9
votes
2
answers
3k
views
How exactly does the one-byte "char" type work in PostgreSQL?
I often see people talking about "char". I've never used it. It's defined in the docs as,
The type "char" (note the quotes) is different from char(1) in that it only uses one byte of storage. It is ...
- 59.6k
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
6
votes
1
answer
3k
views
SQL Server on SAN, same LUN: one logical drive vs multiple
First, let me start by saying that I did notice there are multiple similar questions to this, but neither of them is exactly what I want to ask, and not one of them has a definite answer.
Second, let ...
- 81
1
vote
1
answer
133
views
Proposal: MySQL blob handling revision [closed]
Why does MySQL store blobs in the direct table rather than off to the side so that if it needs to read the corresponding stuff it will read the stuff off to the side? Essentially it would create its ...
- 111
13
votes
4
answers
11k
views
Drives vs. Mount Points?
The previous Senior DBA set up mount points for all of our drives across every SQL Server throughout the company. The new Senior DBA is horrified by mount points wants to change our standard (mainly, ...
- 811
8
votes
2
answers
15k
views
Should I use ReFS for a new SQL Server?
We are in the process of building a new production Windows Server 2012 R2 server to run SQL Server 2014 Enterprise Edition.
There is a bit of advice on the internet that suggests for Windows Server ...
- 3,202
7
votes
2
answers
4k
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 ...
- 59.6k
6
votes
1
answer
515
views
Are TOAST rows written for UPDATEs not changing the TOASTable column?
Imagine I have a table in Postgres 13 like this:
CREATE TABLE public.people (
id integer PRIMARY KEY,
full_name character varying(255),
bio text
);
I then insert a row with enough ...
- 281
5
votes
1
answer
4k
views
Storing phone number in E.164
TLDR; If I store a phone number in MySQL(or any DB with equivalent constraints), would the correct format be E.164 values into a VARCHAR(32) field?
Have been reading up on format/storage of ...
- 51
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
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
3
votes
1
answer
2k
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), ...
- 45
3
votes
1
answer
888
views
Use of integer instead of interval (of one type)
Our DB design presently has a interval column which will only be storing days (no other interval type) so it is making sense to use INT2 (smallint) instead of interval. Reference to documentation.
...
- 180
2
votes
1
answer
4k
views
I have delete database from mysql but storage is not freed
I have deleted multiple databases (schema) from mysql on amazon RDS but the storage is not freed, is there anything else I should do to free up the storage?
I have a mysql database that the total ...
- 143
2
votes
2
answers
101
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 ...
- 131
1
vote
2
answers
8k
views
What is the default size of text field in Postgres?
In the official documentation (http://www.postgresql.org/docs/9.1/static/datatype-character.html), it states text field is variable unlimited length.
I wonder what is the default size of this field ...
- 191
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
1
vote
1
answer
550
views
What does "volume" mean in MAX_IOPS_PER_VOLUME?
When setting MAX_IOPS_PER_VOLUME in a Resource Pool, what exactly does "volume" mean? Specifically, how many "volumes" would be the following cases:
Locally attached disk that's split into two ...
- 751
0
votes
2
answers
369
views
MySQL Backup Strategy
We are running the backup from the replication server and it is working good, since we are not facing any other issues, some information for better understanding our strategy:
Backup Type: Full ...
- 2,144
0
votes
0
answers
74
views
MySQL Storage Engine and Memory
I recently found out that, the databases does not have standard architecture(data model), some of them are,
No relationship between tables(entities).
No indexes defined.
Only integer and varchar data ...
- 2,144
0
votes
1
answer
43
views
Searching for scalable storage solution
In one project I save statuses of processing in MySQL table:
`id` `name` `email` `has_apples` `has_bananas`
1 Bob a@b.c 1 0
2 Mike c@d.e 0 1
3 ...
- 121