Optimising mysql config (/etc/my.cnf) advice?

Hello, i hope my request is not spam. I want to ask for advices optimising mysql on the server (VM860) i manage. I will be updating this thread with new data untill mysql is optimisedd.

So first day data:

mysqld -v

Server version: 5.5.36-cll

Code (markup):

mysqltuner.pl

-------- Performance Metrics -------------------------------------------------
[--] Up for: 6d 10h 41m 2s (68M q [122.858 qps], 2M conn, TX: 1596B, RX: 11B)
[--] Reads / Writes: 81% / 19%
[--] Total buffers: 1.7G global + 18.6M per thread (200 max threads)
[OK] Maximum possible memory usage: 5.4G (37% of installed RAM)
[OK] Slow queries: 0% (27K/68M)
[!!] Highest connection usage: 100%  (201/200)
[OK] Key buffer size / total MyISAM indexes: 8.0M/1.1G
[OK] Key buffer hit rate: 95.0% (576M cached / 28M reads)
[OK] Query cache efficiency: 74.0% (38M cached / 52M selects)
[!!] Query cache prunes per day: 787070
[OK] Sorts requiring temporary tables: 0% (6K temp sorts / 2M sorts)
[!!] Joins performed without indexes: 243830
[!!] Temporary tables created on disk: 32% (1M on disk / 3M total)
[OK] Thread cache hit rate: 98% (40K created / 2M connections)
[!!] Table cache hit rate: 0% (512 open / 30M opened)
[OK] Open file limit used: 2% (918/43K)
[OK] Table locks acquired immediately: 99% (22M immediate / 22M locks)
[OK] InnoDB buffer pool / data size: 400.0M/300.5M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
  Run OPTIMIZE TABLE to defragment tables for better performance
  Enable the slow query log to troubleshoot bad queries
  Reduce or eliminate persistent connections to reduce connection usage
  Increasing the query_cache size over 128M may reduce performance
  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_cache gradually to avoid file descriptor limits
  Read this before increasing table_cache over 64: *
Variables to adjust:
  max_connections (> 200)
  wait_timeout (< 28800)
  interactive_timeout (< 28800)
  query_cache_size (> 334M) [see warning above]
  join_buffer_size (> 16.0M, or always use indexes with joins)
  table_cache (> 512)

Code (markup):

mysql tuning primer data:
http://pastebin.com/ma7Xm0w5

/etc/my.cnf values

thread_cache_size=12
max_connections=200
innodb_buffer_pool_size=400M
join_buffer_size=16M
query_cache_size=334M
query_cache_limit=256M
table_cache=1024
table_open_cache=512
tmp_table_size=1024M
max_heap_table_size=1024M

Code (markup):

free -m

  total  used  free  shared  buffers  cached
Mem:  14500  6020  8479  0  0  0
-/+ buffers/cache:  6020  8479
Swap:  0  0  0

Code (markup):

so i changed:
table_cache to 6024M
and restarted mysql.

please u have any advices on commands/changes? Thank You

 

Most likely nobody replied here yet as your post is chaotic and seems like you first need to dig into the MySQL documentation before trying to optimize something (how would Gordon Ramsay say: learn first to walk before you run).

* Without knowing the HW details it is impossible to give you exact help (also need to know what else is running)
* MySQL uses storage engines - each one optimizes themself differently

1) Try to use InnoDB only (as a minimum, XtraDB or TokuDB optionally if you understand what is happening)
2) With InnoDB use approx 70-80% of available RAM (e.g. substract other daemons that are running like Apache/PHP/etc) for innodb_buffer_pool_size - and forget query_cache_*
3) table_cache=6024M does not make sense, as that directive has nothing to do with bytes
4) Use MariaDB instead of MySQL which is a drop-in replacement of MySQL - it comes with a "saner" prepackaged my.cnf that is more optimized than the one from MySQL (you will just need to set the buffer-pool-size accordingly)

 

I will need to stay with mysql. Im hosting various clients and new coming and they have various engines i think.
I have VPS with 13GB ram burst and 4gb dedi. around 30% ram is used almost permanently...

 

I keep seeing posts saying to use InnoDB, but my databases query 100 times faster under MyISAM, and InnoDB doesnt support FULLTEXT index... ?

 

This is second run.

mysqltuner.pl

 >>  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] Currently running supported MySQL version 5.5.37-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 5G (Tables: 6985)
[--] Data in MRG_MYISAM tables: 21M (Tables: 3)
[--] Data in InnoDB tables: 291M (Tables: 979)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 3M (Tables: 23)
[!!] Total fragmented tables: 789

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 18d 4h 8m 29s (196M q [124.907 qps], 8M conn, TX: 5187B, RX: 30B)
[--] Reads / Writes: 81% / 19%
[--] Total buffers: 1.9G global + 18.6M per thread (200 max threads)
[OK] Maximum possible memory usage: 5.6G (32% of installed RAM)
[OK] Slow queries: 0% (17K/196M)
[OK] Highest usage of available connections: 57% (115/200)
[OK] Key buffer size / total MyISAM indexes: 8.0M/1.5G
[OK] Key buffer hit rate: 95.2% (1B cached / 77M reads)
[OK] Query cache efficiency: 76.9% (115M cached / 150M selects)
[!!] Query cache prunes per day: 755056
[OK] Sorts requiring temporary tables: 0% (30K temp sorts / 8M sorts)
[!!] Joins performed without indexes: 658581
[!!] Temporary tables created on disk: 35% (3M on disk / 10M total)
[OK] Thread cache hit rate: 99% (66K created / 8M connections)
[!!] Table cache hit rate: 0% (512 open / 91M opened)
[OK] Open file limit used: 2% (959/43K)
[OK] Table locks acquired immediately: 99% (56M immediate / 56M locks)
[OK] InnoDB buffer pool / data size: 400.0M/291.3M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Increasing the query_cache size over 128M may reduce performance
    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_cache gradually to avoid file descriptor limits
    Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
    query_cache_size (> 512M) [see warning above]
    join_buffer_size (> 16.0M, or always use indexes with joins)
    table_cache (> 512)

Code (markup):

mysql tuning primer: http://pastebin.com/qmA89REM

/etc/my.cnf

max_connections=200
max_user_connections=18

innodb_file_per_table=1
local-infile=0
max_allowed_packet=268435456
open_files_limit=43762
ft_min_word_len=3

thread_cache_size=12
max_connections=200
innodb_buffer_pool_size=400M
join_buffer_size=16M
query_cache_size=512M
query_cache_limit=512M
table_cache=6024
table_open_cache=512
tmp_table_size=1024M
max_heap_table_size=1024M

Code (markup):

free -m

             total       used       free     shared    buffers     cached
Mem:         17500       7031      10468          0          0          0
-/+ buffers/cache:       7031      10468
Swap:            0          0          0

Code (markup):

i did not changed anything now as it appears quite ok to me