Apr 1 2011

5,742,000 Queries Per Hour = DataBound = Slowdowns

Posted by Mike Brunt at 10:42 AM Web Servers | DataBase | CloudComputing | ColdFusion | JRun-J2EE

First of all, apologies for the silence here, a lot has happened in the past month, I relocated, temporarily and that took a good deal of my time, plus we are very busy at present.  In this posting I want to introduce a mildly un technical but nevertheless critical issue for web applications reliant upon Relational Database Management Systems, RDBMS's, which is still most sites out there.  One of the first things we look at, typically either via FusionReactor or SeeFusion, is the number of queries per request; hence my reference to this being mildly non-technical.

I will start at the end and say this, any application running with more than 10 queries per request is a potential red-flag for us.  There are good reasons for this and here is an example why; we have a current client who's initial query to request count was 45-50 queries per request.  We immediately homed in on this as a potential throttle point to the
site performance, for may reasons.  Calls to RDBMS's almost always traverse a network and very often the database is in a different subnet, both those things are probable points
of bottlenecks, then there is the work needing to be done by the RDBMS and in almost all cases there will be something lacking in the installation of the RDBMS, for instance hard drive arrays with everything on one array, data logs TempDB etc, shared SAN's and in a worse case scenario the RDBMS resides in a Cloud.

Here is an actual example which caused a spike in busy threads and a slow-down in performance, as observed via Metrics Logging.  In one 20 second period on one web-ColdFusion server 212 requests were handled so with a ratio of 50 queries per request that is 10,600 queries in 20 seconds or 31,800 per minute or 1,908,000 per hour from a single web-CF server and there are 3 web-CF servers in a cluster so if all is being load-balanced equally that is 5.7 million queries per hour hitting the RDBMS.  That is a high number of transactions and sure enough every time the system is handling over 120 requests every 20 seconds, everything degrades.

So, we have definitive evidence that a high query to request ratio will degrade performance and it is something to watch out for right from the beginning.  As a rule of thumb, do not go beyond 10 queries per request and if possible stay well below that and do not ever have your transaction logs and TempDB and of course the data itself all on the same drive array and never put those in a Cloud.  Following these simple procedures will prevent your applications from becoming "data-bound" with inevitable performance problems.

Comments

Aaron Kocourek

Aaron Kocourek wrote on 08/04/11 1:49 PM

Thank you for providing this information, it helped us out greatly! <a href="http://aaronkocourek.net/">Aaron Kocourek</a>
hermes

hermes wrote on 09/07/11 2:43 AM

Thanks a lot for sharing this. I have never thought that surfing online can be so much beneficial and entertained in a good shape. I feel really happy and grateful for providing me with such priceless sound track. All are good here simply best. I want such article again and again
superdry uk

superdry uk wrote on 11/18/11 6:14 PM

Every superdry sale fans convinced superdry will be next huge designer. The colors of superdry uk are just amazing because they are universally flattering! It is a great alternative for people who are tired of buying out the entire superdry outlet line and are finished shopping at Our Superdry uk sale store.
jkider

jkider wrote on 11/24/11 4:10 PM

Daily meticulously shaping a hair, will make people look extra spirit [url=http://www.bestlisseurghdfr.info/][b]ghd lisseur[/b][/url] Today we introduce some friends of female beauty does not hurt the hair shaping straight hair, [url=http://www.bestlisseurghdfr.info/][b]styler ghd [/b][/url]the product uses a high temperature ceramic plates, [url=http://www.bestlisseurghdfr.info/][b]lisser ghd[/b][/url] hair can minimize the damage will be. Straight hair looks elegant design [url=http://www.bestlisseurghdfr.info/][b]ghd hair straighteners[/b][/url] Body with a high temperature ceramic plates smooth and delicate texture to prevent their hair, [url=http://www.bestlisseurghdfr.info/][b]ghd france[/b][/url] to protect the scalp healthy.
superdry sale hoodies

superdry sale hoodies wrote on 11/27/11 11:04 PM

What an inspiring article you wrote! I totally like the useful info shared in the article.
Online Business Listing

Online Business Listing wrote on 01/18/12 12:25 AM

I enjoy reading it. I have need of to learn more on this subject.. Thanks in return theme this marvellous post.. Anyway, I am gonna subscribe to your silage and I desire you post again soon.
handbags

handbags wrote on 02/07/12 9:08 PM

Thank you! You often write very interesting articles. You improved my mood.I'll be back again.
Austin Custom Home Builders

Austin Custom Home Builders wrote on 06/02/12 5:08 AM

The information and the aspect were just wonderful. I think that your viewpoint is deep, it’s just well thought out and truly incredible to see someone who knows how to put these thoughts so well. Good job!
rak toko swalayan murah

rak toko swalayan murah wrote on 06/17/12 10:07 AM

This posting is very cool and interesting post.This is a great inspiring article.I am very much pleased with your good work.You put really very helpful information in this site.

http://www.rakminimarket.com

therefore, I would like to thank you for the efforts you have made in writing this article.
write my research paper for me

write my research paper for me wrote on 09/25/12 8:56 AM

It is outstanding article. It is very useful and informative. I was searching this information.
Prix France

Prix France wrote on 11/04/12 6:51 AM

Prix France
financial adviser tips

financial adviser tips wrote on 01/04/13 3:43 AM

Pleas share some suggestion so that i can get rid off from this problem.
my budgetingwork sheet

my budgetingwork sheet wrote on 01/04/13 3:43 AM

This is great inspiring article. I am pretty much pleased with your good work.You put really very helpful information. Keep it up. Keep blogging. Looking to reading your next post.
section 125 guide

section 125 guide wrote on 01/04/13 3:44 AM

You have shared best information in your blog. I have also seen the above link which you have mentioned in above.
us top credit cards

us top credit cards wrote on 01/04/13 3:44 AM

I want to know more about Fibromyalgia Network. Please make a note on that.
401 kloans guide

401 kloans guide wrote on 01/04/13 3:45 AM

I would like to say thank for sharing this great article. We can’t get this kind of information from anywhere.
grosir jilbab murah

grosir jilbab murah wrote on 01/05/13 5:17 PM

You got fantastic nice ideas there. I made a research on the topic and got most peoples will agree with your blog. All you have to do is spend some time on the Internet to find stores that offer the items you are most interested in, so start browsing today!
empresa reformas madrid

empresa reformas madrid wrote on 03/16/13 5:00 AM

That is very good comment you shared.Thank you so much that for you shared those things with us.Im wishing you to carry on with ur achievements.All the best
alquiler barco

alquiler barco wrote on 03/26/13 3:02 AM

This is really a fascinating blog, lots of stuff that I can get into. One thing I just want to say is that your design is so perfect! Afrer reading your post i could understand that the content is most simple and great. Thank you for your great share. Also this is my 1st visit at your site and after visiting your site i enjoyed.
tas pria

tas pria wrote on 04/02/13 9:30 PM

Me and my friend were arguing about an problem similar to this! Now I realize that I had been correct AUTO
retro jordans for sale

retro jordans for sale wrote on 04/23/13 8:58 PM

shoe outsole stable, focusing on comfort.Many people often ask to buy basketball shoes cushion shoes, or is not made ??of genuine leather. On this point, Jordan sports designer explained that the cushion is just one of the cushioning technology. There are many cushioningtechnology is adopted. So look at the shoes is good or bad can not just look at there is no cushion, and not everyone needs a special cushioning. Moreover, the development of science and technology many new upper material from either the structure or function are closer to natural leather, breathable, washable and color brightness or even better than natural leather. Therefore, it is not natural leather upper has an important symbol not distinguish between high-end sports shoes.DEMI
moqueta ferial

moqueta ferial wrote on 05/23/13 9:51 AM

It’s a great blog to visit because it’s like a learning experience and building the confidence up.
autobuses Sevilla

autobuses Sevilla wrote on 05/23/13 10:01 AM

It proved to be Very helpful to me and I am sure to all the commenters here! It's always nice when you can not only be informed,
jual tas murah branded

jual tas murah branded wrote on 05/28/13 8:58 PM

It proved to be Very helpful to me and I am sure to all the commenters here! It's always nice when you can not only be informed,
jual dompet wanita branded

jual dompet wanita branded wrote on 05/28/13 9:00 PM

Vous ne savez jamais ce que vous pouvez jusqu'à ce que vous essayez. Merci pour votre merveilleuse sharing.So je tiens à dire que j'ai vraiment apprécié l'analyse de vos messages blog. De toute façon je vais être abonnés à votre flux, et je m'attends que vous publiez à nouveau peu de temps.
darrendemers12

darrendemers12 wrote on 10/30/13 2:50 AM

I was really impressed. This is really interesting topic, I'll write a paper on it or probably I'll just buy essay . Thank you very much!
http://www.new-york-computerforensics.com/
http://www.cheap-new-computers.com/
http://www.computerrecyclingpro.com/
http://www.transparentcomputers.com/
http://www.and-pia-computer-lab.com/
replica watches factory

replica watches factory wrote on 03/16/14 11:47 PM

Eveyone has individa tastes when it comes to shoes Vibeg Boots povide exceent eathe boots Yo wi impessed by o speb qaity Louis Vuitton bags? Upon delivery, it will be immediately employed on a bareboat charter for a five-year term to a European shipping <strong> replica Ebel watches </strong>.
clothing manufacturer

clothing manufacturer wrote on 07/04/15 4:18 AM

Thanks a lot for sharing this. I have never thought that surfing online can be so much beneficial and entertained in a good shape. I feel really happy and grateful for providing me with such priceless sound track. All are good here simply best. I want such article again and again
تكييف شارب

تكييف شارب wrote on 03/01/16 9:20 PM

elarabygroupsharp
اسعار تكييف شارب

اسعار تكييف شارب wrote on 03/01/16 9:20 PM

اسعار تكييف شارب
مبيعات تكييف شارب

مبيعات تكييف شارب wrote on 03/01/16 9:20 PM

مبيعات تكييف شارب
توكيل تكييف شارب

توكيل تكييف شارب wrote on 03/01/16 9:21 PM

توكيل تكييف شارب
تكييف شارب العربي

تكييف شارب العربي wrote on 03/01/16 9:21 PM

تكييف شارب العربي
شارب العربي

شارب العربي wrote on 03/01/16 9:21 PM

شارب العربي
boom beach hile apk

boom beach hile apk wrote on 05/14/16 3:08 AM

I’m going to read this. I’ll be sure to come back. thanks for sharing. and also This article gives the light in which we can observe the reality. this is very nice one and gives in depth information. thanks for this nice article
LedgerNote

LedgerNote wrote on 05/16/16 7:12 AM

I’ll be sure to come back. thanks for sharing. and also This article gives the light in which we can observe the reality. this is very nice one and gives in depth information. thanks for this nice article
تكييف شارب

تكييف شارب wrote on 06/25/16 2:28 AM

I was really impressed. This is really interesting topic, I'll write a paper on it or probably I'll just buy essay . Thank you very much!
برامج

برامج wrote on 07/19/16 11:21 PM

I was really impressed. This is really interesting topic, I'll write a paper on it or probably I'll just buy essay . Thank you very much!
برنامج الاذان

برنامج الاذان wrote on 07/19/16 11:26 PM

I’ll be sure to come back. thanks for sharing. and also This article gives the light in which we can observe the reality. this is very nice one and gives in depth information. thanks for this nice article
مواقيت الصلاة

مواقيت الصلاة wrote on 07/19/16 11:27 PM

Diamonds will almost always be in structure and fashion. Signifying female’s wonders, your female feels
مواقيت الصلاة القاهرة

مواقيت الصلاة القاهرة wrote on 07/19/16 11:28 PM

I’ll be sure to come back. thanks for sharing. and also This article gives the light in which we can observe the reality. this is very nice one and gives in
مواقيت الصلاه

مواقيت الصلاه wrote on 07/19/16 11:32 PM

’m going to read this. I’ll be sure to come back. thanks for sharing. and also This article gives the light in which we can observe the reality. this is very nice one and gives in depth information. thanks for this nice article
مواعيد الصلاة

مواعيد الصلاة wrote on 07/19/16 11:42 PM

fvgxhfjbdzstdfdvgc
ماسنجر

ماسنجر wrote on 07/19/16 11:44 PM

There is free 2-hour street parking on the north side of the building, on 98th St., or you can park in the parking structure. There is a parking fee of $1.50 for 20 minutes. The doors open at 6PM, and the meeting will start promptly at 7PM. The meeting should end around 8:30PM
55 plus communities in texas

55 plus communities in texas wrote on 10/14/16 8:17 AM

From this blog I understood more about Java memory management and tuning process. I have studied Java and topics like this are very helpful for me. It is good that you have given the complete details of this process. Thanks for sharing