Помогите с оптимизацией MySQL.
Mysqltuner
>> MySQLTuner 1.7.0 - Major Hayden <major@mhtx.net>
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 5.1.67
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Data in MyISAM tables: 1G (Tables: 539)
[!!] Total fragmented tables: 1
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1h 19m 23s (67K q [14.233 qps], 4K conn, TX: 1G, RX: 19M)
[--] Reads / Writes: 96% / 4%
[--] Binary logging is disabled
[--] Physical Memory : 4.0G
[--] Max MySQL memory : 138.7M
[--] Other process memory: 142.4M
[--] Total buffers: 16.0M global + 832.0K per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 23.3M (0.57% of installed RAM)
[OK] Maximum possible memory usage: 138.7M (3.39% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (2/67K)
[OK] Highest usage of available connections: 5% (9/151)
[OK] Aborted connections: 0.00% (0/4642)
[!!] 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% (77 temp sorts / 15K sorts)
[!!] Joins performed without indexes: 70
[!!] Temporary tables created on disk: 52% (5K on disk / 11K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (4 open / 47K opened)
[OK] Open file limit used: 0% (8/1K)
[OK] Table locks acquired immediately: 99% (64K immediate / 64K locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 56.2% (9K used / 16K cache)
[OK] Key buffer size / total MyISAM indexes: 16.0K/48.2M
[OK] Read Key buffer hit rate: 98.1% (21M cached / 397K reads)
[!!] Write Key buffer hit rate: 66.2% (96K cached / 32K writes)
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
OPTIMIZE TABLE infomafia.core_cache; -- can free 286.719413757324 MB
Total freed space after theses OPTIMIZE TABLE : 286.719413757324 Mb
Remove Anonymous User accounts - there are 2 anonymous accounts.
Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
18 CVE(s) found for your MySQL release. Consider upgrading your version !
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
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
Set thread_cache_size to 4 as a starting value
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 (1024) variable
should be greater than table_open_cache ( 4)
Variables to adjust:
query_cache_size (>= 8M)
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_open_cache (> 4)
my.cnf
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
query_cache_type = 1
query_cache_limit = 2M
query_cache_size = 32M
join_buffer_size = 8M
tmp_table_size = 64M
max_heap_table_size = 32M
thread_cache_size = 4
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 384M
sort_buffer_size = 32M
[mysqlhotcopy]
interactive-timeout
