Need help Mysql Optimizations

eliko

• June 11, 2014 02:49

I'm using MySQLTuner.pl to optimize my site/srever.... though I'm not entirely sure how to resolve some of these issues and am wondering if someone can help me out. I have VPS with 4GB ram, THIS IS my.cnf settings: [mysqld] expire_logs_days=14 sync_binlog=1 query_cache_limit=32M query_cache_size=128M slow_query_log=1 log_queries_not_using_indexes=0 thread_cache_size=50 max_allowed_packet=16M max_connect_errors=1000000 max_connections=250 key_buffer_size=8M open_files_limit=65535 tmp_table_size=256M max_heap_table_size=256M table_definition_cache=2048 table_open_cache=2048 default_storage_engine=InnoDB innodb_flush_method=O_DIRECT innodb_file_per_table=1 innodb_log_files_in_group=2 innodb_additional_mem_pool_size=40M innodb_max_dirty_pages_pct=90 innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=64M innodb_log_file_size=256M innodb_buffer_pool_size=1152M
Here's the output of mysqltuner: -------- Performance Metrics ------------------------------------------------- [--] Up for: 2d 8h 36m 40s (7M q [34.897 qps], 504K conn, TX: 41B, RX: 735M) [--] Reads / Writes: 68% / 32% [--] Total buffers: 2.0G global + 2.8M per thread (200 max threads) [OK] Maximum possible memory usage: 2.5G (63% of installed RAM) [OK] Slow queries: 0% (30K/7M) [OK] Highest usage of available connections: 20% (40/200) [OK] Key buffer size / total MyISAM indexes: 8.0M/7.8M [OK] Key buffer hit rate: 99.9% (1M cached / 1K reads) [OK] Query cache efficiency: 29.3% (1M cached / 4M selects) [!!] Query cache prunes per day: 11573 [OK] Sorts requiring temporary tables: 0% (3K temp sorts / 999K sorts) [!!] Temporary tables created on disk: 48% (54K on disk / 112K total) [OK] Thread cache hit rate: 99% (40 created / 504K connections) [OK] Table cache hit rate: 33% (499 open / 1K opened) [OK] Open file limit used: 0% (380/65K) [OK] Table locks acquired immediately: 99% (4M immediate / 4M locks) [OK] InnoDB buffer pool / data size: 1.1G/1001.0M [OK] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Increasing the query_cache size over 128M may reduce performance Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses Variables to adjust: query_cache_size (> 512M) [see warning above]
Is there anything I also can improve for better performance? thank you

• 

  cPanelMichael

  • June 11, 2014 13:05

  Hello :) Are you experiencing any particular issues with MySQL, or are you simply attempting to optimize it so it runs faster? Thank you.

  0
• 

  eliko

  • June 11, 2014 13:24

  1. I would be happy if you can give their opinions on the setting in my.cnf (Because I'm not an expert and the setting is what I saw in other systems on the internet, maybe some incorrect) 2. i want to optimize for fast and efficient thank you.

  0

Please sign in to leave a comment.