MySQL Optimization - Need advices -

i have centos 7 vm 32 gb ram (4 cores/8 threads) 3.4 ghz+ ran mysqltuner script, , followed recommendations gave, it's still slow

my current config in my.cnf file is:

[mysqld] local-infile=0 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock  symbolic-links=0  key_buffer        = 32m max_allowed_packet    = 268435456 thread_stack        = 192k thread_cache_size       = 384 key_buffer_size=32g max_connections = 1200 max_user_connections=1000 table_open_cache=3000 table_open_cache=5000 table_definition_cache=2048 sort_buffer_size=32m join_buffer_size = 32m read_buffer_size=32m wait_timeout=20 read_rnd_buffer_size=786432 bulk_insert_buffer_size = 8m  myisam_sort_buffer_size=64m query_cache_size=128m query_cache_limit=8m query_cache_type = 1 query_prealloc_size = 262144 query_alloc_block_size = 65535 transaction_alloc_block_size = 8192 transaction_prealloc_size = 4096 max_write_lock_count = 8 tmp_table_size=320m thread_concurrency=32   innodb_lock_wait_timeout = 600 innodb_additional_mem_pool_size=60m innodb_buffer_pool_size=20g innodb_buffer_pool_instances=20 innodb_table_locks=0 max_heap_table_size=128m net_buffer_length = 16384 max_connect_errors = 10 open_files_limit=50000 default-storage-engine=innodb low-priority-updates=1 innodb_file_per_table=1 concurrent_insert=always   [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid  # # include files config directory # !includedir /etc/my.cnf.d           [myisamchk] key_buffer_size=512m sort_buffer_size=256m read_buffer=256m write_buffer=256m   [mysqldump] quick quote-names max_allowed_packet    = 16m    [isamchk] key_buffer = 384m sort_buffer = 384m read_buffer = 256m write_buffer = 256m 

i restarted mysql short while ago apply recent changes.

when run script, get:

 >>  mysqltuner 1.4.4 - major hayden <major@mhtx.net>  >>  bug reports, feature requests, , downloads @ http://mysqltuner.com/  >>  run '--help' additional options , output filtering [[0;32mok[0m] running supported mysql version 5.5.41-mariadb [[0;32mok[0m] operating on 64-bit architecture  -------- storage engine statistics ------------------------------------------- [[0;34m--[0m] status: [0;32m+archive [0m[0;32m+aria [0m[0;32m+blackhole [0m[0;32m+csv [0m[0;32m+federated [0m[0;32m+innodb [0m[0;32m+mrg_myisam [0m [[0;34m--[0m] data in myisam tables: 23m (tables: 75) [[0;34m--[0m] data in innodb tables: 65m (tables: 643) [[0;34m--[0m] data in performance_schema tables: 0b (tables: 17) [[0;34m--[0m] data in memory tables: 0b (tables: 4) [[0;31m!![0m] total fragmented tables: 516  -------- security recommendations  ------------------------------------------- [[0;32mok[0m] there no anonymous account in database users [[0;32mok[0m] database users have passwords assigned [[0;31m!![0m] user 'pixoneo@%' hasn't specific host restriction. [[0;34m--[0m] there 605 basic passwords in list.  -------- performance metrics ------------------------------------------------- [[0;34m--[0m] for: 20m 58s (877k q [697.220 qps], 52k conn, tx: 16b, rx: 195m) [[0;34m--[0m] reads / writes: 96% / 4% [[0;34m--[0m] total buffers: 22.3g global + 4.9m per thread (1200 max threads) [[0;31m!![0m] maximum possible memory usage: 28.1g (89% of installed ram) [[0;32mok[0m] slow queries: 0% (9/877k) [[0;32mok[0m] highest usage of available connections: 40% (483/1200) [[0;32mok[0m] key buffer size / total myisam indexes: 2.0g/773.0k [[0;31m!![0m] key buffer hit rate: 94.3% (53 cached / 3 reads) [[0;32mok[0m] query cache efficiency: 45.6% (497k cached / 1m selects) [[0;32mok[0m] query cache prunes per day: 0 [[0;32mok[0m] sorts requiring temporary tables: 0% (0 temp sorts / 22k sorts) [[0;31m!![0m] joins performed without indexes: 15817 [[0;32mok[0m] temporary tables created on disk: 11% (23k on disk / 204k total) [[0;32mok[0m] thread cache hit rate: 99% (483 created / 52k connections) [[0;32mok[0m] table cache hit rate: 102% (1k open / 1k opened) [[0;32mok[0m] open file limit used: 23% (239/1k) [[0;32mok[0m] table locks acquired immediately: 100% (199k immediate / 199k locks)  -------- innodb metrics ----------------------------------------------------- [[0;34m--[0m] innodb enabled. [[0;34m--[0m] innodb bufferpool size :20.0g [[0;34m--[0m] innodb bufferpool inst :1 [[0;32mok[0m] innodb buffer pool / data size: 20.0g/65.4m [[0;31m!![0m] innodb buffer pool instances: 1 [[0;32mok[0m] innodb log waits: 0  -------- recommendations ----------------------------------------------------- general recommendations:     run optimize table defragment tables better performance     restrict host user@% user@specificdnsorip     mysql started within last 24 hours - recommendations may inaccurate     reduce overall mysql memory footprint system stability     enable slow query log troubleshoot bad queries     adjust join queries utilize indexes variables adjust:     join_buffer_size (> 1.0m, or use indexes joins)     innodb_buffer_pool_instances(=20) 

does have recommendations now? thank you

key_buffer_size=32g

no! have left no room else. using myisam? if so, set 4g 32gb server. if not, set 20m.

key buffer size / total myisam indexes: 2.0g/773.0k

that contradictory! 2g or 32g? show variables 'key_buffer_size';. if says 2g, have not shown my.cnf being used.

total fragmented tables: 516

ignore; tables "fragmented" of time. it's not worth worrying about.

877k queries in 20 minutes? that's rather busy. if slowlog turned on, let's see showed in it.

483/1200 connections. forgetting disconnect? else might leading max_used_connections = 483?

thread cache hit rate: 99% (483 created / 52k connections)
table_open_cache=5000

5000 overkill

anyway, real thing @ "slow" slowlog:

• set long_query_time=1
• turn on slow log
• wait day
• use pt-query-digest summarize slowlog
• show first couple of queries in summary
• include show create table , explain select ... table(s) , queries