Hello,

We have a 1 GB Linux box on which we have observed that MySQL consumes almost entire available physical memory. We increased the Swap memory from 1 GB to 3 GB hoping that MySQL will start using the swap memory along with RAM. What we observed though was that the swap memory was hardly used until the physical memory was first exhausted. Could anyone please share information related the queries that i am listing below?

1) Is there a MySQL parameter setting whcih allows me to force MySQL to use swap memory on the Linux box?

2) Is there a MySQL parameter setting which allows me to set the maximum memory (both RAM and swap)that can be used by MySQL server?

3) Which are the MySQL parameters that i would have to focus on to improve MySQL memory performance?

Thanking you in advance.

Regards,
Vikrant

Hello vikrantpatil,

I am not an expert but I do fiddle with my MySQL settings occasionally. Before I share with you what I know, can you tell us how you figured out that your "MySQL consumes almost entire available physical memory"?

It is my experience that when this server starts using its swap memory, everything slows down to a crawl. So most of the time, I have to watch that the MySQL server (on this machine) uses just 33% of the physical RAM available on this server and not any more than that.

As for your 1st question, I believe there is no such option for you to do that - at least not that I am aware.

Looking forward to your response...

Hi Admin,

Thanks a lot for your time and for your quick reply.

We checked the free memory available by using free -m command on Linux server before we started putting load on the MySQL server by a testing tool.

This output of free -m command showed that 666 MB of RAM was available.

Then we executed the free -m after increasing the number of MySQL connections which in turn applied load on the MySQL (by executing random queries) and the output of the free -m command showed that the free memory (RAM) had gone down to just 11 MB.

Further we also used the following code to determine the memory that MySQL was using both before applying load when it was 2.2897 GB and after applying load when it reached 2.4438 GB.


SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_additional_mem_pool_size';
SHOW VARIABLES LIKE 'innodb_log_buffer_size';
SHOW VARIABLES LIKE 'thread_stack';
SET @kilo_bytes = 1024;
SET @mega_bytes = @kilo_bytes * 1024;
SET @giga_bytes = @mega_bytes * 1024;
SET @innodb_buffer_pool_size = 2 * @giga_bytes;
SET @innodb_additional_mem_pool_size = 16 * @mega_bytes;
SET @innodb_log_buffer_size = 8 * @mega_bytes;
SET @thread_stack = 192 * @kilo_bytes;
SELECT
( @@key_buffer_size + @@query_cache_size + @@tmp_table_size
+ @innodb_buffer_pool_size + @innodb_additional_mem_pool_size
+ @innodb_log_buffer_size
+ @@max_connections * (
@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size
+ @@join_buffer_size + @@binlog_cache_size + @thread_stack
) ) / @giga_bytes AS MAX_MEMORY_GB;


and yes you are absolutely right,when MySQL does use swap memory after RAM is exhausted, everything slows down to a crawl.

Do let me know if you require more information.

Thanks,
Vikrant