{"id":1198,"date":"2023-10-04T16:39:18","date_gmt":"2023-10-04T11:09:18","guid":{"rendered":"https:\/\/www.veeble.org\/kb\/?p=1198"},"modified":"2025-03-14T12:06:52","modified_gmt":"2025-03-14T06:36:52","slug":"optimizing-mysql-mariadb-ram-usage-resource-efficiency-guide","status":"publish","type":"post","link":"https:\/\/www.veeble.com\/kb\/optimizing-mysql-mariadb-ram-usage-resource-efficiency-guide\/","title":{"rendered":"Optimizing MySQL\/MariaDB RAM Usage: Resource Efficiency Guide"},"content":{"rendered":"\n

Your server’s performance is sluggish, and your websites are crawling like a snail. The main issue? MySQL is consuming too much of your server’s memory, as shown by commands like top and free<\/a>. How can you improve MySQL performance and enhance your server’s efficiency?<\/p>\n\n\n\n

\n
\"High<\/a><\/figure>\n<\/figure>\n\n\n\n
\n
\"High<\/a><\/figure>\n<\/figure>\n\n\n\n

Notice that mysql<\/a> is at the top of the list, indicating a high memory occupancy by MySQL. You can address this issue by implementing limits to reduce MySQL’s memory utilization. The data in the ‘buff\/cache’ section represents cached memory pages containing data used by MySQL and other server services. The system caches data in memory to minimize the need for read\/write operations to slower hard disk devices, significantly improving overall system performance and reducing latency in responses from disk devices.
<\/p>\n\n\n

Flushing the Memory (RAM) cache<\/h3>\n\n\n

To flush the Memory (RAM) cache by following the instructions:

To release cached memory effectively, you can leverage the ‘drop_caches<\/a>‘ feature embedded in the Linux kernel. But before you proceed, it’s crucial to ensure all ‘dirty’ pages are written to disk. Execute the ‘sync<\/a>‘ command to accomplish this, reducing the number of dirty pages within the caches.<\/p>\n\n\n\n

Accessing the ‘drop_caches’ capability involves passing a specific numeric control to the ‘\/proc\/sys\/vm\/drop_caches’ path through the ‘echo’ command. For example:<\/p>\n\n\n\n

echo 3 > \/proc\/sys\/vm\/drop_caches<\/pre>\n\n\n\n
More details regarding the above command will be available in the blog: https:\/\/linux-mm.org\/Drop_Caches<\/a> . Refer to the article before doing the command!<\/p>\n\n\n
Flushing Memory (RAM) Cache automatically<\/h3>\n\n\n
You can use a cronjob to automatically flush the Memory cache by following the instructions:<\/p>\n\n\n\n
The root crontab provides the capability to schedule the execution of a script at specific times. You can achieve this by configuring a task in the cron job<\/a> file, based on an analysis of how frequently you require cache clearance.
To accomplish this task, Open the root user’s crontab file by executing the command:<\/p>\n\n\n\n
crontab -u root -e<\/pre>\n\n\n\n
Within the crontab editor, add the specific cron job entry that suits your requirements and then save.
For example:
0 0 * * * echo 3 > \/proc\/sys\/vm\/drop_caches<\/em><\/p>\n\n\n\n
For more details regarding setting cron jobs refer to the article: https:\/\/www.geeksforgeeks.org\/crontab-in-linux-with-examples\/<\/a><\/p>\n\n\n\n
\n
Note: The steps outlined for adjusting MySQL parameters are applicable to systems that possess control over their own kernel. However, in cases such as VPS environments utilizing OpenVZ technology<\/a>, where each VPS instance does not have its own kernel, it may not be possible to apply these settings since the kernel settings are typically managed at the host level rather than at the individual VPS level.<\/p>\n<\/blockquote>\n\n\n
Altering caching by editing MySQL configuration<\/h3>\n\n\n
To alter caching in the MySQL configuration file, follow the steps below:<\/p>\n\n\n\n
 1. Ensure you have a safety net by backing up your database first.<\/p>\n\n\n\n
mysqldump -u [username] -p [database_name] > backup.sql<\/pre>\n\n\n\n
2. Execute the below command to understand its current configuration:<\/p>\n\n\n\n
mysql -u root -p -e \"SHOW VARIABLES;\"<\/pre>\n\n\n\n
3. Open the MySQL configuration file, typically found at \/etc\/my.cnf or \/etc\/mysql\/my.cnf<\/p>\n\n\n\n
nano \/etc\/my.cnf<\/pre>\n\n\n\n
 3.1. Set the size of the InnoDB buffer pool (innodb_buffer_pool_size) based on available RAM:<\/p>\n\n\n\n
innodb_buffer_pool_size = [size]G<\/pre>\n\n\n\n
 3.2. Make sure to set the values to be following for all key values in the configuration file:<\/p>\n\n\n\n
query_cache_type = 1\nquery_cache_size = [size]M\nkey_buffer_size = [size]M\nthread_cache_size = [size]\nthread_concurrency = [value]\nslow_query_log = 1\nslow_query_log_file = \/var\/log\/mysql\/mysql-slow.log\nlog_error = \/var\/log\/mysql\/error.log\n<\/pre>\n\n\n\n
4. Restart the mysqld by the following command:<\/p>\n\n\n\n
systemctl restart mysql<\/pre>\n\n\n\n
<\/div>\n\n\n\n
Premium KVM VPS, Without the Premium Price<\/h3>
Get the performance you need at a price you’ll love. Our KVM VPS plans are designed for value.<\/p><\/div>
View Pricing<\/a><\/div><\/div>\n","protected":false},"excerpt":{"rendered":"
Your server’s performance is sluggish, and your websites are crawling like a snail. The main issue? MySQL is consuming too much of your server’s […]<\/p>\n","protected":false},"author":7,"featured_media":8077,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_uag_custom_page_level_css":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"set","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[11],"tags":[],"class_list":["post-1198","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql"],"uagb_featured_image_src":{"full":["https:\/\/www.veeble.com\/kb\/wp-content\/uploads\/2023\/10\/Optimizing-MySQLMariaDB-RAM-Usage-Resource-Efficiency-Guide.jpg",1366,768,false],"thumbnail":["https:\/\/www.veeble.com\/kb\/wp-content\/uploads\/2023\/10\/Optimizing-MySQLMariaDB-RAM-Usage-Resource-Efficiency-Guide-150x150.jpg",150,150,true],"medium":["https:\/\/www.veeble.com\/kb\/wp-content\/uploads\/2023\/10\/Optimizing-MySQLMariaDB-RAM-Usage-Resource-Efficiency-Guide-300x169.jpg",300,169,true],"medium_large":["https:\/\/www.veeble.com\/kb\/wp-content\/uploads\/2023\/10\/Optimizing-MySQLMariaDB-RAM-Usage-Resource-Efficiency-Guide-768x432.jpg",768,432,true],"large":["https:\/\/www.veeble.com\/kb\/wp-content\/uploads\/2023\/10\/Optimizing-MySQLMariaDB-RAM-Usage-Resource-Efficiency-Guide-1024x576.jpg",1024,576,true],"1536x1536":["https:\/\/www.veeble.com\/kb\/wp-content\/uploads\/2023\/10\/Optimizing-MySQLMariaDB-RAM-Usage-Resource-Efficiency-Guide.jpg",1366,768,false],"2048x2048":["https:\/\/www.veeble.com\/kb\/wp-content\/uploads\/2023\/10\/Optimizing-MySQLMariaDB-RAM-Usage-Resource-Efficiency-Guide.jpg",1366,768,false]},"uagb_author_info":{"display_name":"Adrian Antony","author_link":"https:\/\/www.veeble.com\/kb\/author\/adrian\/"},"uagb_comment_info":0,"uagb_excerpt":"Your server’s performance is sluggish, and your websites are crawling like a snail. The main issue? MySQL is consuming too much of your server’s […]","_links":{"self":[{"href":"https:\/\/www.veeble.com\/kb\/wp-json\/wp\/v2\/posts\/1198","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.veeble.com\/kb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.veeble.com\/kb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.veeble.com\/kb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.veeble.com\/kb\/wp-json\/wp\/v2\/comments?post=1198"}],"version-history":[{"count":14,"href":"https:\/\/www.veeble.com\/kb\/wp-json\/wp\/v2\/posts\/1198\/revisions"}],"predecessor-version":[{"id":7563,"href":"https:\/\/www.veeble.com\/kb\/wp-json\/wp\/v2\/posts\/1198\/revisions\/7563"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.veeble.com\/kb\/wp-json\/wp\/v2\/media\/8077"}],"wp:attachment":[{"href":"https:\/\/www.veeble.com\/kb\/wp-json\/wp\/v2\/media?parent=1198"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.veeble.com\/kb\/wp-json\/wp\/v2\/categories?post=1198"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.veeble.com\/kb\/wp-json\/wp\/v2\/tags?post=1198"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}