[ anon70939 @ 30.04.2016. 14:06 ] @
my.cnf... zvuci mi kao my kampf... a stvarno se i borim...

mysqltuner mi sada posle cackanja od jutros pokazuje ovo

root@server /var/www/vhosts/**.****.info/httpdocs/shell # mysqltuner

>> MySQLTuner 1.1.1 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.7.12
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[--] Data in MyISAM tables: 94M (Tables: 859)
[--] Data in InnoDB tables: 1G (Tables: 4261)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 87)
[--] Data in MEMORY tables: 0B (Tables: 167)
[!!] Total fragmented tables: 88

-------- Security Recommendations -------------------------------------------
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1054 (42S22) at line 1: Unknown column 'password' in 'where clause'
[OK] All database users have passwords assigned
mysql: [Warning] Using a password on the command line interface can be insecure.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 9s (9 q [1.000 qps], 6 conn, TX: 17K, RX: 982)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 24.6G global + 128.9M per thread (214 max threads)
[OK] Maximum possible memory usage: 51.6G (81% of installed RAM)
[OK] Slow queries: 0% (0/9)
[OK] Highest usage of available connections: 0% (1/214)
[OK] Key buffer size / total MyISAM indexes: 32.0M/13.4M
[!!] Key buffer hit rate: 50.0% (6 cached / 3 reads)
[!!] Query cache efficiency: 0.0% (0 cached / 4 selects)
[OK] Query cache prunes per day: 0
[OK] Thread cache hit rate: 83% (1 created / 6 connections)
[OK] Table cache hit rate: 24% (27 open / 109 opened)
[OK] Open file limit used: 0% (6/1K)
[OK] Table locks acquired immediately: 100% (100 immediate / 100 locks)
[OK] InnoDB data size / buffer pool: 1.4G/24.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Variables to adjust:
query_cache_limit (> 48M, or use smaller result sets)




A ovo je moj fajl. I ok, sad treba da cekam 24h bar pa ponovo da proverim sa mysqltuner

Code:

early-plugin-load=keyring_file.so

key_buffer_size        = 32M
max_allowed_packet    = 256M

thread_cache_size       = 300
max_connections        = 1000
table_open_cache = 1200
query_cache_limit    = 48M
query_cache_type = 1
query_cache_size        = 96M
join_buffer_size = 128M
tmp_table_size = 512M
max_heap_table_size = 512M
#myisam-recover         = BACKUP
#thread_stack        = 192K
#log_slow_queries    = /var/log/mysql/mysql-slow.log
#long_query_time = 1
#expire_logs_days    = 10
#max_binlog_size         = 100M
#innodb_buffer_pool_size = 35G
#innodb_log_file_size = 8G
#innodb_log_buffer_size=1M
##innodb_flush_log_at_trx_commit = 1
##########################
innodb_read_io_threads=4
innodb_write_io_threads=8  #To stress the double write buffer
innodb_buffer_pool_size=24G
innodb_buffer_pool_load_at_startup=ON
innodb_log_file_size = 32M #Small log files, more page flush
innodb_log_files_in_group=2
innodb_file_per_table=1
innodb_log_buffer_size=8M
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=0



Ovo sto je komentovano, tako mi je bilo sa mysql 5.7, ali za sada je komentovano.
Ove innodb vrednosti, ne znam sta rade, njih sam jos na pocetku posle upgrade iskopirao odnekud.
Mozda sam preterao sa nekim vredsnostima? :)
Ali mysqltuner kad god resetujem servis, je savetovao jos da povecam...


Bio bih veoma zahvalan ako biste prosli kroz ovo sa mnom :)
[ Aleksandar Đokić @ 30.04.2016. 14:09 ] @
query_cache_limit kaze koliki je najveca velicina query-ja koja se kesira, ali sad ti prepricavam ono sto je lepo napisano u mysql manualu, bez veze je da to radim
[ bogdan.kecman @ 30.04.2016. 14:41 ] @
obavezan alat za tuning query kesha:

https://dom.as/tech/query-cache-tuner/

ako nesto nije jasno vezano za koristenje alata, slobodno pitaj