Hot answers tagged hardware
8
Variants of this question come up semi-regularly:
SQL Server build
Help me choose a RAID level combination for a SQL Server 2008 instance
How should I configure these disks on a SQL Server for a BI configuration?
There are also occasional bun fights about the data/log separation "best practice".
Placing Transaction Log on Separate Volume [solid ...
7
Typically the features that you don't hear about from marketing are the ones that don't bring in all the money (e.g. pushed as "enterprise feaures" in order to sell Enterprise Edition). I answered a similar question here that provided a list of my favorite new features in 2012, that also aren't limited to Enterprise Edition:
What are Objective Business ...
7
Correct. In theory, if you have 100s of DBs you need 100s of drives, one for each log. In practice though one does not care for such case, cause when you have 100s of DBs you obviously don't expect top-notch TPC performance for each DB. You will likely have some DBs with high throughput and stringent SLAs and you could have them each on separate spindles, ...
6
Even though you have memory to spare at the moment (70GB database vs 128GB server) I would still configure the maximum server memory as per latest guidance.
...reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed
from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB
RAM.
That equates to 110GB on a 128GB server. ...
6
If there is room for more drives, order more drives. If there isn't room for more drives, order more drives... and an external enclosure.
I'm assuming this data has at least some value to the business and from your comment we can also infer that you're experiencing performance issues already. So, zero redundancy and single spindle performance isn't going to ...
5
It all depends on your workload, but with only 6 drives it does limit your options. If your workload is not heavily dependent on tempdb for things such as sorts, hash tables, and snapshot isolation, then you might be better off using the 6 SAS drives together in RAID 10. However, if you know or have the metrics to prove that tempdb is heavily utilized, ...
5
This is essentially a hardware capacity planning question. The problem with them is that if you give enough details that we can tell you what hardware you need, it'll become "too localized" to your particular set of circumstances. That said, we've got a number of pseudo-answers already written to help you:
Can you help me with my capacity planning?
How do ...
4
+1 for the answers from @CadeRoux and @ChrisS, they make valid points.
Your comments to those answers highlight that this is essentially a proof-of-concept venture at this stage and you want to minimise your capital investment. If that's the case, forget spending $1000s on hardware and licenses, rent.
You don't appear to need to store a vast quantity of ...
4
From the comments chain, it looks like you're interpreting ASYNC_NETWORK_IO waits to mean that the problem is related to the network. It (typically) isn't.
As @MartinSmith hinted at (twice) the most likely explanation for that is SSMS or the application you're using not consuming the results as fast as SQL Server is serving them. Follow either of the ...
3
"Isn't it possible to scale database servers through virtualization,
like forever?"
Yes, you can make more and more database servers and you can also make a larger and larger database server (but the scale up limit can be reached very quickly), but in the scale out scenario are the servers you mention supposed to cooperate in any way or are they ...
3
Yes you'll want 64k blocks on the array and the disk as SQL Server will be doing the bulk of it's operations in 64k chunks. While there will be some reads which are smaller than this and some that are larger (such as read ahead) the bulk of the operations will be 64k in size.
Assuming that the server is a Windows 2008 server (or newer) and that the LUN is ...
3
Quoting from Brent Ozar's article SQL Server Virtualization Q&A:
Question: “Regarding Virtualization and SAN… is there a recommendation
for setting block size on SQL Server 2008?”
There’s a few related settings here including NTFS allocation unit
size, RAID stripe size, and partition offset. Check with your SAN
vendor’s documentation to ...
3
Based on everything you have said in the question and the comment, I don't think you need to worry about hardware yet unless you are trying to just get a ballpark price estimate for feasibility.
Since you are a software person, I would build the prototype on commodity hardware like your ordinary laptop, analyze and understand the problem and then spend ...
3
It's hard to know / to figure out:
Even some simple reads, could be IO hungry.
And the amount of users doesn't tell us how many request ( read / write).
Without more data, it hard to tell, but that seem to be a good server. I believe that it'll be good enough, but that's just a opinion.
2
Two things catch my attention
MEMORY
CPU
I have addressed these issue in past posts
Is the CPU performance relevant for a database server?
Insert-heavy InnoDB table won't use all my CPU
Please follow suggestions centered around InnoDB as multiple core engagement is only available in MySQL 5.5's native InnoDB as well Percona's XtraDB.
2
Ideally, you'd have the same configuration in development as you do in production. That way, you can have a higher probability that if it's slow in dev, it will be slow in prod, and if it's fast in dev, it will be fast in prod. I'm guessing that this would be a pretty significant drop from what you have in production. But given that few places have the ...
1
I would only use RAID 0 for the logs if you're okay with having reduced disaster recovery capability. You won't be able to make log tail backups in the event of a disk failure, and will only be able to restore to your most recent backup. However, this may be less of an issue if you're running Availability Groups, particularly with synchronous mirroring. You ...
1
It very much depends on what you mean by "very busy": different workload patterns (write heavy or not, bulk operations common or not, level of concurrent access, to name but three of the many variables) can have a drastic effect on the performance of any given spindle arrangement.
For a write heavy situation separating the logs from the data can make a ...
1
Typically I recommend to my clients leaving the MAXDOP alone. SQL Server is NUMA aware so it knows which NUMA node the processors are in and which NUMA node the memory is in and it'll do it's best to assign the work to a scheduler on the correct NUMA node for the data that you are looking for.
Looking into this sort of stuff also requires knowing how many ...
1
This pretty much depends on your I/O subsystem. I generally expect 100-120 IOPS per spindle on 7200 rpm S-ATA and 160-180 iops per spindle on 15k rpm SAS.
IOPS in this context typically refers to random 4K reads or writes, and I'm not entirely sure if the number you have given refer to physical 4k iops, or logical 8k iops.
Either way, 87 or 174 iops could ...
1
Plus one on the book suggestion and the thoughts on leaving memory for the OS and about the CPU load in Kevin's answer and Kevin brings up some good points but a few more thoughts for you to ponder as you settle on a solution:
1.) Development environments don't always (and in my experience don't typically) match production and I'm not sure you always need ...
1
Your question lends itself to this: Is my concern for MySQL data performance or consistency?
If consistency is the prime thing, then keep InnoDB's ACID settings.
Your innodb_flush_log_at_trx_commit should be 1. You can set it to 0 or 2 for increased performance but at the risk of data loss in case of a crash.
You can tweek innodb_flush_method to augment ...
1
Yes it is always safer. Thought the chance of actually getting corruption is low. When the filesystem gets corrupted it is very likely that repairing it will be successful. ACID compliant databases like InnoDB also do fsyncs/barriers to make sure committed changes are permanently stored on disk.
Don't forget that in a production environment you should be ...
1
The transaction log is exactly that... a running log of all transactions. As such it fills continuously in one direction until checkpointed and overwritten, or truncated and overwritten. The overwriting is sequential.
Consider your data files. A customer record may contain an order from 5 years ago, and one from 10 years ago. If you delete those orders ...
Only top voted, non community-wiki answers of a minimum length are eligible
