• When MySQL is not configured properly, it ends up using a lot of memory in MySQL.
• Sometimes, the processors in the server requires higher memory than the ones present in the server.
• Coding Complexities, Un-optimized queries, and not configuring the application softwares like PHP, Java & Python properly causes high memory usage in MySQL.

What should be done?

I. Steps to optimize MySQL settings:

• Global Buffers is the memory that is permanently reserved for its use. During server boot-up, this memory is obtained from the operating system & is not released into another process.
• Thread buffers is the memory that is requested as and when a query is processed. It is released back once the query is executed.

Total memory usage in MySQL = Thread Buffers + Global Buffers + Allowed Connections

For dedicated database Server, the value needs to be below 90% of the server memory. For shared server, the value needs to be below 50% of the server memory.

Innodb_buffer_pool_size — The size of the buffer pool plays a crucial role in the performance of the system & is assigned a value 50-70% of the RAM available. Either way, if it is too small, flushing of pages happen excessively & if large, swapping happens due to high competition for memory.

key_buffer_size – This factor helps in identifying the caching and key_buffer_size needs to be focused as per the RAM that is around 20% of RAM size.

Max_connections — It is used for the following

• To prevent a single user from overloading a server.
• To restrict the no. of connections at a given time

Formula: max_connections = (Available RAM – Global Buffers) / Thread Buffers

Query_cache_size: Used when you have tables that do not change often & for identical queries. It stores the text of the SELECT together with the corresponding result. This parameter is mostly used for application servers. Otherwise it is set to zero.

II. Upgrade your RAM & slow queries need to be taken care of:

You need to upgrade the speed of the RAM, if it retorts to swapping memory even after optimizing the database settings. Optimization is needed for queries that take a long time to execute. Slow queries result in high memory usage & once this is fixed, the server performance would be significantly increased.

III. Use the PROCESSLIST:

Identify the top-users & prevent access to abusive users using PROCESSLIST.

If you have any problems with your server management, you can always get in touch with us. Our experience and expertise in Linux / Windows server management keeps your IT infrastructure intact 24×7 and enables robust web presence for your enterprise, thus helping you save time, space and ultimately money.

To know more, you can visit: http://www.actsupport.com/server-management

The post High Memory Usage in SQL – How to avoid it? appeared first on Outsourced Support | Web Hosting Support | Server Management.