• Home
  • Services
    • DBA Support
    • DBA Consultancy Services
    • PostgreSQL Support
    • Website Maintenance
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum Database

    Greenplum Database

    $2,000.00 $1,500.00
    Read More
  • Company
    • FAQs
    • About Us
    • Contact
  • Events
  • Portfolio
  • Blogs
    • Blog – RayaFeeL
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
    • Blog – Medical Coding
      • Cart

        0

    Have any question?
    (+91)8838953252
    ITsupport@rayafeel.com
    RegisterLogin
    RayaFeeL
    • Home
    • Services
      • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
      • Website Maintenance
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum Database

      Greenplum Database

      $2,000.00 $1,500.00
      Read More
    • Company
      • FAQs
      • About Us
      • Contact
    • Events
    • Portfolio
    • Blogs
      • Blog – RayaFeeL
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
      • Blog – Medical Coding
        • Cart

          0

      Blog

      • Home
      • Blog
      • Blog
      • MariaDB Server Performance Tuning & Optimization

      MariaDB Server Performance Tuning & Optimization

      • Posted by Nijamutheen J
      • Categories Blog
      • Date March 26, 2019
      • Comments 0 comment

      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

      • Share:
      Admin bar avatar
      Nijamutheen J

      Nijamutheen J 7+ years of experience in PostgreSQL, Linux admin , web hosting - apache server , Oracle ,mySQL, Mriadb, vertica DB & Server security administrator

      My updated resume is https://www.tutorialdba.com/p/hi-i-have-4.html

      Previous post

      Amazon AWS Tutorial
      March 26, 2019

      Next post

      Nijamutheen Resume - For PostgreSQL ,Greenplum, Oracle, MySQL , Mariadb, MSSQL, AWS, Linux admin job
      April 20, 2019

      Leave A Reply Cancel reply

      You must be logged in to post a comment.

      Login with:

      Login with Google Login with Twitter Login with LinkedIn Login with Microsoft


      Search

      ADVERTISEMENT

      Latest Posts

      PostgreSQL Patching version 9, 10,11
      10Oct2019
      Tools for PostgreSQL
      16Sep2019
      Postgres user creation and restrict DDL & database access
      13Sep2019
      PostgreSQL SSL Setup
      07Sep2019
      How to DELETE current XLOG / WAL LOG in postgresql database ?
      19Aug2019

      Latest Courses

      PostgreSQL Database

      PostgreSQL Database

      $600.00 $500.00
      Greenplum Database

      Greenplum Database

      $2,000.00 $1,500.00

      Preview Course

      Free

      Recent Forum Topics

      • thought behind whiteboard activity
      • Are you going to take your first ste
      • How to start working on an application?
      • please let me know pre requirements to increase work_mem
      • how to copy some data in one table to another table in postgres

      2ndquadrant.in

      (+91) 8838953252

      ITsupport@rayafeel.com

      Company

      • About Us
      • Contact
      • Our Team
      • Blog

      COURSES

      • List Of Course
      • Become An Instructor
      • Events
      • Postgres Support Blog

      Support

      • DBA Support
      • Consultancy Services
      • Postgres Migration Blogs
      • Forum

      Recommend

      • Groups
      • Login
      • FAQs
      • SignUp

      IT Services by rayafeel.com. Powered by Rayafeel Technologies Pvt Ltd.

      • Privacy
      • Terms

      Become An Instructor?

      Join thousand of instructors and earn money hassle free!

      Get Started Now

      Login with:

      Login with Google Login with Twitter Login with LinkedIn Login with Microsoft

      Login with your site account

      Lost your password?

      Not a member yet? Register now

      Register a new account

      Are you a member? Login now