TUNING: Taking Advantage of New Features

Wouldn't it be great if we always were able to design things from scratch? So many things could be re-hashed and re-designed. One of the real reason's we're (DBA's that is) frustrated all the time with poorly scalable products, is that we don't pro-actively encourage evolving architectures in a meaningful, planned way.

It's always those developers throwing things over the wall, or the feature requests from product managers - never giving the time to design it right the first time. There may be some thread of truth to those statements, but if we take advantage of those new features, bug fixes, maint windows, we could do a lot for ourselves in terms of performance tuning.

Here's a couple examples to get you started thinking:

A new feature:

1. Does it require new tables?
• Have we thought about partitioning should the data in those tables scale up?
• Have we thought about primary keys and which columns should or should not be included given usage and future modifications?
• Have we thought about foreign keys and associated indexes?
• Have we thought about normalization?
  
2. Is the new feature heavy read or heavy write?
• Should we be considering additional data files and/or data stores?
• Are there certain types of indexes that might be more prudent in the long term?
• How does this fit in with our HA / disaster recovery scenarios?
  
3. Does the new feature have any long term reporting benefits?
• Are changes easily monitored?
• Is the data easily transformed to dimensions / facts?
• Should there be some archival process?

Those are just a few questions that might be thought about and pondered when new features or bug fixes come across in our applications from a database perspective. Many times, we rely on developers who are under tight schedules and deadlines to think these things through, when they are really focusing on usability and functionality.

If we want to make our lives easier, we need to be the ones asking those questions, and in such a way that helps the development team see the benefit in making those decisions now, and not a year from now when we've added a new customer with 500,000 new transactions a day...

As DBA's we need to strive to get on the front end of the development cycles to make it as easy as possible for our development teams to take advantage of features and designs that will allow scalability and manageability with our databases - whatever those databases might be.

If you're not doing that, then in my opinion, you might as well outsource your database administration to some company that just gonna monitor alerts and backup your data....

MySQL: getting started for newbies

When MySQL first came out and started popping up all over the web in the e-commerce arena, if you were like me you looked at it as a wannabe. 'Call me in a few years when you grow up'. Yea, sometimes the Oracle in us goes to our heads a little to fast...

MySQL has really gone mainstream in the last few years. Companies that never would have considered MySQL in the past for critical database systems now have it on the forfront of their minds when evaluating different database technologies. Clustering, Replication, Monitoring are all well used now with MySQL and well tested.

Well, if I'm not using it now, how do I get on board?

Don't wait for your boss to evaluate it, that's for sure. Go get your MySQL VM set up on your laptop and stop thinking about it! It took me about 1/2 an hour to get the MySQL VM installed, up and running, and security settings all in place to try out the latest version of the MySQL Administrator up and running to play with.

Most likely you have various programs that are already using MySql as a backend - you just might not be maintaining them or playing with them because they are in your corporate environment instead of your production environment. This is the perfect oportunity to get started with MySQL.

Start by downloading the MySQL VM and get it runing on your PC or laptop.

Then download the MySQL Administrator and set up your security and connetions.

Next get the MySql Migration Toolkit and migrate one of your smaller SQLServer or Oracle databases to it.

Now, get familiar with the tools, features, backup and recovery, etc, etc. Try setting up replication; try out clustering, try out the partitioning....

Lastly, admit your Oracle snobbery and get on board :)

VMWare: The greatest thing since sliced bread

So if you like to try out new things, and you're not using VMWare yet, get with the program!

Really, though - it's so easy now (and the VMServer is free) . Just download and install.

VMWare lets you run mini virtualized server environments within your current operating system. Yea, there is a little overhead, but for testing and trying out new databases, configurations, applications, it's really cool.

The best part of it all, is the fact that you can download a host of pre-built applications to run with in the VMServer you just installed. Just startup the server and, wala... a running MYSql database in slackware on your Dell windows laptop. Yea, they have an Oracle 10g RAC appliance, as well as a SQLServer 2005 appliance.

Sweet. If you haven't checked it out, do so now...

SQLServer 2005: DMV's and determiniing fragmentation

Dynamic Management Views (which are not all views, some are stored procedures / functions) are very useful and if you're a DBA working with SQLServer, you should know them inside and out, and have you're list of scripts ready at hand.

Here's a couple that work well for determinining fragmentaion issues:

*Note: Though these can be done on line, the due use CPU and heavy READ I/O - be sure to know your application and how that affects performance, and carfully track performance while they are running the first few times. (Use the DMV options to control how much I/O is done - in the example we use the 'LIMITED' option)

SELECT  substring (b.name,1,40) + '.' +
    substring (c.name,1,50) AS Object,
    a.avg_fragmentation_in_percent AS fragmentation_pct
FROM    sys.dm_db_index_physical_stats
        (
              db_id ('{DBNAME}'),
              object_id ('{TABLE/OBJECT NAME}'),
              NULL,
              NULL,
              'LIMITED'
         ) a
JOIN    sysobjects b
    ON    a.object_id = b.id
LEFT OUTER JOIN sysindexes c
    ON    a.object_id = c.id
AND     a.index_id = c.indid