Tuning mysql. Mysqltuner recommendations

Question

Good afternoon. The site is located on VPS (10 GB RAM, 2 CPU core, 65 GB SSD). I want to improve the speed of the site. But I do not understand all the parameters of mysql. I wrote out the current mysql settings, as well as the mysqltuner recommendations. In this project, we do not allocate resources for analysis and modification in the code. I would like to get a speed gain through the settings. If possible. Thank you.

Current Mysql settings

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
log-error=/var/log/mysql.log
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
local-infile=0
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address    = 127.0.0.1

log-error   = /var/log/mysql/error.log

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
[mysqld]
key_buffer_size = 8388608
innodb_buffer_pool_size = 368M
query_cache_size = 32M
max_heap_table_size = 147M
tmp_table_size = 147M
join_buffer_size = 1887K
innodb_flush_log_at_trx_commit = 2
explicit_defaults_for_timestamp = 0
sql_mode=
query_cache_type = 1

bind-address = 127.0.0.1

Mysqltuner references

 >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.6.40
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 37M (Tables: 5)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[--] Data in InnoDB tables: 7G (Tables: 818)
[!!] Total fragmented tables: 79

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 37d 5h 54m 55s (190M q [59.283 qps], 3M conn, TX: 861B, RX: 188B)
[--] Reads / Writes: 52% / 48%
[--] Total buffers: 571.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 981.4M (9% of installed RAM)
[OK] Slow queries: 0% (846/190M)
[OK] Highest usage of available connections: 43% (66/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/4.5M
[OK] Key buffer hit rate: 100.0% (5B cached / 4K reads)
[OK] Query cache efficiency: 81.3% (120M cached / 148M selects)
[!!] Query cache prunes per day: 305162
[OK] Sorts requiring temporary tables: 0% (43K temp sorts / 6M sorts)
[!!] Joins performed without indexes: 65951
[!!] Temporary tables created on disk: 42% (4M on disk / 9M total)
[OK] Thread cache hit rate: 99% (3K created / 3M connections)
[OK] Table cache hit rate: 53% (1K open / 3K opened)
[OK] Open file limit used: 0% (61/16K)
[OK] Table locks acquired immediately: 99% (116M immediate / 116M locks)
[!!] InnoDB  buffer pool / data size: 368.0M/7.6G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    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 without LIMIT clauses
Variables to adjust:
    query_cache_size (> 32M)
    join_buffer_size (> 1.8M, or always use indexes with joins)
    tmp_table_size (> 147M)
    max_heap_table_size (> 147M)
    innodb_buffer_pool_size (>= 7G)

That's not a question to be asked on stackoverflow, as it not about a programming problem and does not describe a specific problem. https://stackoverflow.com/help/how-to-ask — Capricorn, Jun 17 '18 at 17:00
Realistically you need a) enough knowledge of MySQL as a database platform to ensure you are getting respectable performance out of the resources you are paying for, b) developers who understand MySQL (and SQL in general) well enough to use these resources sensibly and tune their use of them properly. The platform on its own will never be able to get rid of the effects of /all/ bad SQL, however much hardware and SQL configuration you throw at it. Make your developers tune and analyse their SQL properly as part of their deliverable (any professional developer should be doing so anyway). — MandyShaw, Jun 17 '18 at 20:02
@capricorn Please be kind to Borada32 and tell him/her WHERE to post questions of this nature. This is amazingly similar to SO question 50680294 of Jun 4, 2018. We know he/she is new to MySQL, help as much as possible with direction, please. — Wilson Hauck, Jun 18 '18 at 00:01

score -1 · Answer 1 · edited Jun 20 '20 at 09:12

Suggestions to consider for your my.cnf-ini [mysqld] section (some will be new to your my.cnf-ini)

join_buffer_size=1887K # lead with # for default

query_cache_min_res_unit=512  # from 4096 to reduce QC prunes per day
tmp_table_size=200M  # from 147M to reduce 42% on disk
max_heap_table_size=200M  # from 147M to keep size matched to tmp_table_size
innodb_buffer_pool_size=6G  # from 368M for 60% of RAM in VPS environment
innodb_buffer_pool_instances=6  # from 1 to reduce mutex contention
max_connections=100  # from 151 since only 66 max_used_connections in 37 days
thread_cache_size=100  # from autocalc to reduce threads_created of 3K
table_open_cache=3000  # from 1000 to avoid overhead of tables_opened
innodb_purge_threads=6  # from 1 to match # ibp instances
innodb_io_capacity_max=20000  # from 2000 to use your SSD capacity
innodb_io_capacity=10000  # from 200 to use your SSD capacity
innodb_flush_neighbors=0  # from 1  to conserve CPU, there is no rotational delay on SSD
innodb_lru_scan_depth=100  # from 1024 to save CPU cycles every SECOND

. if you are allowed to manage table indexes, 70,000 joins performed without indexes can be minimized, see REFMAN for tips on how to accomplish this task.

. when time permits, let us know your progress with this instance, by reposting complete new report from MySQL Tuner.pl after 7 days minimum UPTIME.

for additional assistance please find contact information in my profile.

Tuning mysql. Mysqltuner recommendations

1 Answers1

join_buffer_size=1887K # lead with # for default