Should I Upgrade my server RAM?

Portal Home > Knowledgebase > Articles Database > Should I Upgrade my server RAM?

Posted by khanbaba, 09-22-2012, 11:56 PM
Hello, My server has 4GB of Ram, here is what free -m shows. root@server [~]# free -m total used free shared buffers cached Mem: 3939 3698 240 0 115 2117 -/+ buffers/cache: 1465 2474 Swap: 2047 0 2047 my.cnf file has the following: [mysqld] connect_timeout=30 query_cache_min_res_unit=2K query_cache_size=512M ;record_buffer=3M max_allowed_packet=100M log-slow-queries="/var/log/slow.log" wait_timeout=50 skip-external-locking interactive-timeout=30 read_buffer_size=8M thread_cache=8 thread_stack=16M max_user_connections=50 max_heap_table_size=256M ft_min_word_len=3 datadir="/var/lib/mysql" sort_buffer_size=8M read_rnd_buffer_size=8M long_query_time=2 tmp_table_size=256M max_connections=250 join_buffer_size=6M socket="/var/lib/mysql/mysql.sock" thread_cache_size=128M table_cache=1500K myisam_sort_buffer_size=24M innodb_file_per_table=1 default-storage-engine=MyISAM [isamchk] read_buffer=8M key_buffer=16M write_buffer=8M sort_buffer=16M [mysqlhotcopy] interactive-timeout local-infile=0 [mysql] no-auto-rehash [myisamchk] read_buffer=8M key_buffer=16M write_buffer=8M sort_buffer=16M [safe_mysqld] err-log="/var/log/mysqld.log" open_files_limit=8192 pid-file="/var/lib/mysql/mysql.pid" max_allowed_packet=32M --------- Server Hosting company suggested to upgrade the Ram, our website is highly dependent on Php/MySQL. Please suggest.
Posted by BestServerSupport, 09-23-2012, 12:39 AM
4 GB RAM sounds good. You probably need to tweak some my.cnf and Apache settings for better performance. 1. What is the processor type and speed? 2. How many average visitors you got per day? 3. Which OS are you using?
Posted by TravisT-[SSS], 09-23-2012, 12:44 AM
Shared hosting? How is PHP being ran? Are you using Eac/XCache at all?
Posted by khanbaba, 09-23-2012, 12:57 AM
Thanks for your reply. 1. Processor is Intel Xeon E3 2. We have few sites hosted on server unique visitors for all sites sums up about 15-18k per day. 3. CentOS Also i have posted the my.cnf settings, what changes you will suggest?
Posted by khanbaba, 09-23-2012, 12:59 AM
No its a dedicated server, I don't have any about Eac/XCache, can you please further elaborate? -Regards.
Posted by funkywizard, 09-23-2012, 03:26 AM
2gb of your 4gb ram is being used by disk cache, so I would say you probably have enough unless your server occasionally crashes during peak times. Ram is cheap, so if you suspect you need more, it shouldn't cost much to get 8gb or 16gb ram, but from what you've shown so far, it doesn't look like you need it.
Posted by khanbaba, 09-23-2012, 05:02 AM
Thanks for your reply, Well is there any way to optimize the Ram usage I mean can we limit the Usage by Disk Cache? Also while checking WHM Stats I can see Memory usage never goes above 40%. Please suggest.
Posted by funkywizard, 09-23-2012, 06:21 AM
Disk caching is an automatic function of the linux OS. It is used to speed up disk access if you have extra memory. If you need the memory for programs instead, the contents of the cache will be flushed automatically, so there is no reason to try to limit this in any way.
Posted by George_Fusioned, 09-23-2012, 06:40 AM
Try using tmpfs for your MySQL tmpdir. Here's how you can do it:
Posted by dareORdie, 09-23-2012, 06:46 AM
Hello, Well, Have you checked my.cnf using mysqltuner script ? If no then I will suggest you please run this script on your server and then mysqltuner will suggest you where you need to changes in my.cnf. https://github.com/rackerhacker/MySQ.../mysqltuner.pl Thanks
Posted by khanbaba, 09-23-2012, 07:11 AM
Hi, I am already using it, below please find result of it. root@server [~]# /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl >> MySQLTuner 1.1.2 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.25-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 1585) [--] Data in InnoDB tables: 128K (Tables: 8) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [--] Data in MEMORY tables: 55M (Tables: 16) [!!] Total fragmented tables: 28 -------- Performance Metrics ------------------------------------------------- [--] Up for: 1d 18h 19m 53s (17M q [117.773 qps], 1M conn, TX: 67B, RX: 1B) [--] Reads / Writes: 63% / 37% [--] Total buffers: 920.0M global + 46.0M per thread (300 max threads) [!!] Maximum possible memory usage: 14.4G (373% of installed RAM) [OK] Slow queries: 0% (8/17M) [OK] Highest usage of available connections: 21% (65/300) [OK] Key buffer size / total MyISAM indexes: 8.0M/167.2M [OK] Key buffer hit rate: 99.9% (362M cached / 335K reads) [OK] Query cache efficiency: 77.6% (10M cached / 13M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (1K temp sorts / 806K sorts) [!!] Joins performed without indexes: 480 [OK] Temporary tables created on disk: 4% (28K on disk / 622K total) [OK] Thread cache hit rate: 99% (65 created / 1M connections) [OK] Table cache hit rate: 49% (1K open / 3K opened) [OK] Open file limit used: 41% (3K/8K) [OK] Table locks acquired immediately: 99% (5M immediate / 5M locks) [OK] InnoDB data size / buffer pool: 128.0K/128.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Reduce your overall MySQL memory footprint for system stability Adjust your join queries to always utilize indexes Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** join_buffer_size (> 6.0M, or always use indexes with joins) root@server [~]#
Posted by khanbaba, 09-23-2012, 07:12 AM
I really appreciate that you replied, well can you please elaborate what the above mentioned thing will do? I will defiantly add it but please clear up my mind on that. Thanks
Posted by George_Fusioned, 09-23-2012, 07:32 AM
Queries written to the tmp table, are by default written to disk. Disk of course is a lot slower than RAM, so this makes the process slower => queries execute slower. By letting MySQL use tmpfs for it's tmpdir we make sure that everything written to it gets written into RAM (fast!) rather than disk (slow..)
Posted by BestServerSupport, 09-23-2012, 08:29 AM
Yes, By letting MySQL use tmpfs for it's tmpdir is a better idea to improve performance.
Posted by khanbaba, 09-23-2012, 09:53 AM
I will implement the suggestion and will update you with the results. Meanwhile if you have any other suggestions that relates to MySQL optimization I will request to please share those. -Regards.
Posted by RRWH, 09-23-2012, 10:12 AM
Never a good thing to have an application configured to use more resources than you have. Leave the config as-is and make sure your server has at least 16Gig.
Posted by khanbaba, 09-23-2012, 10:20 AM
Someone is suggested to move to MariaDB, can anybody please comment on that?
Posted by George_Fusioned, 09-23-2012, 12:19 PM
Take a backup of your current my.cnf config, in case you need to revert back. - Reduce your max connections to 100 (your max was 65/300) - Change the following buffer sizes to reduce per thread memory and add key_buffer - Finally you can reduce query_cache_size and add query_cache_limit. Make these changes, restart mysql, wait 24 hours, re-run mysqltuner and post the output here again.
Posted by khanbaba, 09-23-2012, 12:36 PM
I ll do this immediately, also here is what we have done after you suggested. below is the response from Support. ************* I've successfully implemented this tmpfs for MySQL tmp. I added the following line to your fstab file: tmpfs on /tmp/mysqltmp type tmpfs (rw,uid=27,gid=27,size=512M,nr_inodes=10k,mode=0700) When I first restarted it, I saw this in your MySQL error log: 120923 9:47:28 InnoDB: Using Linux native AIO 120923 9:47:28 InnoDB: Error: Linux Native AIO is not supported on tmpdir. InnoDB: You can either move tmpdir to a file system that supports native AIO InnoDB: or you can set innodb_use_native_aio to FALSE to avoid this message. 120923 9:47:28 InnoDB: Error: Linux Native AIO check on tmpdir returned error[22] 120923 9:47:28 InnoDB: Warning: Linux Native AIO disabled. I subsequently added the following to your /etc/my.cnf innodb_use_native_aio=0 I'm not entirely sure of the performance implications of this change. Because you have almost no InnoDB usage anyways, I don't think it's an important consideration at this time. Right now, your MySQL error log shows no errors and it looks like things are running well. Let me know your thoughts. Kind regards, Nick Teeple
Posted by George_Fusioned, 09-25-2012, 04:53 AM
Hey Nick, Did you make the memory settings adjustments? What is the output of mysqltuner now?
Posted by khanbaba, 09-26-2012, 11:40 PM
The MySQL tuner script has completed. Pasted bellow are the results. Based on the recommendations I have made the following changes: 1. Increased the max_connections value from 125 to 175. 2. Lowered wait_timeout and interactive_timeout respectively to 40 and 20. 3. Increased the query_cache_size and the join_buffer_size respectively to 288M and 6M. Here are the results from the mysqltuner script: # /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl >> MySQLTuner 1.1.2 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.25-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 1585) [--] Data in InnoDB tables: 128K (Tables: 8) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [--] Data in MEMORY tables: 27M (Tables: 16) [!!] Total fragmented tables: 36 -------- Performance Metrics ------------------------------------------------- [--] Up for: 2d 17h 46m 3s (30M q [128.693 qps], 1M conn, TX: 113B, RX: 3B) [--] Reads / Writes: 63% / 37% [--] Total buffers: 688.0M global + 24.0M per thread (125 max threads) [!!] Maximum possible memory usage: 3.6G (93% of installed RAM) [OK] Slow queries: 0% (29/30M) [!!] Highest connection usage: 88% (110/125) [OK] Key buffer size / total MyISAM indexes: 128.0M/169.0M [OK] Key buffer hit rate: 100.0% (573M cached / 15K reads) [OK] Query cache efficiency: 79.2% (18M cached / 23M selects) [!!] Query cache prunes per day: 112338 [OK] Sorts requiring temporary tables: 0% (1K temp sorts / 1M sorts) [!!] Joins performed without indexes: 1114 [OK] Temporary tables created on disk: 3% (33K on disk / 971K total) [OK] Thread cache hit rate: 99% (110 created / 1M connections) [OK] Table cache hit rate: 99% (2K open / 2K opened) [OK] Open file limit used: 15% (3K/24K) [OK] Table locks acquired immediately: 99% (8M immediate / 8M locks) [OK] InnoDB data size / buffer pool: 128.0K/32.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Reduce your overall MySQL memory footprint for system stability Reduce or eliminate persistent connections to reduce connection usage Increasing the query_cache size over 128M may reduce performance Adjust your join queries to always utilize indexes Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** max_connections (> 125) wait_timeout (< 50) interactive_timeout ( 256M) [see warning above] join_buffer_size (> 4.0M, or always use indexes with joins)
Posted by RRWH, 09-27-2012, 01:57 AM
As a general rule how is the server performing now? It still looks like more RAM would be of benefit to you.
Posted by khanbaba, 09-27-2012, 02:01 AM
I can't see any issues with server its working fluently, by the way how should I noticed that RAM usage is 100% and its blocking visitors from viewing pages etc. I haven't noticed anything though.
Posted by khanbaba, 09-28-2012, 10:16 AM
We have updated the Ram from 4GB to 8GB. below please find the results. root@server [~]# free -m total used free shared buffers cached Mem: 7973 6529 1444 0 344 4328 -/+ buffers/cache: 1855 6117 Swap: 2047 0 2047 root@server [~]# /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl >> MySQLTuner 1.1.2 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.25-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 1585) [--] Data in InnoDB tables: 128K (Tables: 8) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [--] Data in MEMORY tables: 19M (Tables: 16) [!!] Total fragmented tables: 42 -------- Performance Metrics ------------------------------------------------- [--] Up for: 2h 49m 57s (1M q [104.141 qps], 78K conn, TX: 4B, RX: 106M) [--] Reads / Writes: 63% / 37% [--] Total buffers: 720.0M global + 26.0M per thread (175 max threads) [OK] Maximum possible memory usage: 5.1G (66% of installed RAM) [OK] Slow queries: 0% (21/1M) [OK] Highest usage of available connections: 17% (31/175) [OK] Key buffer size / total MyISAM indexes: 128.0M/169.7M [OK] Key buffer hit rate: 100.0% (28M cached / 10K reads) [OK] Query cache efficiency: 72.7% (564K cached / 776K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (455 temp sorts / 60K sorts) [!!] Joins performed without indexes: 35 [OK] Temporary tables created on disk: 5% (2K on disk / 42K total) [OK] Thread cache hit rate: 99% (31 created / 78K connections) [OK] Table cache hit rate: 26% (1K open / 6K opened) [OK] Open file limit used: 13% (3K/24K) [OK] Table locks acquired immediately: 99% (386K immediate / 387K locks) [OK] InnoDB data size / buffer pool: 128.0K/32.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Adjust your join queries to always utilize indexes Variables to adjust: join_buffer_size (> 6.0M, or always use indexes with joins) root@server [~]# Do you think adding more Ram has done the trick? Or we still need further optimization at my.cnf please suggest.
Posted by RRWH, 09-28-2012, 09:18 PM
you will never be able to just leave your MySQL config - as long as your database is growing, it will need occasional tweaking. At the moment, it looks good, so what you shoud do from here is to not stress over it and check the performance every few weeks, You will find that you might have to make small tweaks every now and again. Justpart of running a server - this is never going to be a set and never look at again config.
Posted by khanbaba, 10-02-2012, 11:52 AM
I am extremely getting fraustrated about all this while checking the results of tuner script, below please find that line. [!!] Maximum possible memory usage: 8.4G (107% of installed RAM) that script seems to be going mad . while checking htop command it always suggest that i am only using 2GB of Ram. Please help.

Add to Favourites Print this Article