11

This is related to this question. It does help to get better performance for InnoDB tables.

According to MySQL manual, innodb_flush_log_at_trx_commit is a global dynamic variable. Thus, I can change it using SET GLOBAL command and it seems to be working.

mysql> SET GLOBAL innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected

mysql> SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 2     |
+--------------------------------+-------+
1 row in set

But, it did not make the actual MySQL setting changed. When I updated my.cnf and restarted the MySQL server, it did work. So, I cannot change the global variable at run time?

I prefer the default value innodb_flush_log_at_trx_commit=1, but I need to change it to 2 before I run a restore process for a large database to get faster. But when the process done, I want to change the value back to 1. Is it possible to do this at run time?

I don't have access to my.cnf on my shared hosting server.

2 Answers 2

12

While I agree with Rolando's recommendation to change innodb_flush_method, I wasn't 100% clear what you meant by:

it did not make the actual MySQL setting changed

I want to point out the caveat that making a change to the GLOBAL variable affects any new connections, but does not modify the current session (emphasis mine):

The global variable change does not affect the session variable for any client that is currently connected (not even that of the client that issues the SET GLOBAL statement).

So to check that:

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)


mysql> SHOW SESSION VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 2     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> connect;
Connection id:    6
Current database: *** NONE ***

mysql> SHOW SESSION VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 2     |
+--------------------------------+-------+
1 row in set (0.00 sec)
7
  • 2
    This answer makes sense. The documentation ( dev.mysql.com/doc/refman/5.5/en/… ) does not say that the variable at the session level is able to be changed, only the global level. I experienced that many times changing max_connections with SET GLOBAL max_connections = 1000; and when I run SHOW VARIABLES LIKE 'max_connections'; to see the old value would drive mean nuts until I log out and back in. +1 for this viewpoint that is taken-for-granted and often-forgotten. Dec 7, 2012 at 16:51
  • @Rolando me too! also, was glad when I found that I could 'connect;' instead of logging out and back in. Saves time! Dec 7, 2012 at 17:09
  • That concept of running connect is actually new to me in MySQL. I have done that a million times in PostgreSQL and Oracle. I never once thought of MySQL allowing that Dec 7, 2012 at 18:15
  • @DTest, Thanks for your answer. According to my deep test, it did work dynamically. In my localhost, the session variable was changed without running connect ( I got error when issue connect ). With the value 2, importing 2,241,319 records took 27 mins 43 secs, whereas it took about 1 days with the value 1. The setting seems to be working in the current session but it restored the original setting (from my.cnf) after restart.
    – Sithu
    Dec 11, 2012 at 5:02
  • @DerekDowney, Is this just for certain settings like innodb_flush_log_at_trx_commit? Or is it that for all settings, setting the global wouldn't affect the current session?
    – Pacerier
    Apr 9, 2015 at 11:54
7

By setting innodb_flush_log_at_trx_commit, you risk confusion with mysqld/OS interoperability. I say this because the OS is being trusted to perform the flush.

Note the caution in from the MySQL Documentation

Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not. Then the durability of transactions is not guaranteed even with the setting 1, and in the worst case a power outage can even corrupt the InnoDB database. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try using the Unix command hdparm to disable the caching of disk writes in hardware caches, or use some other command specific to the hardware vendor.

What this tells is the following: The OS can lie like a cheating husband. The OS says it will flush to disk and simply does not do it. Therefore, even if you set innodb_flush_log_at_trx_commit, you must divorce the OS flushing to disk from mysqld's flushing to disk.

Try setting innodb_flush_method to O_DIRECT if you haven't already done so. You may see a difference because the flush method differs greatly (See my Mar 04, 2011 post Clarification on MySQL innodb_flush_method variable ).

CAVEAT

As you mentioned, you do not have access to my.cnf. Please contact the SysAdmin at your provider and get innodb_flush_method changed.

UPDATE 2012-12-10 12:45 EDT

I am current running MySQL 5.5.12 on my PC. When I connect and run show variables like 'innodb_flush_method'; I get

mysql> show variables like 'innodb_flush_method';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_flush_method |       |
+---------------------+-------+
1 row in set (0.05 sec)

mysql>

Since it is blank, it just indicates that the default setting is used. Please read my Mar 04, 2011 post for Clarification on MySQL innodb_flush_method variable

3
  • I tested in my localhost first. I could not find innodb_flush_method in my.ini (not my.cnf). Server information - Apache 2.4.1, PHP 5.4.4, MySQL 5.5
    – Sithu
    Dec 9, 2012 at 15:40
  • I noticed that regardless of server version or ini/cnf, the configuration file does not have innodb_flush_method setting and SHOW VARIABLES doesn't show it.
    – Sithu
    Dec 10, 2012 at 3:05
  • Thanks for your UPDATE, I got it too, I just wondered why we can't see its value. Since I could not find it in my.ini or my.cnf and it is not a dynamic variable, I'm not sure how can I configure it.
    – Sithu
    Dec 11, 2012 at 4:56

Your Answer

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

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