Nov 7 2009

Is The Database A Poor Step-Child?

Posted by Mike Brunt at 9:57 AM
20 comments
- Categories: Web Servers | .NET | DataBase | CloudComputing | ColdFusion | JRun-J2EE

Databases are so important to us, in many ways, they are the number one dependency we deal with in ColdFusion and yet, in many ways,  we treat them as ignored step-children and I fear the advent of Hibernate ORM in CF 9 will make matters even worse.  If client variables are in use, this becomes even more critical, tuning the database will always certainly yield more benefits than any amount of JVM tuning.  By tuning I mean looking at indexing, affinities, CPU allocation, hard-drive layout, etc.  All of theses things can yield immediate benefits or cause immediate pain if they are not right.

Typically, when we get new clients we try to persuade them to spend some time on our database review and report offering but most of the time they decline, wanting to focus all time/spend on the CF-web server, eventually we are typically able to persuade them to let us look at the database.  They would genuinely benefit greatly if we started out at the database.  Here are the main things we look at and report on...

  • Table Indexing
  • Back-up Redundancy Schema
  • Security Settings - Ports etc
  • Security Settings - User Accounts
  • Use of Views
  • Use of Stored Procedures
  • CPU Affinity
  • CPU Allocated to Database
  • Service Pack Level
  • Data File Location
  • Log Files Location
  • Compatibility Level (SQL Server Specific)
  • Heavy/bad processes such as Auto_Shrink
  • Data Type usage (looking for the most efficient data types)
  • Unnecessary Services
  • Long Running SQL

 

 

 

Comments

Bucky Schwarz

Bucky Schwarz wrote on 11/07/09 11:39 AM

I agree completely. Hell, even before you get to the tuning of the actual database, how much better would so many apps run if better SQL was written. It seems like instead of adopting the mindset that a good CF developer should write good CF as well as good SQL, we instead go in the opposite direction and say "don't worry about the SQL, ColdFusion 9 will handle that for you." I think the result is going to be the database becoming a sort of 'black box' where magic just kind of happens for you. What happens when the magic doesn't work? You end up digging around in the database and figuring how that works, THEN figuring out how to make the database work with Hibernate. What happens when you have to start writing complex queries with HQL? At that point, is it even worth it to use Hibernate?

But back to your original point, I think the database is absolutely the red-headed stepchild, and I'm not really sure why. The whole point of web apps is managing data and presenting it in a useful and meaningful way. It's kind of what web developers do: manage data and give it to the layman in a way that makes sense, so he doesn't have to go digging around in a database. A lot of people just hate dealing with databases, which should make you happy since I guess it gives you more business opportunities ;)
Mike Brunt

Mike Brunt wrote on 11/07/09 11:59 AM

@Bucky, thanks for taking the time to add just a great comment, you expressed it far better than I did. Sometimes I have people ask me why I spend so much time focused on the database as they thought I was JVM specialist, well I hope I am a performance specialist and of course as all data passes through ColdFusion, guess what takes the blame? I have two very good DBA colleagues one in the SQL Server world the other in Oracle and they do not like ORM centric work.
TJ Downes

TJ Downes wrote on 11/07/09 5:35 PM

I'd venture to say that the relational database is nearing the end of its life for many types of application development. OO development has made object storage more feasible and common, and applications will be better for it, in my opinion :)
Mike Brunt

Mike Brunt wrote on 11/08/09 10:13 AM

@TJ thanks for your comment and no doubt there are other mechanisms afoot, CouchDB for instance. From experience with clients, I still see mostly RDBM's and I think it will be some time, if ever they go away completely. What I do see happening relatively quickly is the adoption of caching mechanisms, such as memcached, to move data out of its slowest responding place; the hard drives.
Mike Kaplan

Mike Kaplan wrote on 11/09/09 6:14 AM

Don't forget about defragging your indexes. We have a weekly job that checks all indexes and defrags those that surpass a certain level of logical scan fragmentation. DBCC SHOWCONTIG and DBCC INDEXDEFRAG are powerful weapons in making sure your indexes keep doing what they're supposed to be doing, particularly in a system with a lot of data being added and edited.
Peter Bell

Peter Bell wrote on 11/09/09 6:16 AM

I think this is one of those cases where people try to generalize rules that are applicable to specific situations. The purpose of an ORM is to speed development of applications. The purpose of database tuning is to optimize performance of applications.

The vast majority of apps written never get heavy load - of course Mike, you probably don't get called in to do performance consulting on the many apps which don't need to perform! For some apps cost/effort/time to market is less important than the ability to immediately handle high loads (especially when replacing legacy systems - green field apps seldom need to handle huge loads on day 1 unless there's a lot of marketing or pent up demand to drive traffic).

For a lot of apps an ORM allows you to get to market quicker, then if you have a performance problem, load testing will identify bottlenecks (usually in the DB) which you can remove one at a time until the app performs acceptably for the current load - sometimes through DB only changes, sometimes through replacing specific hibernate queries with HQL or even with direct SQL through JDBC (although that raises a host of other issues), and sometimes through better caching strategies in the app level.

I think it's important to realize that the DB is often the source of performance issues, but also to understand that there are plenty of apps where cost and time to market are more important than performance. If there weren't, we'd write all of our apps in Java - not ColdFusion, Groovy or Ruby which are nowhere near as performant for many operations.
Mike Brunt

Mike Brunt wrote on 11/09/09 2:55 PM

@ Mike, thanks for the pointers and tips I am sure they will help many.

@ Peter, thank you for your comments and insights. You are right of course that most of the time we are called in for large applications where performance has become an issue. My view on this is that there are cost-effective ways to engineer and test applications before they are released, irrespective of size and it is important to do so. Mainly because small applications can grow big rather quickly if they become compelling enough. Load testing should be part of all application deployments in my opinion.
SEO link vine review

SEO link vine review wrote on 04/18/10 9:03 PM

nice post !As the old adage goes, "timing is everything." But while the idealistic mantra of direct marketing has always been to make the right offer to the right customer at the right time, the reality is very different. Thanks
SHUANGWU

SHUANGWU wrote on 06/12/10 2:19 AM

A modern variation of this technique has been developed by
<a href="http://www.beyondsneakers.com"><strong>MADFOOT</strong></a>;
intelligence researcher Dr. Win Wenger and it is detailed in his book “The Einstein Factor”,This exercise is sometimes called “Putting on the Expert’s Head” This technique enables you to access some of<a href="http://www.eshoppingluxury.com2010-christian-louboutin-??????????-c-15.html"><strong>??????????</strong></a>; the enormous untapped potential that resides in the mind of every individual.
Clement

Clement wrote on 09/05/10 9:04 AM

Hello guys,

All very good points! I am a DBA so I like to give some more details about how approaching perofrmance at the data tier as well.

Sometimes, it is not worth getting into the database to improve performance: true.
However, as Mike says very well, usually your successfull application can end-up be performing very badly due to a lack of database administration/tuning.

The most annoying thing for me that i can see with clients is their lack of supervision at the data tier. The application works, we are happy but in certain cases, it suddenly drops in performance or simply does not work because of the data-tier. And not because the data-tier is not performing well in general but because nobody cared about administrating the data-tier side.

One client had their backup regime not working for months but did not know. Once we got them back on track, a week later, they got their server to crash. Fortunately for them they had their disaster recovery solution thanks to our intervention. My point is you should not only see performance, you have to see administration! Having backups failing and let them fail is just absolutely horrifying!

Another event that happenned to another client was a major sql injection and a point-in-time restore saved their entire business!

Of course performance tuning is key for having the website running but backup regime, High availability strategy and overall scalability makes your business still run in case of hardware failure or other exceptional failure!

Do not discount the database level!
Childrens Birthday Parties

Childrens Birthday Parties wrote on 12/26/10 11:16 PM

oh, honey, I am so sorry you are going through this. someday your mom will have so much remorse. what you describe is more common than you think. Just invite your mom and brothers out or go see them when your stepdad is at work.I wish things would get better for you. I know your mom loves you. sometimes moms just try to do the right thing and end up making the wrong choice.
dublin stag weekends

dublin stag weekends wrote on 03/30/11 4:41 AM

corporate events and chauffeur driven nights out on the town, providing professional, courteous and discreet drivers who will go the extra mile with a smile to assist you. Wait & Return trips are also part of our regular service we provide to our clients when needed
référencer son site google

référencer son site google wrote on 10/29/12 10:36 AM

Genial ! Je partage ca sur facebook. J'ai vraiment apprecié de lire cette article. Bonne journée

référencement google gratuit http://agencewebmarketingales.wordpress.com référencement web gratuit
glcaol

glcaol wrote on 10/22/13 9:08 AM

The blog is good.i recommend a great deal. These proceed through intensive study as well as growth to improve overall performance http://www.ronaldocleats.com characteristics which are good to gamers. Adidas, Nike, The puma company along with Umbro are the hottest and a lot donned cristiano ronaldo mercurial boot makes. The best measurement on your foot provides you with the best outstanding overall performance within a game. For most ages now, Chaussure de foot Nike Hypervenom may be famous for producing many different sneaker items that offers provide many players worldwide in several sports http://www.lionelmessif50.com as well as games. In almost any sport, Nike firm constantly make an effort to generate a particular footwear product which can be fitting to a certain sports activity variety. Thus, because presented within the Lionel Messi ads as well as used by way of a web host http://www.chaussurefootronaldo.com involving renowned people just like Gareth Bale, Jesse Silva, Luis Suarez as well as Pavel Pogrebnyak.This is exactly what Nike has developed for Ronaldo brand new footwear. Nonetheless, seems to be and all sorts of set aside. These shoes are produced from excellent unprocessed trash and are engineering based.A player must consider special care when picking her or his boots since it is critical Chaussure de foot Nike Hypervenom that it suits them personal, that means body along with really feel from the start. These boots need to suit the players?ft . properly, using the shape of the particular base into mind.
xihcang

xihcang wrote on 05/20/14 11:16 AM

In the course of every Main competition or even opposition http://www.chaussurefootronaldo.com such as the Entire world Pot, individuals are unable to support yet put on their own spikes http://www.lionelmessif50.com
together with entire satisfaction along with eagerness. The particular shoes http://www.chaussuresfootacc.com that will Nike manufacturer generates spring from an intensive investigation.
برامج

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

nice post
برنامج الاذان

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

Truly decorous Internet performer entirely, an appropriate confidential alternative determine a trend numerous processs earlier watching your protected perpetrate.
مواقيت الصلاه

مواقيت الصلاه wrote on 07/19/16 11:35 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.
ماسنجر

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

extensive load testing of Zeus as yet but will be doing so

Write your comment



(it will not be displayed)



Leave this field empty: