All Questions
Tagged with transaction-log innodb
17 questions
3
votes
0
answers
78
views
Are Innodb redo logs written atomically?
When flushing dirty pages from buffer pool to disk pages, it uses double write buffering to avoid half written pages. But redo logs do not have such mechanism. So, how does it prevent redo logs from ...
- 135
3
votes
2
answers
1k
views
Why does MySQL require two or more redo log files for InnoDB?
A single redo log file can also be written in a circular fashion.
In fact, MariaDB has changed its InnoDB redo log to a single file, and its developers claimed that "the log file was ...
- 31
0
votes
3
answers
1k
views
Is there an InnoDB max transaction size and what happens if it is reached?
I would like to know if there is a max transaction size for InnoDB. Probably it is innodb_log_file_size * innodb_log_files_in_group + innodb_log_buffer_size? And this max size would be shared between ...
- 113
1
vote
3
answers
4k
views
What's the exact meaning of `innodb_max_dirty_pages_pct`, and how does it relate to the redo log?
I'm puzzled by the innodb_max_dirty_pages_pct parameter, and its relation to the redo log.
The definition itself is simple:
InnoDB tries to flush data from the buffer pool so that the percentage ...
- 390
0
votes
0
answers
20
views
InnoDB: does `redo log` contain initial value and final value? or just final value?
start transaction;
update users set age = 34 where user_id = 3 // update age from 30 to 34 years old.
....
....
when I am updating the age of this user, InnDB will write redo log.
What's the ...
- 523
1
vote
3
answers
695
views
InnoDB: will uncommitted transaction produce redo log?
Before MySQL commit a transaction, it will write REDO log first, then commit the transaction, that is write ahead log.
start transaction;
update users set uuid = UUID() from user where id = 1
update ...
- 523
2
votes
1
answer
4k
views
Innodb: When bulk inserting or updating - how to disable transactions?
When doing a mass insert like INSERT INTO SELECT FROM the speed benefit of doing that in chunks of 10k records can be enormous, in my tests a query that was running for 5 days took only 2 hours using ...
- 402
3
votes
2
answers
6k
views
MySQL `innodb_log_file_size`: how big is too big?
In many answers, discussions, and comments, the prevailing wisdom seems to be to "set innodb_log_file_size to be 25% of the inndb_buffer_pool_size.
What about fairly large buffer pools? For example, ...
0
votes
1
answer
2k
views
Trying to make sense of the InnoDB log file stats
I've been reading the high performance mysql book by Baron Schwartz and after reading about how important it is to set the log file size correctly, I thought I'd take a look at one of our servers at ...
- 279
2
votes
1
answer
2k
views
Can I disable the redo log for a DELETE query or a whole transaction?
I'm working on an existing database, whose structure I can't change. I need to clear (empty) several InnoDB tables (that I can't TRUNCATE due to references in other tables' FK constraints), but I have ...
- 396
1
vote
1
answer
510
views
Galera cluster: change the size of InnoDB Redo log files online?
We have a 3 node Galera cluster. I need to change the mysqld innodb_log_file_size parameter (see Changing the Number or Size of InnoDB Redo Log Files).
I do this by stopping all 3 nodes and ...
- 2,558
-1
votes
1
answer
723
views
Mysql: How does a rollback operation be performed inside InnoDB
I would like to hear in simple words Please answer me in simple words to the question on the below scenario.
==============
Engine: Innodb
Trx1 : started and a DML is executed at 10:00 AM and rolled ...
- 814
4
votes
2
answers
810
views
Should the Redo log on a production MySQL server be configured to another location?
Concerning the innoDB storage engine, should the innodb_log_group_home_dir option (The directory path to the InnoDB log files) be configured to another disk, other than the default directory, in order ...
- 314
12
votes
5
answers
32k
views
When is it safe to disable InnoDB doublewrite buffering?
MySQL InnoDB allows us to disable doublewrite buffering by setting innodb_doublewrite = 0. Other databases doesn't seem to allow this setting to be tweaked.
How could InnoDB still be able to maintain ...
- 491
4
votes
1
answer
20k
views
Shut Down MySQL during InnoDB rollback after crash
I am puzzled once again.
We had a server crash last night during some huge queries. Upon restart in the morning MySQL went into recovery mode. I needed to shut down the MySQL service and it is ...
3
votes
2
answers
5k
views
MySQL InnoDB log files on different location
I am using MySQL version: 5.5.38
There are following parameters that can control InnoDB log files:
innodb_log_files_in_group - amount of log files in the group, by default 2. Will create 2 files ...
- 213
3
votes
2
answers
2k
views
Too much IO to MySQL InnoDB log files
I am using MySQL version: 5.5.38
I have dedicated HDD to InnoDB log files using innodb-log-group-home-dir option.
atopsar -d 30 shows big load (>50%) to this HDD
The thing is that I have only 75 ...
- 213