Optimization 10.1.17-MariaDB with MySQLTuner 1.6.18

3awh

Active Member
Sep 1, 2008
35
1
58
WWW
Please help not sure how to move on from here seems like it says the same thing each time.
I have adjusted a lot and let it run for a long while on this result so it can get a better report, and here is the results of MySQLTuner 1.6.18


Code:
 >>  MySQLTuner 1.6.18 - Major Hayden <[email protected]>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.1.17-MariaDB
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 191M (Tables: 581)
[--] Data in InnoDB tables: 1G (Tables: 3048)
[OK] Total fragmented tables: 0
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 612 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 12d 6h 30m 38s (116M q [109.754 qps], 802K conn, TX: 527G, RX: 32G)
[--] Reads / Writes: 75% / 25%
[--] Binary logging is disabled
[--] Physical Memory     : 31.3G
[--] Max MySQL memory    : 27.8G
[--] Other process memory: 1.5G
[--] Total buffers: 26.4G global + 2.9M per thread (500 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 26.7G (85.25% of installed RAM)
[!!] Maximum possible memory usage: 27.8G (88.94% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 2% (2M/116M)
[OK] Highest usage of available connections: 18% (93/500)
[OK] Aborted connections: 0.06%  (513/802359)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache efficiency: 43.4% (62M cached / 144M selects)
[!!] Query cache prunes per day: 497764
[OK] Sorts requiring temporary tables: 0% (54 temp sorts / 3M sorts)
[!!] Joins performed without indexes: 11737
[!!] Temporary tables created on disk: 63% (4M on disk / 6M total)
[OK] Thread cache hit rate: 99% (133 created / 802K connections)
[!!] Table cache hit rate: 0% (300 open / 743K opened)
[OK] Open file limit used: 1% (141/10K)
[OK] Table locks acquired immediately: 99% (56M immediate / 56M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 8 thread(s).
[--] Using default value is good enough for your version (10.1.17-MariaDB)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 20.0% (6M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/32.5M
[OK] Read Key buffer hit rate: 98.8% (133M cached / 1M reads)
[!!] Write Key buffer hit rate: 56.7% (21M cached / 9M writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 97.0% (142M cached / 4M reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 26.0G/1.3G
[!!] InnoDB buffer pool instances: 8
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (3825353586 hits/ 3825389008 total)
[!!] InnoDB Write Log efficiency: 45.77% (9506050 hits/ 20770097 total)
[OK] InnoDB log waits: 0.00% (0 waits / 11264047 writes)
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Reduce your overall MySQL memory footprint for system stability
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (10000) variable
    should be greater than table_open_cache ( 300)
Variables to adjust:
    query_cache_size (> 100M)
    join_buffer_size (> 256.0K, or always use indexes with joins)
    tmp_table_size (> 150M)
    max_heap_table_size (> 150M)
    table_open_cache (> 300)
    innodb_buffer_pool_instances(=26)
here is the my.cnf

Code:
# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
# Configuration name 3Aliens_New_SSD_Server generated for - Removed - at 2016-08-19 22:59:00

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/kc29.example.com.pid

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
sql-mode                       = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sysdate-is-now                 = 1
innodb                         = FORCE

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
#log-bin                        = /var/lib/mysql/mysql-bin
#expire-logs-days               = 14
#sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 150M
max-heap-table-size            = 150M
query-cache-type               = 1
query-cache-limit              = 256K
query-cache-min-res-unit       = 2k
query-cache-size               = 100M
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 300

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 26G

# LOGGING #
log-error                      = /var/log/mysql/mysql_error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/log/mysql/mysql_slow.log
max_allowed_packet=268435456
open_files_limit=10000
thanks
Mitch
 
Last edited by a moderator:

3awh

Active Member
Sep 1, 2008
35
1
58
WWW
Seriously!
I looked and really need some help on this as the other threads did not help
I bumped because my thread is being ignored
I guess I will have to hire someone thanks cPanel Sr. Product Evangelist
Great support here people

You know and another thing I looked at all the other threads and everyone else got fast support from cpanel support people and your first person replying to this thread and a support person at that denied helping

Seriously!
Have you read my thread where it says
# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
I looked and really need some help on this as the other threads did not help
I bumped because my thread is being ignored
Thanks for nothing
Unbelievable
and your a cPanel Sr. Product Evangelist
I'm done with Cpanel
 
Last edited by a moderator:

3awh

Active Member
Sep 1, 2008
35
1
58
WWW
I have explained it in my first post.
I have done adjustments to
query_cache_size (> 100M)
join_buffer_size (> 256.0K, or always use indexes with joins)
tmp_table_size (> 150M)
max_heap_table_size (> 150M)
table_open_cache (> 300)
innodb_buffer_pool_instances(=26)
and they keep saying the same thing
If you don't want to help me then stop replying to this thread