Optimal setting for mysql (mariadb optimization 10.5) in my.cnf (Centos 8)

Question

Here are my mysqltuner results:

 [--] Skipped version check for MySQLTuner script
    [!!] Successfully authenticated with no password - SECURITY RISK!
    [!!] Your MySQL version 10.5.0-MariaDB is EOL software!  Upgrade soon!
    [OK] Operating on 64-bit architecture

    -------- Log file Recommendations ------------------------------------------------------------------
    [!!] Log file  doesn't exist

    -------- Storage Engine Statistics -----------------------------------------------------------------
    [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
    [--] Data in InnoDB tables: 4.4G (Tables: 227)
    [OK] Total fragmented tables: 0

    -------- Analysis Performance Metrics --------------------------------------------------------------
    [--] innodb_stats_on_metadata: OFF
    [OK] No stat updates during querying INFORMATION_SCHEMA.

    -------- Security Recommendations ------------------------------------------------------------------
    [OK] There are no anonymous accounts for any database users
    [OK] All database users have passwords assigned
    [!!] There is no basic password file list!

    -------- CVE Security Recommendations --------------------------------------------------------------
    [--] Skipped due to --cvefile option undefined

    -------- Performance Metrics -----------------------------------------------------------------------
    [--] Up for: 50m 57s (911K q [298.114 qps], 2K conn, TX: 3G, RX: 104M)
    [--] Reads / Writes: 99% / 1%
    [--] Binary logging is disabled
    [--] Physical Memory     : 31.2G
    [--] Max MySQL memory    : 3.2G
    [--] Other process memory: 0B
    [--] Total buffers: 417.0M global + 18.9M per thread (151 max threads)
    [--] P_S Max memory usage: 0B
    [--] Galera GCache Max memory usage: 0B
    [OK] Maximum reached memory usage: 568.3M (1.78% of installed RAM)
    [OK] Maximum possible memory usage: 3.2G (10.24% of installed RAM)
    [OK] Overall possible memory usage with other process is compatible with memory available
    [OK] Slow queries: 0% (0/911K)
    [OK] Highest usage of available connections: 5% (8/151)
    [OK] Aborted connections: 0.00%  (0/2906)
    [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
    [!!] Query cache may be disabled by default due to mutex contention.
    [!!] Query cache efficiency: 0.0% (0 cached / 888K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (286 temp sorts / 50K sorts)
    [!!] Joins performed without indexes: 18
    [!!] Temporary tables created on disk: 81% (15K on disk / 18K total)
    [OK] Thread cache hit rate: 99% (8 created / 2K connections)
    [OK] Table cache hit rate: 93% (93 open / 99 opened)
    [OK] table_definition_cache(400) is upper than number of tables(391)
    [OK] Open file limit used: 0% (28/4K)
    [OK] Table locks acquired immediately: 100% (143 immediate / 143 locks)

    -------- Performance schema ------------------------------------------------------------------------
    [--] Performance schema is disabled.
    [--] Memory used by P_S: 0B
    [--] Sys schema isn't installed.

    -------- ThreadPool Metrics ------------------------------------------------------------------------
    [--] ThreadPool stat is enabled.
    [--] Thread Pool Size: 32 thread(s).
    [--] Using default value is good enough for your version (10.5.0-MariaDB)

    -------- MyISAM Metrics ----------------------------------------------------------------------------
    [!!] Key buffer used: 18.2% (24M used / 134M cache)
    [!!] Cannot calculate MyISAM index size - re-run script as root user

    -------- InnoDB Metrics ----------------------------------------------------------------------------
    [--] InnoDB is enabled.
    [--] InnoDB Thread Concurrency: 0
    [OK] InnoDB File per table is activated
    [!!] InnoDB buffer pool / data size: 128.0M/4.4G
    [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 96.0M * 1/128.0M should be equal to 25%
    [OK] InnoDB buffer pool instances: 1
    [--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
    [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
    [OK] InnoDB Read buffer efficiency: 95.00% (920105182 hits/ 968548737 total)
    [!!] InnoDB Write Log efficiency: 33.6% (1776 hits/ 5285 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 7061 writes)

    -------- AriaDB Metrics ----------------------------------------------------------------------------
    [--] AriaDB is enabled.
    [OK] Aria pagecache size / total Aria indexes: 128.0M/320.0K
    [OK] Aria pagecache hit rate: 98.6% (936K cached / 13K reads)

    -------- TokuDB Metrics ----------------------------------------------------------------------------
    [--] TokuDB is disabled.

    -------- XtraDB Metrics ----------------------------------------------------------------------------
    [--] XtraDB is disabled.

    -------- Galera Metrics ----------------------------------------------------------------------------
    [--] Galera is disabled.

    -------- Replication Metrics -----------------------------------------------------------------------
    [--] Galera Synchronous replication: NO
    [--] No replication slave(s) for this server.
    [--] Binlog format: MIXED
    [--] XA support enabled: ON
    [--] Semi synchronous replication Master: OFF
    [--] Semi synchronous replication Slave: OFF
    [--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
             (specially the conclusions at the bottom of the page).
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Performance schema should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
    Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: 

Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    performance_schema = ON enable PFS
    innodb_buffer_pool_size (>= 4.4G) if possible.
    innodb_log_file_size should be (=32M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

Who can optimize this what should i do ? to make things faster ?

I want to optimise for better CPU usage.

Server Specs: 16 core 32 threads 32 GB RAM

Need a optimal configuration for my mysql settings

Did you notice this line `Your MySQL version 10.5.0-MariaDB is EOL software! Upgrade soon!` — RiggsFolly, Jan 24 '20 at 13:03
yes but i stick with 10.5 maybe soon they will release a final and then i will update — Wed, Jan 24 '20 at 13:28
I don't understand why do you want to change the settings. It would be more interesting to know what is wrong with current configuration. Where are the bottlenecks? Is the overall performance too slow? — Georg Richter, Jan 25 '20 at 17:52
If you value your time, you will not use 10.5.0 which is clearly marked Alpha release on Dec 19, 2019 - unless you have all the time in the world to find and report bugs. From this url, https://mariadb.com/kb/en/release-notes-mariadb-104-series/ pick a version marked Stable at least 6 months ago to protect your investment of time. I would choose 10.4.7 to conserve my time. — Wilson Hauck, Jan 25 '20 at 20:35
@wilson hauck: 10.5.alpha means, that that all new features in 10.5 are in an alpha state. Everything which worked in 10.4 will also work also in 10.5. even if it's alpha. For that we have a huge test suite running frequently on every supported platform. — Georg Richter, Jan 25 '20 at 20:57
@GeorgRichter Does to old advice of do not use Alpha release for production have no meaning anymore? — Wilson Hauck, Jan 25 '20 at 20:59
@Wilson Hauck: It's of course a legal advice, not just a warning. The main problem is that an alpha version is running in a production environement. While there a lot of tools around for upgrading, you will not find any tool for downgrading. According to https://mariadb.com/kb/en/what-is-mariadb-105/ there are a lot of changes in InnoDB, which might cause some trouble when going back to an older stable version. — Georg Richter, Jan 25 '20 at 21:16

score 1 · Accepted Answer · answered Jan 30 '20 at 05:48

Set innodb_buffer_pool_size to about 70% of available RAM. The 4.4G suggested by mysqltuner will handle all your current data. If you expect it to grow, then give it more. This setting will probably help with I/O (not CPU).

(Actually "InnoDB Read buffer efficiency: 95.00% (920105182 hits/ 968548737 total)" says that the paultry 128M buffer_pool seems to handle the "working set" adequately.)

You cannot tune for "optimize for better CPU usage". You can find the slow queries and work on indexing (especially 'composite' indexes) and query formulation. Those will help with CPU.

http://mysql.rjweb.org/doc.php/mysql_analysis

score 0 · Answer 2 · answered Jan 25 '20 at 20:56

0

Suggestion for your my.cnf or my.ini [mysqld] section

innodb_buffer_pool_size=6G
thread_handling=pool-of-threads

After 24 hours of uptime get a new MySQLTuner complete report posted so we can see your progress, even with this Alpha release that is NOT to be used in production.

answered Jan 25 '20 at 20:56

Wilson Hauck

2,094
1
11
19

1

[mysqld] innodb_log_file_size = 2048M innodb_buffer_pool_size = 10240M innodb_buffer_pool_instances = 10 query_cache_size = 0 query_cache_type = 0 query_cache_limit = 1M tmp_table_size = 256M max_heap_table_size = 256M performance_schema = ON join_buffer_size = 1M max_connections = 10000 max_user_connections = 6000 thread_handling=pool-of-threads – Wed Jan 26 '20 at 02:45
@Wed FYI, overhead of 8 innodb_buffer_pool_instances is less than any greater number of instances. With 8 instances, innodb_lru_scan_depth should be 100 to conserve 90% of CPU cycles used for the function. Number of instances should be matched in innodb_page_cleaners and innodb_purge_threads when you have time. Looking forward to your 'new dated' MySQLTuner report AFTER your working day Monday. – Wilson Hauck Jan 26 '20 at 12:20
General recommendations: MySQL was started within the last 24 hours - recommendations may be inaccurate Reduce your overall MySQL memory footprint for system stability Dedicate this server to your database for highest performance. Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for Maria->did not add yet innodb_lru, ipc and ipt yet – Wed Jan 26 '20 at 12:25
@Wed MariaDB 10.3.10 had innodb_lru_scan_depth available. Another reason to avoid anything released less than 6 months ago - in the future. I hope you can continue your 10.5.0 journey - because there is no convenient way to go back. Installing Sys schema would be on my back burner at least until a Stable marked version that is at least 6 months old is available for download. – Wilson Hauck Jan 26 '20 at 12:30

Optimal setting for mysql (mariadb optimization 10.5) in my.cnf (Centos 8)

2 Answers2