Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 7 down vote accepted

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)
share|improve this answer
1  
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. –  RolandoMySQLDBA Dec 7 '12 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! –  Derek Downey Dec 7 '12 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 –  RolandoMySQLDBA Dec 7 '12 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 '12 at 5:02

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

share|improve this answer
    
Thanks for your great answer. I will try it. –  Sithu Dec 7 '12 at 10:20
    
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 '12 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 '12 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 '12 at 4:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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