Database tuning is an art, rather than a science. I speak as someone who’s worked as a professional DBA for over 15 years and is certified in multiple commercial RDBMS techologies. There is no set of formulas that can be presented that work in all cases, because the data sets and workloads are too variable. If we had a server with static data, unchanging server configuration, and known workloads, we could work out the ideal configuration…but if would only be useful in that one fixed scenario.

However, we can generalize some good techniques for RDBMS tuning and point out some high-level areas to look at.

Rule #1: Avoid Overtuning

There are tons of bottons, knobs, dials, switches, and configuration parameters. The vast majority do not need to changed. Do not change any parameters without a specific reason for doing so. The more obscure the parameter, the less likely that you need to change it. Also be careful to observe only tuning advice that is relevant to your particular version. Because things live forever on the Internet, there is advice out there for tuning MySQL 3.x and 4.x that would be completely inappropriate for MariaDB 10. For example, many parameters discussed in older tuning guids are MyISAM-related, and devoting memory to benefitting MyISAM tables is pointless if you’re using InnoDB tables.

Tune Your Code and Your Structure

The #1 thing you can do to improve database performance is to write good SQL and have a solid database structure. Both topics are far beyond what can be taught in a tutorial but some major areas to look at are:

1. Is the data you’re querying properly indexed? A single index can often vastly improve performance, because it’s the difference between going to a single address in a couple steps versus driving up and down the entire street.
2. Is your data over-indexed? If you index every single column, you’ll find inserting and updating data to be much slower because the engine has to maintain all those indexes.
3. Are you using bind variables? They reduce parsing work for the engine.
4. Have you profiled (EXPLAIN PLAN) your query? Look at the most expensive parts and see if you can rewrite them, then explain again, then rewrite, then explain…

As an illustration of the power of indexing, consider this table:

MariaDB [tester]> desc data;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  | MUL | NULL    |       |
| first_name | varchar(30) | YES  |     | NULL    |       |
| last_name  | varchar(30) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

Without any indexing this simple query takes .3 seconds (on a very busy MariaDB server) when there are 50,000 rows in the table:

select * from data where id = 2482;
real 0m0.305s

After creating an index and doing a similar query:

create index idx1 on data(id):

select * from data where id = 16159;
real 0m0.014s

An improvement of 21x! And that’s from a change that can be done online.

The Slow Query Log

Not sure which queries to attack? Turn on the slow query log by placing the following in your .my.cnf configs. MariaDB Will create a log of queries that that more than 1 second to complete.

slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
long_query_time = 1

You can also turn on logging for queries that don’t use indexes with the following .my.cnf directive:

log_queries_not_using_indexes=ON

innodb_buffer_pool_size

Buffer pool is for caching retrieved table and index data in memory. This is the “big one” and you want to tune it as high as your RAM will permit – as high as 60-80% of RAM. A cached query is much faster than a query the engine has to recompute.

Thread Pool Size and Connections

Your max_connections should be set to a reasonable peak. If you exceed this number, no one can connect, which definitely impacts performance :-)

The thread_pool_size parameter sizes the thread pool, as you might expect. When a thread is released (by a closed connection), it goes back into the thread pool for reuse. A reasonable number is 2x CPUs, but going above 16 is rarely necessary.

Temp Tables

MariaDB uses temp tables for sorting. If you have a big sort (ORDER BY, etc.) that exceeds the limit set for temp tables, then MariaDB may have to page to disk, which is much slower.

There are two parameters to consider. max_heap_table_size is the maximum size for any temporary table (either users using in-memory tables or the engine). tmp_table_size is the max temporary table size you can set, but it cannot be bigger than max_heap_table_size, so size them in unison. How big to make it? The dreaded “it depends” is applicable here. Set it as big as the likely peak of all sorted rows.

You can use use these status variables to see if you’re creating temp files on disk. If you are, you need more temp table space.

show status like 'Created_tmp_disk_tables' 
show status like 'Created_tmp_disk'

Reverse DNS

One easy way to improve performance is to turn off reverse DNS lookups. By default, MariaDB will do a reverse DNS lookup on the client’s IP for record-keeping, but you can disable this by putting this parameter in your .my.cnf files:

skip-name-resolve

• Author
• Recent Posts

raindog308

Dread Lord of LowEnd Content at LowEndBox

I'm raindog308, techno polymath and long-time LowEndTalk community Administrator. My technical interests include all things Unix, perl, python, golang, shell scripting, vintage operating systems such as MVS, and relational database systems such as Oracle, PostgreSQL, and MySQL.

I also enjoy using LEBs! I have an empire of little guys for VPNs, larger guys for apps, and big guys for databases and storage. When I'm not in front a screen I'm into German Shepherd dogs, quality knives, target shooting, theology, tabletop roleplaying games, and forest hiking.

I enjoy writing technical articles here on LowEndBox to help people get more out of their systems. You can find me on LowEndTalk @raindog308

Latest posts by raindog308 (see all)

• Merry Christmas from LowEndBox! - December 25, 2024
• We are Social Butterflies!Check Us Out Wherever You Browse, View, or Tap! - December 23, 2024
• Let’s Celebrate the Winter Solstice with Awesome Deals and a Free Bonus Code for RackNerd’s Giveaway! - December 22, 2024