About the Author:
Lukas Vileikis is an ethical hacker and a frequent conference speaker.
Since 2014, Lukas has found and responsibly disclosed security flaws in some of the most visited websites in Lithuania.
He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.
If you are a DBA and have ever worked with MySQL in particular, you have probably noticed some bottlenecks and issues in MySQL performance. In this blog post, we will go through some MySQL performance anti-patterns and tell you how can dbWatch help tackle those.
What are MySQL Performance Anti-Patterns?
Some of the MySQL performance anti-patterns might include:
- - Using wrong database engines (engines that do not improve performance) or using the suitable engines, but not optimizing them properly (using the relevant engines but not knowing what they are capable of)
- - Not monitoring the availability of your databases.
- - Not knowing what the capacity of your database instances is.
- - Not monitoring the performance of your database instances when performing specific tasks or tests (for example, when inserting large quantities of data, utilizing large amounts of SELECT queries or when ALTER TABLE statements on large tables are in use, etc.)
Now we will delve into those sins of MySQL DBAs deeper.
Incorrectly Using Database Engines
Choosing the wrong database engine can be detrimental to your business – incorrectly choosing a MySQL storage engine can be a matter of life and death as far as databases are concerned. For example, in MySQL you have a couple of choices:
- - InnoDB – the default storage engine since MySQL 5.5. InnoDB’s key advantages include supporting row-level locking and foreign keys; the engine is also widely known as the engine that follows the Atomicity Consistency Isolation Durability (ACID) model. The ACID model generally ensures that data stays consistent despite any kinds of power failures or other mishaps.
- - MyISAM – the default storage engine for MySQL versions up to 5.5. MyISAM is based on an Indexed Sequential Access Method (ISAM), an indexing method developed by IBM that allows to create, maintain, and manipulate data so that records can be retrieved sequentially or randomly by one or more keys. The engine is widely known to be extremely fast with COUNT(*) queries as it stores the row count in table metadata, but it does not support the aforementioned ACID model.
- - MEMORY – the MEMORY engine creates special-purpose tables with contents that are stored in memory.
- - CSV – the CSV storage engine in MySQL can be used when a need to treat comma-separated values as a table arises. The engine does not support indexes.
- - ARCHIVE – the ARCHIVE storage engine can be used when a need to store data in a very small footprint arises.
- - BLACKHOLE – this storage engine can find performance bottlenecks since it accepts data, but never stores it.
- - MERGE (MRG_MyISAM) – this storage engine is a collection of identical MyISAM tables.
- - FEDERATED – this MySQL storage engine can let you access data from a MySQL database without clustering or replication.
- - EXAMPLE – this MySQL storage engine can be used as an example of how to begin writing new MySQL engines.
In this case, to correctly choose a storage engine, make sure to evaluate your needs upfront and desire such an engine that meets all of your business needs. For most businesses, that would be InnoDB because InnoDB is the only engine in MySQL that supports the ACID model meaning that it’s a set of properties of database transactions that guarantees validity despite any kinds of errors or power outages, failures, or other mishaps. Plus, if you’re running MySQL, the chances are that you should be are using InnoDB. Even though MyISAM may frequently be one of the most popular choices, majority of its features that were previously were exclusive to it are now found in to InnoDB.
In this case, it would probably be a good idea to monitor your MySQL database instances using dbWatch – dbWatch has jobs that are specifically designed to monitor and improve your InnoDB or MyISAM performance. For example, here’s how the InnoDB buffer pool checking job looks like:
Bear in mind that all jobs can also be configured to meet your needs – this MySQL job isn’t an exception either. In this case, you can configure the hit ratio alarm and warning thresholds – configuring those thresholds will let you modify at what percentage of the buffer pool hit ratio dbWatch will provide an alarm or a warning:
The MyISAM job isn’t an exception, too – if you head over to the left side of dbWatch, you will see a Key buffer checking job listed under the Performance section:
This job can be easily configured as well – in this case; you have some more options to change – you can change the buffer utilization and read ratio thresholds or the data retention period (in days) – this job can be useful if you want dbWatch to provide an alarm if the buffer utilization or read ratio exceeds specified values:
Not Monitoring the Availability and Performance of Your Databases
If you elect not to monitor the availability and performance of your MySQL database instances, you could very well be in line for trouble too. To ensure that your database instances are always available, you could use the dbWatch jobs in the Availability category. The Availability category has three jobs listed:
These jobs could help you be alarmed if, for example, your connections go awry – for example, if there are too many aborted connections or if you exceed the maximum allocated connections.
As far as the performance of your MySQL database instances is concerned, you do have more options:
dbWatch has specific jobs to help you monitor a load of your databases. You are sure that your databases are in tip-top shape. As discussed earlier, you can watch the performance of your MyISAM or InnoDB engines (the dbWatch blog has some older blogs related to this, so if you’re interested, do check it out). As you can see above, dbWatch also has a Collation check job which means that you can check whether all of the collations you use in your MySQL instance are compatible with each other or not. Checking out your thread cache hit rate with dbWatch might also set you (and your MySQL instances) on a good path.
As far as MySQL performance is concerned, dbWatch can help you with a whole host of different things Monitoring availability, capacity, and performance of your MySQL instances with dbWatch, you won’t fall prey to any kinds of MySQL performance anti-patterns.