Certainly, the most coveted secret among DBAs, data analysts, developers, and everyone involved directly or indirectly with databases—especially MySQL/MariaDB—is: how to increase database performance. This is undoubtedly the Holy Grail of the modern world, which increasingly piles up stacks of information inside its databases.
Statistics from a respected European institute reached the following conclusion: “more than 40% of the data stored in databases will never be used for any purpose.” And, from my many years of experience, I can say that this is absolutely true. Great—part of the secret has already been revealed: Stop storing junk! Someone once said that in order to receive, you must let go of what you don’t use!
Like a true Mr. M of databases, I’m now going to share with you the secrets of Performance Tuning!
Access the MySQL/MariaDB client/console and run the following command:
mysql> SET GLOBAL MYSQL_SPEED = ‘FAST’;
By default, this variable is set to ‘SLOW’. The values you can configure are: ‘RIDICULOUS’, ‘SLOW’, ‘ANNOYING’, ‘MEDIUM’, ‘FAST’, and ‘GO-SAKILA-GO’. Simple, right?
Of course not. There’s no magic trick. I’ve read blogs and more blogs filled with paranoid configurations that cross the line into ridiculousness. “Truco! It’s 12, thief!” (a Brazilian card game expression meaning “no way!”) Every database has its own life, its own behavior, different workloads and usage patterns, running on different hardware. So why would there be a one-size-fits-all “Matrix-style” configuration that fixes everything with a single magic spell?
You can’t achieve optimal performance just by tweaking a few variable “settings.” There’s a whole set of factors that need to be considered. It’s the classic squeeze-this-squeeze-that approach, and in the end, you get something substantial. Some of these factors are obvious, while others require careful analysis to understand their contribution to achieving high performance.
The factors to be analyzed are as follows:
a) Hardware: When specifying requirements for new hardware, be realistic. Let your client (internal or external) know that databases love memory and fast disks. An alternative to purchasing is renting servers in the USA.
b) Operating System and File System: I know, I know… you’re more familiar with the dark side (despite the blue screens), but Linux won’t kill you. For database servers, prefer Unix-like operating systems. Besides many flavors, you’ll find a wide variety of file systems such as EXT3, EXT4, OCFS, ZFS, NTFS, HFS, among others. Oh! For fans of Freddy Krueger and Jason, there’s the excellent ReiserFS.
c) mysqld (Server) Configuration: The famous “variable setting.” This is a topic I’ll cover in another article. Basically, you need to understand: available resources (hardware), database workload, whether the database is OLTP or OLAP, among other factors. There is a set of variables that need to be properly adjusted to fit the “face of your operation.”
d) Database Modeling: My friend, not everything in life is just “CREATE TABLE something.” Properly modeling your database by applying normalization (NF) or denormalization, depending on each ENTITY/table, is fundamental for a performant database. Also, use the most appropriate and rational data types. Create functional indexes considering their usage, distribution, and selectivity.
e) SQL: Write rational and logical queries that always use indexes (or at least in the vast majority of cases). Make extensive use of EXPLAIN to understand how MySQL builds the execution plan (QEP).
f) Preventive Maintenance: Create a preventive maintenance plan to permanently remove deleted rows, reorganize indexes, and update their statistics.
g) Monitoring: Always monitor query execution times via the slow query log (SLOW LOG) so you can optimize queries and indexes. The database has a life of its own. The number of rows changes, affecting index distribution and selectivity, so a query efficient today might not be tomorrow.
In general terms, these are the points a DBA should be concerned with. Unfortunately, there is no magic or trick—what does exist is continuous work, with lots of blood, sweat, and tears.
Visit our Blog
Learn more about databases
Learn about monitoring with advanced tools
Have questions about our services? Visit our FAQ
Want to see how we’ve helped other companies? Check out what our clients say in these testimonials!
Discover the History of HTI Tecnologia
