52
It very much depends on details of your setup and requirements.
Note that since Postgres 11, only adding a column with a volatile DEFAULT still triggers a table rewrite. Unfortunately, this is your case.
If you have sufficient free space on disk - at least 110 % of pg_size_pretty((pg_total_relation_size(tbl)) - and can afford a share lock for some time and ...
answered Oct 30 '13 at 19:44
Erwin Brandstetter
126k16 gold badges290 silver badges430 bronze badges
30
This is far less often a disk issue, and far more often a networking issue. You know, the N in SAN?
If you go to your SAN team and start talking about the disks being slow, they're gonna show you a fancy graph with 0 millisecond latency on it and then point a stapler at you.
Instead, ask them about the network path to the SAN. Get speeds, if it's ...
24
No, there is no 1-byte integer in the standard distribution of Postgres. All built-in numeric types of standard Postgres occupy 2 or more bytes.
Extension pguint
But yes, there is the extension pguint, maintained by Peter Eisentraut, one of the Postgres core developers. It's not part of the standard distribution:
In addition to various unsigned integer types,...
19
Regardless of specific datatype, you need to be able to store whatever the application requests to be stored. You cannot specify something smaller than the max size of what will actually be saved.
You also do not need, nor want, to specify a column length larger than the maximum actual size that will be stored for a variety of reasons: query memory ...
18
I don't have a "best" answer, but I have a "least bad" answer that might let you get things done reasonably fast.
My table had 2MM rows and the update performance was chugging when I tried to add a secondary timestamp column that defaulted to the first.
ALTER TABLE mytable ADD new_timestamp TIMESTAMP ;
UPDATE mytable SET new_timestamp = old_timestamp ;
...
17
We have a similar setup and recently encountered these messages in the logs. We are using a DELL Compellent SAN. Here are some things to check when receiving these messages that helped us find a solution
Review your windows performance counters for your disks that the warning messages are pointing to, specifically:
Disk avg. read time
Disk avg. write time
...
16
If your table doesn't have a clustered index, then deletes don't deallocate empty pages by default.
Your options are:
ALTER TABLE dbo.MyTable REBUILD - which will take your table offline in Standard Edition, building a new copy of it with everything packed in nicely like sardines
Do your deletes with the TABLOCK hint - which can prove problematic for ...
14
Since the columns e, k, and n can be NULL, I assume "100% empty" means NULL.
NULL storage is cheap. Each NULL "costs" one bit in the null bitmap for storage and otherwise hardly effects performance. Effective storage requirement depends on whether a null bitmap for each row already exists and has room for 3 more bits.
Tables with up to 8 ...
answered Feb 12 '15 at 21:56
Erwin Brandstetter
126k16 gold badges290 silver badges430 bronze badges
14
Each row has a minimum of 7 bytes of overhead when stored in FixedVar format (the default). There will also be a (typically relatively small) number of pages used for the upper levels of the clustered index. Optimally stored, and disregarding the upper index levels, 2 million rows would require just over:
(7 + 8 bytes) * 2,000,000 = 28.61MB.
More ...
13
A ratio of 1/4 to 1/2 times the number of TempDB data files to machine cores has long been the recommendation...
But there's now even better guidance. At PASS in 2011, my good friend Bob Ward, who’s the top guy in SQL
Product Support, espoused a new formula: if you have less than 8
cores, use #files = #cores. If you have more than 8 cores, use 8 files
...
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 ...
12
Absolutely possible, because someone with the resources to store
6021188640340442162025691220451771208370039202613309330051794368412 Terabyte
definitely has enough money to get a custom made database system for his purposes as well.
I might be available as a contractor, for only 0.01€ per Terabyte.
12
Size can differ due to several reasons:
indexes take some disk space,
there can be several copies of the same record on disk,
slack space in pages.
Indexes take up disk space in order to facilitate faster lookup. The more indexes you have, the more disk space your DB will take up. GIN indexes are usually smaller, but aren't useful if you use range queries. ...
12
Yes, there can be downsides. If another query looks at a different data segment not determined by the date, it might take a performance hit if rows are spread out over more data pages now. Just the same way as your first query profits. That completely depends on information not in your question.
other queries using a PK of table (let say id_foo)
That ...
11
To answer your question literally, yes, in MySQL, views do exist as occupied space on the disk. But of course they do: if the didn't, where would they exist? If you rebooted your server, how would the views persist?
I imagine what you really meant was "do MySQL views occupy physical space in proportion to the number of rows they contain?", in which case the ...
11
My field avgObjSize was around 6442 bytes. I chose a random document in my collection, I typed :
Object.bsonsize(db.collectionName.find({"_id" : ObjectId("5508497c51a990da07b07106")}))
And I obtained 810 bytes.
Why do I have this huge difference ?
The db.collection.find() method returns a cursor, so what you have calculated is the ...
11
1. chr(10)
... produces the LINEFEED character (a.k.a. escape sequence \n) and psql displays the character with a newline (indicated by +). Everything correct there.
2. & 3. ascii() produces 128 or 192?
It starts with a mistake I made. I carelessly assumed "char" would cover the range of an unsigned 1-byte integer (0 to 255) in the referenced answer (...
11
is there any performance benefits to keep the data files split across different logical drives?
If all the volumes map to the same set of physical disks on the SAN there's normally no difference.
However, if each Volume maps to a different SAN LUN, it's possible for the SAN to allocate storage resources differently to the volumes. For instance, they can be ...
answered Nov 24 '20 at 21:47
David Browne - Microsoft
29.3k2 gold badges32 silver badges66 bronze badges
10
Like most general guidelines, it is a an oversimplification in its most positive light. At best, it is a good starting point (provided you don't aren't keeping the 1:1 core:data file ratio with a large amount of cores).
There is no replacement for proper design and proper follow-up monitoring and baselining. The reason behind having multiple data files ...
10
I'd run DBCC UPDATEUSAGE against the table as a first step, since the symptoms show inconsistent space usage.
DBCC UPDATEUSAGE corrects the rows, used pages, reserved pages, leaf pages and data page counts for each partition in a table or index. If there are no inaccuracies in the system tables, DBCC UPDATEUSAGE returns no data. If inaccuracies are found ...
10
Create a clustered index so that your table is not a heap. When a row is deleted from a heap, the space will not be reused. Even empty pages don’t get cleared up. This problem won’t happen if your table is a clustered index.
9
Why storing the data on a SAN? What's the point? All database performance is tied to Disk I/O and you are using 3 servers with only one device for the I/O behind them. That makes no sense... and unfortunately so common.
A SQL server software is a very advanced piece of software, it is designed to take advantage of any bits of hardware, CPU cores, CPU cache, ...
8
Sql server's query optimizer does not take variations in disk performance into consideration when compiling a query plan. Paul White provides a great overview of Sql Server's cost based optimizer here:
https://sqlkiwi.blogspot.com/2010/09/inside-the-optimizer-plan-costing.html
Some key points are:
The optimizer isn't trying to calculate the exact cost ...
8
You have to use compression (columnstores should do it). I suggest RLE (Run Length Encoding), you can store (5HpHagT65TZzG1PH3CSu63k8DbpvD8s5ip4nEB3kEsreAbuatmU ,1MsHWS1BnwMc3tLE8G35UXsS58fKipzB7a ,1Q1pE5vPGEEMqRcVRMbtBK842Y6Pzo6nK9) as the value and 115792089237316195423570985008687907853269984665640564039457584007913129639936 as the run length. Document ...
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 ...
answered Aug 4 '17 at 19:19
Sean Gallardy - Retired User
20.7k1 gold badge30 silver badges57 bronze badges
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....
8
Keep in mind that there are 4 "row formats". A main difference between then has to do with how wide columns are handled.
The reference points to an Answer written early in 2010, a few months before DYNAMIC and COMPRESSED were introduced in the "InnoDB plugin".
So, I claim that that other Q&A is out of date! That is the 754 Upvotes ...
7
SQL Server 2008 R2 installed on a virtual server
IMO, the SQL Server VM should live with the rest of the VMs. If this device goes kaput (the Cybernetics website says nothing about the number of controllers in this model range, so presumably there's only one), you don't want all your eggs in one basket. Hopefully there are other, better protected devices ...
7
Your problem is not disk fragmentation. Your problem is RAM and application table scans:
4GB RAM ... 68GB ... Page life expectancy 20 seconds
You need way more RAM. As in your new server should have way, way, way, way more than 12GB. Start with 64 GB, it costs basically dimes. And yes, fix your app to use indexes. 20 seconds is very clear indication of ...
7
There is a bit of advice on the internet that suggests for Server 2012 and SQL 2012 I should stick to NTFS e.g. itknowledgeexchange) as database commands like DBCC CHECKDB don't work.
This is no longer true with SQL Server 2014 and is fully documented in KB 2974455
This problem is resolved in Microsoft SQL Server 2014. Beginning in this version of SQL ...
Only top voted, non community-wiki answers of a minimum length are eligible
