0

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
Improve this question
10
  • 1
    try to stop mysqld, start it and check logs and update your question.
    – alexus
    Jan 22, 2015 at 17:16
  • thank you, updated the question with the log file content.
    – pixeline
    Jan 22, 2015 at 17:33
  • 1
    I see a lot of errors, that may cause mysqld not to run properly, i'd do backup of all databases now, wipe out datadir and restore databases, bottom line you need to make sure you're not getting any errors while starting mysqld.
    – alexus
    Jan 22, 2015 at 17:36
  • thank you alexus, i'll do that. Can you tell me if there is anything wrong in my my.cnf file according to that message "thread cache is disabled"?
    – pixeline
    Jan 22, 2015 at 17:41
  • looks right to me, but then again it's not working and one of the reason could be due to all those errors while mysqld starting up.
    – alexus
    Jan 22, 2015 at 17:43

1 Answer 1

Reset to default
0

[group]

group is the name of the program or group for which you want to set options. After a group line, any option-setting lines apply to the named group until the end of the option file or another group line is given.

Options unknown to mysqld_safe are passed to mysqld if they are specified on the command line, but ignored if they are specified in the [mysqld_safe] group of an option file.

remove [mysqld_safe] in between:

myisam_recover = FORCE,BACKUP
[mysqld_safe]  <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<  !!!
log-error=/var/log/mysqld.log

or move this group to the end of the configuration file:

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Improve this answer
2
  • thank you for the added details. Do you mean to say that mysqld_safe disables the thread cache ? Perhaps the pid-file setting?
    – pixeline
    Jan 29, 2015 at 8:29
  • this is not about "thread_cache" or "mysqld_safe", this is about mysql options file.
    – ADM
    Jan 29, 2015 at 11:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .