1

I'm new to Mysql, and i'm reading this in a book: "A large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O."

Ok...Perfect.. so i'm going to issue this statement:

set global innodb_log_buffer_size=xxx;
Mysql reply to me with: "ERROR 1238 (HY000): Variable 'innodb_log_buffer_size' is a read only variable"

So, How can i make it bigger in case i have big transaction, and need to increase the innodb_log_buffer_size?

I use mysql 5.7 on Windows.

Thank you.

1
  • Another question: 'innodb_log_buffer_size' is the same of 'innodb_log_file_size'? the word 'buffer' confuse me a lot.
    – andrea
    Commented Feb 19, 2017 at 23:41

1 Answer 1

2

The files are created when you first install MySQL. It is clumsy to change the size after that. In particular, the size cannot be changed dynamically.

How big is the setting now? How many rows with the one transaction be affecting? It may not be worth it.

If you do choose to make a change; see How to safely change MySQL innodb variable 'innodb_log_file_size'? Be sure to read the Comments, too.

Not Dynamic

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_log_file_size says that innodb_log_file_size is not dynamic, meaning that your SET cannot work. You can change the value in my.cnf, but then you have the issues discussed above. A note from the 5.6.8 Changelog:

On startup, MySQL would not start if there was a mismatch between the value of the innodb_log_file_size configuration option and the actual size of the ib_logfile* files that make up the redo log. This behavior required manually removing the redo log files after changing the value of innodb_log_file_size. The fix causes MySQL to write all dirty pages to disk and re-create the redo log files during startup if it detects a size mismatch. (Bug #14596550)

4
  • Thank you for the reply. It is not a real need. I'm just studying, and I read that it would be better to change that value if you have a big transaction. So I tried but I got that error. Second question: 'innodb_log_file_size' is the same as 'innodb_log_buffer_size' ?? there is the word 'buffer' more. And i'm on windows, so i do not have 'my.cnf' file, but 'my.ini' file
    – andrea
    Commented Feb 19, 2017 at 22:57
  • Oops. It seems that I am the one who got confused. Neither setting is "dynamic", though it might be handy in the situation you describe. I think the buffer is shared among all users, so changing it dynamically could be messy.
    – Rick James
    Commented Feb 20, 2017 at 1:03
  • 1
    The STATUS expression of Innodb_log_waits / Innodb_log_writes gives an indication of whether the buffer is not big enough.
    – Rick James
    Commented Feb 20, 2017 at 1:04
  • Even with the default 8MB, the buffer rarely overflows.
    – Rick James
    Commented Feb 20, 2017 at 1:05

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.