Strict Standards: Declaration of Walker_Page::start_lvl() should be compatible with Walker::start_lvl(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 576

Strict Standards: Declaration of Walker_Page::end_lvl() should be compatible with Walker::end_lvl(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 576

Strict Standards: Declaration of Walker_Page::start_el() should be compatible with Walker::start_el(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 576

Strict Standards: Declaration of Walker_Page::end_el() should be compatible with Walker::end_el(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 576

Strict Standards: Declaration of Walker_PageDropdown::start_el() should be compatible with Walker::start_el(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 593

Strict Standards: Declaration of Walker_Category::start_lvl() should be compatible with Walker::start_lvl(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 687

Strict Standards: Declaration of Walker_Category::end_lvl() should be compatible with Walker::end_lvl(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 687

Strict Standards: Declaration of Walker_Category::start_el() should be compatible with Walker::start_el(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 687

Strict Standards: Declaration of Walker_Category::end_el() should be compatible with Walker::end_el(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 687

Strict Standards: Declaration of Walker_CategoryDropdown::start_el() should be compatible with Walker::start_el(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 710

Strict Standards: Redefining already defined constructor for class wpdb in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/wp-db.php on line 58

Deprecated: Assigning the return value of new by reference is deprecated in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/cache.php on line 99

Strict Standards: Redefining already defined constructor for class WP_Object_Cache in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/cache.php on line 404

Deprecated: Assigning the return value of new by reference is deprecated in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/query.php on line 21

Deprecated: Assigning the return value of new by reference is deprecated in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/theme.php on line 576
MySQL
Apr 22

Once upon a year ago, when Sun Microsystems acquired MySQL, there were many bloggers who theorized that Big Red, who had a long-running, close partnership with Sun, was pulling some strings in this deal.  The people who endorsed the idea that Oracle couldn’t put the kibosh on MySQL without a PR headache (But Sun could), were dismissed as crazy, conspiracy-theory people.  My only surprise so far is the courteous lack of ‘I told you so’ popping up in the expert blogs.

So where do we go from here?  Oracle doesn’t have any experience in hardware.  If they keep most of Sun’s staffing, and continue to fund their innovation efforts, we may continue to see excellent products from them.  But will they retain their stellar brand identity?  Will they abandon the Sparc chip architecture and adopt x86?  It seems the best solution here looks more like a tightly coupled partnership rather than a merging of the two companies.

From Oracle’s whitepaper on the decision, MySQL’s fate seems a little less promising:

MySQL will be an addition to Oracle’s existing suite of database products, which already includes Oracle Database 11g, TimesTen, Berkeley DB open source database, and the open source transactional storage engine, InnoDB.

This doesn’t sound like Oracle is poised to grow MySQL and allow it to flourish.  At this time MySQL 6.0 is in Public Alpha, and has added the Falcon transactional engine as an advanced alternative to InnoDB, and SolidDB.  Looking at the architecture, this engine brings some industrial-grade caching, recovery, and long transaction support to MySQL.  Couple this with the real deal disaster recovery 6.0 is bringing to the table, and you have a free multi-platform database that rivals everything an Oracle database can offer outside of Enterprise Edition, and soundly trounces the latest Microsoft SQLServer offering.

But will Oracle put the resources toward MySQL, to allow it to be all it can be?  Personally, I don’t see it happening, but I hope I am very, very wrong.

Sid

Nov 15

GUIs are for look’n, the Command Line is for Doin’” – That is some of the best mentoring advice I have received or could give as a data storage professional, and it is true to this day!

GUIs (Graphical User Interfaces) have really made enterprise-class databases much more accessible, and have made viewing data and corralling vital stats wonderfully pleasant and simple. MySQL Enterprise Monitor and Oracle Enterprise Manager include some excellent, time-saving ‘advisers’ that simply tuning tasks as well. They have come a long way, and their utility is undeniable.

But, as a data storage professional, we are expected to be able to restore and return the system to and operational capacity when things go badly. Usually, this is where we need the skills to ‘pop open the hood’.

Just as a good drummer should be able to do whatever can be done with their hands with their feet, when they are behind their kit - A good DBA will be able to perform any action in the GUI, at the command-line as well. This is critically important because:

  • The GUI contains a subset of the CLI capabilities, utilities, and tools
  • The GUI is a separate piece of software, often with additional dependencies, that can break, while leaving the database up and available.

Remember, of all the duties a DBA is asked to perform, there is one that we must do correctly and effectively EVERY time - Data Recovery. Data loss is absolutely unacceptable. So, you must honestly ask yourself - If the database goes down, the GUI is unusable, and the data must be recovered, can I do it at the command line? If not, it should be your focus to develop that skill set immediately - Not being able to recover your company’s or client’s data because you couldn’t ‘point n’ click‘ your way through the process, your company can lose a fortune – And it will, most likely, cost you your job!

Oracle Enterprise Manager is a great example. It is extremely useful, but in my experience, extremely delicate. It cannot withstand being cloned or moved to a different server, and it can break with any ungraceful handling of its repository, inside the database. Chances are, if the database is in dire straits, EM will not be there.

Will you be ready?

Brian Fedorko

May 27

A planned installations always requires...  Plans!Designing the data structure

If there were a more crucial time for a Database Administrator to team with and guide the application developers, I can not think of one. Getting this first step as correct as possible will save rework ranging from an inordinate amount of time dedicated to tuning to total application overhaul. This translates into your company/client hemorrhaging thousands of hours/hundreds-of-thousands-of dollars of dollars of unnecessary spending… or saving that very same amount. This is what a Professional DBA brings to the table. But how do you know if you are doing it well?
You design the database for the data. It is ALWAYS about the data, and how the user interacts with the data. Requirements are a great place to start if they are well-written, but mapping out use cases with the developer and the user is simply the best way to go. By exhaustively examining all the use cases, your structure will practically write itself. A solid understanding of the use cases will tell you:

  • How transactional and dynamic your database will be
  • What data will be input, when, and how
  • Where relationships and data constraints need to be implemented
  • What data will be extracted and how it will be grouped
  • Where locking issues will manifest
  • What data may need special handling (HIPAA, SOX, DoD Sensitive, Privacy Act, etc.)

The use cases, combined with a bit of foresight and communications, you can determine if the data will need warehousing in the future, if the system will require inordinate scalability, and/or the necessity of alternate operational sites. Initially designing the data system for end-game use will help you evolve the system as it is developed, rather than bolting on solutions in an ad-hoc manner as the needs become critical.

Common Pitfalls to Avoid:

Over-Normalization: There is no shame in under-normalizing your database if you have a solid reason to skip some normalization opportunities. Commonly, you can improve performance and maintainability – And if your data will eventually be warehoused, it will need to be (sometimes greatly) denormalized. Being able to efficiently convert your transactional data storage structure into a warehoused structure, optimized for data mining and reporting truly requires a planned, engineered effort.

The Developer Mindset: An excellent developer with a focus on efficiency and optimization is careful to only create and use resources a long as is absolutely necessary. However, an excellent data structure must be extremely static. Creation and destruction of tables is not only a hallmark of suspect design, but also creates a host of security and auditing challenges.

Data Generation: Any data created for storage must be carefully and thoroughly scrutinized. Fields of created data, stored to increase application performance, can reduce the performance of the entire database. If this practice is prevalent enough, storage requirements can increase dramatically! I have seen very few instances where the data manipulation is not best handled during retrieval.

Incremental Primary Keys: Iterative ID fields (‘Auto-Number’) in transactional tables must be avoided! Not only does it compromise our goal of not creating or destroying stored data, but it wreaks havoc on any sort of multi-master, bi-directional replication (ex. Oracle Streams, Advanced Replication, etc.). For example, if two sites are being used to accept transactions, the chances are excellent that the sites will receive separate transactions at the same time. If both create their Primary Key from the last record, incremented by one, they will BOTH have the same ID and a collision will occur.

Sure, you could design logic to constantly monitor for this issue, and gain additional overhead. I’ve also seen the transactions staggered by ‘odds and evens’. But what happens when you add an additional site? Your scalability is inherently limited.

There are very few instances where a natural key cannot be drawn from existing data. Usually, a timestamp combined with 1 or 2 data fields (ex. PRODUCT_ID, LOCATION, SSN - if protected, etc.) will produce an excellent, unique key. In the very RARE cases that it is impossible to generate a unique natural key, the Universal/Global Unique Identifier (UUID/GUID) is a viable alternative. All major databases support the generation of this ID, based on Timestamp, MAC address, MD5 Hash, SHA-1 Hash, and/or Random numbers depending on the version used. Given that there are 3.4 × 10^38 combinations, it is unlikely that you’ll run out. Ever. Every major DBMS has a utility to generate a UUID/GUID - SYS_GUID() in Oracle, UUID() in MySQL, and NEWID() in TSQL. There are also implementations for creating the UUID/GUID in C, Ruby, PhP, Perl, Java, etc.

This is just a light touch of creating a solid, production-grade data structure, but it is a good start. We’ll have plenty of room to explore some additional facets and expand on some of the items mentioned in further articles. Always remember, a good DBA must synergize with the development team, bringing different mindsets with distinct goals together to provide a robust, efficient solution

Brian Fedorko