Mysqltuner сообщает: «Кеш потоков отключен», но, согласно my.cnf, это не так.
Это почему?
Вот содержимое my.cnf.
[mysqld]
log=/var/log/mysql.log
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
query_cache_type = 1
query_cache_limit = 1M
query_cache_size = 32M
# For MyISAM #
key_buffer_size = 24M
myisam_recover = FORCE,BACKUP
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# LOGGING #
log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /var/log/mysqld-slow-query.log
tmp_table_size = 32M
max_heap_table_size = 32M
max_connections = 500
thread_cache_size = 50
open_files_limit = 65535
table_definition_cache = 4096
table_open_cache = 512
Вот вывод Mysqltuner:
>> MySQLTuner 1.1.1 - Major Hayden <major@mhtx.net>
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.41-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in InnoDB tables: 26M (Tables: 23)
[!!] Total fragmented tables: 23
-------- Security Recommendations -------------------------------------------
ERROR 1142 (42000) at line 1: SELECT command denied to user 'user'@'localhost' for table 'user'
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 8h 8m 40s (650K q [22.182 qps], 2K conn, TX: 9B, RX: 79M)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 216.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 631.2M (33% of installed RAM)
[OK] Slow queries: 0% (0/650K)
[OK] Highest usage of available connections: 13% (20/151)
[!!] Cannot calculate MyISAM index size - re-run script as root user
[!!] Query cache efficiency: 1.3% (6K cached / 472K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 155K sorts)
[!!] Temporary tables created on disk: 49% (155K on disk / 312K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 12% (98 open / 777 opened)
[OK] Open file limit used: 0% (72/65K)
[OK] Table locks acquired immediately: 99% (468K immediate / 468K locks)
[OK] InnoDB data size / buffer pool: 26.1M/128.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_limit (> 1M, or use smaller result sets)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_cache (> 400)
Файл журнала после перезапуска
150122 18:18:52 [Note] /usr/libexec/mysqld: Normal shutdown
150122 18:18:52 [Note] Event Scheduler: Purging the queue. 0 events
150122 18:18:54 [Warning] /usr/libexec/mysqld: Forcing close of thread 2548 user: 'mydatabase_live'
150122 18:18:54 [Warning] /usr/libexec/mysqld: Forcing close of thread 2547 user: 'mydatabase_dev'
150122 18:18:54 [Warning] /usr/libexec/mysqld: Forcing close of thread 2538 user: 'mydatabase_live'
150122 18:18:54 [Warning] /usr/libexec/mysqld: Forcing close of thread 2537 user: 'mydatabase_live'
150122 18:18:54 [Warning] /usr/libexec/mysqld: Forcing close of thread 2536 user: 'mydatabase_live'
150122 18:18:54 [Warning] /usr/libexec/mysqld: Forcing close of thread 2535 user: 'mydatabase_live'
150122 18:18:54 [Warning] /usr/libexec/mysqld: Forcing close of thread 2534 user: 'mydatabase_live'
150122 18:18:54 [Warning] /usr/libexec/mysqld: Forcing close of thread 2533 user: 'mydatabase_live'
150122 18:18:54 [Warning] /usr/libexec/mysqld: Forcing close of thread 2532 user: 'mydatabase_live'
150122 18:18:54 [Warning] /usr/libexec/mysqld: Forcing close of thread 2531 user: 'mydatabase_live'
150122 18:18:54 [Warning] /usr/libexec/mysqld: Forcing close of thread 2530 user: 'mydatabase_dev'
150122 18:18:54 [Warning] /usr/libexec/mysqld: Forcing close of thread 2529 user: 'mydatabase_dev'
150122 18:18:54 [Warning] /usr/libexec/mysqld: Forcing close of thread 2528 user: 'mydatabase_dev'
150122 18:18:54 [Warning] /usr/libexec/mysqld: Forcing close of thread 2527 user: 'mydatabase_dev'
150122 18:18:54 [Warning] /usr/libexec/mysqld: Forcing close of thread 2526 user: 'mydatabase_dev'
150122 18:18:54 [Warning] /usr/libexec/mysqld: Forcing close of thread 2525 user: 'mydatabase_dev'
150122 18:18:54 [Warning] /usr/libexec/mysqld: Forcing close of thread 2524 user: 'mydatabase_dev'
150122 18:18:54 [Warning] /usr/libexec/mysqld: Forcing close of thread 2523 user: 'mydatabase_dev'
150122 18:18:54 InnoDB: Starting shutdown...
150122 18:18:57 InnoDB: Shutdown completed; log sequence number 8201827191
150122 18:18:57 [Note] /usr/libexec/mysqld: Shutdown complete
150122 18:18:57 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
150122 18:18:58 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
150122 18:18:58 [Warning] The syntax '--log' is deprecated and will be removed in a future release. Please use '--general-log'/'--general-log-file' instead.
150122 18:18:58 [Warning] Using unique option prefix myisam_recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
150122 18:18:58 [Note] Plugin 'FEDERATED' is disabled.
150122 18:18:58 InnoDB: The InnoDB memory heap is disabled
150122 18:18:58 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150122 18:18:58 InnoDB: Compressed tables use zlib 1.2.3
150122 18:18:58 InnoDB: Using Linux native AIO
150122 18:18:58 InnoDB: Initializing buffer pool, size = 128.0M
150122 18:18:58 InnoDB: Completed initialization of buffer pool
150122 18:18:58 InnoDB: highest supported file format is Barracuda.
150122 18:18:58 InnoDB: Waiting for the background threads to start
150122 18:18:59 InnoDB: 5.5.41 started; log sequence number 8201827191
/usr/libexec/mysqld: File '/var/log/mysql.log' not found (Errcode: 13)
150122 18:18:59 [ERROR] Could not use /var/log/mysql.log for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.
150122 18:18:59 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
150122 18:18:59 [Note] - '0.0.0.0' resolves to '0.0.0.0';
150122 18:18:59 [Note] Server socket created on IP: '0.0.0.0'.
150122 18:18:59 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
150122 18:18:59 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
150122 18:18:59 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
150122 18:18:59 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
150122 18:18:59 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
150122 18:18:59 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
150122 18:18:59 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
150122 18:18:59 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure
150122 18:18:59 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure
150122 18:18:59 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
150122 18:18:59 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure
150122 18:18:59 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure
150122 18:18:59 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure
150122 18:18:59 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure
150122 18:18:59 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure
150122 18:18:59 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure
150122 18:18:59 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
150122 18:18:59 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
150122 18:18:59 [Note] Event Scheduler: Loaded 0 events
150122 18:18:59 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.41-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Remi
[группа]
group - это имя программы или группы, для которой вы хотите установить параметры. После строки группы любые строки установки параметров применяются к названной группе до конца файла параметров или до другой строки группы.
Параметры, неизвестные mysqld_safe, передаются mysqld, если они указаны в командной строке, но игнорируются, если они указаны в группе [mysqld_safe] файла параметров.
удалите [mysqld_safe] между:
myisam_recover = FORCE,BACKUP
[mysqld_safe] <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< !!!
log-error=/var/log/mysqld.log
или переместите эту группу в конец файла конфигурации:
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid