Please help me to optimize mysql (my.cnf)

[avinash.pudota]

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

[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

-------- 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)

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

 

[thinkbot]

add in the end


table_open_cache = 2000
table_definition_cache = 1000
key_buffer_size = 100M
innodb_buffer_pool_size = 500M
innodb_buffer_pool_instances = 4
join_buffer_size=1M
read_rnd_buffer_size=2M

and change
max_connections=500
to
max_connections=100


then restart

 

[avinash.pudota]

I have added the above lines and restarted mysql, but i'm getting the following error

[email protected] [~]# service mysql start
Starting MySQL................... ERROR! Manager of pid-file quit without updating file.

i reverted the file back to original setting and it started sucessfully

 

[avinash.pudota]

I have tested by removing one line at a time and trying to restart mysql. when i remove this line mysql service started sucessfully.

innodb_buffer_pool_instances = 4

 

[cPanelMichael]

I have tested by removing one line at a time and trying to restart mysql. when i remove this line mysql service started sucessfully.
innodb_buffer_pool_instances = 4

The "innodb_buffer_pool_instances" variable was introduced in MySQL version 5.5. It's not supported in MySQL version 5.1.

Thank you.

 

[luigidelgado]

Sorry for being a party pooper for some but mySqlTuner is not recommended for MySQL 5.5 and later. Your are safe on 5.1 but all others beware:

Compatibility:

MySQL 3.23, 4.0, 4.1, 5.0, 5.1 (full support)
MySQL 5.4 (not fully tested, partially supported)
MySQL 6.0 (partial support)
MariaDB 10.x (partial support)
Perl 5.6 or later (with perl-doc package)
Unix/Linux based operating system (tested on Linux, BSD variants, and Solaris variants)
Windows is not supported at this time
Unrestricted read access to the MySQL server (OS root access recommended for MySQL < 5.0)


Thread: [Case # 90317] - Warning on using mysqltuner and MySQL 5.6!
http://forums.cpanel.net/f354/case-90317-warning-using-mysqltuner-mysql-5-6-a-389971.html

Cheers.