Skip to main content

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

Comments

12 comments

Date Votes
  • 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
    0
  • 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.
    0
  • 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/
    0
  • 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
    0
  • cPanelPeter cPanel Staff
    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
    0
  • SpaceCowboy
    [quote="thinkbot, post: 1567851">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! [quote="cPanelPeter, post: 1567921">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?
    0
  • cPanelPeter cPanel Staff
    [quote="SpaceCowboy, post: 1567972">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?
    0
  • SpaceCowboy
    [quote="cPanelPeter, post: 1568011">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.
    0
  • 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.
    0
  • 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.
    0
  • 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://linuxcommand.org/learning_the_shell.php]LinuxCommand.org: Learning the shell. [url=http://community.linuxmint.com/tutorial/view/100]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":
    0
  • SpaceCowboy
    Thanks for all the links really appreciated. i'll try to learn as much as i can!
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post