Fast Writes
If you want fast writes you need to concentrate more on the log file (in SQL at least) so that the transactions can be committed as fast as possible. If you are using a synchronous commit (mirroring, availability groups etc) then you want a network that doesn't lag. To get the log file quick you really need to concentrate on speeding up the IO. The log file is sequential so you want the transactions committed as quickly as possible. I wouldn't worry too much about the structure of the tables so much other than to ensure that you don't go silly with the foreign keys and indexes as they need updating as well. Things for you to be aware of here are page splits (as data gets inserted into the b-tree index) and locking of the data. By having the log file fast you reduce the duration that the lock is held for in effect. A lock is created, the change made and committed, this is the hardened in the log and the lock released.
TempDB
The single best option here is to avoid TempDB.
With SQL, if you end up using TempDB then this also needs to be fast. Again, use the SSD for this. It makes the world of difference. Traditional recommendations are to separate your logs and data files onto different volumes and an SSD is a great way of doing this cheaply (I would recommend putting the log files and TempDB together on the SSD unless you really need to crank out the performance). You should also add more files to TempDB with a general rule of 1 file per every 2 cores (up to 8 files) and that's likely good enough as a quick pass. If you want to know why then Google for 'SGAM contention'.
General Database Structure (under the hood)
General Database Structure (under the hood)
I generally go further and split the database so that the biggest tables (or major entities if you will) have their own file group. I drop non clustered indexes into their own file group as well as well as create a new one the default file group. I do this so that the mdf file only contains the basic system information needed as this can speed up some recovery options.
Then each file group is given several files (for simplicity I generally recommend the same as you need to do with TempDB). Why ? Well, the more files you add the faster you go (to a point) and it helps with hyperthreading. SQL uses a proportional fill algorithm so ensure all of these files are created the same size too. I have been known to move individual file groups to different RAID sets depending on the characteristics on the type of data (R5 for reads, R10 for writes). I also prefer dedicated spindles as you get a lot more performance and don't wait for some other process to finish with the disks that you need. I can very happily recommend FusionIO and suggest dropping all files plus log and TempDB onto a drive (it's pretty mind blowing for performance).
Work with your SAN admin if you can and give them detailed info on what you need to achieve. Tell them why you need dedicated IO and what performance characteristics you need. If that doesn't work, try FusionIO and tell them you don't need them anymore :-)
Then each file group is given several files (for simplicity I generally recommend the same as you need to do with TempDB). Why ? Well, the more files you add the faster you go (to a point) and it helps with hyperthreading. SQL uses a proportional fill algorithm so ensure all of these files are created the same size too. I have been known to move individual file groups to different RAID sets depending on the characteristics on the type of data (R5 for reads, R10 for writes). I also prefer dedicated spindles as you get a lot more performance and don't wait for some other process to finish with the disks that you need. I can very happily recommend FusionIO and suggest dropping all files plus log and TempDB onto a drive (it's pretty mind blowing for performance).
Work with your SAN admin if you can and give them detailed info on what you need to achieve. Tell them why you need dedicated IO and what performance characteristics you need. If that doesn't work, try FusionIO and tell them you don't need them anymore :-)
Data Reads
For reads, as a general rule of thumb, if you want you data to go fast, you want to get the entire set of data plus indexes into memory. If you can't get the data into memory, then you need to get it from disk. It sounds simple, but how do you do that ?
If you look at the Microsoft Fast Track Architecture you will see they go to a load of pain separating things out to maximise the speed. Basically using a lot of disks (a RAID 5 set per core in essence). Smaller and cheaper set-ups don't need that although you must remember to get decent IO performance to suit.
I honestly don't think that this is the right approach for most cases. It is perfectly valid and simply assumes you need to perform scans on the biggest set of data within the database. To me, this is throwing a lot of money at the problem.
I honestly don't think that this is the right approach for most cases. It is perfectly valid and simply assumes you need to perform scans on the biggest set of data within the database. To me, this is throwing a lot of money at the problem.
Getting the data itself right is the better approach if you have the time.
To really get your data fast, you have to learn about b-tree indexes (balanced tree). Once you understand how to make use of these (I will blog about these too shortly) you can learn how to index your data so that your queries are optimised. The effort you need to put in is in the design of the table, but more importantly the indexes and them the queries that fit on top. If you use an ORM to save to development time you still need to understand what is being asked for and build the indexes to suit. Don't be lazy and assume that the ORM gives you the most performant queries. It won't. Check the queries and pick the battles you need to fight.
The key to this (in SQL) is the execution plan. This gives you everything you need but it takes some time to get to know this in detail. You need to aim for a single version of the execution plan so that you don't spend time compiling a new plan each time.
What you are aiming for is Clustered Index Seeks where possible. You need to avoid Table (or index) Scans.
Small data types are worthwhile the effort when you are dealing with high concurrency systems. It might seem like micro-optimisation but it isn't. Bringing back data that you already have is also something to be avoided. This is where you will have a potential disagreement with your developers as that approach will be better suited to the object they want but ultimately it hampers the speed of your data.
I'll write another blog soon on why joins aren't really a bad thing if you do them right.
What you are aiming for is Clustered Index Seeks where possible. You need to avoid Table (or index) Scans.
Small data types are worthwhile the effort when you are dealing with high concurrency systems. It might seem like micro-optimisation but it isn't. Bringing back data that you already have is also something to be avoided. This is where you will have a potential disagreement with your developers as that approach will be better suited to the object they want but ultimately it hampers the speed of your data.
I'll write another blog soon on why joins aren't really a bad thing if you do them right.
As a quick summary
For fast writes, speed up your log file
For fast sorts and grouping, speed up TempDB.
For fast reads, get the data in memory or get the IO right if you can't.
Overall, get the data structure right, get the indexes right and tune them hand in hand with the queries.
Aim for a single execution plan (no compiling after the first hit) that gets re-used.
Keep your queries simple and avoid putting calculations in the where clause if you can (these will be re run for every line that matches).
Don't keep returning data you have just received.
Take the approach of a precision missile strike rather than carpet bombing the whole thing.
Don't keep returning data you have just received.
Take the approach of a precision missile strike rather than carpet bombing the whole thing.
No comments:
Post a Comment