Thursday, 13 July 2017

Top 6 MySQL DBA Mistakes

To err is human, or so the saying goes.  Unfortunately, in the world of IT, innocuous mistakes made early on can lead to really daunting problems down the road. While you can never eliminate human error or bad decisions, there are steps that you can take to minimize the likelihood of finding yourself in a pickle due to a hasty decision arrived at in the spur of the moment or a mistake made early on in design.  In today’s article, we’ll go over a few of the most common DBA mistakes to avoid.  Some of these relate specifically to MySQL, while others may be applicable to any RDBMS.

1.Using the Wrong Storage Engine

MySQL has a number of database engines, but the two most common are MyISAM and InnoDB.  Prior to MySQL v5.5.5, the default engine was MyISAM.  Since then, it’s InnoDB.  The latter's strengths are the enforcing of foreign key constraints and transaction support.  That's where changes made by two or more operations are handled as a single unit of work, so that all of the changes are either applied, or reverted.

These two features are absent from the MyISAM engine. Another potential drawback of MyISAM is that DML statements obtain an exclusive lock on the table. All other operations must therefore wait until the current operation concludes. This can have a detrimental performance as usage grows.

This is not to say that you should simply accept the default InnoDB engine every time.  Benchmarks show that MyISAM tables beat every other engine for speed and use fewer system resources.  Hence, if you’re creating a fairly simple or test database or table, and don't require the features provided by InnoDB, then MyISAM should be up for consideration.

Oracle Database Tutorials and Materials, Database Certifications
Figure 1: Table Explorer in MySQL Workbench

2. Not Specifying or Using the Wrong CHARSET

Just as MySQL has a default engine type, there is also a default CHARSET, which defines how string data is encoded in the database. The default is “Latin1,” but this may not support all the characters you might want to use.  Developers in English speaking countries have a nasty habit of discounting internationalization considerations when designing their databases and applications.   Your best choice for supporting a wide variety of languages is UTF-8 encoding.  To utilize UTF-8 encoding on a table, add "DEFAULT CHARSET=utf8" right after the "ENGINE=xxx":

CREATE TABLE mywork.articles (
 title VARCHAR(255) NOT NULL,
 CHARSET = utf8;

3.Using the Wrong Data Type

MySQL offers a range of numeric, string, and time data types. In fact, the large number of data types can make choosing the right one a challenge.  I've seen people use varchars for almost every field, thinking that it will simplify things.  It won't.  There's a reason that MySQL recognizes so many different data types.  It might make database management easier, but MySQL will become a "dumb" data store and that may lead to problems.

Here a few considerations:
  • Using a CHAR(n) type can be better than a VARCHAR(n) if you’re always going to be storing n characters.
  • Specify UNSIGNED when you will only need to store positive numbers.
  • Using the correct size of an integer can allow MySQL to index columns faster. For instance, an INT(10) will index much faster than a BIGINT(20).

4. Selecting All Columns in Queries

The ubiquitous "SELECT * " may be quicker to write than selecting a bunch of columns but it can greatly increase the amount of data that the database server needs to return to your application, especially on really large core tables.

In most cases, you don’t need to use all columns in a query’s result set and would be better served by specifying just the column names that you will actually be referencing. This reduces not only the time to transmit the data, but also how much memory is required to store the query result set.

5. Neglecting to Perform Regularly Scheduled Backups

True story, I once recorded a CD with a very established producer whose backup practices put many IT shops too shame.  He was once haphazard towards backups, that is until he lost all of his work after a computer crash.  After that, he backed up each and every recording session to another hard drive, and to a DVD, which he stored down the street at another location.   

Although a rare occurrence, I have still been witness to many a loss of data due to malfunction or disaster.  So much can go wrong: databases can fail; hard drives can suddenly stop; servers can explode in a puff of smoke; Web hosts can go bankrupt.  Losing your MySQL data can be catastrophic to your business, so ensure you have automated backups or replication in place.

6. Incorrect Indexing

As a general rule of thumb, indexes should be applied to any column named in the WHERE clause your SELECT queries. By indexing a column, the database engine can find all the records that match much more quickly. Otherwise, the SQL engine has to look at every single record in the table to locate all the records you may be requesting. Indexed integer columns are faster that indexed character columns, so whenever possible, use integer columns.

When it comes to indexing, less is more as indexes are regenerated during every table INSERT or UPDATE operation. Therefore, you have to weigh a query’s retrieval speed versus DML (Data Manipulation Language) operations.  Your best bet is to only add indexes when necessary and leave other columns alone.