June 27, 2010 at 1:45 pm
Hi
Suppose we have a website with 1,000 viewers each second. And we want to insert the viewer’s information(for example) to the SQL database. So we must insert 1,000 records to the SQL each second. And it may take long time to do.
My question is: How can we decrease this process?.
Can we fix it programmatically or we must use hardware solutions?
Best Regards. Morteza
June 27, 2010 at 2:58 pm
irmorteza2000 (6/27/2010)
HiSuppose we have a website with 1,000 viewers each second. And we want to insert the viewer’s information(for example) to the SQL database. So we must insert 1,000 records to the SQL each second. And it may take long time to do.
My question is: How can we decrease this process?.
Can we fix it programmatically or we must use hardware solutions?
Best Regards. Morteza
It depends. There can be a lot of issues to be resolved (e.g. network issues, inefficient programming, hardware issues, SQL Server configuration, data/log file configuration ...list continues...).
From my point of view this question is beyond the scope of a forum. I strongly recommend to get a consultant in to help you analyze and resolve the issues.
One of the easier questions would be: Is "1,000 viewers each second" the min, max or avg. value? ...
June 27, 2010 at 4:58 pm
Another simple question... do you really need to log all that activity. Assuming yes you'll need a lot of hardware AND onsite consultant to get you there.
June 27, 2010 at 5:36 pm
irmorteza2000 (6/27/2010)Suppose we have a website with 1,000 viewers each second. And we want to insert the viewer’s information(for example) to the SQL database. So we must insert 1,000 records to the SQL each second.
I'm curious about what kind of infrastructure is currently supporting such application. Could you please share?
Is it a data driven website? how is the backend handling such traffic?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 27, 2010 at 5:59 pm
Sites like myspace handle heck of a lot more than that... but still this is a nightmare to setup and maintain :w00t:.
June 28, 2010 at 8:25 am
Volume like this in sQL Server will require both hardware and careful database design, appropriate tsql, good indexes, proper maintenance... I'm with everyone else. You're probably better off hiring someone who has done this previously.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 28, 2010 at 8:40 am
I want to buy shares in your company or, at least, buy advertising space on your site !!!! 😀
1000 (new) viewers per second during 8 hours working day means 28,800,000 potential clients per working day. Are you going to be 24hr business? Even better, it will be 86,400,000 of them :-D:-D:-D
I think you are a bit over-optimistic, but who knows, who knows...
:hehe:.
Or, my understanding is completely wrong. You will have 1000 users logged-in at once (during 1 second) and you want to log every second during their stay on the site?
Actually, logging 86,400,000 records per day into SQL server database (and it is just one log table) will cost you some good money in hardware...
June 28, 2010 at 8:41 am
Ninja's_RGR'us (6/27/2010)
Sites like myspace handle heck of a lot more than that... but still this is a nightmare to setup and maintain :w00t:.
Agreed but, look at the infrastructure MySpace had four years ago:
"MySpaces extensive IT architecture currently features 2,682 Web servers, 90 Cache servers with 16GB RAM, 450 Dart Servers, 60 database servers, 150 media processing servers, 1,000 disks in a SAN (storage area network) deployment, three data centers and 17,000MB per second of bandwidth throughput." (1)
Don't think MySpace got there by posting a simple question in a forum 😀
(1) http://www.eweek.com/c/a/Data-Storage/MySpace-Makes-Room-with-EMC-Isilon/
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 28, 2010 at 8:45 am
PaulB-TheOneAndOnly (6/28/2010)
...Don't think MySpace got there by posting a simple question in a forum 😀
...
Of cause NO.
They just GOOGLED it out! 😀
June 28, 2010 at 8:57 am
By the way, my SQL Express manages to insert 10000 records into the table in around 15 milliseconds. So, if you like you can allow your web site to be viewed by around 660,000 users per second, providing the coverage for all Earth population in less than hour... Again, the small issue would be the hardware cost to keep the log for everyone who viewed and enjoed your page 😀
June 28, 2010 at 8:59 am
PaulB-TheOneAndOnly (6/28/2010)
Ninja's_RGR'us (6/27/2010)
Sites like myspace handle heck of a lot more than that... but still this is a nightmare to setup and maintain :w00t:.Agreed but, look at the infrastructure MySpace had four years ago:
"MySpaces extensive IT architecture currently features 2,682 Web servers, 90 Cache servers with 16GB RAM, 450 Dart Servers, 60 database servers, 150 media processing servers, 1,000 disks in a SAN (storage area network) deployment, three data centers and 17,000MB per second of bandwidth throughput." (1)
Don't think MySpace got there by posting a simple question in a forum 😀
(1) http://www.eweek.com/c/a/Data-Storage/MySpace-Makes-Room-with-EMC-Isilon/
I was in Pass when the Lead DBA of MySpace was there to host conferences. IIRC they were handling something like 1 M queries a minute. I was shaking for hours after hearing that!
I also remember that they had to tune 1 query. Just trimming 10 ms off that query meant a couple LESS server at the end of the month... which their were adding at a rate of 1-2 per week back then.
The tweak was to go from "TOP 1 ORDER BY" to "MAX()"...
June 28, 2010 at 9:02 am
Eugene Elutin (6/28/2010)
By the way, my SQL Express manages to insert 10000 records into the table in around 15 milliseconds. So, if you like you can allow your web site to be viewed by around 660,000 users per second, providing the coverage for all Earth population in less than hour... Again, the small issue would be the hardware cost to keep the log for everyone who viewed and enjoed your page 😀
Just keep in mind that in most cases, logging takes far less ressources than actually showing the page(s) and querying the data to do so ;-).
Further keep in mind that the OP needs to insert 1000 times 1 record VS you whod did 1 time 1000 records which makes a hell of a lot of difference in the required time department.
June 28, 2010 at 9:54 am
Ninja's_RGR'us (6/28/2010)
...Just keep in mind that in most cases, logging takes far less ressources than actually showing the page(s) and querying the data to do so ;-).
...
Further keep in mind that the OP needs to insert 1000 times 1 record VS you whod did 1 time 1000 records which makes a hell of a lot of difference in the required time department.
First of all, you don't need to query the database to show the page. I guess you can hard code something along the lines of "HTTP 404 ..." and that should be quite easy to do and very quick to show 😀
For a second one... O man, I guess what you do, is: you bunch up 1000 logged-in users and log them all at once to spead up logging process, who really care if they logged in a few milliseconds appart! :hehe:
June 28, 2010 at 10:01 am
Eugene Elutin (6/28/2010)
Ninja's_RGR'us (6/28/2010)
...Just keep in mind that in most cases, logging takes far less ressources than actually showing the page(s) and querying the data to do so ;-).
...
Further keep in mind that the OP needs to insert 1000 times 1 record VS you whod did 1 time 1000 records which makes a hell of a lot of difference in the required time department.
First of all, you don't need to query the database to show the page. I guess you can hard code something along the lines of "HTTP 404 ..." and that should be quite easy to do and very quick to show 😀
For a second one... O man, I guess what you do, is: you bunch up 1000 logged-in users and log them all at once to spead up logging process, who really care if they logged in a few milliseconds appart! :hehe:
That works... only 1 problem here, you need to have a single shared "memory" on the web server for all the executions. Then you need to batch process that (where you still need to handle reads writes and what's processed, or processing). This makes for a very dangerous bottleneck for the application...
June 28, 2010 at 10:04 am
Ninja's_RGR'us (6/28/2010)I was in Pass when the Lead DBA of MySpace was there to host conferences. IIRC they were handling something like 1 M queries a minute.
Exactly. Volume is what separates boys from men.
Most people don't grasp the concept that the negative impact of bad design and bad coding increases exponentially with volume. Large environments are the field where true colors are shown.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply