If you’re a MySQL DBA, you probably already know a thing or two about storage engines in MySQL. If you dug into MySQL or MariaDB here or there, you probably already know that both MySQL and MariaDB offers numerous storage engines (one of those is InnoDB, there also is MyISAM, MEMORY, CSV, etc.) – MySQL even has a storage engine called a “black hole”, no joke.
----------------------------------------------------------------------------------------------------------
If you’re a MySQL DBA, you probably already know a thing or two about storage engines in MySQL. If you dug into MySQL or MariaDB here or there, you probably already know that both MySQL and MariaDB offers numerous storage engines (one of those is InnoDB, there also is MyISAM, MEMORY, CSV, etc.) – MySQL even has a storage engine called a “black hole”, no joke.
Each of those storage engines hashave their own upsides, downsides, and nuances. Each of them needs specialized care. For example:
- - Use InnoDB if you are new to MySQL, if you want high performance and resilience against crashes, or if you have no idea what storage engine you should use.
- - Use MyISAM if the data you are working with is not very important, if you want your tables to have a small footprint on the drives that they are stored on, or the majority of the queries you are running are COUNT(*) queries.
- - Use CSV if the majority of data you are working with is in CSV format.
Complex? We think so too o-no worries: here’s where dbWatch can step in - dbWatch can monitor all of the most popular database instances ranging from SQL Server, Sybase, and Oracle to PostgreSQL and MySQL.
Install dbWatch, import your database instance, and dbWatch will handle the issues related to your MySQL storage engines. First off, expand the Availability section, and select the Engine Optimizer job.
As you can see, dbWatch will already tell you what settings your engine should use.
In order to make use of this engine optimizing job, right- click it and select Configure to configure it:
Set the default engine and what status type you want dbWatch to return when the wrong database engine is in use (0 returns OK, 1 returns a warning, and 2 returns an ALARM): by saying “wrong” in this case we mean that the engine should not be used because another database storage engine might be a better fit. How does dbWatch determine this? Great question.
Right- click the Engine Optimizer job once again – this time, click on Configure. You will be presented with a screen like so:
That’s a lot of information, isn’t it? Well, yes, it is, but don’t fret. We will explain everything you need to know in a second:
- This parameter returns information that tells you:
- - What is the default storage engine of your MySQL instance.
- - What storage engine your database instance should use (what is the recommended storage engine for your MySQL instance): the job does this by observing the relevant settings in your my.cnf file which houses all of the crucial settings relevant to your MySQL database instances.
- - What is the size of the recommended parameters for your InnoDB instances – dbWatch will also tell this by looking at the my.cnf file that is available. We will perhaps get into more detail in the upcoming blog posts, but that’s the crux of it.
- - dbWatch also lists MySQL storage engine details (in other words, dbWatch lists the size of your database instance. Keep in mind that the size of your database instances is displayed in bytes, so if you want to know how many MB or GB your databases occupy on the disk, you will have to multiply the value).
Why dbWatch?
By now, you might have caught yourself thinking – “why do I need to know all of this? Moreover, why should I use your tool? I can calculate all of the relevant values myself, can’t I?”
Well, you can. It’s just that it would be an extremely time-consuming task. Imagine completing the following steps on top of everything else you might need to do to keep your business up and running:
- Locate my.cnf, figure out what it’s used for and why.
- - Know all of the settings relevant to your storage engine of choice.
- - Know what those settings can be made able to do.
- - Know how those settings can be optimized.
- - Know how optimizing such settings can impact your database performance now and in the future.
On top of completing all of those steps, you would also need to ensure that you adhere to best practices given by database (MySQL and MariaDB) engineers, and you would also need to ensure that the values you set are not going to break your MySQL instances.
Instead of completing all of those steps, you can make use of the dbWatch engine optimizing job. If you look closely, you will notice that the job provides you with all of the values your settings should be set to! In this case, dbWatch already provided us with the storage engine our MySQL instance should be running on, and it also provided us with all of its parameters. These parameters can be tuned by DBAs from all across the globe from Norway to Japan. All we need to do is open up my.cnf and set the appropriate settings to their respective values, then restart MySQL. Simple as that!
If you find yourself asking why the values that dbWatch gave us are so low (less than 1GB), that is the case because dbWatch is providing advice based on the size of your database instances. In other words, that means that the values given by dbWatch are directly dependent on the amount of data you have inside of your database instances – if we would run, say, a database instance of terabytes in size, the values of the parameters given by dbWatch would be higher.
You might find yourself asking what should you do in such a scenario: the answer is pretty simple – since dbWatch provided us with values that are incredibly small, we should leave the values of the respective settings at their default and only change them if the advice given by dbWatch says opposite (or if the values given by dbWatch are significantly higher than the ones we are running on at present).
Summary
In this blog post, we have provided you with a very brief overview of how you can optimize your MySQL storage engines by using dbWatch. In the upcoming blog posts, we will perhaps dig into more detail explaining why exactly doing so might benefit both your database servers and your business at once, but that’s it for now.
When optimizing your database instances with dbWatch remember the advice above. and you can be sure that your MySQL database instances will soon be on a different path destined towards high availability and high performance.
Should you want to learn more, look through the dbWatch blog, and if you have any questions, feel free to contact the team – they will be glad to help you out in any scenario.
--------------------
Other Blogs:
FSQL Server and Hard Drives with dbWatch
Breaking in as an Accidental DBA in the world of database administration
--------------------
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.
