Mysql optimization Request
Hello please suggest me mysql optimization below is report generated with mysqlmymonlite.sh
Report Generated:
Fri Jul 17 01:23:13 IST 2015
-------------------------------------------------
mysqltuner output
-------------------------------------------------
>> MySQLTuner 1.4.3 mod - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Modified by George Liu (eva2000) at http://vbtechsupport.com/
>> Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.42-cll
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 10M (Tables: 26)
[--] Data in InnoDB tables: 24M (Tables: 367)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 17)
[!!] Total fragmented tables: 12
-------- Security Recommendations -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
ERROR 1267 (HY000) at line 1: Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
[!!] There is not basic password file list !
-------- Performance Metrics -------------------------------------------------
[--] Up for: 8h 47m 30s (107K q [3.392 qps], 2K conn, TX: 203M, RX: 46M)
[--] Reads / Writes: 95% / 5%
[--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 583.2M (56% of installed RAM)
[OK] Slow queries: 0% (0/107K)
[OK] Highest usage of available connections: 6% (10/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/2.6M
[OK] Key buffer hit rate: 98.5% (162K cached / 2K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 21K sorts)
[!!] Temporary tables created on disk: 68% (14K on disk / 20K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 10% (400 open / 3K opened)
[OK] Open file limit used: 0% (24/10K)
[OK] Table locks acquired immediately: 100% (210K immediate / 210K locks)
[OK] InnoDB buffer pool / data size: 128.0M/25.0M
[OK] InnoDB buffer pool instances: 1
[OK] InnoDB log waits: 0
-------- 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.
Do not forget to disable slow query logging after troubleshooting
- For MySQL 5.0 http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
- For MySQL 5.1 http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
- For MySQL 5.5 http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html
- For MySQL 5.6 http://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html
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_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
query_cache_size (>= 8M)
tmp_table_size (> 16M, increase tmp_table_size)
max_heap_table_size (> 16M, increase max_heap_table_size)
thread_cache_size (start at 4)
table_open_cache (> 400)
Report Complete:
Fri Jul 17 01:23:17 IST 2015
-
Hello, Can you please try to update following value in your my.cnf file and run your mysqlmymonlite scripts again after 24 hours key_buffer_size = 150M query_cache_limit = 1M query_cache_size = 30M query_cache_type = 1 table_open_cache = 1000 thread_cache_size = 16 tmp_table_size = 50M max_heap_table_size = 50M0 -
Hello :) Please ensure you let MySQL run for at least 24 hours before running a tuner to ensure accurate results. Thank you. 0
Please sign in to leave a comment.
Comments
2 comments