Mysqltuner says " Thread cache is disabled" but according to my.cnf, it is not.
Why is that?
Here is the my.cnf content.
[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
Here is Mysqltuner output:
>> 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)
Log file after restart
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
mysqld
, start it and check logs and update your question.mysqld
not to run properly, i'd do backup of all databases now, wipe outdatadir
and restore databases, bottom line you need to make sure you're not getting any errors while startingmysqld
.mysqld
starting up.