46
It very much depends on the details of your requirements.
If you have sufficient free space (at least 110% of pg_size_pretty((pg_total_relation_size(tbl))) on disk and can afford a share lock for some time and an exclusive lock for a very short time, then create a new table including the uuid column using CREATE TABLE AS. Why?
What causes large INSERT to ...
answered Oct 30 '13 at 19:44
Erwin Brandstetter
110k12 gold badges237 silver badges370 bronze badges
26
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 ...
17
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 ...
15
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 ;
...
15
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
...
14
You need to rebuild the clustered index after making the columns sparse. The dropped columns still exist in the data page until you do this as can be seen with a query against sys.system_internals_partition_columns or using DBCC PAGE
SET NOCOUNT ON;
CREATE TABLE Thing
(
ThingId int IDENTITY CONSTRAINT PK PRIMARY KEY,
USER_CHAR1 nvarchar(150) null,
...
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 ...
answered Apr 21 '15 at 13:53
14
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 ...
14
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 ...
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
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 columns can ...
answered Feb 12 '15 at 21:56
Erwin Brandstetter
110k12 gold badges237 silver badges370 bronze badges
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
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. ...
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 (...
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
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 ...
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 ...
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
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 ...
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 says Remove Sara Chipps
20.3k2 gold badges31 silver badges61 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
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.
I spend my life encountering poorly designed hardware platforms where people just try to design a large scale computer. All CPU power here, ...
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
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
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 are no longer valid....
7
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.
Sounds good. Have you collected and cut up the minifilter and storport tracing, yet? If so, what did it show?
At first glance it looks to be clearly an I/O subsystem error and the ...
answered Apr 2 '19 at 21:30
Sean says Remove Sara Chipps
20.3k2 gold badges31 silver badges61 bronze badges
6
One thing to consider is that the log files are sequential writes where as the data files are non sequential. That is one of the reasons for separate LUNs. Log files write faster if they are on their own LUN because the spindles don't have to skip around, just write sequential. If you add in a data file then the spindles have to skip around and you lose ...
Only top voted, non community-wiki answers of a minimum length are eligible
