Posted on

Migration to MySQL – What is it and why is it important?

MySQL is a relational database that backs many internet websites and enterprise applications.  Like all enterprise software, it has a whole complement of features which are well documented, such as data types, storage engines, transactional behaviors and so forth.  It also has a set of processes, many of which involve how software operates on Linux servers, such as how it gets installed, where binaries and libraries will get placed, where to find logfiles, and how to move directories and set permissions.  Thirdly it is important to understand the culture, in this case Unix-based, forum discussions and community contributions as an open-source project.

MySQL can do much of the workhorse kind of stuff you see in databases like Oracle or SQL Server, but sometimes it achieves those goals in very different ways.  For instance there are many open-source projects that support and surround the database, such as mysqltuner an analysis script, innotop a unix top-like utility for monitoring on-going activity in the database, and maatkit a whole suite of tools that build on and expand the features already present in the MySQL database.

Some Limitations in MySQL

  1. Complex queries and subqueries specifically can be problematic in MySQL.  If you’re used to writing huge queries in Oracle, and having the CBO figure everything out for you, you’ll be in for a surprise with MySQL.  Keep your queries simple, proper columns indexed and avoid complex joins where possible.  The EXPLAIN facility is available to you and at your disposal.  Use it!
  2. Vertical Scalability problems – primarily addressed in 5.5, the latest version of MySQL, previously the database did not scale well on greater than four processor boxes.  SMP or Symmetric Multiprocessing servers were less common 10-15 years ago when MySQL was in it’s infancy, and development is slowly catching up with the big iron of today.
  3. There is no flashback table, tablespace or database that you might find in other databases such as Oracle.  You can achieve the same thing with point-in-time recovery, so keep regular backups of your database, and also backup the transaction logs.
  4. MySQL can do JOINs, but only with the nested loops algorithm.  It can’t do sort merge join or hash join.
  5. MyISAM is the default table type and storage engine.  It is not crash safe and not transactional.  On new installations it’s recommended that you change this to InnoDB and use InnoDB for most if not all of your tables.  It’s very reliable and very fast!
  6. There is a query cache, but it caches result sets not query plans!  It also has some performance issues and shows some erratic behavior on larger SMP boxes.  Query plans are cached on a session basis, but when a session is closed and reopened, MySQL must reparse and reexecute that query.
  7. MySQL does not have a facility like Oracle’s Real Application Clusters.  It does have NDB Cluster which is an all-in-memory clustering solution.  Despite it’s promise, it tends to have very serious performance problems with any type of join, and is mainly good for single table index-based lookups.  If managed well it can increase availability but will probably reduce performance.
  8. MySQL’s default replication solution is statement based.  Although it is easy to setup, it breaks almost as easily, sometimes with resolvable errors, and sometimes silently.  Consider row-based replication, and definitely make use of Maatkit’s mk-table-checksum and mk-table-sync tools.  Also be sure to do thorough and regular monitoring of your replication setup.
  9. There are no in-built materialized views or snapshots in MySQL.  There is an open-source project called Flexviews by Justin Swanhart that provides this facility to the MySQL community.
  10. MySQL provides stored procedures, triggers and functions as a regular feature to the database.  However I would use them with caution.  They are very difficult to edit, troubleshoot and diagnose when they are causing troubles.  Also as with the query plan caching, stored procedures are cached at the session level, so they can be expensive to execute over and over again in different areas of your application.  They can cause real performance problems.
  11. There is no in-built mechanism for auditing that you find in relational databases such as Oracle 11g.
  12. Only b-tree indexes are supported, no bitmap indexes, index-organized tables, clustered indexes or other more exotic index types.
  13. ALTER TABLE is generally a locking and blocking operation.  For example if you add a new column or change a columns data type, the entire table will be locked for the duration of the operation.  This will be a surprise coming from the Oracle world where these type of operations can routinely be done online.

MySQL’s Strengths Are Numerous

  1. Install with an RPM using Yum or Aptget.  Fast & simple!
  2. Works great in the cloud, using MySQL Community distro, Percona distro, or Amazon’s own RDS solution.
  3. Comes out-of-the-box with an excellent command line shell providing all sorts of features and power that are constant frustrations on the Oracle side.  Command history, standard input/output redirection support, a full compliment of features and options, and easy autologin with a user level my.cnf file which fits in nicely with the global settings as well.
  4. A simpler mechanism to serve unique id columns with the auto-increment data type.  Although Oracle’s sequence method is extremely scalable, for many many developers it is troublesome and confusing.
  5. Good support of the LIMIT clause allowing an easier method for developers to fetch a subset of data.
  6. A huge community of users, forums, and support in third party applications such as monitoring (Nagios etc…) as well as metrics collection (Munin, Cacti, OpenNMS, Ganglia etc.)
  7. Great visibility of system variables with SHOW VARIABLES.  Many can be changed dynamically as well, just like Oracle.
  8. Great visibility of internal system state with SHOW PROCESSLIST.
  9. System counters for all sorts of internal instrumentation data using SHOW STATUS and SHOW INNODB STATUS.  Ultimately it is not as comprehensive as Oracle’s own data dictionary and millions of instrumentation counts.  However Oracle could take a huge page out of the MySQL book in terms of usability.  The obfuscation of Oracle’s internal kernel state makes it all but unusable by most.
  10. innotop, the utility much like the unix TOP facility that all Unix & Linux folks love, it provides instant visibility into what queries are running, what work is being done, and what is blocking.  Oracle could really take a page from this playbook, as this tool is so invaluable.
  11. The incredible Maatkit, a veritable goldmine of great community contributed powertools.  Query analyzers, profilers, log tools, replication tools, data archiver, a find facility, and a whole lot more!

Sean Hull discusses further on Quora – What considerations are important when migrating to MySQL?

Posted on

Root Cause Analysis – What is it and why is it important?

Root Cause Analysis is the means to identify the ultimate source and cause of an outage.  When an outage occurs that causes serious downtime of a website, typically organizations are in crisis mode.  Urgency of resolution sometimes pushes aside due process, change management and general caution.  Root Cause Analysis attempts to as much as possible isolate logfiles, configurations, and the current state of systems for later analysis.

With traditional physical servers, physical hardware failure, operator error, or a security breach can cause outages.  Since you’re dealing with one physical machine, resolving that issue necessarily means moving around the things that broke.  So caution and later analysis must be balanced with the immediate problem resolution.

Another silver lining in cloud hosted solutions is around root cause analysis.  If a server was breached for example, that server can immediately be shutdown, while maintaining it’s current state as a disk or EBS snapshot.  A new server can then be fired up from a AMI image, then your server rebuilt from scripts or template and you’re back up and running.  Save the snapshot then for later analysis.

This could be used for analysis of operator error related outages as well.  Hardware failures are more expected and common in cloud hosted environments, so this should and really must push adoption of best practices around infrastructure, that is having scripts at hand that rebuild everything from bare metal.

More discussion of root cause analysis by Sean Hull on Quora.

Posted on

Offsite Backups – What are they and why are they important?

Backups are obviously an important part of any managed infrastructure deployment.  Computing systems are inherently fallible, through operator error or hardware failure.  Existing systems must be backed up, from configurations, software and media files, to the backend data store.

In a managed hosting environment or cloud hosting environment, it is convenient to use various filesystem snapshot technologies to perform backups of entire disk volumes in one go.  These are powerful, fast, reliable, and easy to execute.  In Amazon EC2 for example these EBS snapshots are stored on S3.  But what happens if your data center goes down – through network outage or power failure?  Or further what happens if S3 goes offline?  Similar failures can affect traditional managed hosting facilities as well.

This is where offsite backups come in handy.  You would the be able to rebuild your application stack and infrastructure despite your entire production servers being offline.  That’s peace of mind!  Offsite backups can come in many different flavors:

  • mysqldump of the entire database, performed daily and copied to alternate hosting facility
  • semi-synchronous replication slave to alternate datacenter or region
  • DRBD setup – distributed filesystem upon which your database runs
  • replicated copy of version control repository – housing software, documentation & configurations

Offsite backups can also be coupled with a frequent sync of the binlog files (transaction logs).  These in combination with your full database dump will allow you to perform point-in-time recovery to the exact point the outage began, further reducing potential data loss.

Offsite Backups – What are they – discussed on Quora by Sean Hull

Posted on

Database Replication – What is it and why is it important?

Database replication is a service for shipping changes to your database, off to a copy housed on another server, potentially even in another data center.

Each change to the records of information in your database or groups of them are organized into transactions.  These each get unique identifiers or change numbers.  Those groupings of changes are logged to transaction logs which are then moved across to the sibling database server, and applied there.

Oracle offers this facility in the form of Standby Database aka Dataguard.  Although it can be messy to setup, it is fairly bulletproof.  If you don’t receive any errors in your logfiles, you can rest assured that the data on your main or master database server looks exactly the same as what’s on your secondary server.  With MySQL the situation can be a bit more complicated.  MySQL replication was built based on statements, not changed data.  So those same statements or instructions (SQL statements of DML & DDL) get rerun on the secondary database server.   Some of the problems associated with this are:

  1. MySQL standby transactions may execute at different times than master
  2. Serialization is important to maintain order.  Transactions need to get applied in the same order as on the primary.  However all tables & storage engines are not necessarily transactional in MySQL.  This can cause troubles, and potentially break replication on the slave side.
  3. MySQL replication can fail silently, that is data can drift out of sync with the master without your knowing.

Caveats aside, MySQL replication is powerful, and quite fast.  With the right caution, and correct tools, it can be as robust and reliable as Oracle’s own standby database technology.

  • Use checksum tools like Maatkit to verify & compare tables on primary & secondary servers
  • Use rigorous monitoring to watch error logs, and checksum logs hourly if necessary.
  • Use Maatkit sync tool to resync tables if they get out of whack.
  • Be especially vigilant if you’re taking backups off the secondary server

Replication can also be a powerful high availability tool in your arsenal.  Setup as a master-master cluster, you can keep your application pointed to the “active” side or role while performing blocking operations on the inactive database server.  Since master-master cluster also known as circular replication applies changes to both databases, you’ll need to stop replication during the blocking operation.  Once it is completed on the inactive side, redirect the webservers & application to the database where you completed the operation, then reenable replication and watch the databases catch up with each other!

Quora discussions – What is database replication?

Posted on

Infrastructure Provisioning – What is it and why is it important?

In the old days…

You would have a closet in your startup company with a rack of computers.  Provisioning involved:

  1. Deciding on your architectural direction, what, where & how
  2. Ordering the new hardware
  3. Waiting weeks for the packages to arrive
  4. Setup the hardware, wire things together, power up
  5. Discover some component is missing, or failed and order replacement
  6. Wait longer…
  7. Finally get all the pieces setup
  8. Configure software components and go

Along came some industrious folks who realized power and data to your physical location wasn’t reliable.  So datacenters sprang up.  With data centers, most of the above steps didn’t change except between steps 3 & 4 you would send your engineers out to the datacenter location.  Trips back and forth ate up time and energy.

Then along came managed hosting.  Managed hosting saved companies a lot of headache, wasted man hours, and other resources.  They allowed your company to do more of what it does well, run the business, and less on managing hardware and infrastructure.  Provisioning now became:

  1. Decide on architecture direction
  2. Call hosting provider and talk to sales person
  3. Wait a day or two
  4. Setup & configure software components and go

Obviously this new state of affairs improved infrastructure provisioning dramatically.  It simplified the process and sped it up as well.  What’s more a managed hosting provider could keep spare parts and standard components on hand in much greater volume than a small firm.  That’s a big plus.  This evolution continued because it was a win-win for everyone.  The only downside was when engineers made mistakes, and finger pointing began.  But despite all of that, a managed hosting provider which does only that, can do it better, and more reliably than you can yourself.

So where are we in present day?  We are all either doing, or looking out cloud provisioning of infrastructure.  What’s cloud provisioning?  It is a complete paradigm shift, but along the same trajectory as what we’ve described above.  Now you removed all the waiting.  No waiting for sales team, or the ordering process.  That’s automatic.  No waiting for engineers to setup the servers, they’re already setup.  They are allocated by your software and scripts.  Even the setup and configuration of software components, Operating System and services to run on that server – all automatic.

This is such a dramatic shift, that we are still feeling the affects of it.  Traditional operations teams have little experience with this arrangement, and perhaps little trust in virtual servers.  Business units are also not used to handing the trigger to infrastructure spending over to ops teams or to scripts and software.

However the huge economic pressures continue to push firms to this new model, as well as new operational flexibility.  Gartner predicts this trend will only continue. The advantages of cloud infrastructure provisioning include:

  1. Metered payment – no huge outlay of cash for new infrastructure
  2. Infrastructure as a service – scripted components automate & reduced manual processes
  3. Devops – Manage infrastructure like code with version control and reproduceability
  4. Take unused capacity offline easily & save on those costs
  5. Disaster Recovery is free – reuse scripts to build standard components
  6. Easily meet seasonal traffic requirements – spinup additional servers instantly

On Quora Sean Hull asks – What is infrastructure provisioning and why is it important?

Posted on

Data Spot Checks – What are they and why are they important?

Loading data into a database whether it is Oracle or MySQL, involves passing a lot of hurdles.  If you’re working with CSV files, special characters may cause trouble, end of line characeters, very long lines, and so forth.

Once your data load is complete, spot checks involve basic checks to determine that all went well.  Counting the number of records which should have been loaded and comparing the table’s count before and after the load, would be one simple spot check.

These types of checks are a basic once over, as opposed to a more thorough and rigorous analysis of the data, for example calculating checksums, that can guarantee that a data load completed and all data is in good shape.

Quora discussion – What are data spot checks and why are they important?

Posted on

Relational Database – What is it and why is it important?

A relational database is the warehouse of your data.  Your crown jewels.  It’s your excel spreadsheet or filing cabinet writ large.  You use them everyday and may not know it.  Your smartphone stores it’s contacts in a relational database, most likely sqlite – the ever present but ever invisible embedded platform.  Your online bank at Citibank or Chase stores all your financial history, statements, contact info, personal data and so forth, all in a relational database.



Relational databases are:

  • organized around records
  • data points are columns in a table
  • relationships are enforced with constraints
  • indexing data brings hi-speed access
  • SQL is used to get data in and out of the db
  • triggers, views, stored procs & materialized views may also be supported

Need help? Check out my pricing page and also How daily notes are helping me work better with clients

Like excel, relational databases are organized around records.  A record is like a 3×5 card with a number of different data points on it.  Say you have 3×5 cards for your addressbook.  Each card holds one address, phone number, email, picture, notes and so forth.  By organizing things nicely on cards, and for each card predictable fields such as first name, last name, birthday etc, you can then search on those data points.  Want all the people in your addressbook with birthday of July 5th, no problem.

Related: What is a database migration & why is it important?

While relational databases have great advantages, they require a lot of work to get all of your information into neatly organized files.  What’s more the method for getting things into and out of them – SQL is a quirky and not very friendly language.  What’s more relational databases have trouble clustering, and scaling horizontally.  NOSql database have made some headway in these departments, but at costs to consistency and reliability of data.

Read: 5 Ways to get your data to Amazon Redshift warehouse database

As servers continue to get larger, it becomes rarer that a single web-facing database really needs more than one single server.  If it’s tuned right, that is.  Going forward and looking to the future, the landscape will probably continue to be populated by a mix of traditional relational databases, new nosql type databases, key-value stores, and other new technologies yet to be dreamed up.

Also: Are SQL databases dead?

Sean Hull asks on Quora – What is an rdbms and why are they important?

Posted on

Database Change Management – What is it and why is it important?

During the software development process, whether you’re cutting edge Agile developers, or traditional waterfall method folks, your code changes are periodically accompanied by database changes.  For instance tables have particular rows and columns.  When developers add new columns (ie fields on an index card), or create new tables, relationships, indexes or other database objects, all of these are lumped together as database changes.

Version control systems have brought great manageability gains to software projects, even ones involving only  a single developer.  That’s because they allow you to rewind to any savepoints, just like you can undo and redo changes in a word document.  With database changes, however the picture because more muddied.

Database Change Management Best Practices

  1. Require developers to include a roll forward and a roll back script with each set of schema changes.
  2. Check in those scripts into the version control system just like other software code.
  3. Be sure developers test the roll forward and roll backward operation on dev.
  4. Ensure that changes are documented, as well as possible side affects.

During deployment, operations folks and/or a DBA must still have their fingers on the trigger.  Some frameworks such as Ruby on Rails include Migration scripts.  Do not for example allow rollback scripts to run automatically.  This is a recipe for disaster.

  • Be sure to take a backup of the database before running any schema change scripts.
  • Consider application downtime if ALTERs or other operations may lock large tables.
  • Perform another backup following the schema changes
  • If database is particularly large you may isolate your backup to the schema or tables being altered.

Above all use common sense, and always second and/or third guess yourself.  Better to be safe than sorry when juggling your crown jewels.

Quora discussion by Sean Hull – What is database change management and why is it important?

Posted on

Business Continuity Planning – What is it and why is it important?

BCP or BCRP if you want to also include “resiliency” in the acronym, basically outlines planning for the worst.  In the old days you had a filing cabinet with documents, for example there might be a central government office which houses birth certificates or titles and deeds.  Perhaps a copy of documents is regularly created, and kept offsite in case of fire.

In the digital era, more and more companies have most of their assets in digital form.  So the lifespan of those digital copies, and the contents thereof must be protected.  Since running computing systems and data centers always involves operational risk, planning for the worst is the essence of business continuity planning.

For example you may have redundant switches, routers and networking cables, but what if the whole data center goes down?  Do you have offsite backups that you’re creating regularly?  When was the last time those backups were tested with a full application restore and fire drill?

Discussed by Sean Hull on Quora – What is BCP and why is it important?