Higher MySQL RAM usage, need to optimize

cbu

February 12, 2014 20:51

Hello all! I have VPS with running 2.5GB RAM, Intel(R) Xeon(R) CPU E5-2630 0 @ 2.30GHz (cache 15360 KB). It front end Nginx and backend Apache. This VPS run two wordpress sites. First site page preview 3,000-3,500 (daily) second site 500-600 (daily). First site mysql database size is 56.1MB and 2nd site MySQL database size is 10.3MB.

root@host [~]# mysql --version
mysql  Ver 14.14 Distrib 5.5.35, for Linux (i686) using readline 5.1

When I'm running ps -aux command, I can see higher RAM usage

root@host [~]# ps -aux
Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQ

USER   PID    %CPU %MEM    VSZ   RSS      TTY   STAT START   TIME COMMAND
mysql  11980  0.4       3.5   755956 93016   ?        Sl   07:50     0:05  /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql -

When I'm running free -m I receive following output.

root@host [~]# free -m
             total       used       free     shared    buffers     cached
Mem:          2560       1927        632          0          0       1516
-/+ buffers/cache:        410       2149
Swap:            0          0          0

When I'm running MySQL tuner, I get following output.

root@host [~]# ./mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden 
 >>  Bug reports, feature requests, and downloads at http://pastebin.com/x5FwuKiK

How do I reduce MySQL memory usage?

Thanks!

Comments

32 comments

thinkbot

February 13, 2014 11:48
replace my.cnf with this [mysqld] default-storage-engine = MyISAM local-infile = 0 max_connections = 50 wait_timeout = 30 connect_timeout = 10 thread_cache_size = 16 table_open_cache = 700 key_buffer_size = 30M join_buffer_size = 512K sort_buffer_size=256K query_cache_type = 1 query_cache_size = 30M query_cache_limit = 1M tmp_table_size = 30M max_heap_table_size = 30M open_files_limit=5000 innodb_buffer_pool_size = 30M innodb_file_per_table = 1 innodb_stats_on_metadata=0 slow_query_log=1 slow_query_log_file=mysql-slow.log long_query_time=0.1 memory savings will be huge, without loosing anything from the performance
0
cbu

February 13, 2014 12:21
Thank you thinkbot your support. Now it giving me following recommendation. Could you please tell me how to correct that issue? Also is it OK use "Maximum possible memory usage: 201.6M (7% of installed RAM)" Could you please tell me how do I adjust Maximum possible memory usage? This is my MySQLTuner log. [QUOTE]root@host [~]# mysqltuner.pl >> MySQLTuner 1.2.0 - Major Hayden >> Bug reports, feature requests, and downloads at replace my.cnf with this [mysqld] default-storage-engine = MyISAM local-infile = 0
0
thinkbot

February 13, 2014 12:36
There is nothing to adjust now, it just tells you that your maximum memory usage for MySQL can be 201.6M, meaning 7% of installed RAM previously it was 786.1MB [OK] Maximum possible memory usage: 786.1M (30% of installed RAM) of course it's not very accurate, its just a pointer, calculating mysql memory is bit more complicated
0
cPanelMichael

February 13, 2014 12:59
Hello :) To note, you might find the tuner available in the following thread better suited towards newer versions of MySQL: mysqlmymonlite.sh server stats gathering tool for cPanel Server Thank you.
0
cbu

February 13, 2014 13:18
Now I'm using thinkbot updated configuration. Looks like table_cache getting lower in every minutes. 20 minutes ago it's ratio is 98%, but now it's reduced to 10%. PHPMyAdmin advice to "Flushing the query cache". Here's it advice, [QUOTE]Issue: Less than 80% of the query cache is being utilized. Recommendation: This might be caused by query_cache_limit being too low. Flushing the query cache might help as well. Justification: The current ratio of free query cache memory to total query cache size is 38.6%. It should be above 80% Used variable / formula: 100 - Qcache_free_memory / query_cache_size * 100 Test: value < 80
This is the MySQL Tuner result. [QUOTE] root@host [~]# mysqltuner.pl >> MySQLTuner 1.2.0 - Major Hayden >> Bug reports, feature requests, and downloads at - - - Updated - - - Thank you Michael. I use MySQLTuner. So if I use both of these scripts same time, it will cause any issue? [quote="cPanelMichael, post: 1571731">Hello :) To note, you might find the tuner available in the following thread better suited towards newer versions of MySQL: mysqlmymonlite.sh server stats gathering tool for cPanel Server Thank you.
[COLOR="silver">- - - Updated - - - Thank you Michael. I use MySQLTuner. So if I use both of these scripts same time, it will cause any issue? [quote="cPanelMichael, post: 1571731">Hello :) To note, you might find the tuner available in the following thread better suited towards newer versions of MySQL: mysqlmymonlite.sh server stats gathering tool for cPanel Server Thank you.
0
thinkbot

February 13, 2014 13:22
Hey, this is not an issue, you got 239 MyiSAM tables, they all fit in table_open_cache 700 buffer, there are also some temporary tables created along the way It's very good
0
cbu

February 14, 2014 01:22
[quote="thinkbot, post: 1571802">Hey, this is not an issue, you got 239 MyiSAM tables, they all fit in table_open_cache 700 buffer, there are also some temporary tables created along the way It's very good
Thank thinkbot.
0
cbu

February 14, 2014 13:28
Randomly I can see several MySQL server has gone away for query MySQL error. How do I fix it? eg:- [QUOTE]WordPress database error MySQL server has gone away for query SELECT option_value FROM xxxx_xxxx_options WHERE option_name = '_transient_doing_cron' LIMIT 1 made by _get_cron_lock
0
thinkbot

February 14, 2014 16:11
this particular query should be immediate, probably query before that one took longer than wait_timeout allowed checkout slow query log
0
cbu

February 15, 2014 14:11
After the 24 hours, I get following result. [QUOTE]Last login: Sat Feb 15 18:26:37 2014 from 103.21.164.6 root@host [~]# ./mysqltuner.pl >> MySQLTuner 1.2.0 - Major Hayden >> Bug reports, feature requests, and downloads at for info about MySQL's Enterprise Monitoring and Advisory Service SLOW QUERIES The slow query log is enabled. Current long_query_time = 0.100000 sec. You have 100 out of 1851471 that take longer than 0.100000 sec. to complete Your long_query_time seems to be fine BINARY UPDATE LOG The binary update log is NOT enabled. You will not be able to do point in time recovery See [url=http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html]MySQL :: MySQL 5.5 Reference Manual :: 7.5 Point-in-Time (Incremental) Recovery Using the Binary Log WORKER THREADS Current thread_cache_size = 16 Current threads_cached = 14 Current threads_per_sec = 0 Historic threads_per_sec = 0 Your thread_cache_size is fine MAX CONNECTIONS Current max_connections = 50 Current threads_connected = 2 Historic max_used_connections = 51 The number of used connections is 102% of the configured maximum. You should raise max_connections INNODB STATUS Current InnoDB index space = 1 M Current InnoDB data space = 4 M Current InnoDB buffer pool free = 51 % Current innodb_buffer_pool_size = 30 M Depending on how much space your innodb indexes take up it may be safe to increase this value to up to 2 / 3 of total system memory MEMORY USAGE Max Memory Ever Allocated : 172 M Configured Max Per-thread Buffers : 65 M Configured Max Global Buffers : 106 M Configured Max Memory Limit : 171 M Physical Memory : 2.50 G Max memory limit seem to be within acceptable norms KEY BUFFER Current MyISAM index space = 7 M Current key_buffer_size = 30 M Key cache miss rate is 1 : 24817 Key buffer free ratio = 79 % Your key_buffer_size seems to be too high. Perhaps you can use these resources elsewhere QUERY CACHE Query cache is enabled Current query_cache_size = 30 M Current query_cache_used = 24 M Current query_cache_limit = 2 M Current Query cache Memory fill ratio = 80.27 % Current query_cache_min_res_unit = 4 K However, 12701 queries have been removed from the query cache due to lack of memory Perhaps you should raise query_cache_size MySQL won't cache query results that are larger than query_cache_limit in size SORT OPERATIONS Current sort_buffer_size = 256 K Current read_rnd_buffer_size = 256 K Sort buffer seems to be fine JOINS Current join_buffer_size = 516.00 K You have had 5626 queries where a join could not use an index properly You should enable "log-queries-not-using-indexes" Then look for non indexed joins in the slow query log. If you are unable to optimize your queries you may want to increase your join_buffer_size to accommodate larger joins in one pass. Note! This script will still suggest raising the join_buffer_size when ANY joins not using indexes are found. OPEN FILES LIMIT Current open_files_limit = 5000 files The open_files_limit should typically be set to at least 2x-3x that of table_cache if you have heavy MyISAM usage. Your open_files_limit value seems to be fine TABLE CACHE Current table_open_cache = 700 tables Current table_definition_cache = 400 tables You have a total of 308 tables You have 521 open tables. The table_cache value seems to be fine TEMP TABLES Current max_heap_table_size = 40 M Current tmp_table_size = 40 M Of 61907 temp tables, 16% were created on disk Created disk tmp tables ratio seems fine TABLE SCANS Current read_buffer_size = 128 K Current table scan ratio = 166 : 1 read_buffer_size seems to be fine TABLE LOCKING Current Lock Wait ratio = 1 : 3219 You may benefit from selective use of InnoDB. If you have long running SELECT's against MyISAM tables and perform frequent updates consider setting 'low_priority_updates=1' If you have a high concurrency of inserts on Dynamic row-length tables consider setting 'concurrent_insert=ALWAYS'. root@host [~]#
0
thinkbot

February 15, 2014 14:24
you dont have to increase all the values recommended by this script, those are only pointers, you need to understand the impact of the change before making the actuall change Please review your slow queries log, since you probably have some queries that stuck your server, and this is why your connections limit gets to max you dont have to increase or change those values wait_timeout (< 30) interactive_timeout (< 28800)
0
cbu

February 15, 2014 14:29
[quote="thinkbot, post: 1573142">this particular query should be immediate, probably query before that one took longer than wait_timeout allowed checkout slow query log
Thank you thinkbot your support. I checked the slow query log, it has lots of things. [QUOTE]# Query_time: 0.283086 Lock_time: 0.000267 Rows_sent: 0 Rows_examined: 1 use database_name_here; SET timestamp=1392400502; UPDATE `table_prefix_here_options` SET `option_value`
Most of the log query shows wp options table. Yes there are some wordpress plugin too. Specially security plugin (eg:-Wordfence Security) [COLOR="silver">- - - Updated - - - [quote="thinkbot, post: 1573532">you dont have to increase all the values recommended by this script, those are only pointers, you need to understand the impact of the change before making the actuall change Please review your slow queries log, since you probably have some queries that stuck your server, and this is why your connections limit gets to max you dont have to increase or change those values wait_timeout (< 30) interactive_timeout (< 28800) you can increase table_open_cache to 6000 since you got a lot of temporary tables
Thanks again thinkbot. I just increased table_open_cache. Looks like it improved the page loading time. Are there any way to periodically delete unwanted table cache? I think only way to reduce temporary tables is restart the SQL server. Am I correct? I can't restart SQL service regularly.
0
thinkbot

February 15, 2014 14:33
"Are there any way to periodically delete unwanted table cache? I think only way to reduce temporary tables is restart the SQL server. Am I correct? I can't restart SQL service regularly." You don't understand, there is nothing to deltee there to reduce temporary tables on disk, you have to optimize/rewrite slow queries First check what queries takes the most time, to do that review slow query log then you can check why they take so much time, and optimize them and install munin plugin in WHM, so you can have better view on server resources usage
0
cbu

February 15, 2014 14:56
[quote="thinkbot, post: 1573561">"Are there any way to periodically delete unwanted table cache? I think only way to reduce temporary tables is restart the SQL server. Am I correct? I can't restart SQL service regularly." You don't understand, there is nothing to deltee there to reduce temporary tables on disk, you have to optimize/rewrite slow queries First check what queries takes the most time, to do that review slow query log then you can check why they take so much time, and optimize them and install munin plugin in WHM, so you can have better view on server resources usage
Thanks again. Yes, I have munin server monitor plugin. Again thank you so much for your help. :D
0
thinkbot

February 15, 2014 15:01
if you have, you can post here please images for CPU, LOAD, RAM ? it would show us more the difference and current load
0
cbu

February 16, 2014 14:10
[quote="thinkbot, post: 1573582">if you have, you can post here please images for CPU, LOAD, RAM ? it would show us more the difference and current load
Thank you again think bot your support. These are the MUNIN screenshots. Looks like slow mysql queries started after tweak the my.cnf file. Is it its configuration issue or just a normal? MySQL .vB .vB RAM. .vB Load. .vB CPU .vB [QUOTE][mysqld] default-storage-engine = MyISAM local-infile = 0 event_scheduler = on max_connections = 80 wait_timeout = 30 connect_timeout = 10 thread_cache_size = 16 table_open_cache = 6000 key_buffer_size = 30M join_buffer_size = 512K sort_buffer_size = 256K query_cache_type = 1 query_cache_size = 70M query_cache_limit = 4M query_cache_wlock_invalidate=ON tmp_table_size = 80M max_heap_table_size = 80M open_files_limit=5000 innodb_buffer_pool_size = 30M innodb_buffer_pool_instances = 6 innodb_flush_log_at_trx_commit = 2 innodb_file_per_table = 6 innodb_stats_on_metadata = 0 innodb_use_sys_malloc = 0 #sql tune prime recommendations low_priority_updates = 1 concurrent_insert=ALWAYS #general_log slow_query_log=1 slow_query_log_file=mysql-slow.log long_query_time=0.1 log-error = /var/log/mysqld.log log-queries-not-using-indexes
0
cbu

February 16, 2014 14:31
Is it ok if I adjust long_query_time=0.1 value. Because I can see lots of WordPress database error MySQL server has gone away for query errors in WordPress debug.
0
thinkbot

February 16, 2014 16:34
long_query_time=0.1 means only to mark queries as slow when they take more than 0,1s so it doesn't make any difference, it's just for logging queries, so we know which ones are slower the best thing you can do now, is make a reeview of slow queries cd /root wget
0
cbu

February 17, 2014 14:14
[quote="thinkbot, post: 1574042">long_query_time=0.1 means only to mark queries as slow when they take more than 0,1s so it doesn't make any difference, it's just for logging queries, so we know which ones are slower the best thing you can do now, is make a reeview of slow queries cd /root wget
0
thinkbot

February 17, 2014 17:24
It will generate data based on your current slow log, so you can do it now
0
cbu

February 20, 2014 04:03
[quote="thinkbot, post: 1574632">It will generate data based on your current slow log, so you can do it now
After the 24 hours left, this is my log. [url=http://pastebin.com/FJ1G2z8V]slow queries - Pastebin.com Could you please tell me what's the wrong in my database?
0
thinkbot

February 20, 2014 08:47
./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt did you run this ? you were supposed to copy slow.txt file, the result of pt-query-digest anyways, based on this file, slow queries are from # Time range: 2014-02-13 19:09:55 to 21:54:02 only and there is nothing really there, except some standard wordpress queries So it's good
0
cbu

February 20, 2014 13:44
[quote="thinkbot, post: 1577041">./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt did you run this ? you were supposed to copy slow.txt file, the result of pt-query-digest anyways, based on this file, slow queries are from # Time range: 2014-02-13 19:09:55 to 21:54:02 only and there is nothing really there, except some standard wordpress queries So it's good
Thank Thinkbot. Yes, I run it once I installed that script. But after that I rebooted the VPS several times. However before I take that log, I didn't run that command. Should I again take that slow query log? 19.00.00 to 00:00 is the peak time. That time according to the G analytics there's 15-20 active visitors in my site. I think that heavy traffic caused that issue.
0
thinkbot

February 20, 2014 15:29
no no, its becouse you didn't run pt-query-digest again it should be run to update info about slow queries before sending it here Anyways, you don't have to send it, the results are good Your CPU usage is very small, but I dont understand why are you rebooting VPS ?
0
cbu

February 21, 2014 14:51
[quote="thinkbot, post: 1577511">no no, its becouse you didn't run pt-query-digest again it should be run to update info about slow queries before sending it here Anyways, you don't have to send it, the results are good Your CPU usage is very small, but I dont understand why are you rebooting VPS ?
Normally after 24 hours left, (specially my sites backup plugins ran) I can see there's only 300-500MB free RAM. After the reboot I can gain more RAM. That's why I do it.
0
thinkbot

February 21, 2014 15:01
Ohh gossh, it's useless, the RAM you are reading is with cache and buffers Read something in google about memory management in linux
0
cbu

February 23, 2014 13:45
[quote="thinkbot, post: 1578262">Ohh gossh, it's useless, the RAM you are reading is with cache and buffers Read something in google about memory management in linux
Thanks thinkbot. Then if I need to downgrade the VPS, how do I determine its required RAM? :confused:
0
thinkbot

February 23, 2014 13:55
0
cbu

March 05, 2014 14:50
[quote="thinkbot, post: 1579561">
0
cbu

March 06, 2014 14:53
Could you please tell me what do you mean by well optimized? Is it MySQL, Apach, Nginx optimization or anything else? I really need to reduce my VPS cost. Now I'm paying $40 in each month for 2.5GB dual core CPU managed VPS. It's too much cost for me. Monthly earning less than $120.
0

Please sign in to leave a comment.

Comments

Didn't find what you were looking for?