?
Solved

High volumne

Posted on 2014-01-14
8
Medium Priority
?
246 Views
Last Modified: 2014-01-18
Can anyone provide me some concerns/suggestions/tipical issues/related tips ... on High Volume Transaction ? I have basic knowledge but have not ever exposured to a high volume transaction system yet. Can anyone give me a good leading so that it would helpful for me to develop solutions for high volume transaction enterprise class systems in commercial settings.
Thanks a lot.
0
Comment
Question by:minglelinch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39780588
Check this https://secure.logmein.com/welcome/visualization/fullscreen/ 
that's an example of a high volume application
0
 
LVL 9

Assisted Solution

by:Beartlaoi
Beartlaoi earned 400 total points
ID: 39780633
There are multiple variables to consider.
First though the main performance bottleneck in a database is the storage.  Disks can only seek so fast.  To deal with this you should have many disks and partition schemes to balance the daily load to all the disks.
Also, do not have SQL do things that the app/middle tier can do like sorting and some joining like translating IDs to names of non-changing data.  Also avoid using SQL to generate Identity values, with proper data design the IDs can be generated by the client and the DB only has to store it.
You can partition your data between servers like last names between A-M on one server and the others on the other, use as many servers as you like.  But programming for this can be tricky and may not be agile enough to change where the boundaries are to keep things properly load balanced.
If your budget is really big, you can look at MS Data Warehouse where they mainly throw hardware at it and give you an effectively load-balanced SQL/Storage solution.
0
 
LVL 3

Assisted Solution

by:smilieface
smilieface earned 800 total points
ID: 39780940
For high volume you're going to need a few things. This is my personal order of priority.

1. Memory. Lots of memory dedicated to Buffer Space. Without any real metrics I would suggest at least 25% of the database size available as Buffer. Note that this must be backed by real RAM. Paging is your enemy on a database server.
2. Dedicated servers. Nothing worse than wanting the CPU and something else has it, or waiting for someone else's write to complete.
3. Storage. Faster the better, and the good news is that there is some staggeringly fast storage available through SSD now. Your targets for SSD disk are TempDB and the .LDF files. Don't waste SSD storage on the .MDF files, as they'll be fine on slower storage.
4. CPU. Enough that you are consistently below 60% busy.

There are lots of forums out there that will help (including this one), but to get a more specific answer, you'll need to ask a more specific question!
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 1

Author Comment

by:minglelinch
ID: 39781130
Thanks for the great helpful comments.

How about high availability strategies?
0
 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 400 total points
ID: 39781214
the answer is "it depends":
on your budget
technology, software
and what do you plan to use
etc,

----
select what one you plan (e.g. real time )

and check

Transaction processing system
http://en.wikipedia.org/wiki/Transaction_processing_system
0
 
LVL 3

Assisted Solution

by:smilieface
smilieface earned 800 total points
ID: 39781236
And on your software edition. As this is in a SQL forum, I will make the recommendation to check the features of the various of SQL server editions http://www.microsoft.com/en-us/sqlserver/editions.aspx
0
 

Accepted Solution

by:
Secondstreet earned 400 total points
ID: 39790159
Certainly there are a lot of options for you and as others have noted it "depends" on a lot of things... especially the budget.

Questions:

1) Is your goal a high transaction volume (writes - inserts/updates/deletes) or high capacity working with lots of data collected? If you're into transaction rate that IO latency/IOPS are going to be your bottlenecks... If high capacity processing then you'll likely need RAM and CPU power.

2) What is your ballpark budget for this?

Comments:

1) Since this is posted in MS SQL I'll assume the solution you want is Microsoft SQL Server based.

2) If high transaction volume is required you'll need storage with low-latency to achieve the highest IOPS. As mentioned in other comments the FusionIO IODrive 2 is pretty wicked fast in terms of IO latency and IOPS... those are your tickets to high transaction throughput.

3) The RAID controller in your SQL Server(s) may have a battery-backed RAM cache that allows you to do write caching. This can greatly increase your ability to handle bursts of transactions... although doesn't help much in terms of sustained high transaction rates.

4) SQL 2014, soon to be released, will be interesting for transaction volume. If you can tolerate some potential transaction data loss (last few seconds) in case of a catastrophic server failure... and that's a BIG IF... then SQL 2014 has a new feature called "delayed durability" which can greatly increase transaction throughput. I'm getting much higher rates in my tests.

5) Test... test... test. If you can try different hardware and properly configure and test it.

6) If you need High Availability and extreme Transaction Rates you may want to use Mirroring instead of clustering. I use clustering in many of my installations but transaction volume is often limited by IO latency for those who need extreme transaction rates.

I'm currently on a project that requires that we attain extreme transaction/sec rates and hardware wise am spending $110K for a pair (for High Availability) of servers and lots of PCIe based storage.
0
 
LVL 1

Author Closing Comment

by:minglelinch
ID: 39791452
Thanks for all helpful comments.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question