Need Help Optimizing 10.1.16-MariaDB

3awh

Well-Known Member
Sep 1, 2008
63
5
58
WWW
I used MySQLTuner 1.6.14 - Major Hayden
I have MySQL version 10.1.16-MariaDB
Server restarted Exactly 24 hours 1 minute ago
Server OS is CENTOS 6.8 x86_64 standard with Of Course Cpanel WHM 56.0 (build 28)


Here is the MySQLTuner Report

Code:
 >>  MySQLTuner 1.6.14 - 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.16-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: 580M (Tables: 1697)
[--] Data in InnoDB tables: 1G (Tables: 4037)
[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 605 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 0h 1m 33s (4M q [51.770 qps], 46K conn, TX: 37G, RX: 1G)
[--] Reads / Writes: 77% / 23%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 23.5G
[--] Max MySQL memory    : 21.8G
[--] Other process memory: 1.4G
[--] Total buffers: 2.7G global + 129.5M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 6.6G (28.32% of installed RAM)
[!!] Maximum possible memory usage: 21.8G (93.03% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/4M)
[OK] Highest usage of available connections: 20% (31/151)
[OK] Aborted connections: 0.03%  (13/46314)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache efficiency: 39.9% (2M cached / 5M selects)
[!!] Query cache prunes per day: 225437
[OK] Sorts requiring temporary tables: 0% (117 temp sorts / 127K sorts)
[!!] Joins performed without indexes: 872
[!!] Temporary tables created on disk: 68% (168K on disk / 246K total)
[!!] Table cache hit rate: 0% (128 open / 214K opened)
[OK] Open file limit used: 0% (51/13K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[OK] Binlog cache memory access: 99.92% ( 621360 Memory / 621886 Total)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 16 thread(s).
[--] Using default value is good enough for your version (10.1.16-MariaDB)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (97M used / 536M cache)
[OK] Key buffer size / total MyISAM indexes: 512.0M/101.4M
[OK] Read Key buffer hit rate: 96.2% (2M cached / 103K reads)
[!!] Write Key buffer hit rate: 50.4% (278K cached / 138K writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 96.7% (5M cached / 167K reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 1.5G/1.4G
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 40.87% (40181 used/ 98303 total)
[OK] InnoDB Read buffer efficiency: 99.98% (171893893 hits/ 171927388 total)
[!!] InnoDB Write Log efficiency: 59.17% (896411 hits/ 1514925 total)
[OK] InnoDB log waits: 0.00% (0 waits / 618514 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
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    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 (13000) variable
    should be greater than table_open_cache ( 128)
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 100M)
    join_buffer_size (> 128.0M, or always use indexes with joins)
    table_open_cache (> 128)
Here is MY.CNF

Code:
# The following options will be passed to all MariaDB clients
[client]
#password    = your_password
port        = 3306
socket        = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MariaDB server
[mysqld]
port        = 3306
socket        = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 512M
max_allowed_packet=268435456
table_open_cache = 128
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
query_cache_type=1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 100M
tmp_table_size = 500M
max_heap_table_size = 500M
join_buffer_size = 128M

default-storage-engine=InnoDB

# Point the following paths to different dedicated disks
#tmpdir        = /tmp/

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id    = 1

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1536M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_buffer_pool_instances=1

open_files_limit=13000
innodb_file_per_table=1
[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
I Adjusted setting the yesterday and this is the results and not sure what to do from here
table_open_cache (> 128) - I just adjusted that to 256 Not sure what to do with the rest.
thank You for your help in advance
Mitch
 
Last edited by a moderator:

3awh

Well-Known Member
Sep 1, 2008
63
5
58
WWW
The my.cnf page it gave me has - in stead of _ does it matter

tmp-table-size = 32M
instead of
tmp_table_size = 32M
 

3awh

Well-Known Member
Sep 1, 2008
63
5
58
WWW
I didn't take a chance and converted it to the underscore. I will try to find a contact or feedback form and let the site know. I hope the config was correct.
I did change two values thought they were too low this is what it gave me

Code:
# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
# Configuration name 3Aliens generated for EMAIL REMOVED at 2016-07-23 01:44:06

[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/mysql.pid

# MyISAM #
key_buffer_size                = 32M
myisam_recover                 = FORCE,BACKUP

# SAFETY #
max_allowed_packet             = 16M
max_connect_errors             = 1000000

# 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                 = 32M
max_heap_table_size            = 32M
query_cache_type               = 1
query_cache_size               = 8M
max_connections                = 500
thread_cache_size              = 50
open_files_limit               = 65535
table_definition_cache         = 4096
table_open_cache               = 128

# 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        = 3G

# LOGGING #
log_error                      = /var/lib/mysql/mysql-error.log
log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /var/lib/mysql/mysql-slow.log
Anything you see I don't have or should have from my original? Or I shouldn't have?
 

3awh

Well-Known Member
Sep 1, 2008
63
5
58
WWW
Had to put back my old my.cnf on first post Server load was too high with the new one, could not do anything so back to my original question can someone please help me tune the first posts my.cnf file
thanks
Mitch
 

Infopro

Well-Known Member
May 20, 2003
17,075
524
613
Pennsylvania
cPanel Access Level
Root Administrator
Twitter
please help me tune the first posts
How did you make out with the original recommendations you posted above?

Code:
-------- 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
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    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 (13000) variable
    should be greater than table_open_cache ( 128)
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 100M)
    join_buffer_size (> 128.0M, or always use indexes with joins)
    table_open_cache (> 128)
 

3awh

Well-Known Member
Sep 1, 2008
63
5
58
WWW
How did you make out with the original recommendations you posted above?

Code:
-------- 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
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    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 (13000) variable
    should be greater than table_open_cache ( 128)
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 100M)
    join_buffer_size (> 128.0M, or always use indexes with joins)
    table_open_cache (> 128)
It didn't run 24 hours I changed it to the tools.percona.com my.cnf and then before 24 hours was up the server had a heart attack and I reverted back to the one on the first post now its running with a low cpu. I did change the value of table_open_cache to 256
i will get back to you in 20 hours.

i just tested this is what it said
Code:
 >>  MySQLTuner 1.6.14 - 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.16-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: 187M (Tables: 646)
[--] Data in InnoDB tables: 1G (Tables: 3355)
[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 605 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 5h 13m 49s (841K q [44.692 qps], 9K conn, TX: 7G, RX: 490M)
[--] Reads / Writes: 85% / 15%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 23.5G
[--] Max MySQL memory    : 21.4G
[--] Other process memory: 858.6M
[--] Total buffers: 2.3G global + 129.5M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 6.7G (28.51% of installed RAM)
[!!] Maximum possible memory usage: 21.4G (91.07% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/841K)
[OK] Highest usage of available connections: 23% (35/151)
[OK] Aborted connections: 0.06%  (5/9027)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (11 temp sorts / 148K sorts)
[!!] Joins performed without indexes: 1144
[!!] Temporary tables created on disk: 78% (100K on disk / 128K total)
[!!] Table cache hit rate: 0% (64 open / 50K opened)
[OK] Open file limit used: 0% (12/13K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] Binlog cache memory access: 99.49% ( 67770 Memory / 68115 Total)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 16 thread(s).
[--] Using default value is good enough for your version (10.1.16-MariaDB)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (98M used / 536M cache)
[OK] Key buffer size / total MyISAM indexes: 512.0M/27.1M
[OK] Read Key buffer hit rate: 96.1% (887K cached / 34K reads)
[!!] Write Key buffer hit rate: 62.1% (63K cached / 24K writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[!!] Aria pagecache hit rate: 94.5% (1M cached / 100K reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 1.5G/1.2G
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 50.01% (49164 used/ 98303 total)
[OK] InnoDB Read buffer efficiency: 99.98% (108002814 hits/ 108025823 total)
[OK] InnoDB Write log efficiency: 93.23% (1024955 hits/ 1099413 total)
[!!] InnoDB log waits: 0.00% (1 waits / 74458 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:
    MySQL started within last 24 hours - recommendations may be inaccurate
    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 (13000) variable
    should be greater than table_open_cache ( 64)
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_type (=1)
    join_buffer_size (> 128.0M, or always use indexes with joins)
    tmp_table_size (> 128M)
    max_heap_table_size (> 128M)
    table_open_cache (> 64)
    innodb_log_buffer_size (>= 8M)
 

3awh

Well-Known Member
Sep 1, 2008
63
5
58
WWW
Forget my last post I just checked the my.cnf on the server because I remembered I changed
query_cache_type (=1) the other day.
I just put the correct one on the server.
I guess I will be back in 24 hours and let you know the results of the new config
Man I hate when I mess up
Mitch
 

3awh

Well-Known Member
Sep 1, 2008
63
5
58
WWW
1 Day 10 Mins and I'm back with the results

Code:
 >>  MySQLTuner 1.6.14 - 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.16-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: 189M (Tables: 646)
[--] Data in InnoDB tables: 1G (Tables: 3355)
[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 605 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 0h 10m 11s (4M q [50.769 qps], 47K conn, TX: 41G, RX: 1011M)
[--] Reads / Writes: 78% / 22%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 23.5G
[--] Max MySQL memory    : 21.8G
[--] Other process memory: 1.5G
[--] Total buffers: 2.7G global + 129.5M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 10.1G (42.88% of installed RAM)
[!!] Maximum possible memory usage: 21.8G (93.03% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/4M)
[OK] Highest usage of available connections: 38% (58/151)
[OK] Aborted connections: 0.06%  (27/47315)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache efficiency: 38.6% (2M cached / 5M selects)
[!!] Query cache prunes per day: 278028
[OK] Sorts requiring temporary tables: 0% (494 temp sorts / 147K sorts)
[!!] Joins performed without indexes: 799
[!!] Temporary tables created on disk: 72% (185K on disk / 254K total)
[!!] Table cache hit rate: 0% (256 open / 129K opened)
[OK] Open file limit used: 2% (362/13K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[OK] Binlog cache memory access: 99.81% ( 520382 Memory / 521349 Total)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 16 thread(s).
[--] Using default value is good enough for your version (10.1.16-MariaDB)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (97M used / 536M cache)
[OK] Key buffer size / total MyISAM indexes: 512.0M/27.4M
[OK] Read Key buffer hit rate: 97.7% (3M cached / 71K reads)
[!!] Write Key buffer hit rate: 53.9% (335K cached / 154K writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 96.1% (4M cached / 183K reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 1.5G/1.2G
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 52.01% (51124 used/ 98303 total)
[OK] InnoDB Read buffer efficiency: 99.99% (342038386 hits/ 342069239 total)
[!!] InnoDB Write Log efficiency: 64.98% (973560 hits/ 1498190 total)
[OK] InnoDB log waits: 0.00% (0 waits / 524630 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
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    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 (13000) variable
    should be greater than table_open_cache ( 256)
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 100M)
    join_buffer_size (> 128.0M, or always use indexes with joins)
    table_open_cache (> 256)
I have Searched or I should say I have tried to search for each item in the suggestion list and I get other peoples results and not any explanation on what to change.

EDITED: Did some changes

Don't know what to do with these
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


Temporary table size is already large - reduce result set size

I changed it to tmp_table_size = 300M


Not sure what to do with this
Reduce your SELECT DISTINCT queries without LIMIT clauses

Increase table_open_cache gradually to avoid file descriptor limits

change it from
table_open_cache = 256
to
table_open_cache = 300
Not sure what to do with the rest
query_cache_size (> 100M)
join_buffer_size (> 128.0M, or always use indexes with joins)

I have added logging Figured it was a good idea for optimizing
# LOGGING #
log_error = /var/lib/mysql/mysql-error.log
log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log
When it says
query_cache_size (> 100M)
does it want you to increase or decrease it?
I'm thinking > = Great then (increase it)?

thanks
Mitch
 
Last edited:

3awh

Well-Known Member
Sep 1, 2008
63
5
58
WWW
I know its only 22 hours but close to the 24 hrs and its the same recommendations What should I do? I'm at a Loss

Code:
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    MySQL started within last 24 hours - recommendations may be inaccurate
    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
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    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 (13000) variable
    should be greater than table_open_cache ( 300)
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 100M)
    join_buffer_size (> 128.0M, or always use indexes with joins)
    table_open_cache (> 300)
 

3awh

Well-Known Member
Sep 1, 2008
63
5
58
WWW
Its been up for 2d 7h 47m 29s
Same results
Code:
 >>  MySQLTuner 1.6.14 - 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.16-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: 208M (Tables: 646)
[--] Data in InnoDB tables: 1G (Tables: 3014)
[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 605 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2d 7h 47m 29s (15M q [79.427 qps], 106K conn, TX: 92G, RX: 4G)
[--] Reads / Writes: 48% / 52%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 23.5G
[--] Max MySQL memory    : 21.6G
[--] Other process memory: 929.8M
[--] Total buffers: 2.5G global + 129.5M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 7.3G (31.26% of installed RAM)
[!!] Maximum possible memory usage: 21.6G (92.20% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/15M)
[OK] Highest usage of available connections: 25% (38/151)
[OK] Aborted connections: 0.06%  (62/106809)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache efficiency: 39.1% (4M cached / 11M selects)
[!!] Query cache prunes per day: 237438
[OK] Sorts requiring temporary tables: 0% (224 temp sorts / 263K sorts)
[!!] Joins performed without indexes: 1949
[!!] Temporary tables created on disk: 71% (376K on disk / 526K total)
[!!] Table cache hit rate: 0% (300 open / 161K opened)
[OK] Open file limit used: 2% (304/13K)
[OK] Table locks acquired immediately: 99% (10M immediate / 10M locks)
[OK] Binlog cache memory access: 99.83% ( 1322804 Memory / 1325120 Total)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 16 thread(s).
[--] Using default value is good enough for your version (10.1.16-MariaDB)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (97M used / 536M cache)
[OK] Key buffer size / total MyISAM indexes: 512.0M/32.4M
[OK] Read Key buffer hit rate: 99.2% (55M cached / 446K reads)
[!!] Write Key buffer hit rate: 57.6% (9M cached / 4M writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 95.0% (7M cached / 374K reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 1.5G/1.1G
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 44.82% (44062 used/ 98303 total)
[OK] InnoDB Read buffer efficiency: 99.99% (503584056 hits/ 503614885 total)
[!!] InnoDB Write Log efficiency: 62.78% (2229213 hits/ 3550944 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1321731 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
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    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 (13000) variable
    should be greater than table_open_cache ( 300)
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 100M)
    join_buffer_size (> 128.0M, or always use indexes with joins)
    table_open_cache (> 300)