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. How can you improve MySQL performance and enhance your server’s efficiency?
Notice that mysqld 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.
Flushing the Memory (RAM) cache
To flush the Memory (RAM) cache by following the instructions:
To release cached memory effectively, you can leverage the ‘drop_caches‘ 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‘ command to accomplish this, reducing the number of dirty pages within the caches.
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:
echo 3 > /proc/sys/vm/drop_caches
More details regarding the above command will be available in the blog: https://linux-mm.org/Drop_Caches . Refer to the article before doing the command!
Flushing Memory (RAM) Cache automatically
You can use a cronjob to automatically flush the Memory cache by following the instructions:
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 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:
crontab -u root -e
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
For more details regarding setting cron jobs refer to the article: https://www.geeksforgeeks.org/crontab-in-linux-with-examples/
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, 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.
Altering caching by editing MySQL configuration
To alter caching in the MySQL configuration file, follow the steps below:
1. Ensure you have a safety net by backing up your database first.
mysqldump -u [username] -p [database_name] > backup.sql
2. Execute the below command to understand its current configuration:
mysql -u root -p -e "SHOW VARIABLES;"
3. Open the MySQL configuration file, typically found at /etc/my.cnf or /etc/mysql/my.cnf
nano /etc/my.cnf
3.1. Set the size of the InnoDB buffer pool (innodb_buffer_pool_size) based on available RAM:
innodb_buffer_pool_size = [size]G
3.2. Make sure to set the values to be following for all key values in the configuration file:
query_cache_type = 1 query_cache_size = [size]M key_buffer_size = [size]M thread_cache_size = [size] thread_concurrency = [value] slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log log_error = /var/log/mysql/error.log
4. Restart the mysqld by the following command:
systemctl restart mysql
