Skip to main content

MySQL Optimization

johnchristy
CentOS 6.5 Ram 3GB Core 8 MySQL Tuner >> MySQLTuner 1.3.0 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [OK] Currently running supported MySQL version 5.5.36-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 7M (Tables: 465) [--] Data in InnoDB tables: 82M (Tables: 3558) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 15 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 1d 18h 34m 37s (459K q [3.000 qps], 6K conn, TX: 564M, RX: 100M) [--] Reads / Writes: 98% / 2% [--] Total buffers: 168.0M global + 2.8M per thread (151 max threads) [OK] Maximum possible memory usage: 583.2M (20% of installed RAM) [OK] Slow queries: 0% (2/459K) [OK] Highest usage of available connections: 4% (7/151) [OK] Key buffer size / total MyISAM indexes: 8.0M/2.3M [OK] Key buffer hit rate: 99.4% (274K cached / 1K reads) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 92K sorts) [!!] Joins performed without indexes: 1280 [OK] Temporary tables created on disk: 9% (6K on disk / 68K total) [!!] Thread cache is disabled [!!] Table cache hit rate: 1% (400 open / 32K opened) [OK] Open file limit used: 0% (77/17K) [OK] Table locks acquired immediately: 100% (872K immediate / 872K locks) [OK] InnoDB buffer pool / data size: 128.0M/82.8M [OK] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Adjust your join queries to always utilize indexes Set thread_cache_size to 4 as a starting value Increase table_cache gradually to avoid file descriptor limits Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C Variables to adjust: query_cache_size (>= 8M) join_buffer_size (> 128.0K, or always use indexes with joins) thread_cache_size (start at 4) table_cache (> 400)
Pls check and let me know what are the exact changes I should do. If you could provide me the file name and the code I need to change/add it would be helpful Thanks

Comments

9 comments

Date Votes
  • thinkbot
    add this in my.cnf under [mysqld] query_cache_type = 1 query_cache_size = 30M table_cache_size = 1000 thread_cache_size = 20 join_buffer_size = 512K
    0
  • johnchristy
    This didnt work I got this error while restarting mysql : Starting MySQL... ERROR! The server quit without updating PID file. I had to remove these entries from my.cnf and restart. Any idea?
    0
  • thinkbot
    can you copy your my.cnf here ?
    0
  • cPanelMichael
    [quote="johnchristy, post: 1631351">I got this error while restarting mysql : Starting MySQL... ERROR! The server quit without updating PID file. I had to remove these entries from my.cnf and restart. Any idea?
    Could you provide the output from your MySQL error log (/var/lib/mysql/$hostname.err) when adding those entries? It should output the specific entry that was invalid. Thank you.
    0
  • johnchristy
    Sure [~]# cat /etc/my.cnf [mysqld] innodb_file_per_table=1 local-infile=0 log-slow-queries=/var/lib/mysql/slow.log open_files_limit=17058
    0
  • johnchristy
    Hi michael 140429 10:30:00 mysqld_safe mysqld from pid file /var/lib/mysql/hostname.pid ended 140429 10:30:01 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 140429 10:30:01 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead. 140429 10:30:01 [Note] Plugin 'FEDERATED' is disabled. 140429 10:30:01 InnoDB: The InnoDB memory heap is disabled 140429 10:30:01 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140429 10:30:01 InnoDB: Compressed tables use zlib 1.2.3 140429 10:30:01 InnoDB: Using Linux native AIO 140429 10:30:01 InnoDB: Initializing buffer pool, size = 128.0M 140429 10:30:01 InnoDB: Completed initialization of buffer pool 140429 10:30:01 InnoDB: highest supported file format is Barracuda. 140429 10:30:01 InnoDB: Waiting for the background threads to start 140429 10:30:02 InnoDB: 5.5.36 started; log sequence number 192786833 140429 10:30:02 [ERROR] /usr/sbin/mysqld: unknown variable 'table_cache_size=1000' 140429 10:30:02 [ERROR] Aborting 140429 10:30:02 InnoDB: Starting shutdown... 140429 10:30:03 InnoDB: Shutdown completed; log sequence number 192786833 140429 10:30:03 [Note] /usr/sbin/mysqld: Shutdown complete 140429 10:30:03 mysqld_safe mysqld from pid file /var/lib/mysql/hostname.pid ended [COLOR="silver">- - - Updated - - - the variable is table_cache and not table_cache_size? and michael these values should work best with my setup? query_cache_type = 1 query_cache_size = 30M table_cache_size = 1000 thread_cache_size = 20 join_buffer_size = 512K
    0
  • Archmactrix
    You should use correct variables for your mysql version: Use: [QUOTE]slow_query_log_file=/var/lib/mysql/slow.log table_open_cache=your-value
    Instead of: [QUOTE]table_cache_size = your-value log-slow-queries=/var/lib/mysql/slow.log
    I'm not sure if slow_query_log_file= enables the log, or you need to use the slow_query_log variable also. edit: Either of these would work if you need to use it: slow_query_log slow_query_log=1
    0
  • thinkbot
    Yes sorry use table_cache instead of table_cache_size in newer versions its table_cache_size
    0
  • johnchristy
    cool, Ill try this and will update you
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post