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.
Best Answer
While I agree with Rolando's recommendation to change
innodb_flush_method
, I wasn't 100% clear what you meant by: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):
So to check that: