Apologies to Rogers and Hammerstein; but the title fits.   In addition to listening to the scores of old musicals, one of my hobbies is watching the weather.  I have about 20 years of weather data in a MariaDB database - for those unfamiliar with OpenSource, MariaDB is a MySQL compatible OpenSource database offering. That database is getting pretty large, with over 700,000 rows in the “observation” table, and performance was starting to suffer.  The weather station reports it’s data about once every minute; but, it was sometimes taking more than a minute to complete the process of inserting that data into the database.   I’ve worked with Oracle databases for over 25 years; but am definitely “out of my depth” when it comes to tuning MariaDB.

Fortunately, I work for Quest, and we have a solution for DBAs who may be expert in one database but need to manage a “database of a different color”.  Foglight, with cartridges for many different databases, including Oracle, SQLServer, MySQL (and MariaDB), Cassandra, Mongo, and others, can help you be an expert in all of the databases in today’s heterogeneous environments.

Foglight to The Rescue

Since I already had Foglight running in my lab, downloading and installing the Foglight for MySQL Cartridge, written by our partners at Systems Management Associates, was quick and easy.   After following the Installation Guide, and a few quick configuration steps, I saw these alarms:

Identifying the problem

Drilling down (clicking) on the alert about Thread Pools, I saw this:

The full text of the advice offered this insight:

Thread Pool Hit Rate is 93.30%.

Each connection to the MySQL database server runs in its own thread. Thread creation takes time, so rather than killing the thread when a connection is closed, the server can keep the thread in its thread cache and use it for a new connection later.

Advice

Increase the thread_cache_size variable dynamically and monitor the thread cache hit ratio. When it reaches an acceptable level, put the corresponding value of thread_cache_size in your my.cnf/my.ini file so the variable is set properly when the server is restarted. The ideal situation is to get Threads_created as close as possible to thread_cache_size - no new connections having to wait for new thread allocation - staying as close to around a 99% thread cache hit ratio as you can.

The Solution

Looking at the application, I was able to determine that because it only connected to the database once every minute or so to insert new data, the connections to the database were timing out, and a NEW connection was being made each minute.  Adjusting the connection timeout parameter in MySQL took care of that issue and the time required to insert observations dropped to a few seconds. 

Next I took a look at the Query Cache, Key Buffer and Excessive Table Scans alerts.  It turns out that the weather application I’m running is mostly INSERTs and that in MariaDB, an INSERT will invalidate the Query Cache.  So, the best solution here was to turn off the Query Cache, which actually improved performance when the application generates new Web Pages, once every 5 minutes.  Now, instead of taking over 2 minutes, that process also runs in less than a minute.

Tuning and More – Become a Multi-Platform Expert

No matter if you’re a SQLServer DBA who has been asked to manage your organization’s mission-critical Oracle Database; or, like me, an Oracle DBA who needs to be “up-to-speed” on OpenSource alternatives, Quest and SMA can help provide you the insight you need to be successful.

Here are some additional screenshots from the MySQL cartridge.

Overview

Database Connections

Configuration Parameters