Optimization for 2GB VPS CENTOS 6.2

[SpaceCowboy]

Hi guys i hope someone could advice me on what setting to use for this server. Since the suggestions i tried by lurking other folks thread didn't work so good for me.

This is the actual my.cnf:

[mysqld]
set-variable = max_connections=500
log-slow-queries
safe-show-database

mysqltunner:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.72-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 9M (Tables: 78)
[--] Data in InnoDB tables: 45M (Tables: 141)
[!!] Total fragmented tables: 143

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 14h 4m 49s (39M q [127.551 qps], 729K conn, TX: 551B, RX: 4B)
[--] Reads / Writes: 96% / 4%
[--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
[OK] Maximum possible memory usage: 1.4G (70% of installed RAM)
[OK] Slow queries: 0% (0/39M)
[OK] Highest usage of available connections: 13% (69/500)
[OK] Key buffer size / total MyISAM indexes: 8.0M/16.6M
[OK] Key buffer hit rate: 99.9% (133M cached / 76K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 7M sorts)
[!!] Temporary tables created on disk: 39% (3M on disk / 9M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 19K opened)
[OK] Open file limit used: 0% (17/2K)
[OK] Table locks acquired immediately: 99% (40M immediate / 40M locks)
[!!] InnoDB data size / buffer pool: 45.7M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    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_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (>= 8M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 45M)

Bare in mind this is 1 website only with about 10,000 daily unique visitors using wordpress+small phpbb forums.
I tried some tips on other threads but the site was unresponsive: pages started loading after a few seconds i clicked the links instead of instantly..site partially loading (missing sidebar), and general slowness, that's why i disabled cache, but maybe i was doing all wrong so what settings do you recommend for such a site?

 

[thinkbot]

remove all the stuf you got in your current my.cnf
and add

[mysqld]
skip-name-resolve

myisam_use_mmap=1

max_connections = 200
max_user_connections = 75

join_buffer_size=2M
sort_buffer_size=256K

table_open_cache = 1000
table_definition_cache = 400
max_allowed_packet = 32M

thread_cache_size = 16

query_cache_type = 1
query_cache_size =30M
query_cache_limit = 1M

max_heap_table_size = 50M
tmp_table_size = 50M

key_buffer_size = 100M

innodb_buffer_pool_size = 100M
innodb_stats_on_metadata=0

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1


and restart, after several hours rerun mysqltuner.pl and post it here
the difference should be huge, since your setup is very bad as default one

 

[SpaceCowboy]

Hi thinkbot! Thanks for helping me out, i am now using the config you suggested.
Mysql service restarted with no problems the site seems to be running perfectly fine.
I will post new stats tomorrow.

Is there another files that might need tweaking?
I'm thinking of php and apache config files, i haven't touch those at all.
I don't know where they are either or if they are important to reduce server resources usage.

 

[SpaceCowboy]

Here the results after 24 hs uptime:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.72-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 9M (Tables: 78)
[--] Data in InnoDB tables: 42M (Tables: 141)
[!!] Total fragmented tables: 141

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 0h 2m 40s (9M q [111.018 qps], 162K conn, TX: 127B, RX: 1B)
[--] Reads / Writes: 92% / 8%
[--] Total buffers: 282.0M global + 2.9M per thread (200 max threads)
[OK] Maximum possible memory usage: 857.0M (42% of installed RAM)
[OK] Slow queries: 0% (331/9M)
[OK] Highest usage of available connections: 22% (44/200)
[OK] Key buffer size / total MyISAM indexes: 100.0M/13.4M
[OK] Key buffer hit rate: 99.7% (8M cached / 22K reads)
[OK] Query cache efficiency: 90.7% (6M cached / 7M selects)
[!!] Query cache prunes per day: 80424
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 158K sorts)
[OK] Temporary tables created on disk: 6% (24K on disk / 380K total)
[OK] Thread cache hit rate: 99% (316 created / 162K connections)
[!!] Table cache hit rate: 17% (262 open / 1K opened)
[OK] Open file limit used: 9% (217/2K)
[OK] Table locks acquired immediately: 99% (781K immediate / 781K locks)
[OK] InnoDB data size / buffer pool: 42.8M/100.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 30M)
    table_cache (> 1000)

Note that i used 2 methods to optimize database tables but none worked
1)mysqlcheck -o --all-databases
2)/http://www.justin.my/2010/09/optimize-only-fragmented-tables-in-mysql/

 

[thinkbot]

those 2 methods don't change much

Your current results are very good, you got only 6% of queries that create temporary tables on disk, so it's ok
You have no problems to worry about now, it's very good

you can increase query_cache_size a bit, to 50M or something like that

 

[cPanelPeter]

Hello,

Anyone using mysqltuner.pl should read the following thread I just posted. Especially if you are running on MySQL 5.6.

Warning on using mysqltuner with MySQL 5.6

 

[SpaceCowboy]

those 2 methods don't change much

Your current results are very good, you got only 6% of queries that create temporary tables on disk, so it's ok
You have no problems to worry about now, it's very good

you can increase query_cache_size a bit, to 50M or something like that

Nice thanks a lot!

Hello,

Anyone using mysqltuner.pl should read the following thread I just posted. Especially if you are running on MySQL 5.6.

Warning on using mysqltuner with MySQL 5.6

I see, thanks for the advice.


How many days is ok to let the server run before doing a reboot?
And is there a way to automatize periodic restarts maybe with a cron job?
I think it would be a good idea right?

 

[cPanelPeter]

Nice thanks a lot!


I see, thanks for the advice.


How many days is ok to let the server run before doing a reboot?
And is there a way to automatize periodic restarts maybe with a cron job?
I think it would be a good idea right?

It's not necessary or recommended to restart the MySQL server on a regular basis. Why would you want to do that?

 

[SpaceCowboy]

It's not necessary or recommended to restart the MySQL server on a regular basis. Why would you want to do that?

I was referring to the whole server, maybe to give it a break and start over clean i don't know...i thought it was needed, i feel stupid now.

 

[JaredR.]

The only normal reasons to reboot a Linux server are to upgrade the kernel, or if it is completely unresponsive. Other than that, Linux does not need to be rebooted periodically. It is different from Windows in that regard. Since Linux can stop and start processes easily, rebooting is not necessary to keep the server running well, and rebooting is also not a useful troubleshooting step.

If there is a problem with a specific service on Linux, it is best to look at the logs for that service and troubleshoot it accordingly. Rebooting almost never helps and it can actually hurt the troubleshooting process, because after the reboot you cannot see what was running when the problem was happening.

I hope this helps. Many new Linux users are accustomed to the Windows procedure of rebooting the server as a first troubleshooting step anytime a problem is encountered, or to try to keep the system running smoothly, but on Linux, rebooting is not helpful for troubleshooting and it is almost never needed for system maintenance.

 

[SpaceCowboy]

Oh, good to know i had no idea. Sometimes i'm forced to reboot it because i cannot fix the problem with loads going above "2.0" they can get up to 15 or even higher numbers then the site get's extremely slow. i don't know Linux so looking at the logs makes no sense to me. Is there any other config file on the server that could help stop this from happening? I would like track it down to fix it...but i don't know how to.

 

[JaredR.]

A basic working knowledge of the shell is important to successfully manage a server. Not everything is available in the WebHost Manager (many logs, for example). One of Linux's great strengths is that it logs almost everything, so understanding how to log into the shell and use basic commands to view and search through log files is very useful for running a Linux server.

The following guide may help you to find the cause of the high load:

http://forums.cpanel.net/f34/troubleshooting-high-server-loads-linux-servers-319352.html

If you are not comfortable using the Linux shell, you may want to consider hiring a qualified administrator to help you. We have a list of companies that offer server management services here:

All Services

If you are interested in learning how to use the Linux shell to administer your server, there are many tutorials online. I did a Google search for "linux shell tutorial" and found the following:

LinuxCommand.org: Learning the shell.
The 5-Minute Essential Shell Tutorial - Linux Mint Community

If you have never logged into a Linux system via SSH, there are also tutorials that explain how to do that online. I found the following using a Google search for "how to use ssh":

http://www.wikihow.com/Use-SSH

 

[SpaceCowboy]

Thanks for all the links really appreciated. i'll try to learn as much as i can!