0

We are facing Out of memory issue from last few days. Doing research on that said to update some of the values, but we are not sure what values we should put as per our system configurations.

System Details:

  • Windows Server 2012 R2 Standard
  • OS - 64 bit OS, x64 based processor
  • RAM - 24GB
  • MySQL Version - 5.6.19-log

Let us know how can we get solution on the same? Please find below variables.

    log-output=FILE
    general-log=0
    slow-query-log=1
    slow_query_log_file="MySQLSlow.log"
    long_query_time=10
    log-bin=MySQLBinLog
    expire_logs_days=15
    max_connections=1000
    query_cache_size=350M
    table_open_cache=2000
    tmp_table_size=205M
    thread_cache_size=38
    myisam_max_sort_file_size=100G
    myisam_sort_buffer_size=379M
    key_buffer_size=256M
    read_rnd_buffer_size=256K
    sort_buffer_size=1024K
    innodb_additional_mem_pool_size=48M
    innodb_flush_log_at_trx_commit=1
    innodb_log_buffer_size=16M
    innodb_buffer_pool_size=2304M
    innodb_log_file_size=190M
    innodb_thread_concurrency=33
    innodb_autoextend_increment=64
    innodb_buffer_pool_instances=8
    innodb_concurrency_tickets=5000
    innodb_old_blocks_time=1000
    innodb_open_files=300
    innodb_stats_on_metadata=0
    innodb_file_per_table=1
    innodb_checksum_algorithm=0
    back_log=80
    flush_time=0
    join_buffer_size=256K
    max_allowed_packet=4M
    max_connect_errors=100
    open_files_limit=4161
    query_cache_type=0
    sort_buffer_size=256K
    table_definition_cache=1400
    binlog_row_event_max_size=8K

Please note that we are using INNODB format to store table data. Also note that this server is dedicated to MySQL Database only, so we can increase the variable if needed.

1
  • Let's see the query that was running when it crashes. It may be an accidental "cross join".
    – Rick James
    Commented Jan 27, 2023 at 23:40

1 Answer 1

0

Are you sure this is a MySQL error? The wording sounds like what I see in PHP when you try to fetch a large result set from a query. Perhaps it's not a MySQL Server problem at all.

That said, I have comments on your configuration.

My general rule is that I don't set an option in the config file unless I have a specific reason to do so. Leave most of the options at their default values, by omitting the line from the config file. That way if a given MySQL option is given a better default value in a later version, you will take advantage of it automatically when you upgrade.

Another good rule is to add comments to the config file to describe exactly why you chose the values. It's not sufficient to write, "I don't know why but I saw this on an internet blog that is totally not related to my server's performance, and I thought I'd try this value." You should read and test until you know what the effect of the option is, and you should be able to describe why you chose the specific value you did.

Here are some comments about specific options:

max_connections=1000

Do you need to allow this many connections? Run SHOW GLOBAL STATUS LIKE 'Max_used_connections'; to check if you actually ever use so many.

query_cache_size=350M

Set the query cache size to 0. I saw that you set query_cache_type=0 later, but I recall it still allocates the memory requested. There's no need for this memory if you just disable it later.

tmp_table_size=205M

The default tmp_table_size is 16M in MySQL 5.6. Do you have need to increase it this much? Also since your max_heap_table_size is not set similarly, the tmp table size setting has no effect.

myisam_max_sort_file_size=100G
myisam_sort_buffer_size=379M
key_buffer_size=256M

These three are for MyISAM tables. Since you said you use InnoDB, I would remove these lines. Let the values remain their defaults. You still have some MyISAM tables in the system schema, but they are small. The default values are plenty.

read_rnd_buffer_size=256K

This is equal to the default value for read_rnd_buffer_size anyway, so you might as well remove this line.

sort_buffer_size=1024K

This is 4x the default value for sort_buffer_size. Do you have a reason to increase this? If not, let it be the default.

innodb_additional_mem_pool_size=48M

The default value for innodb_additional_mem_pool_size is 8M. What is your reason to increase this? There's actually no reason to set this, since InnoDB will allocate memory dynamically if it needs some. I set it to 0, or just leave it out of the config file.

innodb_log_buffer_size=16M

The default is 8M. What is your reason for increasing it? If you have none, let it be the default.

innodb_buffer_pool_size=2304M

This may not be high enough, if your database is a lot larger than the buffer pool, you may benefit from allocating more memory to this option. But don't increase it too much if you run out of memory.

join_buffer_size=256K
sort_buffer_size=256K

These last two are the default values anyway.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.