Need Help Optimizing 10.1.16-MariaDB
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
Here is MY.CNF
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
>> MySQLTuner 1.6.14 - 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.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
# 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
-
Take a look at the Percona optimization wizard . This will generate configuration for MySQL, Maria, Percona, and works pretty well from what I've seen myself 0 -
Take a look at the Percona optimization wizard . This will generate configuration for MySQL, Maria, Percona, and works pretty well from what I've seen myself
Thanks It does say This configuration should not be used to fine-tune an existing server.
Thanks for your suggestion though I'm so close to optimizing my.cnf above. if anyone can help me with that please0 -
That's just a warning. I've used it to config an existing server before. Just make sure to copy your config file so you can revert to it if necessary. 0 -
The my.cnf page it gave me has - in stead of _ does it matter tmp-table-size = 32M instead of tmp_table_size = 32M 0 -
that should be tmp_table_size. Not sure why it gave you tmp-table-size, really odd 0 -
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 # 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?0 -
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 0 -
please help me tune the first posts
How did you make out with the original recommendations you posted above?-------- 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)0 -
How did you make out with the original recommendations you posted above?
-------- 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>> MySQLTuner 1.6.14 - 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.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)0 -
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 0 -
1 Day 10 Mins and I'm back with the results >> MySQLTuner 1.6.14 - 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.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 Mitch0 -
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 -------- 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)0 -
Its been up for 2d 7h 47m 29s Same results >> MySQLTuner 1.6.14 - 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.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)0
Please sign in to leave a comment.
Comments
13 comments