mysqltuner:线程缓存已禁用

mysqltuner:线程缓存已禁用

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 <[email protected]>

-------- 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

答案1

[团体]

group 是要为其设置选项的程序或组的名称。在 group 行之后,任何选项设置行都适用于命名的组,直到选项文件结束或给出另一个 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

相关内容