Hello, i have a VPS with 2 GB memory and around 50 websites and all are very low traffic websites (less than 500 visits per day in total, i expect 1 website may get more than 1000 visits per day soon so lets assume my server gets 2000 visits per day in total). static pages and other files load very quickly but pages which need database interaction is taking lot of time which i have found recently. After a small research i've found that i need to optimize my.cnf for better speed. I dont have much knowledge related to mysql, so please suggest me a my.cnf configuration file from the below inputs.
Technical details of my VPS:
OS: Cent OS
Architecture i686
Memory: 2 GB
Processor: Intel(R) Xeon(R) CPU X3430 @ 2.40GHz
Apache version 2.2.24
PHP version 5.3.26
MySQL version 5.1.70-cll
Current my.cnf file
mysqltuner.pl output
My VPS consumes around 550 to 600 MB out of 2 GB all the time but some times it goes upto 1.5 GB (for few minutes) without any reason and even i cant see any heavy memory consuming processes during that time
Technical details of my VPS:
OS: Cent OS
Architecture i686
Memory: 2 GB
Processor: Intel(R) Xeon(R) CPU X3430 @ 2.40GHz
Apache version 2.2.24
PHP version 5.3.26
MySQL version 5.1.70-cll
Current my.cnf file
Code:
[mysqld]
max_connections=500
set-variable = max_connections=500
safe-show-database
open_files_limit=48244
skip-name-resolve
interactive_timeout=600
wait_timeout=600
query_cache_size=10M
thread_cache_size=4
mysqltuner.pl output
Code:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.70-cll
[OK] Operating on 32-bit architecture with less than 2GB RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 97M (Tables: 3975)
[--] Data in InnoDB tables: 271M (Tables: 3507)
[--] Data in MEMORY tables: 253K (Tables: 93)
[!!] Total fragmented tables: 3551
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 5h 8m 28s (706K q [6.733 qps], 17K conn, TX: 2B, RX: 351M)
[--] Reads / Writes: 95% / 5%
[--] Total buffers: 44.0M global + 2.7M per thread (500 max threads)
[OK] Maximum possible memory usage: 1.4G (67% of installed RAM)
[OK] Slow queries: 0% (653/706K)
[OK] Highest usage of available connections: 4% (24/500)
[OK] Key buffer size / total MyISAM indexes: 8.0M/36.6M
[OK] Key buffer hit rate: 99.5% (5M cached / 28K reads)
[OK] Query cache efficiency: 43.4% (270K cached / 623K selects)
[!!] Query cache prunes per day: 23067
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 19K sorts)
[!!] Joins performed without indexes: 6101
[OK] Temporary tables created on disk: 20% (5K on disk / 29K total)
[OK] Thread cache hit rate: 98% (206 created / 17K connections)
[!!] Table cache hit rate: 0% (64 open / 120K opened)
[OK] Open file limit used: 0% (68/48K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[!!] InnoDB data size / buffer pool: 271.7M/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 10M)
join_buffer_size (> 128.0K, or always use indexes with joins)
table_cache (> 64)
innodb_buffer_pool_size (>= 271M)