Optimization 10.1.17-MariaDB with MySQLTuner 1.6.18

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

 >>  MySQLTuner 1.6.18 - Major Hayden 
>>  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

# 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

Infopro

September 15, 2016 19:23

See if this thread is of some use to you: Optitmize MySQL Configuration to reduce CPU usage There are plenty of other similar threads you can use for suggestions already posted as well. No need to bump your thread for assistance.

3awh

September 15, 2016 19:59

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 (

September 15, 2016 20:32

You never mentioned what issues you were having that you really need help with. What purpose would suggesting changes for an unknown issue solve? The Recommendations section of your test result are more helpful than any suggestions from me.

September 15, 2016 20:38

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

cPanelMichael

September 22, 2016 15:08

and they keep saying the same thing

Hello, Did you restart MySQL after making those changes? If so, you may want to try a different MySQL tuning application, like the one on this thread: mysqlmymonlite.sh server stats gathering tool for cPanel Server Thank you.

Optimization 10.1.17-MariaDB with MySQLTuner 1.6.18

Comments

Didn't find what you were looking for?