June 27, 2017

Poorly Tuned Databases Exact a Heavy Toll: 3 MariaDB Tuning Tips

Optimizing your database performance is important, but it often falls by the wayside in the bustle of daily business. How big a deal is it?

Let’s look at potential consequences of a poorly tuned database:

Bad user experience: Obviously you never want customers to complain of bad performance from your application. That’s the first step toward…

Lost sales: Customers may walk away when your application is just too slow. But it gets worse. You’re more likely to run into downtime when your database is overloaded, and if the server that’s down is an ordering or credit card–processing system, you’re guaranteed to forfeit sales. That money talk leads us to…

Unnecessary spending: You can spend more money on hardware (RAM, etc.) to speed up your system, or you can simply set up your database to run efficiently.

You can avoid these unwanted outcomes! Here’s a quick look at three common issues, with easy-to-address tuning solutions.

Problem: Poor overall database performance

Tuning fix: Control your swappiness!

Swapping is the exchange of data from real memory (RAM) to virtual memory (disk). MariaDB's algorithms assume memory is not swap, and they become highly inefficient if it is. The swappiness value ranges from 0 to 100; we recommend setting swappiness to 1 for MariaDB databases. Some sources advise setting it to 0, but keeping that 1 point of emergency swap available allows you some scope to kill runaway processes.

Problem: Slow query execution

Tuning fix: Dive into the InnoDB buffer pool

innodb_buffer_pool_size is the most important of all system variables. The buffer pool caches data and indexes, and you usually want it as large as possible so you keep that information in RAM, minimizing disk IO. You can set the buffer pool size to 70–80% of available memory on a dedicated database server. However, there are two major considerations:

  • Total memory allocated is about 10% more than the value you specify, due to space being reserved for control structures and buffers.

  • If your buffer pool is too large, it’ll cause swapping—which more than offsets the benefit of a large buffer pool.

Good news: Starting with MariaDB Server 10.2.2, you can set the InnoDB buffer pool size dynamically.

Problem: “Too many connections” error

Tuning fix: Check your max_connections server variable

Having excessive connections can cause high RAM usage and make your server lock up. Getting a too_many_connections error may be a symptom of slow queries and other bottlenecks, but if you’ve ruled those out, you can fix the issue by increasing the max_connections value. (To gauge your target number, check out the max_used_connections status variable to see the peak value.)

Performance tuning doesn’t stop with these three tips, of course. If you’re looking for more ways to whip your application into tip-top shape, watch the recorded database tuning and optimization webinar. We’ll cover available communication and monitoring tools, common bottlenecks and errors, best practices for sizing the InnoDB buffer pool and log files, and more.

About Amy Krishnamohan

Amy is a Director of Product Marketing at MariaDB. She has diverse experience across product marketing, marketing strategy and product management from enterprise software companies such as Teradata, SAP, Accenture, Cisco and Intuit. Amy holds a master’s degree in software management from Carnegie Mellon University.

Read all posts by Amy Krishnamohan