MariaDB Server Performance Tuning & Optimization

Tips for MariaDB Server performance tuning & optimization

• Agenda MariaDB Performance Tuning:
• Common Principles and Best Practices
• Server Hardware and OS
• MariaDB Configuration Settings
• Database Monitoring
• Query Tuning

Why Tune? The task of scalable server software is to maintain top performance for an increasing number of clients.

•  Make efficient use of server resources
• Best performance for users
• Avoid outages due to server slowness
• Capacity ○ Be Prepared for Application Development Requirements ○ Allow for Unexpected Traffic Spikes or Other Changes in Demand

Tune what? Initial recommended values for performance:

• transaction-isolation = READ-COMMITTED
• key_buffer – 128MB for every 1GB of RAM
• sort_buffer_size – 1MB for every 1GB of RAM
• read_buffer_size – 1MB for every 1GB of RAM
• read_rnd_buffer_size – 1MB for every 1GB of RAM
• thread_concurrency – is based on the number of CPUs so make it CPU*2
• thread-handling=pool-of-threads
• innodb_flush_log_at_trx_commit != 1 – speed changes spectacularly if it’s !=1
• open_files_limit = 50000

Tune what?

● Configuring threadpool

• Pool-of-threads, or threadpool, is a MariaDB feature that improves performance by pooling active threads together instead of the old one thread per client connection method, which does not scale well for typical web-based workloads with many short-running queries.

● Change innodb_flush_log_at_trx_commit to something !=1

• speed changes spectacularly if it is !=1

● The more memory available … the better

MariaDB performance parameters:

•  InnoDB file-per-table
•  InnoDB Buffer Pool Size
•  Disable Swappiness In MariaDB
•  Max Connections
• Thread Cache Size
• Disable MySQL DNS Lookups
• Query Cache Size
• Tmp Table Size & Max Heap Table Size
•  Slow Query Logs
•  Idle Connections

Tuning Routine – When to Tune

•  Use Monitoring Tool – MonYog

• Tune from Start of the Application Lifecycle

• Start Early to Ensure Schema is Well Constructed
• Test Queries on Real Data
• Watch for Bottlenecks
• Over Tuning without Production Data or Traffic is Counter Productive

• Conduct Periodic Reviews of Production Systems

• Watch for Schema, Query and Significant Changes
• Check Carefully New Application Features
• Monitor System Resources — Disk, Memory, Network, CPU

my.cnf configuration file

• Change one setting at a time…

• This is the only way to determine if a change is beneficial.

• Most settings can be changed at runtime with SET GLOBAL.

• It is very handy and it allows you to quickly revert the change if needed.
• To make the change permanent, you need to update the configuration file.

• If a change in the configuration is not visible even after a MariaDB restart…

• Did you use the correct configuration file?
• Did you put the setting in the right section?

• Normally the [mysqld] section for these settings

my.cnf configuration file

• The server refuses to start after a change:

• Did you use the correct units?
• For instance, innodb_buffer_pool_size should be set in bytes while max_connection is dimensionless.

• Do not allow duplicate settings in the configuration file.

• If you want to keep track of the changes, use version control.

• Don’t do naive math, like “my new server has 2x RAM, it’ll just make all the values 2x the previous ones”.

Performance Server Hardware and OS Tuning

Server Hardware

• One Service per Server is Ideal to Prevent Contention

• Have the database server be only a database server etc.

• More CPU Cores is generally Good

• More Disk is usually Better

• Large Datasets, Fast Disks are Ideal

• More RAM is usually Best

• Traffic Dependent
• More of Dataset in Memory, Fewer Slow Disk Operations

OS Settings Linux Settings

•Swappiness

• Value for propensity of the OS to swap to disk
•  Defaults are usually 60 ○ Commonly set low to 10 or so (not 0)

•Noatime

• Mount disks with this option ○ Turns off writing of access time to disk with every file access
• Without this option every read becomes an additional write

Performance MariaDB Configuration Settings

• innodb_buffer_pool_size
• The first setting to update
• The buffer pool is where data and indexes are cached
• Utilize memory for read operations rather than disk
• 80% RAM rule of thumb
• Typical values are
  •  5-6GB (8GB RAM)
  •  20-25GB (32GB RAM)
  • 100-120GB (128GB RAM)

query_cache_size

• Query cache is a well known bottleneck
• Consider setting query_cache_size = 0
• Use other ways to speed up read queries:
  • Good indexing
  • Adding replicas to spread the read load

innodb_log_file_size

•  Size of the redo logs – 25 to 50% of innodb_buffer_pool usually recommended
•  Redo logs are used to make sure writes are fast and durable and also during crash recovery
•  Larger log files can lead to slower recovery in the event of a server crash
•  But! Larger log files also reduce the number of checkpoints needed and reduce disk I/O

innodb_file_per_table

• Each .ibd file represents a tablespace of its own.
• Database operations such as “TRUNCATE” can be completed faster and you may also reclaim unused space when dropping or truncating a database table.
• Allows some of the database tables to be kept in separate storage device. This can greatly improve the I/O load on your disks.

Disable MySQL Reverse DNS Lookups

• MariaDB performs a DNS lookup of the user’s IP address and Hostname with connection
• The IP address is checked by resolving it to a host name. The hostname is then resolved to an IP to verify
• This allows DNS issues to cause delays
• You can disable and use IP addresses only
  • skip-name-resolve under [mysqld] in my.cnf

max_connections

• Too many connections’ error?
• Using a connection pool at the application level or a thread pool at the MariaDB level can help

Check for MySQL idle Connections

• Idle connections consume resources and should be interrupted or refreshed when possible.
• Idle connections are in “sleep” state and usually stay that way for long period of time.
•  To look for idled connections:
•  # mysqladmin processlist -u root -p | grep “Sleep”
•  You can check the code for the cause if many idled
•  You can also change the wait_timeout value

thread_cache_size

•  The thread_cache_size directive sets the amount of threads that your server should cache.
•  To find the thread cache hit rate, you can use the following technique:
  • show status like ‘Threads_created’;
  • show status like ‘Connections’;
•  calculate the thread cache hit rate percentage:
  • 100 – ((Threads_created / Connections) * 100)
• Dynamically set to a new value:
  • set global thread_cache_size = 16;

memory parameters

• MariaDB uses temporary tables when processing complex queries involving joins and sorting
• The default size of a temporary table is very small
  • The size is configured in your my.cnf: tmp-table-size = 1G max-heap-table-size = 1G
• Both should have the same size and will help prevent disk writes
•  A rule of thumb is giving 64Mb for every GB of RAM on the server

Buffer Sizes

•  join buffer size
  • used to process joins – but only full joins on which no keys are possible
•  sort buffer size
  •  Sort buffer size is used to sort data.
  • The system status variable sort_merge_passes will indicates need to increase
  • This variable should be as low as possible.
•  These buffers are allocated per connection and play a significant role in the performance of the system.

max_allowed_packet

•  MariaDB splits data into packets. Usually a single packet is considered a row that is sent to a client.
• The max_allowed_packet directive defines the maximum size of packet that can be sent.
• Setting this value too low can cause a query to stall and you will receive an error in your error log.
•  It is recommended to set the value to the size of your largest packet.
  • Some suggest 11 times the largest BLOB

Performance Monitoring Database Monitoring

System Metrics

Metric	Comments	Suggested Alert
Load	An all-in-one performance metric.	When load is > factor x (number of cores). Our suggested factor is 4.
CPU usage	A high CPU usage is not a bad thing as long as you don’t reach the limit.	None
Memory usage	Ideally your entire database should be stored in memory, but this is not always possible. Give MySQL as much as you can afford but leave enough for other processes to function.	None
Swap usage	Swap is for emergencies only, and it should not be used.	When used swap is > 128MB.
Network bandwidth	Unless doing backups or transferring huge amounts of data, it shouldn’t be the bottleneck.	None
Disk usage	Make sure you always have free space for new data, temporary files, snapshot or backups.	When database, logs and temp is > 85% usage.

Disk Monitoring

Metric	Comments	Suggested Alert
Read/Write requests	IOPS (Input/Output operations per second)	None
IO Queue length	Tracks how many operations are waiting for disk access. If a query hits the cache, it doesn’t create any disk operation. If a query doesn’t hit the cache (i.e. a miss), it will create multiple disk operations.	None
Average IO wait	Time that queue operations have to wait for disk access.	None
Average Read/Write time	Time it takes to finish disk access operations (latency).	None
Read/Write bandwidth	Data transfer from and towards your disk.	None

MariaDB Metrics

Metric	Comments	Suggested Alert
Uptime	Seconds since the server was started. We can use this to detect respawns.	When uptime is < 180.
Threads_connected	Number of clients currently connected. If none or too high, something is wrong.	None
Max_used_connections	Max number of connections at a time since server started. (max_used_connections / max_connections) indicates if you could run out soon of connection slots.	When connections usage is > 85%.
Aborted_connects	Number of failed connection attempts. When growing over a period of time either some credentials are wrong or we are being attacked.	When aborted connects/min > 3. (only on not public exposed servers, otherwise will generate noise)

MariaDB typical errors common failure points to keep an eye on

Metric	Comments	Suggested Alert
(Errors)	Are there any errors on the mysql.log file?	None
(Log files size)	Are all log files being rotated?	None
(Deleted log files)	Were any log files deleted but the file descriptor is still open?	None
(Backup space)	Do you have enough disk space for backups?	None

Monitoring caches, buffers & locks

Metric	Comments	Suggested Alert
Innodb_row_lock_waits	Number of times InnoDB had to wait before locking a row.	None
Innodb_buffer_pool_wait_free	Number of times InnoDB had to wait for memory pages to be flushed. If too high, innodb_buffer_pool_size is too small for current write load.	None
Open_tables	Number of tables currently open. If this is low and table_cache is high, we can reduce cache size. If opposite, we should increase it. If you increase table_cache you might have to increase available file descriptors for the mysql user.	None
(Long running transactions)	Tracks whether too many transactions are locked by other idle transactions, or because of a problem in InnoDB.	None
(Deadlocks)	Deadlocks happen when 2 transactions mutually hold. These are unavoidable in InnoDB and apps should deal with them.	None

Monitoring Queries

Metric	Comments	Suggested Alert
Slow_queries	Number of queries that took more than long_query_time seconds to execute. Slow queries generate excessive disk reads, memory and CPU usage. Check slow_query_log to find them.	None
Select_full_join	Number of full joins needed to answer queries. If too high, improve your indexing or database schema.	None
Created_tmp_disk_tables	Number of temporary tables (typically for joins) stored on slow spinning disks, instead of faster RAM.	None
(Full table scans) Handler_read%	Number of times the system reads the first row of a table index. Sequential reads might indicate a faulty index.	None

Performance Query Tuning

Enable Slow query Logs

Logging slow queries can help you determine issues with your database and help you debug them. This can be easily enabled by adding the following values in your my.cnf configuration file:

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

The first directive enables the logging of slow queries, while the second one tells MariaDB where to store the actual log file. Use long_query_time to define the amount of time that is considered long for a MariaDB query to be completed.

Query Analysis

•  Use the Slow Log to find Problem Queries
•  Use mysqldumpslow Utility for Manageable Reports
•  Use EXPLAIN to see how MariaDB Executes a Troublesome Query and if Indexes are Used
•  Use EXPLAIN EXTENDED and SHOW WARNINGS to see how MariaDB Rearranges a Query before

Execution

 EXPLAIN SELECT * FROM employees WHERE MONTH(birth_date) = 8 G
id: 1
select_type: SIMPLE
table: employees
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 299587
Extra: Using where

Query Tuning Overview

•  Try Not to Query Tune on Production Server
• Use Test Server with same Hardware and OS
•  Use Copy of Production Data Set
•  Query Frequency is as Important as Query Speed
  • Moderately Slow Queries are often a Bigger Problem than a Rarely Run Very Slow Query

Indexing

•  Indexes Improve Read Performance
• Without Index, MariaDB Must Read Every Row — Full Table Scan
• With Index, MariaDB can jump to Requested Rows
• Reduced I/O and Improving Performance
• Index Increase cost of Writes
• Find Balance
• Index for Speed, but Avoid Indexing Excessively or Arbitrarily
• Remove Dead or Redundant Indexes

Index Size

• Keep Indexes as Small as Practical
  • Faster since More Likely to Fit in Memory
  • Rebuilds Faster after Writes
  • PRIMARY KEY should be Minimum Useful Size
• Use Partial Prefix Indexes for String Columns
  • May Slow Searches Slightly, but Reduce Index Size
•  Use Index Cardinality (Uniqueness Measure) Only If Necessary — Re-evaluate as Data Grows
  • Low Cardinality Indicates many Duplicates
  • High Cardinality is More Useful

Tools & Statistics

• Identify Accurately and Carefully Trouble Spots
  • Guessing is Rarely Useful
• Gather Performance Stats with MariaDB and OS Tools
  • SHOW Statements
  • PERFORMANCE_SCHEMA
  • CPU, Disk, Network, Memory, & Swap Stats
•  Retain Snapshots of Multiple Stats
  • Data from a Single Point Shows very Little
•  Automate the Collection of Stats into Logs
  • Can be Useful for Emergency Tuning

SHOW PROCESSLIST

• Snapshot of mysqld Activity
•  mysqld is Multi-Threaded, One Thread per
  • Client Connection (i.e., query, transaction) — a “process” is a “thread”
• Accumulate SHOW PROCESSLIST Snapshots to build History of Thread Activities

SHOW STATUS Global or Session

• Returns List of Internal Counters
• GLOBAL for System-Wide Status — Since Start-Up
• SESSION for Local to Client Connection
•  FLUSH STATUS Resets Local Counters
• Monitor Changes to Counters to Identify Hot Spots
• Collect Periodically Status Snapshots to Profile Traffic

PERFORMANCE_SCHEMA

• Similar to INFORMATION_SCHEMA , but Performance Tuning
• Monitors MariaDB Server Events
• Function Calls, Operating System Waits, Internal Mutexes, I/O Calls
• Detailed Query Execution Stages (Parsing, Statistics, Sorting)
• Some Features Storage Engine Specific
•  Monitoring Lightweight and Requires No Dedicated Thread
•  Designed to be Used Iteratively with Successive Refinement