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
- innodb_flush_log_at_trx_commit != 1 – speed changes spectacularly if it’s !=1
- open_files_limit = 50000
● 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
• 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
- Value for propensity of the OS to swap to disk
- Defaults are usually 60 ○ Commonly set low to 10 or so (not 0)
- 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
- 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 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
- 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
- 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
- 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
- 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;
- 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
- 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.
- 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
||An all-in-one performance metric.
||When load is > factor x (number of cores). Our suggested factor is 4.
||A high CPU usage is not a bad thing as long as you don’t reach the limit.
||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.
||Swap is for emergencies only, and it should not be used.
||When used swap is > 128MB.
||Unless doing backups or transferring huge amounts of data, it shouldn’t be the bottleneck.
||Make sure you always have free space for new data, temporary files, snapshot or backups.
||When database, logs and temp is > 85% usage.
||IOPS (Input/Output operations per second)
|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.
|Average IO wait
||Time that queue operations have to wait for disk access.
|Average Read/Write time
||Time it takes to finish disk access operations (latency).
||Data transfer from and towards your disk.
||Seconds since the server was started. We can use this to detect respawns.
||When uptime is < 180.
||Number of clients currently connected. If none or too high, something is wrong.
||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%.
||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
||Are there any errors on the mysql.log file?
|(Log files size)
||Are all log files being rotated?
|(Deleted log files)
||Were any log files deleted but the file descriptor is still open?
||Do you have enough disk space for backups?
Monitoring caches, buffers & locks
||Number of times InnoDB had to wait before locking a row.
||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.
||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.
|(Long running transactions)
||Tracks whether too many transactions are locked by other idle transactions, or because of a problem in InnoDB.
||Deadlocks happen when 2 transactions mutually hold. These are unavoidable in InnoDB and apps should deal with them.
||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.
||Number of full joins needed to answer queries. If too high, improve your indexing or database schema.
||Number of temporary tables (typically for joins) stored on slow spinning disks, instead of faster
|(Full table scans)
|Number of times the system reads the first row of a table index. Sequential reads might indicate a faulty index.
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.
- 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
EXPLAIN SELECT * FROM employees WHERE MONTH(birth_date) = 8 G
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
- 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
- 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
- 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
- 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
- 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